springboot整合mybatis连接多个数据库
数据库准备
DROP TABLE IF EXISTS `order_exp`;
CREATE TABLE `order_exp` (
`id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
`order_no` varchar(50) NOT NULL COMMENT '订单的编号',
`order_note` varchar(100) NOT NULL COMMENT '订单的说明',
`insert_time` datetime NOT NULL COMMENT '插入订单的时间',
`expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
`expire_time` datetime NOT NULL COMMENT '订单的过期时间',
`order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
gradle依赖
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.springframework.boot:spring-boot-starter-aop'
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
compile group: 'org.apache.commons', name: 'commons-lang3', version: '3.9'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.0'
compileOnly 'org.projectlombok:lombok'
runtimeOnly 'mysql:mysql-connector-java'
annotationProcessor 'org.projectlombok:lombok'
implementation 'org.springframework.boot:spring-boot-starter-test'
}核心方法
多数据源和枚举类
public class DBContextHolder {
/*保存系统中存在的数据源的标识符，然后通过该标识符定位到实际的数据源实体*/
private static final ThreadLocal<DBTypeEnum> contextHolder
= new ThreadLocal<DBTypeEnum>();
public static void set(DBTypeEnum dbTypeEnum){
contextHolder.set(dbTypeEnum);
}
public static DBTypeEnum get(){
return contextHolder.get();
}
public static void master(){
set(DBTypeEnum.MASTER);
System.out.println("切换到主库-----------------------");
}
public static void slave(){
set(DBTypeEnum.SLAVE);//轮询
System.out.println("切换到从库-----------------------");
}
}public enum DBTypeEnum {
MASTER,SLAVE;
}AOP定义切点、切面，Master注解的类和增删改的方法都走主库，非master注解和一些查询方法走从库。
@Aspect
@Component
public class DataSourceAop {
/*从库的切点,没有标注Master注解，并且方法名为select和get开头的方法，走从库*/
@Pointcut("!@annotation(com.sss.annotation.Master) " +
"&& (execution(* com.sss.service..*.select*(..)) " +
"|| execution(* com.sss.service..*.get*(..))" +
"|| execution(* com.sss.service..*.find*(..))" +
"|| execution(* com.sss.service..*.query*(..)))")
public void slavePointcut() {
}
/*主库的切点,或者标注了Master注解或者方法名为insert、update等开头的方法，走主库*/
@Pointcut("@annotation(com.sss.annotation.Master) " +
"|| execution(* com.sss.service..*.insert*(..)) " +
"|| execution(* com.sss.service..*.add*(..)) " +
"|| execution(* com.sss.service..*.update*(..)) " +
"|| execution(* com.sss.service..*.edit*(..)) " +
"|| execution(* com.sss.service..*.delete*(..)) " +
"|| execution(* com.sss.service..*.remove*(..))")
public void masterPointcut() {
}
@Before("slavePointcut()")
public void slave() {
DBContextHolder.slave();
}
@Before("masterPointcut()")
public void master() {
DBContextHolder.master();
}
}
```java
public class MyRoutingDataSource extends AbstractRoutingDataSource {
/*在运行时, 根据某种规则，比如key值，比如当前线程的id来动态切换到真正的DataSource上*/
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.get();
}
}多数据源的配置
spring:
datasource:
master:
jdbc-url: jdbc:mysql://192.168.0.103:3306/test?serverTimezone=GMT%2b8&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&autocommit=false
username: root
password: password
driver-class-name: com.mysql.cj.jdbc.Driver
slave1:
jdbc-url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2b8&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&autocommit=false
username: root
password: password
driver-class-name: com.mysql.cj.jdbc.Driver@Configuration
public class DataSourceConfig {
/*主库的数据源*/
@Bean
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return create().build();
}
/*从库1的数据源*/
@Bean
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource() {
return create().build();
}
/*自定义数据源，内部持有了主库和从库的数据源，
通过某种机制让应用程序在进行数据读写时，按业务情况走主库或者从库*/
@Bean
public DataSource myRoutingDataSource(
@Qualifier("masterDataSource")DataSource masterDataSource,
@Qualifier("slave1DataSource")DataSource slaveDataSource){
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MASTER,masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE,slaveDataSource);
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
myRoutingDataSource.setTargetDataSources(targetDataSources);
/*当执行的方法没有被Aop拦截时，缺省使用的数据源*/
myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
return myRoutingDataSource;
}
}测试类
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = SpringbootMultipleDatasourceApplication.class)
public class SpringbootMultipleDatasourceApplicationTests {
@Autowired
private ProcessOrder processOrder;
@Test
public void insertOrders() {
processOrder.insertOrders(5);
}
@Test
public void queryOrders() {
processOrder.findOrders();
processOrder.findOrdersMaster();
}
}测试结果
插入结果：
切换到主库-----------------------
2019-10-19 12:49:24.728 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Preparing: insert into order_exp (order_no, order_note, insert_time, expire_duration,expire_time, order_status) values (?, ?, now(), ?, DATE_ADD(now(),INTERVAL ? SECOND), ?)
2019-10-19 12:49:24.779 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Parameters: DD00_16S(String), 海王5排16号,过期时长:DD00_16S(String), 16(Long), 16(Long), 0(Short)
2019-10-19 12:49:24.824 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : <== Updates: 1
2019-10-19 12:49:24.845 INFO 5036 --- [ Test worker] com.sss.service.ProcessOrder : 保存订单到DB:DD00_16S
2019-10-19 12:49:24.846 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Preparing: insert into order_exp (order_no, order_note, insert_time, expire_duration,expire_time, order_status) values (?, ?, now(), ?, DATE_ADD(now(),INTERVAL ? SECOND), ?)
2019-10-19 12:49:24.847 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Parameters: DD00_24S(String), 海王5排24号,过期时长:DD00_24S(String), 24(Long), 24(Long), 0(Short)
2019-10-19 12:49:24.891 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : <== Updates: 1
2019-10-19 12:49:24.892 INFO 5036 --- [ Test worker] com.sss.service.ProcessOrder : 保存订单到DB:DD00_24S
2019-10-19 12:49:24.892 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Preparing: insert into order_exp (order_no, order_note, insert_time, expire_duration,expire_time, order_status) values (?, ?, now(), ?, DATE_ADD(now(),INTERVAL ? SECOND), ?)
2019-10-19 12:49:24.893 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Parameters: DD00_9S(String), 海王5排9号,过期时长:DD00_9S(String), 9(Long), 9(Long), 0(Short)
2019-10-19 12:49:24.936 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : <== Updates: 1
2019-10-19 12:49:24.937 INFO 5036 --- [ Test worker] com.sss.service.ProcessOrder : 保存订单到DB:DD00_9S
2019-10-19 12:49:24.938 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Preparing: insert into order_exp (order_no, order_note, insert_time, expire_duration,expire_time, order_status) values (?, ?, now(), ?, DATE_ADD(now(),INTERVAL ? SECOND), ?)
2019-10-19 12:49:24.939 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Parameters: DD00_23S(String), 海王5排23号,过期时长:DD00_23S(String), 23(Long), 23(Long), 0(Short)
2019-10-19 12:49:24.981 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : <== Updates: 1
2019-10-19 12:49:24.982 INFO 5036 --- [ Test worker] com.sss.service.ProcessOrder : 保存订单到DB:DD00_23S
2019-10-19 12:49:24.983 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Preparing: insert into order_exp (order_no, order_note, insert_time, expire_duration,expire_time, order_status) values (?, ?, now(), ?, DATE_ADD(now(),INTERVAL ? SECOND), ?)
2019-10-19 12:49:24.983 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : ==> Parameters: DD00_17S(String), 海王5排17号,过期时长:DD00_17S(String), 17(Long), 17(Long), 0(Short)
2019-10-19 12:49:25.027 DEBUG 5036 --- [ Test worker] c.s.dao.OrderExpMapper.insertDelayOrder : <== Updates: 1
2019-10-19 12:49:25.028 INFO 5036 --- [ Test worker] com.sss.service.ProcessOrder : 保存订单到DB:DD00_17S
2019-10-19 12:49:25.173 INFO 5036 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor'
2019-10-19 12:49:25.174 INFO 5036 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-10-19 12:49:25.295 INFO 5036 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
BUILD SUCCESSFUL in 15s查询结果：
切换到从库-----------------------
2019-10-19 12:50:03.163 INFO 19608 --- [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2019-10-19 12:50:05.560 INFO 19608 --- [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2019-10-19 12:50:05.573 DEBUG 19608 --- [ Test worker] c.s.d.OrderExpMapper.selectUnPayOrders : ==> Preparing: select id, order_no, order_note, insert_time, expire_duration, expire_time, order_status from order_exp where order_status = 0
2019-10-19 12:50:05.617 DEBUG 19608 --- [ Test worker] c.s.d.OrderExpMapper.selectUnPayOrders : ==> Parameters:
2019-10-19 12:50:05.660 DEBUG 19608 --- [ Test worker] c.s.d.OrderExpMapper.selectUnPayOrders : <== Total: 0
2019-10-19 12:50:05.676 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : 发现了表中还有[0]个未到期未支付的订单！
切换到主库-----------------------
2019-10-19 12:50:05.676 INFO 19608 --- [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2019-10-19 12:50:06.024 INFO 19608 --- [ Test worker] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
2019-10-19 12:50:06.025 DEBUG 19608 --- [ Test worker] c.s.d.OrderExpMapper.selectUnPayOrders : ==> Preparing: select id, order_no, order_note, insert_time, expire_duration, expire_time, order_status from order_exp where order_status = 0
2019-10-19 12:50:06.025 DEBUG 19608 --- [ Test worker] c.s.d.OrderExpMapper.selectUnPayOrders : ==> Parameters:
2019-10-19 12:50:06.053 DEBUG 19608 --- [ Test worker] c.s.d.OrderExpMapper.selectUnPayOrders : <== Total: 10
2019-10-19 12:50:06.053 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : 发现了表中还有[10]个未到期未支付的订单！
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=16, orderNo=DD00_12S, orderNote=海王5排12号,过期时长:DD00_12S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=17, orderNo=DD00_22S, orderNote=海王5排22号,过期时长:DD00_22S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=18, orderNo=DD00_15S, orderNote=海王5排15号,过期时长:DD00_15S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=19, orderNo=DD00_17S, orderNote=海王5排17号,过期时长:DD00_17S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=20, orderNo=DD00_18S, orderNote=海王5排18号,过期时长:DD00_18S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=21, orderNo=DD00_16S, orderNote=海王5排16号,过期时长:DD00_16S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=22, orderNo=DD00_24S, orderNote=海王5排24号,过期时长:DD00_24S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=23, orderNo=DD00_9S, orderNote=海王5排9号,过期时长:DD00_9S, orderStatus=0]
2019-10-19 12:50:06.054 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=24, orderNo=DD00_23S, orderNote=海王5排23号,过期时长:DD00_23S, orderStatus=0]
2019-10-19 12:50:06.055 INFO 19608 --- [ Test worker] com.sss.service.ProcessOrder : OrderExp [id=25, orderNo=DD00_17S, orderNote=海王5排17号,过期时长:DD00_17S, orderStatus=0]
2019-10-19 12:50:06.128 INFO 19608 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor'
2019-10-19 12:50:06.130 INFO 19608 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-10-19 12:50:06.146 INFO 19608 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
2019-10-19 12:50:06.146 INFO 19608 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Shutdown initiated...
2019-10-19 12:50:06.388 INFO 19608 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Shutdown completed.
BUILD SUCCESSFUL in 14s源代码
https://github.com/smith5623/springboot_demos/tree/master/springboot-multiple-datasource
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
