目前业界操作数据库的框架一般是Mybatis,但在很多业务场景下,我们需要在一个工程里配置多个数据源来实现业务逻辑。在SpringBoot中也可以实现多数据源并配合Mybatis框架编写xml文件来执行SQL。在SpringBoot中,配置多数据源的方式十分便捷,

下面开始上代码:

  • pom.xml文件中需要添加一些依赖
  • <!-- Spring Boot Mybatis 依赖 -->
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.2.0</version>
    </dependency>
    
    <!-- MySQL 连接驱动依赖 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.39</version>
    </dependency>
    
    <!-- Druid 数据连接池依赖 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.18</version>
    </dependency>
  • application.properties 配置两个数据源配置
  • # master 数据源配置
    master:
      datasource:
        url: jdbc:sqlserver://localhost:1433;DatabaseName=RYAccountsDB
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        username: sa
        password: sa!@123
        initial-size: 1
        min-idle: 1
        max-active: 20
        test-on-borrow: true
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-While-Idle: true
        test-on-return: false
        pool-prepared-statements: false
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat,wall,log4j,config
    # second 数据源配置
    second:
      datasource:
        url: jdbc:mysql://localhost:33306/game_score_log?serverTimezone=GMT&useUnicode=true&characterEncoding=utf8
        username: root
        password: 1q2w#E4r
        driver-class-name: com.mysql.cj.jdbc.Driver
        max-idle: 10
        max-wait: 10000
        min-idle: 5
        initial-size: 5
    
    #mybatis:
    #  type-aliases-package: com.cjrh.game_api.dao
    #  mapper-locations: classpath*:mapper/*.xml
    server:
      port: 7000
  • 数据源配置
  • 多数据源配置的时候注意,必须要有一个主数据源,即 MasterDataSourceConfig 配置
    
    @Primary 标志这个 Bean 如果在多个同类 Bean 候选时,该 Bean 优先被考虑。「多数据源配置的时候注意,必须要有一个主数据源,用 @Primary 标志该 Bean
    @MapperScan 扫描 Mapper 接口并容器管理,包路径精确到 master,为了和下面 cluster 数据源做到精确区分
    @Value 获取全局配置文件 application.properties 的 kv 配置,并自动装配sqlSessionFactoryRef 表示定义了 key ,表示一个唯一 SqlSessionFactory 实例
    
    作者:ChinaXieShuai
    链接:https://www.jianshu.com/p/735852145580
    来源:简书
    简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。

    MasterDataSourceConfig的代码:

  • package com.cjrh.game_api.jdbc;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    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;
    
    
    /**
     * @program: game_api
     * @description:
     * @author: Dading
     * @create: 2019-04-12 14:14
     * @version: 1.0
     **/
    @Configuration
    // 扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
    public class MasterDataSourceConfig {
    
    
        // 精确到 master 目录,以便跟其他数据源隔离
        static final String PACKAGE = "com.cjrh.game_api.dao.master";
        static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
    
        @Value("${master.datasource.url}")
        private String url;
    
        @Value("${master.datasource.username}")
        private String user;
    
        @Value("${master.datasource.password}")
        private String password;
    
        @Value("${master.datasource.driver-class-name}")
        private String driverClass;
    
        @Bean(name = "masterDataSource")
        @Primary
        public DataSource masterDataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            return dataSource;
        }
    
        @Bean(name = "masterTransactionManager")
        @Primary
        public DataSourceTransactionManager masterTransactionManager() {
            return new DataSourceTransactionManager(masterDataSource());
        }
    
        @Bean(name = "masterSqlSessionFactory")
        @Primary
        public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(masterDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    
    }

    第二个数据源SecondDataSourceConfig的配置如下:

  • package com.cjrh.game_api.jdbc;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    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;
    
    /**
     * @program: game_api
     * @description:
     * @author: Dading
     * @create: 2019-04-12 14:14
     * @version: 1.0
     **/
    @Configuration
    // 扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
    public class SecondDataSourceConfig {
    
        // 精确到 cluster 目录,以便跟其他数据源隔离
        static final String PACKAGE = "com.cjrh.game_api.dao.second";
        static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";
    
        @Value("${second.datasource.url}")
        private String url;
    
        @Value("${second.datasource.username}")
        private String user;
    
        @Value("${second.datasource.password}")
        private String password;
    
        @Value("${second.datasource.driver-class-name}")
        private String driverClass;
    
        @Bean(name = "secondDataSource")
        public DataSource clusterDataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            return dataSource;
        }
    
        @Bean(name = "secondTransactionManager")
        public DataSourceTransactionManager clusterTransactionManager() {
            return new DataSourceTransactionManager(clusterDataSource());
        }
    
        @Bean(name = "secondSqlSessionFactory")
        public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(clusterDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

    项目目录如下: