接下来为大家介绍如何使用 MyBatis 配置多数据源，并且使用 Druid 进行数据监控。
导入相关包
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.14</version>
</dependency>
配置文件
首先我们需要配置两个不同的数据源：
# mybatis 配置
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.type-aliases-package=com.example.druidmybatis.model
# 数据源1配置
spring.datasource.druid.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.one.url =
jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.druid.one.username = root
spring.datasource.druid.one.password = jacky316
# 数据源2配置
spring.datasource.druid.two.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.two.url =
jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.druid.two.username = root
spring.datasource.druid.two.password = jacky316
# 监控账户密码配置
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
# 过滤器设置
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000
# 数据源1的初始化连接数、最小和最大连接数、超时时间
spring.datasource.druid.one.initial-size=3
spring.datasource.druid.one.min-idle=3
spring.datasource.druid.one.max-active=10
spring.datasource.druid.one.max-wait=60000
# 数据源2的初始化连接数、最小和最大连接数、超时时间
spring.datasource.druid.two.initial-size=6
spring.datasource.druid.two.min-idle=6
spring.datasource.druid.two.max-active=20
spring.datasource.druid.two.max-wait=120000
# 配置下面参数用于启动监控页面，考虑安全问题，默认是关闭的，按需开启
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.web-stat-filter.enabled=true
编写model类和相关Mapper类
package com.example.druidmybatis.model;
import java.io.Serializable;
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private long userId;
private String name;
private int age;
public User(String name, int age){
this.name = name;
this.age = age;
}
public User(long userId, String name, int age) {
this.userId = userId;
this.name = name;
this.age = age;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", name='" + name + '\'' +
", age=" + age +
'}';
}
public long getUserId() {
return userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
package com.example.druidmybatis.mapper.test1;
import com.example.druidmybatis.model.User;
import java.util.List;
public interface User1Mapper {
List<User> getAll();
User getOne(Long id);
void insert(User user);
void update(User user);
void delete(Long id);
}
package com.example.druidmybatis.mapper.test2;
import com.example.druidmybatis.model.User;
import java.util.List;
public interface User2Mapper {
List<User> getAll();
User getOne(Long id);
void insert(User user);
void update(User user);
void delete(Long id);
}
配mybatis的配置文件和不同数据源的Sql操作的xml文件
mybatis的配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias alias="Integer" type="java.lang.Integer" />
<typeAlias alias="Long" type="java.lang.Long" />
<typeAlias alias="HashMap" type="java.util.HashMap" />
<typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
<typeAlias alias="ArrayList" type="java.util.ArrayList" />
<typeAlias alias="LinkedList" type="java.util.LinkedList" />
</typeAliases>
</configuration>
数据源1的Sql操作xml：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.druidmybatis.mapper.test1.User1Mapper" >
<resultMap id="BaseResultMap" type="com.example.druidmybatis.model.User" >
<id column="userId" property="userId" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
userId, name, age
</sql>
<sql id="Base_Where_List">
<if test="name != null and name != ''">
and name = #{userName}
</if>
</sql>
<select id="getAll" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM user_info
</select>
<select id="getOne" parameterType="Long" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM user_info
WHERE userId = #{id}
</select>
<insert id="insert" keyProperty="userId" useGeneratedKeys="true" parameterType="com.example.druidmybatis.model.User" >
INSERT INTO
user_info
(name,age)
VALUES
(#{name}, #{age})
</insert>
<update id="update" parameterType="com.example.druidmybatis.model.User" >
UPDATE
user_info
SET
<if test="name != null">name = #{name},</if>
name = #{name}
WHERE
userId = #{userId}
</update>
<delete id="delete" parameterType="Long" >
DELETE FROM
user_info
WHERE
userId =#{userId}
</delete>
</mapper>
数据源2的Sql操作xml：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.druidmybatis.mapper.test2.User2Mapper" >
<resultMap id="BaseResultMap" type="com.example.druidmybatis.model.User" >
<id column="userId" property="userId" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
userId, name, age
</sql>
<sql id="Base_Where_List">
<if test="name != null and name != ''">
and name = #{userName}
</if>
</sql>
<select id="getAll" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM user_info
</select>
<select id="getOne" parameterType="Long" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM user_info
WHERE userId = #{id}
</select>
<insert id="insert" keyProperty="userId" useGeneratedKeys="true" parameterType="com.example.druidmybatis.model.User" >
INSERT INTO
user_info
(name,age)
VALUES
(#{name}, #{age})
</insert>
<update id="update" parameterType="com.example.druidmybatis.model.User" >
UPDATE
user_info
SET
<if test="name != null">name = #{name},</if>
name = #{name}
WHERE
userId = #{userId}
</update>
<delete id="delete" parameterType="Long" >
DELETE FROM
user_info
WHERE
userId =#{userId}
</delete>
</mapper>
注入多数据源
定义一个 MultiDataSourceConfig 类，对两个不同的数据源进行加载：
package com.example.druidmybatis.datasource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
@Configuration
public class MultiDataSourceConfig {
@Primary
@Bean(name = "oneDataSource",initMethod = "init",destroyMethod = "close")
@ConfigurationProperties("spring.datasource.druid.one")
public DataSource dataSourceOne(){
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "twoDataSource",initMethod = "init",destroyMethod = "close")
@ConfigurationProperties("spring.datasource.druid.two")
public DataSource dataSourceTwo(){
return DruidDataSourceBuilder.create().build();
}
}
必须指明一个为默认的主数据源，使用注解：@Primary。
我们以第一个数据源为例，讲解如何将数据源注入到对应的 mapper 中。
package com.example.druidmybatis.datasource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
// 4.最后将上面创建的SqlSessionTemplate注入到对应的 Mapper 包路径下，这样这个包下面的 Mapper 都会使用第一个数据源来进行数据库操作
@Configuration
@MapperScan(basePackages = "com.example.druidmybatis.mapper.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {
// 1.首先创建一个SqlSessionFactory，将第一个数据源注入到其中，并且要标明是主数据源
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
return bean.getObject();
}
// 2.将数据源添加到事务中,并且标明是主数据源
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("oneDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
// 3.将上面创建的SqlSessionFactory注入，创建在 Mapper 中需要使用的SqlSessionTemplate,并且标明是主数据源
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
package com.example.druidmybatis.datasource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.example.druidmybatis.mapper.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("twoDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("twoDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
从上面的步骤可以总结出来，创建多数据源的过程就是：首先创建DataSource，注入到SqlSessionFactory中，再创建事务，将SqlSessionFactory注入到创建的SqlSessionTemplate中，最后将SqlSessionTemplate注入到对应的 Mapper 包路径下。其中需要指定分库的 Mapper 包路径。
注意在多数据源的情况下，不需要在启动类添加：@MapperScan("com.xxx.mapper")的注解。
创建UserController类来对其进行常规操作
package com.example.druidmybatis.Controller;
import com.example.druidmybatis.mapper.test1.User1Mapper;
import com.example.druidmybatis.mapper.test2.User2Mapper;
import com.example.druidmybatis.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class UserController {
@Autowired
private User1Mapper user1Mapper;
@Autowired
private User2Mapper user2Mapper;
@RequestMapping("/getUsers")
public List<User> getUsers() {
List<User> users=user1Mapper.getAll();
users.addAll(user2Mapper.getAll());
return users;
}
@RequestMapping("/getUser")
public User getUser(Long id) {
User user=user2Mapper.getOne(id);
return user;
}
@RequestMapping("/add")
public void save(User user) {
user2Mapper.insert(user);
}
@RequestMapping(value="update")
public void update(User user) {
user2Mapper.update(user);
}
@RequestMapping(value="/delete/{id}")
public void delete(@PathVariable("id") Long id) {
user1Mapper.delete(id);
}
}
测试使用
以上所有的配置内容都配置完成后，启动项目访问：访问地址http://localhost:8080/druid，点击数据源查看数据库连接信息。
然后我们执行http://localhost:8080/getUsers，来查看数据源1和数据源2的Sql执行情况，点击“SQL监控”可以查看，如下:
总结
Druid 是一款非常优秀的数据库连接池开源软件，使用 Druid 提供的druid-spring-boot-starter可以非常简单的对 Druid 进行集成。Druid 提供了很多预置的功能，非常方便对 SQL 进行监控、分析。在集成多数据源的时候，我们配置不同数据源，采用一层一层注入的形式，最终构建出对应的SqlSessionTemplate，并将其注入到对应的 Mapper 包中来使用。利用 Druid 属性继承的功能，可以简化多数据源的配置，MyBatis+Druid 的多数据源解决方案很简单高效。