最近在做一个项目,需要使用SpringBoot+Mybatis+Druid使用多数据源,前提条件是数据源的个数和名称不确定,是在application.yml文件中设定,在使用时根据条件动态切换。
这样就不能像Druid官网提到的,通过ConfigurationProperties注解创建多个DruidDataSource,因为这样属于硬编码,添加一个数据源就要再添加代码,我考虑的是只使用一套构建DataSource的代码,添加或删除数据源只需要修改配置文件。
Spring提供的AbstractRoutingDataSource提供了运行时动态切换DataSource的功能,但是AbstractRoutingDataSource对象中包含的DataSourceBuilder构建的仅仅是Spring JDBC的DataSource,并不是我们使用的DruidDataSource,需要自行构建。
很重要:使用之前请在springboot的main类上加@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class })注解,关闭springboot的数据源自动注入,示例如下
/**
* ClassName: ArmApplication
* <p>
* Description: ARM Application's boot starter
* </p>
* date:2017年9月20日
* <p>
*
* @author limsh
* @version 0.1
* @since JDK 1.6
*/
@SpringBootApplication(exclude = {
DataSourceAutoConfiguration.class
})
@ComponentScan(basePackages={"com.**.**.**.**"})
@EnableTransactionManagement
public class ArmApplication extends SpringBootServletInitializer implements DisposableBean{
private static final Class<ArmApplication> startApplication = ArmApplication.class;
private final static Logger log = LoggerFactory.getLogger(ArmApplication.class);
public static void main(String[] args) {
SpringApplication.run(startApplication, args);
log.info("Started ARM Application Succeed");
}
@Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
return builder.sources(startApplication);
}
@Override
public void destroy() throws Exception {
// 应用结束时执行
DistributedIdFactory.stop();
}
}
我们构建一个名为SpringBootDruidMultiDB的SpringBoot项目,导入mybatis-spring-boot-starter和spring-boot-starter-web以及spring-boot-starter-test,为了使用Druid方便,项目还导入druid-spring-boot-starter。由于使用Log4j2记录日志,还添加了log4j2所需要的库,pom文件的配置如下
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.6</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</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>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.10.0</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.10.0</version>
</dependency>
<dependency>
<groupId>com.lmax</groupId>
<artifactId>disruptor</artifactId>
<version>3.3.7</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.43</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
一、修改src/main/resources文件,添加多个数据源信息
#数据库主库信息
master:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://****:3306/**?useUnicode\=true&characterEncoding\=utf-8
username: ***
password: ***
initialSize: 5
minIdle: 5
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: true
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
#设置连接池以及数据库从库库信息
slave:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://***:3306/***?useUnicode\=true&characterEncoding\=utf-8
username: ****
password: ****
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: true
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
二、开始我参照单数据源的构建方式,想像下面的方式构建DruidDataSource数据源
- @Primary:指定在同一个接口有多个实现类可以注入的时候,默认选择哪一个,而不是让@Autowire注解报错(一般用于多数据源的情况下)
- @Qualifier:指定名称的注入,当一个接口有多个实现类的时候使用(在本例中,有两个DataSource类型的实例,需要指定名称注入)
- @Bean:生成的bean实例的名称是方法名(例如上边的@Qualifier注解中使用的名称是前边两个数据源的方法名,而这两个数据源也是使用@Bean注解进行注入的)
/**
* 数据库配置
* @author zhangk
* @time 2018-05-23
*/
@Configuration
public class DataBaseConfiguration{
@Value("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
@Bean(name="masterDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 从库
* @return
*/
@Bean(name = "slaveDataSource")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource slaveDataSourceOne(){
return DataSourceBuilder.create().build();
}
@Bean
public JdbcOperations prodJdbcOperations(@Qualifier("masterDataSource") DataSource prodDataSource) {
return new JdbcTemplate(prodDataSource);
}
@Bean
public JdbcOperations devJdbcOperations(@Qualifier("slaveDataSource")DataSource devDataSource) {
return new JdbcTemplate(devDataSource);
}
}
这里prefix是类似于spring.datasource.master的前缀,然而执行后发现生成的DruidDataSource对象的driverClassName,url,username,password等这些基本属性值。
注:只有使用@ConnectionProperties注解构建的DruidDataSource才可以正常赋值(类似下面的代码)。
三、Mybaties数据源配置,这里只支持一主一从的情况,如需一主多从,可自行稍加改造以下roundRobinDataSouceProxy()方法和后面的ReadWriteSplitRoutingDataSource类
/**
* mybatis多数据源配置类:支持一主一从
* @author zhangk
* @time 2018-05-23
*/
@Configuration
@ConditionalOnClass({EnableTransactionManagement.class})
@Import({ DataBaseConfiguration.class})
@MapperScan(basePackages={"com.**.**.**.**.dao"})
public class MybatisConfiguration {
private final JpaProperties jpaProperties;
@Autowired
public MybatisConfiguration(JpaProperties jpaProperties) {
this.jpaProperties = jpaProperties;
}
@Value("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
@Resource(name = "masterDataSource")
private DataSource masterDataSource;
@Resource(name = "slaveDataSource")
private DataSource slaveDataSource;
/**
* 从库的数量
*/
@Value("${spring.datasource.readSize}")
private String dataSourceSize;
@Bean
@ConditionalOnMissingBean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
sqlSessionFactoryBean.setTypeAliasesPackage("com.yamei.account.rela.mgt.model");
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:mapping*//*.xml"));
sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return sqlSessionFactoryBean.getObject();
}
/**
* 有多少个数据源就要配置多少个bean
* @return
*/
@Bean
public AbstractRoutingDataSource roundRobinDataSouceProxy() {
ReadWriteSplitRoutingDataSource proxy = new ReadWriteSplitRoutingDataSource();
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
targetDataSources.put(DataSourceContextHolder.MASTER,masterDataSource);
targetDataSources.put(DataSourceContextHolder.SLAVE,slaveDataSource);
proxy.setDefaultTargetDataSource(masterDataSource);
proxy.setTargetDataSources(targetDataSources);
return proxy;
}
@Autowired
@Bean(name = "entityManagerFactoryBean")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(
EntityManagerFactoryBuilder builder,
AbstractRoutingDataSource dynamicDataSource) {
return builder.dataSource(dynamicDataSource)
.properties(jpaProperties.getProperties())
.packages("com.yamei.account.rela.mgt.model.*")
.persistenceUnit("masterPersistenceUnit")
.build();
}
@Autowired
@Bean
@Primary
public EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
return entityManagerFactoryBean.getObject();
}
@Autowired
@Bean
@Primary
public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
return new XATransactionManagerConfig(entityManagerFactory);
}
}
四、将数据源与当前线程绑定
/**
* 本地线程,数据源上下文
* @author zhangk
*
*/
public class DataSourceContextHolder {
public static final String MASTER_TRANSACTION = "master_transaction";
public static final String MASTER_ANNOTATION = "master_annotation";
public static final String MASTER = "master";
public static final String SLAVE = "slave";
private static final Log log = LogFactory.getLog(DataSourceContextHolder.class);
/**
* 使用ThreadLocal把数据源与当前线程绑定
*/
private static ThreadLocal<String> currentDataSource = new ThreadLocal<>();
public static String getDataSource() {
return currentDataSource.get();
}
public static void setDataSource(String dataSourceName) {
log.debug("Set dataSource: [" + dataSourceName + "]");
String current = currentDataSource.get();
if (!(MASTER_TRANSACTION.equals(current) || MASTER_ANNOTATION.equals(current))) {
currentDataSource.set(dataSourceName);
}
log.debug("===================="+dataSourceName+"======================");
log.debug("Current dataSource is: [" + currentDataSource.get() + "]");
}
public static void clearDataSource() {
log.debug("Close dataSource: [" + currentDataSource.get() + "]");
log.debug("====================清除======================");
currentDataSource.remove();
}
}
五、实现多数据源切换
/**
* 多数据源切换
* @author zhangk
* @time 2018-05-23
*/
public class ReadWriteSplitRoutingDataSource extends AbstractRoutingDataSource {
private static final Log log = LogFactory.getLog(ReadWriteSplitRoutingDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
log.debug("-----------------获取连接"+DataSourceContextHolder.getDataSource()+"----------------------");
return DataSourceContextHolder.getDataSource();
}
}
六、多数据源切面控制
package com.yamei.account.rela.mgt.datasource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* 多数据源切面控制
* @author zhangk
* @time 2018-05-23
*/
@Aspect
@Component
public class DataSourceAop {
private final Log log = LogFactory.getLog(DataSourceAop.class);
@Pointcut("execution(* com.**.account.rela.mgt.dao..*.select*(..)) ||execution(* com.**.account.rela.mgt.dao..*.query*(..)) || execution(* com.**.account.rela.mgt.dao..*.get*(..))")
public void findMethod() {
}
@Before("findMethod()")
public void beforeFindMethod() {
DataSourceContextHolder.setDataSource(DataSourceContextHolder.SLAVE);
log.debug("Find method: [begin], Current dataSource is: ["
+ DataSourceContextHolder.getDataSource() + "]");
}
@After("findMethod()")
public void afterFindMethod() {
String dataSource = DataSourceContextHolder.getDataSource();
log.debug("Find method: [end], Current dataSource is: [" + dataSource + "]");
if (DataSourceContextHolder.SLAVE.equals(dataSource)) {
DataSourceContextHolder.clearDataSource();
}
}
@Pointcut("execution(* com.**.account.rela.mgt.dao..*.add*(..)) || execution(* com.**.account.rela.mgt.dao..*.insert*(..)) || execution(* com.**.account.rela.mgt.dao..*.edit*(..))")
public void editMethod() {
}
@Before("editMethod()")
public void beforeEditMethod() {
DataSourceContextHolder.setDataSource(DataSourceContextHolder.MASTER);
log.debug("Find method: [begin], Current dataSource is: ["
+ DataSourceContextHolder.getDataSource() + "]");
}
@After("findMethod()")
public void afterEditMethod() {
String dataSource = DataSourceContextHolder.getDataSource();
log.debug("Find method: [end], Current dataSource is: [" + dataSource + "]");
if (DataSourceContextHolder.MASTER.equals(dataSource)) {
DataSourceContextHolder.clearDataSource();
}
}
//自定义注解
@Pointcut("@annotation(Master)")
public void masterAnnoMethod() {
}
@Before("masterAnnoMethod()")
public void beforeMaster() {
DataSourceContextHolder.setDataSource(DataSourceContextHolder.MASTER_ANNOTATION);
log.debug("Master annotated method: [begin], Current dataSource is: ["
+ DataSourceContextHolder.getDataSource() + "]");
}
@After("masterAnnoMethod()")
public void afterMaster() {
String dataSource = DataSourceContextHolder.getDataSource();
log.debug("Master annotated method: [end], Current dataSource is: [" + dataSource + "]");
if (DataSourceContextHolder.MASTER_ANNOTATION.equals(dataSource)) {
DataSourceContextHolder.clearDataSource();
}
}
}
package com.yamei.account.rela.mgt.datasource;
/**
* @author zhangk
* @date 2018-05-29
*/
public @interface Master {
}
七、解决一个问题,多数据源情况下事物,为了保证的事物的原子性,一致性。执行含有事物的方法的时候,会默认拿该方法中第一次取得的数据库连接去执行整个方法的所有数据库操作,如果第一次拿到的连接是主库的话这样不会有什么影响,但是如果第一次拿到是的是从库的话执行写操作的时候就会报错,所以这里要重写事物,在执行事物的时候强制使用主库,即重写JpaTransactionManager的doBegin和doCommit方法
/**
* @Description
* @Created by zhangk
* @time 2018/5/29 0029
* @Modified By:
*/
public class XATransactionManagerConfig extends JpaTransactionManager {
public XATransactionManagerConfig() {
}
public XATransactionManagerConfig(EntityManagerFactory emf) {
super(emf);
}
@Override
protected void doBegin(Object transaction, TransactionDefinition definition) {
DataSourceContextHolder.setDataSource(DataSourceContextHolder.MASTER_TRANSACTION);
logger.debug("JPA-Transaction: [begin], Current dataSource is: ["
+ DataSourceContextHolder.getDataSource() + "]");
super.doBegin(transaction, definition);
}
@Override
protected void doCommit(DefaultTransactionStatus status) {
String dataSource = DataSourceContextHolder.getDataSource();
logger.debug("JPA-Transaction: [commit], Current dataSource is: ["
+ dataSource + "]");
super.doCommit(status);
if (DataSourceContextHolder.MASTER_TRANSACTION.equals(dataSource)) {
DataSourceContextHolder.clearDataSource();
}
}
}
八、使用 直接在方法上加事物就行 还有一个自定义Master注解使用方法这里就不贴代码出来了直接@Master放到方法上面就好了
@Transactional(propagation=Propagation.REQUIRED,rollbackFor= {ArmServiceException.class})
@Override
public int addEnterpriseStaff(ArmEnterpriseStaff staff, Long enterpriseId) throws ArmServiceException {
另外,本项目仅仅是SpringBoot + Mybatis + Druid多数据源集成的一个简单Demo,可能有些地方理解不准确,有些做法不正确,如果读者有意见,也可以指出,作者感激不尽。