目录
前言
1、使用Spring提供的AbstractRoutingDataSource
2、使用MyBatis注册多个SqlSessionFactory
3、使用dynamic-datasource框架
前言
Java后台使用MyBatis-plus
快速访问多个数
据源,这里分享三种常用的多数据源管理方案
1、使用Spring提供的AbstractRoutingDataSource
这种方式的核心是使用
Spring
提供的
AbstractRoutingDataSource
抽象类,注入多个数据源。核心代码如下:
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Logger;
@Component
@Primary // 将该Bean设置为主要注入Bean
public class DynamicDataSource extends AbstractRoutingDataSource {
// 当前使用的数据源标识
public static ThreadLocal<String> name=new ThreadLocal<>();
// 写
@Autowired
DataSource dataSource1;
// 读
@Autowired
DataSource dataSource2;
// 返回当前数据源标识
@Override
protected Object determineCurrentLookupKey() {
return name.get();
}
@Override
public void afterPropertiesSet() {
// 为targetDataSources初始化所有数据源
Map<Object, Object> targetDataSources=new HashMap<>();
targetDataSources.put("W",dataSource1);
targetDataSources.put("R",dataSource2);
super.setTargetDataSources(targetDataSources);
// 为defaultTargetDataSource 设置默认的数据源
super.setDefaultTargetDataSource(dataSource1);
super.afterPropertiesSet();
}
}
将自己实现的
DynamicDataSource
注册成为默认的
DataSource
实例后,只需要在每次使用
DataSource
时,提前
改变一下其中的name标识,就可以快速切换数据源。这里使用到AOP功能,关键代码如下:
注解类,用于标识读库或者写库
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/***
* @Author 黎剑
* @Slogan 无意与众不同,只因品位出众,致敬未来的你
*/
@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface WR {
String value() default "W";
}
切面类
import com.lijian.dynamic.datasource.DynamicDataSource;
import com.lijian.dynamic.datasource.annotation.WR;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/***
* @author 黎剑
* @slogan 无意与众不同,只因品位出众,致敬未来的你
*/
@Component
@Aspect
public class DynamicDataSourceAspect implements Ordered {
// 前置
@Before("within(com.lijian.dynamic.datasource.service.impl.*) && @annotation(wr)")
public void before(JoinPoint point, WR wr){
String name = wr.value();
DynamicDataSource.name.set(name);
System.out.println(name);
}
@Override
public int getOrder() {
return 0;
}
//TODO 环绕通知
}
数据库配置类
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.lijian.dynamic.datasource.DynamicDataSource;
import com.lijian.dynamic.datasource.plugin.DynamicDataSourcePlugin;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/***
* @author 黎剑
* @slogan 无意与众不同,只因品位出众,致敬未来的你
*/
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.datasource1")
public DataSource dataSource1() {
// 底层会自动拿到spring.datasource中的配置, 创建一个DruidDataSource
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.datasource2")
public DataSource dataSource2() {
// 底层会自动拿到spring.datasource中的配置, 创建一个DruidDataSource
return DruidDataSourceBuilder.create().build();
}
@Bean
public DataSourceTransactionManager transactionManager1(DynamicDataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
@Bean
public DataSourceTransactionManager transactionManager2(DynamicDataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
}
application.yml
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
datasource1:
url: jdbc:mysql://127.0.0.1:3306/datasource1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 123456
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
driver-class-name: com.mysql.cj.jdbc.Driver
datasource2:
url: jdbc:mysql://127.0.0.1:3306/datasource2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 123456
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
driver-class-name: com.mysql.cj.jdbc.Driver
服务层实现类
import com.lijian.dynamic.datasource.annotation.WR;
import com.lijian.dynamic.datasource.mapper.FrendMapper;
import com.lijian.dynamic.datasource.entity.Frend;
import com.lijian.dynamic.datasource.service.FrendService;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class FrendImplService implements FrendService {
@Autowired
FrendMapper frendMapper;
@Override
@WR("R") // 库2
public List<Frend> list() {
return frendMapper.list();
}
@Override
@WR("W") // 库1
public void save(Frend frend) {
frendMapper.save(frend);
}
}
启动类
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@SpringBootApplication
@MapperScan("com.lijian.dynamic.datasource.mapper")
@EnableAspectJAutoProxy(exposeProxy=true) // 启动AOP
public class DynamicDatasourceApplication {
public static void main(String[] args) {
SpringApplication.run(DynamicDatasourceApplication.class, args);
}
}
2、使用MyBatis注册多个SqlSessionFactory
如果使用MyBatis框架,要注册多个数据源的话,就需要将MyBatis底层的DataSource、SqlSessionFactory、 DataSourceTransactionManager这些核心对象一并进行手动注册。例如:
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import javax.sql.DataSource;
@Configuration
// 继承mybatis:
// 1. 指定扫描的mapper接口包(主库)
// 2. 指定使用sqlSessionFactory是哪个(主库)
@MapperScan(basePackages = "com.lijian.datasource.dynamic.mybatis.mapper.r",
sqlSessionFactoryRef="rSqlSessionFactory")
public class RMyBatisConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.datasource2")
public DataSource dataSource2() {
// 底层会自动拿到spring.datasource中的配置, 创建一个DruidDataSource
return DruidDataSourceBuilder.create().build();
}
@Bean
@Primary
public SqlSessionFactory rSqlSessionFactory()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
// 指定主库
sessionFactory.setDataSource(dataSource2());
// 指定主库对应的mapper.xml文件
/*sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:mapper/r/*.xml"));*/
return sessionFactory.getObject();
}
@Bean
public DataSourceTransactionManager rTransactionManager(){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource2());
return dataSourceTransactionManager;
}
@Bean
public TransactionTemplate rTransactionTemplate(){
return new TransactionTemplate(rTransactionManager());
}
}
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
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 org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionOperations;
import org.springframework.transaction.support.TransactionTemplate;
import javax.sql.DataSource;
/***
*
* 写数据源配置
*/
@Configuration
// 继承mybatis:
// 1. 指定扫描的mapper接口包(主库)
// 2. 指定使用sqlSessionFactory是哪个(主库)
@MapperScan(basePackages = "com.lijian.datasource.dynamic.mybatis.mapper.w",
sqlSessionFactoryRef="wSqlSessionFactory")
public class WMyBatisConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.datasource1")
public DataSource dataSource1() {
// 底层会自动拿到spring.datasource中的配置, 创建一个DruidDataSource
return DruidDataSourceBuilder.create().build();
}
@Bean
@Primary
public SqlSessionFactory wSqlSessionFactory()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
// 指定主库
sessionFactory.setDataSource(dataSource1());
// 指定主库对应的mapper.xml文件
/*sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:mapper/order/*.xml"));*/
return sessionFactory.getObject();
}
@Bean
@Primary
public DataSourceTransactionManager wTransactionManager(){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource1());
return dataSourceTransactionManager;
}
@Bean
public TransactionTemplate wTransactionTemplate(){
return new TransactionTemplate(wTransactionManager());
}
}
注解类
import java.lang.annotation.*;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MultiTransactional {
String[] value() default {};
}
AOP
import com.lijian.datasource.dynamic.mybatis.service.transaction.ComboTransaction;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@Aspect
@Component
public class MultiTransactionAop {
private final ComboTransaction comboTransaction;
@Autowired
public MultiTransactionAop(ComboTransaction comboTransaction) {
this.comboTransaction = comboTransaction;
}
@Pointcut("within(com.lijian.datasource.dynamic.mybatis.service.impl.*)")
public void pointCut() {
}
@Around("pointCut() && @annotation(multiTransactional)")
public Object inMultiTransactions(ProceedingJoinPoint pjp, MultiTransactional multiTransactional) {
return comboTransaction.inCombinedTx(() -> {
try {
return pjp.proceed(); //执行目标方法
} catch (Throwable throwable) {
if (throwable instanceof RuntimeException) {
throw (RuntimeException) throwable;
}
throw new RuntimeException(throwable);
}
}, multiTransactional.value());
}
}
import com.alibaba.druid.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.concurrent.Callable;
import java.util.stream.Stream;
@Component
public class ComboTransaction {
@Autowired
private Db1TxBroker db1TxBroker;
@Autowired
private Db2TxBroker db2TxBroker;
public <V> V inCombinedTx(Callable<V> callable, String[] transactions) {
if (callable == null) {
return null;
}
// 相当于循环 [wTransactionManager,wTransactionManager]
Callable<V> combined = Stream.of(transactions)
.filter(ele -> !StringUtils.isEmpty(ele))
.distinct()
.reduce(callable, (r, tx) -> {
switch (tx) {
case DbTxConstants.DB1_TX:
return () -> db1TxBroker.inTransaction(r);
case DbTxConstants.DB2_TX:
return () -> db2TxBroker.inTransaction(r);
default:
return null;
}
}, (r1, r2) -> r2);
try {
return combined.call();
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.util.concurrent.Callable;
@Component
public class Db1TxBroker {
@Transactional(DbTxConstants.DB1_TX)
public <V> V inTransaction(Callable<V> callable) {
try {
return callable.call();
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.util.concurrent.Callable;
@Component
public class Db2TxBroker {
@Transactional(DbTxConstants.DB2_TX)
public <V> V inTransaction(Callable<V> callable) {
try {
return callable.call();
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
public class DbTxConstants {
public static final String DB1_TX = "wTransactionManager";
public static final String DB2_TX = "rTransactionManager";
}
服务层类
import com.lijian.datasource.dynamic.mybatis.entity.Frend;
import com.lijian.datasource.dynamic.mybatis.mapper.r.RFrendMapper;
import com.lijian.datasource.dynamic.mybatis.mapper.w.WFrendMapper;
import com.lijian.datasource.dynamic.mybatis.service.FrendService;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;
import java.util.List;
@Service
public class FrendImplService implements FrendService {
@Autowired
private RFrendMapper rFrendMapper;
@Autowired
private WFrendMapper wFrendMapper;
@Autowired
TransactionTemplate wTransactionTemplate;
@Autowired
TransactionTemplate rTransactionTemplate;
// 读-- 读库
@Override
public List<Frend> list() {
return rFrendMapper.list();
}
// 保存-- 写库
@Override
public void save(Frend frend) {
wFrendMapper.save(frend);
}
// 保存-- 写库
@Override
public void saveW(Frend frend) {
frend.setName("无羡W");
wFrendMapper.save(frend);
}
// 保存-- 读库
@Override
public void saveR(Frend frend) {
frend.setName("无羡R");
rFrendMapper.save(frend);
}
@Transactional(transactionManager = "wTransactionManager")
public void saveAll(Frend frend) throws Exception {
FrendService frendService = (FrendService) AopContext.currentProxy();
frendService.saveAllR(frend);
}
@Transactional(transactionManager = "rTransactionManager")
public void saveAllR(Frend frend) {
saveW(frend);
saveR(frend);
int a = 1 / 0;
}
}
这样就完成了读库的注册。而读库与写库之间,就可以通过指定不同的
Mapper
和
XML
文件的地址来进行区分。
3、使用dynamic-datasource框架
dynamic-datasource是
MyBaits-plus
作者设计的一个多数据源开源方案。使用这个框架需要引入对应的
pom
依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
这样就可以在
SpringBoot
的配置文件中直接配置多个数据源,application.yaml
spring:
datasource:
dynamic:
#设置默认的数据源或者数据源组,默认值即为master
primary: master
#严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
strict: false
datasource:
master:
url: jdbc:mysql://127.0.0.1:3306/datasource1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 123456
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
driver-class-name: com.mysql.cj.jdbc.Driver
slave_1:
url: jdbc:mysql://127.0.0.1:3306/datasource2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 123456
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
driver-class-name: com.mysql.cj.jdbc.Driver
这样就配置完成了
master
和
slave_1
两个数据库。
接下来在使用时,只要在对应的方法或者类上添加
@DS注解即可。服务层实现类,
例如
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import com.lijian.dynamic.datasource.mapper.FrendMapper;
import com.lijian.dynamic.datasource.entity.Frend;
import com.lijian.dynamic.datasource.service.FrendService;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class FrendImplService implements FrendService {
@Autowired
FrendMapper frendMapper;
@Override
@DS("slave") // 从库, 如果按照下划线命名方式配置多个 , 可以指定前缀即可(组名)
public List<Frend> list() {
return frendMapper.list();
}
@Override
@DS("#session.userID")
@DSTransactional #事务控制
public void save(Frend frend) {
frendMapper.save(frend);
}
@DS("master")
@DSTransactional
public void saveAll(){
// 执行多数据源的操作
}
}