druid连接池 + myBatis 多数据源管理

        
            mysql
            mysql-connector-java
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.1.3
        
        
            org.mybatis.generator
            mybatis-generator-core
            1.3.2
        
        
            com.alibaba
            druid
            1.2.3
        
        
            com.alibaba
            druid-spring-boot-starter
            1.2.1
        
        
 			org.springframework.boot
 			spring-boot-configuration-processor
 			true
		

在项目中创建mapper文件夹

在资源路径classpath下创建mapper文件夹，并创建mapper文件

添加mapper 接口内容

package cn.edu.bistu.cs.example.mapper;

import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
@Repository
public interface ExampleMapper {

    List> getExampleData();

}

添加mapper xml内容
需要注意：此处的namespace需要指向项目中的Mapper接口文件

    
        select * from your_table_name;
    

将需要管理的数据源配置到配置文件中

#数据库配置
spring.datasource.data1.url=jdbc:mysql://localhost:3306/databasename1?serverTimezone=GMT%2B8
spring.datasource.data1.username=root
spring.datasource.data1.password=123456
spring.datasource.data1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.data2.url=jdbc:mysql://localhost:3307/databasename2?serverTimezone=GMT%2B8
spring.datasource.data2.username=root
spring.datasource.data2.password=123456
spring.datasource.data2.driver-class-name=com.mysql.cj.jdbc.Driver

#mybatis配置mapper_locations
mybatis.type-aliases-package=cn.edu.bistu.cs.example.poi.model
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*.xml

#连接池配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=50
spring.datasource.druid.max-wait=60000
spring.datasource.druid.connection-error-retry-attempts=10
spring.datasource.druid.break-after-acquire-failure=true
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.stat-view-servlet.login-username=root
spring.datasource.druid.stat-view-servlet.login-password=123456
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.stat-view-servlet.enabled=true

需要管理几个数据源，需要创建几个管理类，此处以两个为例

package cn.edu.bistu.cs.example.druid;

import com.alibaba.druid.pool.DruidDataSource;
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.beans.factory.annotation.Value;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@ConfigurationProperties(prefix = "spring.datasource.druid")
//此处配置mybatis的mapper接口位置，需要指向项目中mapper接口的文件
@MapperScan(basePackages = "cn.edu.bistu.cs.example.mapper", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DruidDataSource1Config {
    //mybatis 配置文件路径，xml文件路径
    @Value("${mybatis.mapper-locations}")
    private String mapper_locations;

    private int maxActive;
    private int initialSize;
    private int minIdle;
    private int maxWait;
    private int connectionErrorRetryAttempts;
    private boolean breakAfterAcquireFailure;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setConnectionErrorRetryAttempts(int connectionErrorRetryAttempts) {
        this.connectionErrorRetryAttempts = connectionErrorRetryAttempts;
    }

    public void setBreakAfterAcquireFailure(boolean breakAfterAcquireFailure) {
        this.breakAfterAcquireFailure = breakAfterAcquireFailure;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testonBorrow = testOnBorrow;
    }

    @Bean
    //ConfigurationProperties读取并且设置我们在application.properties配置的内容.
    @ConfigurationProperties(prefix = "spring.datasource.data1")
    //@Primary这个注解用来标识当存在多个相同的类型的bean时,优先选用哪个bean注入,
    // 需要注意的是,配置多数据源的时候,必须有一个且只能有一个@Primary注解
    @Primary
    public DataSource db1DataSource() {
        //之前使用的DataSourceBuilder.create().build()
        //导致spring.datasource.druid.*配置的属性都没有生效
        DruidDataSource db1DataSource = new DruidDataSource();
        db1DataSource.setMaxActive(maxActive);
        db1DataSource.setInitialSize(initialSize);
        db1DataSource.setMinIdle(minIdle);
        db1DataSource.setMaxWait(maxWait);
        db1DataSource.setConnectionErrorRetryAttempts(connectionErrorRetryAttempts);
        db1DataSource.setBreakAfterAcquireFailure(breakAfterAcquireFailure);
        db1DataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        db1DataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        db1DataSource.setValidationQuery(validationQuery);
        db1DataSource.setTestWhileIdle(testWhileIdle);
        db1DataSource.setTestOnBorrow(testOnBorrow);
        return db1DataSource;
    }

    @Bean
    @Primary
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_locations));
        return bean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package cn.edu.bistu.cs.example.druid;

import com.alibaba.druid.pool.DruidDataSource;
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.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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
@ConfigurationProperties(prefix = "spring.datasource.druid")
@MapperScan(basePackages = "cn.edu.bistu.cs.example.mapper", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DruidDataSource2Config {
    //mybatis 配置文件路径
    @Value("${mybatis.mapper-locations}")
    private String mapper_locations;

    private int maxActive;
    private int initialSize;
    private int minIdle;
    private int maxWait;
    private int connectionErrorRetryAttempts;
    private boolean breakAfterAcquireFailure;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setConnectionErrorRetryAttempts(int connectionErrorRetryAttempts) {
        this.connectionErrorRetryAttempts = connectionErrorRetryAttempts;
    }

    public void setBreakAfterAcquireFailure(boolean breakAfterAcquireFailure) {
        this.breakAfterAcquireFailure = breakAfterAcquireFailure;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testonBorrow = testOnBorrow;
    }


    @Bean
    //ConfigurationProperties读取并且设置我们在application.properties配置的内容.
    @ConfigurationProperties(prefix = "spring.datasource.data2")
    public DataSource db2DataSource() {
        //之前使用的DataSourceBuilder.create().build()
        //导致spring.datasource.druid.*配置的属性都没有生效
        DruidDataSource db2DataSource = new DruidDataSource();
        db2DataSource.setMaxActive(maxActive);
        db2DataSource.setInitialSize(initialSize);
        db2DataSource.setMinIdle(minIdle);
        db2DataSource.setMaxWait(maxWait);
        db2DataSource.setConnectionErrorRetryAttempts(connectionErrorRetryAttempts);
        db2DataSource.setBreakAfterAcquireFailure(breakAfterAcquireFailure);
        db2DataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        db2DataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        db2DataSource.setValidationQuery(validationQuery);
        db2DataSource.setTestWhileIdle(testWhileIdle);
        db2DataSource.setTestOnBorrow(testOnBorrow);
        return db2DataSource;
    }

    @Bean
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_locations));
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}