SpringBoot + MyBatis + Druid连接池配置多数据源

随着并发量的不断增加，显然单个数据库已经承受不了高并发带来的压力。一个项目使用多个数据库（无论是主从复制- - 读写分离还是分布式数据库结构）的重要性变得越来越明显。传统项目中（个人对传统项目的理解就是所有的业务模块都在一个tomcat中完成，多个相同的tomcat集群也可认为是传统项目）整合多数据源有两种方法：分包和AOP。这篇文章对分包进行讲解。

1、pom.xml引入maven依赖

    <dependencies>
        <!-- spring boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency>
        <!-- sqlserver -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.14</version>
        </dependency>
    </dependencies>

2、在application.properties中配置多个数据源以及Druid配置

#数据库1
spring.datasource.jinggong.name=jinggong
spring.datasource.jinggong.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.jinggong.url=jdbc:sqlserver://ip:port;DatabaseName=db1
spring.datasource.jinggong.username=sa
spring.datasource.jinggong.password=1234

#数据库2
spring.datasource.huating.name=huating
spring.datasource.huating.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.huating.url=jdbc:sqlserver://ip:port;DatabaseName=db2
spring.datasource.huating.username=sa
spring.datasource.huating.password=1234

#数据库3
spring.datasource.huiguan.name=huiguan
spring.datasource.huiguan.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.huiguan.url=jdbc:sqlserver://ip:port;DatabaseName=db3
spring.datasource.huiguan.username=sa
spring.datasource.huiguan.password=1234

#mybatis配置
mybatis.config-location=classpath:/mybatis-config.xml
mybatis.mapper-locations=classpath:mapper/*.xml

#region druid配置
#druid监控页面配置
#允许访问的ip
#druid.allow=176.1.7.55
#不允许访问的ip，优先级高于allow
#druid.deny=192.168.0.200
#用户名和密码
druid.loginUsername=hikvision
druid.loginPassword=hikvision
#是否允许重置监控内容
druid.resetEnable=true
druid.stat.mergeSql=true
druid.stat.slowSqlMillis=1000

#数据源通用配置
#为了启用各种插件
#stat：监控统计用 com.alibaba.druid.filter.stat.StatFilter
#log4j：日志用 com.alibaba.druid.filter.logging.Log4jFilter
#wall：防御sql注入 com.alibaba.druid.wall.WallFilter
druid.filters=stat,wall,log4j
#配置初始化连接个数
druid.initialSize=10
#配置最大连接个数
druid.maxActive=20
#配置最小连接个数
druid.minIdle=10
#配置获取连接时的最大等待时间，配置这个默认开启了公平锁，降低性能但保证每个连接都能得到资源
druid.maxWait=500
#配置连接保持空闲而不被驱逐的最小时间
druid.minEvictableIdleTimeMillis=600000
#配置是否启用非公平锁
druid.useUnfairLock=false
#是否启用PSCache，MySQL不建议使用
#druid.poolPreparedStatements=true
#PSCache最大容量
druid.MaxPSCacheSize=50
#检测SQL连接是否可用
druid.validationQuery=true
#单位是秒，检测连接是否有效的超时时间
druid.validationQueryTimeout=1
#申请连接时是否检测连接是否可用，会降低性能
druid.testOnBorrow=true
#归还连接时是否检测连接是否可用，会降低性能
druid.testOnReturn=true
#申请连接时检测，如果空闲的连接已经空闲了timeBetweenEvictionRunsMillis，那么检测一下
druid.testWhileIdle=true
#线程销毁时会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis，关闭物理连接
druid.timeBetweenEvictionRunsMillis=600000
#endregion

3、建立连接数据源的Java配置

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Properties;

//使用@Configuration注解定义配置类
@Configuration
public class DataSourceConfig {

	@Autowired
	private Environment env;
	
	/**
     * 注册Servlet，用于访问druid监控页面
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(
                new StatViewServlet(), "/druid/*");
        servletRegistrationBean.addInitParameter("loginUsername", env.getProperty("druid.loginUsername"));
        servletRegistrationBean.addInitParameter("loginPassword", env.getProperty("druid.loginPassword"));
        servletRegistrationBean.addInitParameter("resetEnable", env.getProperty("druid.resetEnable"));
        return servletRegistrationBean;
    }
    
    /*
     * 注册拦截器，用于druid统计各种请求
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
    
    /**
     * 以通用配置得到DruidDataSource
     *
     * @Param: []
     * @Return: com.alibaba.druid.pool.DruidDataSource
     **/
    private DruidDataSource getDruidDataSource(String name) throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setFilters(env.getProperty("druid.filters")); //设置各种插件
        /*监控的一些配置*/
        Properties properties = new Properties();
        properties.put("druid.stat.mergeSql", env.getProperty("druid.stat.mergeSql")); //是否合并SQL，可以将一些类似的SQL合并统计
        properties.put("druid.stat.slowSqlMillis", env.getProperty("druid.stat.slowSqlMillis")); //设置慢查询到底有多慢
        dataSource.setConnectProperties(properties);
        dataSource.setMinIdle(Integer.parseInt(env.getProperty("druid.minIdle")));
        dataSource.setName(name); //设置在监控时显示的名字，默认比较low
        dataSource.setUseGlobalDataSourceStat(true); //合并多个数据源的公用监控
        dataSource.setInitialSize(Integer.parseInt(env.getProperty("druid.initialSize")));//设置初始化连接池个数
        dataSource.setMaxActive(Integer.parseInt(env.getProperty("druid.maxActive")));//设置最大连接数
        dataSource.setMaxWait(Long.parseLong(env.getProperty("druid.maxWait")));
        dataSource.setUseUnfairLock(Boolean.parseBoolean(env.getProperty("druid.useUnfairLock")));
        dataSource.setMinEvictableIdleTimeMillis(Long.parseLong(env.getProperty("druid.minEvictableIdleTimeMillis")));
        dataSource.setTimeBetweenEvictionRunsMillis(Long.parseLong(env.getProperty("druid.timeBetweenEvictionRunsMillis")));
        dataSource.setValidationQueryTimeout(Integer.parseInt(env.getProperty("druid.validationQueryTimeout")));
        dataSource.setTestOnBorrow(Boolean.parseBoolean(env.getProperty("druid.testOnBorrow")));
        dataSource.setTestOnReturn(Boolean.parseBoolean(env.getProperty("druid.testOnReturn")));
        dataSource.setTestWhileIdle(Boolean.parseBoolean(env.getProperty("druid.testWhileIdle")));
        return dataSource;
    }
    
    /**
     * SQLServer专用配置得到DataSource
     *
     * @Param: [name]
     * @Return: com.alibaba.druid.pool.DruidDataSource
     **/
    private DruidDataSource getSqlServerDataSource(String name) throws SQLException {
        DruidDataSource druidDataSource = getDruidDataSource(name);
    //        druidDataSource.setPoolPreparedStatements(Boolean.parseBoolean(env.getProperty("druid.poolPreparedStatements")));
            druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(Integer.parseInt(env.getProperty("druid.MaxPSCacheSize")));
            if (Boolean.parseBoolean(env.getProperty("druid.validationQuery"))) {
                druidDataSource.setValidationQuery("select 1");
            }
            return druidDataSource;
        }

    /**
     * 得到SqlSessionFactoryBean，不直接返回SqlSessionFactory
     *
     * @Param: [dataSource]
     * @Return: org.mybatis.spring.SqlSessionFactoryBean
     **/
    private SqlSessionFactoryBean getSqlSessionFactoryBean(DataSource dataSource) throws IOException {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        /* 添加mapper扫描路径 */
        PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();
        String packageSearchPath = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + "/mapper/**.xml";
        bean.setMapperLocations(pathMatchingResourcePatternResolver.getResources(packageSearchPath));
        return bean;
    }
    
    /*************************SqlServer数据源***************************/
    @Bean(name = "jinggongDataSource")
    //表示这个是默认数据源
    @Primary
    public DataSource jinggongDataSource() throws SQLException {
    	DruidDataSource dataSource = getSqlServerDataSource(env.getProperty("spring.datasource.jinggong.name"));
        dataSource.setUrl(env.getProperty("spring.datasource.jinggong.url"));
        dataSource.setDriverClassName(env.getProperty("spring.datasource.jinggong.driver-class-name"));
        dataSource.setUsername(env.getProperty("spring.datasource.jinggong.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.jinggong.password"));
        return dataSource;
    }
    
    @Bean(name = "jinggongSqlSessionFactory")
    public SqlSessionFactory jinggongSqlSessionFactory(@Qualifier("jinggongDataSource") DataSource dataSource) throws Exception {
    	SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        return sqlSessionFactoryBean.getObject();
    }
    
    @Bean(name = "jinggongTransactionManager")
    public DataSourceTransactionManager jinggongTransactionManager(@Qualifier("jinggongDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "jinggongSqlSessionTemplate")
    public SqlSessionTemplate jinggongSqlSessionTemplate(@Qualifier("jinggongSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    /*******************************end********************************/


    /*************************华亭SqlServer数据源***************************/
    @Bean(name = "huatingDataSource")
    public DataSource huatingDataSource() throws SQLException {
        DruidDataSource dataSource = getSqlServerDataSource(env.getProperty("spring.datasource.huating.name"));
        dataSource.setUrl(env.getProperty("spring.datasource.huating.url"));
        dataSource.setDriverClassName(env.getProperty("spring.datasource.huating.driver-class-name"));
        dataSource.setUsername(env.getProperty("spring.datasource.huating.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.huating.password"));
        return dataSource;
    }

    @Bean(name = "huatingSqlSessionFactory")
    public SqlSessionFactory huatingSqlSessionFactory(@Qualifier("huatingDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "huatingTransactionManager")
    public DataSourceTransactionManager huatingTransactionManager(@Qualifier("huatingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "huatingSqlSessionTemplate")
    public SqlSessionTemplate huatingSqlSessionTemplate(@Qualifier("huatingSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    /*******************************end********************************/

    /*************************汇冠SqlServer数据源***************************/
    @Bean(name = "huiguanDataSource")
    public DataSource huiguanDataSource() throws SQLException {
        DruidDataSource dataSource = getSqlServerDataSource(env.getProperty("spring.datasource.huiguan.name"));
        dataSource.setUrl(env.getProperty("spring.datasource.huiguan.url"));
        dataSource.setDriverClassName(env.getProperty("spring.datasource.huiguan.driver-class-name"));
        dataSource.setUsername(env.getProperty("spring.datasource.huiguan.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.huiguan.password"));
        return dataSource;
    }

    @Bean(name = "huiguanSqlSessionFactory")
    public SqlSessionFactory huiguanSqlSessionFactory(@Qualifier("huiguanDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "huiguanTransactionManager")
    public DataSourceTransactionManager huiguanTransactionManager(@Qualifier("huiguanDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "huiguanSqlSessionTemplate")
    public SqlSessionTemplate huiguanSqlSessionTemplate(@Qualifier("huiguanSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    /*******************************end********************************/

}

一定要对其中一个数据源使用@Primary注解，标记为默认数据源，它的作用是当一个接口有多个不同实现时，注入@Primary标记的Bean。

4、封装SqlSessionTemplate

这一步骤不是必须的操作，建议将SqlSessionTemplate的方法封装成一个Repository，对其它类提供操作数据库的能力，代码样例如下：

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

@Repository("huiguanDaoSupport")
public class HuiguanDaoSupport {

	@Resource(name = "huiguanSqlSessionTemplate")
    private SqlSessionTemplate huiguanSqlSessionTemplate;


	/**
	* @Author yaochangjian
	* @Description 获取数据
	* @Date 2019/4/29 11:41
	* @Param [str, obj]
	* @return java.lang.Object
	**/
	public Object findForList(String statement, Object obj) throws Exception{
		return huiguanSqlSessionTemplate.selectList(statement, obj);
	}

	/**
	 * @Author yaochangjian
	 * @Description 获取数据
	 * @Date 2019/5/8 19:51
	 * @Param [statement]
	 * @return java.lang.Object
	 **/
	public Object findForList(String statement) throws Exception{
		return huiguanSqlSessionTemplate.selectList(statement);
	}

	/**
	* @Author yaochangjian
	* @Description 获取数据的数量
	* @Date 2019/4/29 11:43
	* @Param [str, obj]
	* @return int
	**/
	public int findForCount(String statement, Object obj) throws Exception{
		return huiguanSqlSessionTemplate.selectOne(statement, obj);
	}
	
	
	/**
	* @Author yaochangjian
	* @Description 新增数据
	* @Date 2019/4/29 11:44
	* @Param [str, obj]
	* @return int
	**/
	public int save(String statement, Object obj) throws Exception{
		return huiguanSqlSessionTemplate.insert(statement, obj);
	}
	
	
	/**
	* @Author yaochangjian
	* @Description 更新数据
	* @Date 2019/4/29 11:44
	* @Param [str, obj]
	* @return int
	**/
	public int update(String statement,Object obj) throws Exception{
		return huiguanSqlSessionTemplate.update(statement, obj);
	}
}

针对一个数据源建立一个Repository，其它两个同理。