背景
目前报表导出需要多数据库的数据，因此我们需要做Mybatis多数据源的配置
我们之前使用Spring的AbstractRoutingDataSource
做资源隔离redis限制请求频率及资源隔离
但是事实上我们确实存在两个数据源【非读写分离】
两个数据源完全不同 换言之在业务上完全不等价【即A数据源的数据和B数据源的数据不同】
而读写分离是A数据源和B数据源的数据相同【至少逻辑等同，比如分片比如读写分离】
当然利用上述方法依然是可以完成多数据源，只是需要做动态切换
本次我们使用另一种实现方式
在SpringBoot+MyBatis实现多个SqlSessionFactory
步骤
由于我们系统使用多数据源我们需要定义两个数据源
在application.properties中需要定义两个数据源
spring.datasource.url=jdbc:mysql://192.168.1.7:3306/f6dms_20160522?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource2.url=jdbc:mysql://192.168.1.7:3306/f6dms_1116_prod_backup?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource2.username=root
spring.datasource2.password=root
我们使用spring.dataSource2作为第二个数据源的prefix
当只有一个数据源的时候由于druid-starter会自动注册
@Configuration
@ConditionalOnClass(com.alibaba.druid.pool.DruidDataSource.class)
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class,
DruidStatViewServletConfiguration.class,
DruidWebStatFilterConfiguration.class,
DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {
@Bean
@ConditionalOnMissingBean
public DataSource dataSource() {
return new DruidDataSourceWrapper();
}
}
@ConfigurationProperties("spring.datasource.druid")
class DruidDataSourceWrapper extends DruidDataSource implements InitializingBean {
@Autowired
private DataSourceProperties basicProperties;
@Override
public void afterPropertiesSet() throws Exception {
//if not found prefix 'spring.datasource.druid' jdbc properties ,'spring.datasource' prefix jdbc properties will be used.
if (super.getUsername() == null) {
super.setUsername(basicProperties.determineUsername());
}
if (super.getPassword() == null) {
super.setPassword(basicProperties.determinePassword());
}
if (super.getUrl() == null) {
super.setUrl(basicProperties.determineUrl());
}
if (super.getDriverClassName() == null) {
super.setDriverClassName(basicProperties.determineDriverClassName());
}
}
@Autowired(required = false)
public void addStatFilter(StatFilter statFilter) {
super.filters.add(statFilter);
}
@Autowired(required = false)
public void addConfigFilter(ConfigFilter configFilter) {
super.filters.add(configFilter);
}
@Autowired(required = false)
public void addEncodingConvertFilter(EncodingConvertFilter encodingConvertFilter) {
super.filters.add(encodingConvertFilter);
}
@Autowired(required = false)
public void addSlf4jLogFilter(Slf4jLogFilter slf4jLogFilter) {
super.filters.add(slf4jLogFilter);
}
@Autowired(required = false)
public void addLog4jFilter(Log4jFilter log4jFilter) {
super.filters.add(log4jFilter);
}
@Autowired(required = false)
public void addLog4j2Filter(Log4j2Filter log4j2Filter) {
super.filters.add(log4j2Filter);
}
@Autowired(required = false)
public void addCommonsLogFilter(CommonsLogFilter commonsLogFilter) {
super.filters.add(commonsLogFilter);
}
@Autowired(required = false)
public void addWallFilter(WallFilter wallFilter) {
super.filters.add(wallFilter);
}
}
当DataSource未注册时会自动注册DruidWrapper
但是我们需要两个数据源因此必须自己注册
我们定义一个抽象DataSource
public abstract class AbstractDataSourceConfig {
private String driverClassName;
/**
* JDBC url of the database.
*/
private String url;
/**
* Login user of the database.
*/
private String username;
/**
* Login password of the database.
*/
private String password;
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
protected DataSource getDatasource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(this.getUrl());
dataSource.setUsername(this.getUsername());
dataSource.setPassword(this.getPassword());
dataSource.setDriverClassName(this.getDriverClassName());
return dataSource;
}
}
定义数据源1【注意使用了Primary】 primary的作用是当按照类型注册的时候当容器中存在多个将会注入这个Bean
/**
* @author qixiaobo
*/
@Configuration
@ConfigurationProperties("spring.datasource")
public class DataSourceConfig1 extends AbstractDataSourceConfig {
@Bean(PRIMARY_DATA_SOURCE_NAME)
@Primary
public DataSource dataSource1() {
DataSource datasource = getDatasource();
return datasource;
}
}
定义数据源2【注意ConditionalOnProperty会监控系统中存在该property才会注册该Bean】
/**
* @author qixiaobo
*/
@Configuration
@ConfigurationProperties("spring.datasource2")
@ConditionalOnProperty(name = "spring.datasource2.url", matchIfMissing = false)
public class DataSourceConfig2 extends AbstractDataSourceConfig {
@Bean(SECOND_DATA_SOURCE_NAME)
public DataSource dataSource2() {
DataSource datasource = getDatasource();
return datasource;
}
}
如下我们注册MybatisConfiguar
public class AbstractMyBatisConfigurer {
protected static final String SQL_SESSION_FACTORY_NAME = "SqlSessionFactoryBean";
protected static final String TRANSACTION_MANAGER_NAME = "TransactionManager";
protected static final String DATA_SOURCE_NAME = "DataSource";
protected SqlSessionFactoryBean getSqlSessionFactoryBean(DataSource dataSource) {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setTypeAliasesPackage(MODEL_PACKAGE);
//配置分页插件，详情请查阅官方文档
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("pageSizeZero", "true");
//分页尺寸为0时查询所有纪录不再执行分页
properties.setProperty("reasonable", "true");
//页码<=0 查询第一页，页码>=总页数查询最后一页
properties.setProperty("supportMethodsArguments", "false");
//支持通过 Mapper 接口参数来传递分页参数
pageHelper.setProperties(properties);
//添加插件
factory.setPlugins(new Interceptor[]{pageHelper, new SoInterceptor(), new MybatisTransactionTimeoutInterceptor()});
org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
config.setDefaultStatementTimeout(5);
config.setDefaultFetchSize(10000);
config.setDefaultExecutorType(ExecutorType.REUSE);
config.setLogImpl(Slf4jImpl.class);
config.setLogPrefix("dao.");
factory.setConfiguration(config);
return factory;
}
protected MapperScannerConfigurer getMapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
//配置通用Mapper，详情请查阅官方文档
Properties properties = new Properties();
properties.setProperty("mappers", MAPPER_INTERFACE_REFERENCE);
properties.setProperty("notEmpty", "false");
//insert、update是否判断字符串类型!='' 即 test="str != null"表达式内是否追加 and str != ''
properties.setProperty("IDENTITY", "MYSQL");
mapperScannerConfigurer.setProperties(properties);
return mapperScannerConfigurer;
}
}
@Configuration
public class MybatisConfigurer extends AbstractMyBatisConfigurer {
public static final String PRIMARY_SQL_SESSION_FACTORY_NAME = Constants.LEVEL_PRIMARY + SQL_SESSION_FACTORY_NAME;
public static final String PRIMARY_TRANSACTION_MANAGER_NAME = Constants.LEVEL_PRIMARY + TRANSACTION_MANAGER_NAME;
public static final String PRIMARY_DATA_SOURCE_NAME = Constants.LEVEL_PRIMARY + DATA_SOURCE_NAME;
@Bean
@Primary
public SqlSessionFactory sqlSessionFactoryBean(@Autowired DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = getMapperScannerConfigurer();
mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE);
return mapperScannerConfigurer;
}
@Bean
@Primary
public DataSourceTransactionManager transactionManager1(@Autowired DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
/*
* Copyright (c) 2017. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
* Morbi non lorem porttitor neque feugiat blandit. Ut vitae ipsum eget quam lacinia accumsan.
* Etiam sed turpis ac ipsum condimentum fringilla. Maecenas magna.
* Proin dapibus sapien vel ante. Aliquam erat volutpat. Pellentesque sagittis ligula eget metus.
* Vestibulum commodo. Ut rhoncus gravida arcu.
*/
package com.f6car.base.config;
import com.f6car.base.constant.Constants;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.mapper.MapperScannerConfigurer;
import javax.sql.DataSource;
import static com.f6car.base.config.MyBatisConfigurer2.SECOND_DATA_SOURCE_NAME;
import static com.f6car.base.constant.Constants.MAPPER_PACKAGE;
/**
* @author qixiaobo
*/
@Configuration
@ConditionalOnBean(name = SECOND_DATA_SOURCE_NAME)
public class MyBatisConfigurer2 extends AbstractMyBatisConfigurer {
public static final String SECOND_TRANSACTION_MANAGER_NAME = Constants.LEVEL_SECOND + TRANSACTION_MANAGER_NAME;
public static final String SECOND_SQL_SESSION_FACTORY_NAME = Constants.LEVEL_SECOND + SQL_SESSION_FACTORY_NAME;
public static final String SECOND_DATA_SOURCE_NAME = Constants.LEVEL_SECOND + DATA_SOURCE_NAME;
@Bean
public MapperScannerConfigurer mapperScannerConfigurer2() {
MapperScannerConfigurer mapperScannerConfigurer = getMapperScannerConfigurer();
mapperScannerConfigurer.setSqlSessionFactoryBeanName(SECOND_SQL_SESSION_FACTORY_NAME);
mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE + "2");
return mapperScannerConfigurer;
}
@Bean(name = SECOND_TRANSACTION_MANAGER_NAME)
public DataSourceTransactionManager transactionManager2(@Autowired @Qualifier(SECOND_DATA_SOURCE_NAME) DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = SECOND_SQL_SESSION_FACTORY_NAME)
public SqlSessionFactory sqlSessionFactoryBean2(@Autowired @Qualifier(SECOND_DATA_SOURCE_NAME) DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper2/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
}
mapper注册为第一个数据源
mapper2注册为第二个数据源
我们如下文件结构
![images/TR34cKWr2B2MbRfiBPDnJQtN4imhbySX.png]( "images/TR34cKWr2B2MbRfiBPDnJQtN4imhbySX.png")
这样就可以完成多数据源的配置。
本文地址为https://my.oschina.net/qixiaobo025/blog/1584810
