多数据源配置
背景
在日常业务开发中，偶尔还是会遇到多数据源配置需求的项目，比如在业务系统迁移时，需要将SQLServer数据库中的数据导入MySQL数据库中。再比如在我经过的项目中，有静态化网站数据库和搜索引擎数据库两个数据源。
如何配置
在数据访问中包含数据源、事务管理器、会话管理工厂、会话模板、mybatis配置文件、mybatis数据库脚本映射文件(SQL Map)。其他的常用配置，比如驼峰命名映射配置(mybatis.configuration.map-underscore-to-camel-case)、类型别名配置（mybatis.type-aliases-package）、类型处理器配置(mybatis.type-handlers-package)等，不管是单数据源场景还是多数据源场景都建议统一在mybatis.xml配置文件中进行集中配置管理。
- 在基类中配置数据源
@Slf4j
public class BaseDataSourceConfig {
@Value("${spring.datasource.driverClassName}")
protected String driverClass;
@Value("${spring.datasource.druid.filters}")
protected String filters;
@Value("${spring.datasource.druid.initialSize}")
protected Integer initialSize;
@Value("${spring.datasource.druid.minIdle}")
protected Integer minIdle;
@Value("${spring.datasource.druid.maxActive}")
protected Integer maxActive;
@Value("${spring.datasource.druid.maxWait}")
protected Integer maxWait;
@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
protected Integer timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
protected Integer minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validationQuery}")
protected String validationQuery;
@Value("${spring.datasource.druid.testWhileIdle}")
protected Boolean testWhileIdle;
@Value("${spring.datasource.druid.testOnBorrow}")
protected Boolean testOnBorrow;
@Value("${spring.datasource.druid.testOnReturn}")
protected Boolean testOnReturn;
@Value("${spring.datasource.druid.poolPreparedStatements}")
protected Boolean poolPreparedStatements;
@Value("${spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
protected Integer maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.druid.connectionProperties}")
protected Properties connectionProperties;
/**
* 初始化
*
* @return DataSource
*/
protected DruidDataSource initDataSource(String url, String userName, String password) {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(password);
dataSource.setMaxActive(maxActive);
dataSource.setMinIdle(minIdle);
dataSource.setInitialSize(initialSize);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setValidationQuery(validationQuery);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
dataSource.setConnectProperties(connectionProperties);
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
log.error("BaseDataSourceConfig error", e);
}
return dataSource;
}
/**
* 初始化SessionFactory
*
* @param dataSource 数据源
* @param configLocation 配置文件地址
* @param mapperLocations mapper地址
* @return SqlSessionFactory
* @throws Exception 异常
*/
public SqlSessionFactory initSessionFactory(DataSource dataSource, String configLocation, String mapperLocations)
throws Exception {
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(resolver.getResources(mapperLocations));
sessionFactory.setVfs(SpringBootVFS.class);
sessionFactory.setConfigLocation(resolver.getResource(configLocation));
return sessionFactory.getObject();
}
- 扩展基类，创建第一个数据源
@Configuration
@MapperScan(basePackages = OwDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "owSqlSessionFactory")
public class OwDataSourceConfig extends BaseDataSourceConfig {
static final String PACKAGE = "com.xxx.ow.dao";
@Value("${ow.mybatis.mapper-locations}")
private String mapperLocations;
@Value("${ow.mybatis.config-location}")
private String configLocation;
@Value("${ow.datasource.url}")
private String url;
@Value("${ow.datasource.username}")
private String userName;
@Value("${ow.datasource.password}")
private String password;
@Bean(name = "owDataSource")
@Primary
public DataSource owDataSource() {
return initDataSource(url, userName, password);
}
@Bean(name = "owTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(owDataSource());
}
@Bean(name = "owSqlSessionFactory")
@Primary
public SqlSessionFactory owSqlSessionFactory(@Qualifier("owDataSource") DataSource dataSource)
throws Exception {
return initSessionFactory(dataSource, configLocation, mapperLocations);
}
@Primary
@Bean(name = "owSqlSessionTemplate")
public SqlSessionTemplate owSqlSessionTemplate(
@Qualifier("owSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
- 扩展基类，创建第二个数据源
@Configuration
@MapperScan(basePackages = SearchDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "searchSqlSessionFactory")
public class SearchDataSourceConfig extends BaseDataSourceConfig {
static final String PACKAGE = "com.xxx.ow.search.dao";
@Value("${search.mybatis.mapper-locations}")
private String mapperLocations;
@Value("${search.mybatis.config-location}")
private String configLocation;
@Value("${search.datasource.url}")
private String url;
@Value("${search.datasource.username}")
private String userName;
@Value("${search.datasource.password}")
private String password;
@Bean(name = "searchDataSource")
public DataSource searchDataSource() {
return initDataSource(url, userName, password);
}
@Bean(name = "searchTransactionManager")
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(searchDataSource());
}
@Bean(name = "searchSqlSessionFactory")
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("searchDataSource") DataSource dataSource)
throws Exception {
return initSessionFactory(dataSource, configLocation, mapperLocations, typeAliasesPackage, typeHandlersPackage);
}
@Bean(name = "searchSqlSessionTemplate")
public SqlSessionTemplate searchSqlSessionTemplate(
@Qualifier("searchSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
- 在application.properties中增加配置
配置每一个数据源的config-location和mapper-locations。
ow.mybatis.config-location=mybatis.xml
ow.mybatis.mapper-locations=classpath*:/com/xxx/ow/mapper/**/*.xml
search.mybatis.config-location=mybatis-search.xml
search.mybatis.mapper-locations=classpath*:/com/xxx/ow/search/mapper/**/*.xml
在mybatis.xml配置文件中配置更多选项，比如驼峰命名映射配置(mybatis.configuration.map-underscore-to-camel-case)、类型别名配置（mybatis.type-aliases-package）、类型处理器配置(mybatis.type-handlers-package)，如下所示
<!DOCTYPE configuration
PUBLIC "-//config.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.xxx.ow.entity"/>
<package name="com.xxx.ow.enums"/>
<package name="com.xxx.ow.dto"/>
</typeAliases>
<typeHandlers>
<typeHandler handler="com.xxx.ow.enums.typehandler.UniversalEnumHandler"
javaType="com.xxx.ow.enums.ArticleOfProclamationEnum"/>
</typeHandlers>
</configuration>
第二种方式
还有一种简单的方法是使用SpringBoot的注入数据源配置属性，比如DruidDataSource。比如像下面这样：
@Configuration
@MapperScan(basePackages = {"com.xxx.dataimport.dao.mysql"}, sqlSessionFactoryRef = "mySqlSessionFactory")
public class MySqlConfig {
@Value("${spring.datasource.mysql.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.mysql.jdbc-url}")
private String jdbcUrl;
@Value("${spring.datasource.mysql.username}")
private String username;
@Value("${spring.datasource.mysql.password}")
private String password;
@Primary
@Bean(name = "mysqlDataSource")
public DataSource dataSource() {
DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.type(com.alibaba.druid.pool.DruidDataSource.class);
dataSourceBuilder.driverClassName(driverClassName);
dataSourceBuilder.url(jdbcUrl);
dataSourceBuilder.username(username);
dataSourceBuilder.password(password);
return dataSourceBuilder.build();
}
@Primary
@Bean(name = "mysqlTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Primary
@Bean(name = "mySqlSessionFactory")
public SqlSessionFactory basicSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource basicDataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(basicDataSource);
factoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis.xml")[0]);
PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
Resource[] resources1 = resourcePatternResolver.getResources("classpath:com/xxx/ow/dataimport/mapper/mysql/**/*.xml");
factoryBean.setMapperLocations(resources1);
return factoryBean.getObject();
}
@Primary
@Bean(name = "mysqlSqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("mySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
@Configuration
@MapperScan(basePackages = "com.xxx.ow.dataimport.dao.sqlserver", sqlSessionFactoryRef = "sqlServerSessionFactory")
public class SqlServerConfig {
@Value("${spring.datasource.sqlserver.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.sqlserver.jdbc-url}")
private String jdbcUrl;
@Value("${spring.datasource.sqlserver.username}")
private String username;
@Value("${spring.datasource.sqlserver.password}")
private String password;
@Bean(name = "sqlServerDataSource")
public DataSource dataSource() {
DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.type(com.alibaba.druid.pool.DruidDataSource.class);
dataSourceBuilder.url(jdbcUrl);
dataSourceBuilder.driverClassName(driverClassName);
dataSourceBuilder.username(username);
dataSourceBuilder.password(password);
return dataSourceBuilder.build();
}
@Bean(name = "sqlServerTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("sqlServerDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "sqlServerSessionFactory")
public SqlSessionFactory basicSqlSessionFactory(@Qualifier("sqlServerDataSource") DataSource basicDataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(basicDataSource);
factoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis.xml")[0]);
factoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:com/xxx/ow/dataimport/mapper/sqlserver/**/*.xml"));
return factoryBean.getObject();
}
@Bean(name = "sqlServerSqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("sqlServerSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
spring.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.mysql.jdbc-url=jdbc:mysql://xxx:3316/xxx_ow?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&connectTimeout=10000&serverTimezone=Asia/Shanghai
spring.datasource.mysql.username=app_owner
spring.datasource.mysql.password=
spring.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.sqlserver.jdbc-url=jdbc:sqlserver://127.0.0.1:1433;databaseName=ringcms_20210702_2
spring.datasource.sqlserver.username=sa
spring.datasource.sqlserver.password=
########### druid data source配置
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters=stat
# 初始连接数
spring.datasource.druid.initialSize=5
# 最小空闲连接
spring.datasource.druid.minIdle=5
# 最大连接数
spring.datasource.druid.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
# 打开PSCache。如果用Oracle, 则把poolPreparedStatements配置为true, mysql 5.5之后建议true
spring.datasource.druid.poolPreparedStatements=true
# 指定每个连接上PSCache的大小
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# druid 登录账号
spring.datasource.druid.stat-view-servlet.login-username=superadmin
spring.datasource.druid.stat-view-servlet.login-password=Moshou@2018
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
# IP白名单，多个用,分隔 (没有配置或者为空，则允许所有访问)
#spring.datasource.druid.stat-view-servlet.allow=
# IP黑名单 (存在共同时，deny优先于allow)
#spring.datasource.druid.stat-view-servlet.deny=
# 禁用HTML页面上的“Reset All”功能
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.map,*.woff,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
########### druid data source配置
第三种方式，使用@ConfigurationProperties注入数据源配置
- 数据库配置
spring:
datasource:
test1:
jdbc-url: jdbc:mysql://localhost:3306/alice_test?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
driverClassName: com.mysql.cj.jdbc.Driver
username: root
password: xxx
test2:
jdbc-url: jdbc:mysql://localhost:3306/alice_test_two?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
driverClassName: com.mysql.cj.jdbc.Driver
username: root
password: xxx
mybatis:
mapper-locations: classpath:*/mapper/**.xml
- 配置类
- 主数据源
package com.alice.springboot.config;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.alice.springboot.mapper.test", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSourceConfig1 {
// 将这个对象放入Spring容器中
@Bean(name = "test1DataSource")
// 表示这个数据源是默认数据源
@Primary
// 读取application.properties中的配置参数映射成为一个对象
// prefix表示参数的前缀
@ConfigurationProperties(prefix = "spring.datasource.test1")
public DataSource getDateSource1()
{
return DataSourceBuilder.create().build();
}
@Bean(name = "test1SqlSessionFactory")
// 表示这个数据源是默认数据源
@Primary
// @Qualifier表示查找Spring容器中名字为test1DataSource的对象
public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource datasource)
throws Exception
{
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setMapperLocations(
// 设置mybatis的xml所在位置
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/test/*.xml"));
bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis.xml"));
return bean.getObject();
}
@Bean("test1SqlSessionTemplate")
// 表示这个数据源是默认数据源
@Primary
public SqlSessionTemplate test1SqlSessionTemplate(
@Qualifier("test1SqlSessionFactory") SqlSessionFactory sessionFactory)
{
return new SqlSessionTemplate(sessionFactory);
}
}
- 次数据源
package com.alice.springboot.config;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.alice.springboot.mapper.testTwo", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSourceConfig2 {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
public DataSource getDateSource2()
{
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource datasource)
throws Exception
{
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/testTwo/*.xml"));
bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-two.xml"));
return bean.getObject();
}
@Bean("test2SqlSessionTemplate")
public SqlSessionTemplate test2SqlSessionTemplate(
@Qualifier("test2SqlSessionFactory") SqlSessionFactory sessionFactory)
{
return new SqlSessionTemplate(sessionFactory);
}
}
