springboot+mybatis+mysql配置多数据源(连接数据库)

一时失言乱红尘 2023-02-28 12:55 18阅读 0赞

因为项目需要，需要配置三个mysql数据源，记录项目中使用springboot+mybatis+mysql配置多数据源的配置过程。

### 文章目录 ###

*   *  一、application.properties配置
     *  二、config配置
     *  三、将mapper和xml文件分包放置

## 一、application.properties配置 ##

spring.datasource.first.url = jdbc:mysql://192.168.0.1:3306/test1?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&useSSL=false
    spring.datasource.first.username = root
    spring.datasource.first.password = 123456
    spring.datasource.first.driverClassName = com.mysql.jdbc.Driver
    
    spring.datasource.second.url = jdbc:mysql://192.168.0.2:3306/test1?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&useSSL=false
    spring.datasource.second.username = root
    spring.datasource.second.password = 123456
    spring.datasource.second.driverClassName = com.mysql.jdbc.Driver
    
    #获取连接时验证
    spring.datasource.first.test-on-borrow=true
    #验证连接的有效性
    spring.datasource.first.test-while-idle=true
    #空闲连接回收的时间间隔，和test-while-idle一起使用
    spring.datasource.first.time-between-eviction-runs-millis=300000
    #连接池空闲连接的有效时间
    spring.datasource.first.min-evictable-idle-time-millis=1800000
    spring.datasource.first.test-on-return=true
    spring.datasource.first.validation-query=SELECT 1
    
    #获取连接时验证
    spring.datasource.second.test-on-borrow=true
    #验证连接的有效性
    spring.datasource.second.test-while-idle=true
    #空闲连接回收的时间间隔，和test-while-idle一起使用
    spring.datasource.second.time-between-eviction-runs-millis=300000
    #连接池空闲连接的有效时间
    spring.datasource.second.min-evictable-idle-time-millis=1800000
    spring.datasource.second.test-on-return=true
    spring.datasource.second.validation-query=SELECT 1

## 二、config配置 ##

每个数据源都需要一个配置类。同时dao包和mapping包下需要分别再分成first包和second包存放对应的Mapper文件和xml文件。

**1、FirstDataSourceConfig**

@Configuration
    @MapperScan(basePackages = "com.test.dao.first", sqlSessionFactoryRef = "firstSqlSessionFactory")
    public class FirstDataSourceConfig{ 
        
        @Primary
        @Bean(name = "firstDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.first")
        public DataSource firstDataSource(){ 
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "firstSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("firstDataSource") Datasource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources("classpath*:mapping/first/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "firstSqlSessionTemplate")
        public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory){ 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

**2、SecondDataSourceConfig**

@Configuration
    @MapperScan(basePackages = "com.test.dao.second", sqlSessionFactoryRef = "secondSqlSessionFactory")
    public class SecondDataSourceConfig{ 
        @Primary
        @Bean(name = "secondDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.second")
        public DataSource secondDataSource(){ 
            return DataSourceBuilder.create().build();
        }
        
        @Bean(name = "secondSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("secondDataSource") Datasource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources("classpath*:mapping/second/*.xml"));
            return bean.getObject();
        }
        
        @Bean(name = "secondSqlSessionTemplate")
        public SqlSessionTemplate 
        secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory){ 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

## 三、将mapper和xml文件分包放置 ##

将first和second数据源相关的maper和xml文件分别放在dao包和mapping包下的first和second目录中。这个时候就已经可以连接数据库测试了，可以从不同数据源的mapper中操作数据。