SpringBoot整合MyBatis和Druid实现多数据源

在实际开发中，经常会遇到一个应用中可能需要访问多个数据库的情况，典型的使用场景如下：

1. 业务复杂，数据量大，需要使用多个数据库
2. 读写分离，提升系统性能

1. 数据库准备

新建两个数据库dynamic-master和dynamic-slave，在master库中新建customer表，slave库中新建orders表

CREATE TABLE `customer`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `customer_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客户名称',
  `sex` tinyint(1) NOT NULL DEFAULT 0 COMMENT '性别 0:为止;1:男;2:女',
  `age` tinyint(2) NOT NULL DEFAULT 0 COMMENT '年龄',
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '删除标识 0:未删除;1:已删除',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '客户表' ROW_FORMAT = Dynamic;
CREATE TABLE `orders`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_code` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单编码',
  `order_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '订单状态 0:未支付;1:支付中;2:已支付',
  `product_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `customer_id` bigint(20) NULL DEFAULT NULL COMMENT '客户ID',
  `deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '删除标识 0:未删除;1:已删除',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;

2. 引入核心依赖

<dependency>
   <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-webartifactId>
dependency>
<dependency>
    <groupId>org.mybatis.spring.bootgroupId>
    <artifactId>mybatis-spring-boot-starterartifactId>
    <version>2.2.2version>
dependency>
<dependency>
    <groupId>mysqlgroupId>
    <artifactId>mysql-connector-javaartifactId>
    <scope>runtimescope>
dependency>
<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>druid-spring-boot-starterartifactId>
    <version>1.2.9version>
dependency>

3. application.yml配置

server:
  port: 8126
spring:
  application:
    name: springboot-dynamic-mybatis
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    master:
      url: jdbc:mysql://localhost:3306/dynamic-master?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      url: jdbc:mysql://localhost:3306/dynamic-slave?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
      initial-size: 5 #初始连接数
      max-active: 20 #最大连接池
      max-wait: 6000 #最大等待时长，毫秒
      min-idle: 1 #最小连接池
      test-while-idle: true #连接时检测
      test-on-borrow: false #申请连接时执行检测
      test-on-return: false #规划连接时执行检测
      validation-query: SELECT 1 FROM DUAL #连接检测
      time-between-eviction-runs-millis: 60000 #检测间隔时间，毫秒
      min-evictable-idle-time-millis: 300000 #连接池最小生存时间，毫秒
      max-evictable-idle-time-millis: 900000 #连接池最大生存时间，毫秒
mybatis:
  mapper-locations: classpath*:/mapper/**/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

4. 数据库配置文件

新建两个配置文件，分别配置master和slave数据库

@Configuration
@MapperScan(basePackages = {"com.xlhj.boot.dynamic.mybatis.mapper.master"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource dataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "masterTransactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource());
    }

    @Primary
    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
        return factoryBean.getObject();
    }

    @Primary
    @Bean(name = "masterSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

@Configuration
@MapperScan(basePackages = {"com.xlhj.boot.dynamic.mybatis.mapper.slave"}, sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {

    @Bean(name = "slaveDataSource")
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource dataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/slave/*.xml"));
        return factoryBean.getObject();
    }

    @Primary
    @Bean(name = "slaveSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

后记

以上方案虽然实现了多数据，但每多一个数据源就需要新增配置文件，还需要分别新建mapper文件，spring-jdbc提供了AbstractRoutingDataSource抽象类，可以实现动态访问数据库。有需要了解这方面知识的小伙伴可以参阅SpringBoot集成AbstractRoutingDataSource实现动态切换多数据源