SpringBoot mybatis基于注解的方式实现多数据源配置
当项目需要使用到两个或多个数据源时,就需要采用多数据源配置了
第一步 修改配置文件
spring:
datasource:
dbcp1: #第一个数据源
driver_class_name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/shop
username: root
password: root
dialect: mysql
dbcp2: #第二个数据源
driver_class_name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/shorp1
username: root
password: root
dialect: mysql
dbcp3: #第三个数据源
driver_class_name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/shop2
username: root
password: root
dialect: mysql
druid: #druid配置
# 连接池的配置信息
# 初始化大小,最小,最大
initial-size: 5
min-idle: 5
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
配置了三个数据源 建议从两个开始(虽然三个也没差别)
第二步:实现基于注解数据源动态切换
DataSourceConfig负责加载配置文件中数据源的配置信息
package com.shop.config;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
@Configuration
//@RefreshScope
public class DataSourceConfig {
//数据源1
@Bean(name = "dbcp1")
@ConfigurationProperties(prefix = "spring.datasource.dbcp1") // application.properteis中对应属性的前缀
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}
//数据源2
@Bean(name = "dbcp2")
@ConfigurationProperties(prefix = "spring.datasource.dbcp2") // application.properteis中对应属性的前缀
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
//数据源2
@Bean(name = "dbcp3")
@ConfigurationProperties(prefix = "spring.datasource.dbcp3") // application.properteis中对应属性的前缀
public DataSource dataSource3() {
return DataSourceBuilder.create().build();
}
/**
* 动态数据源: 通过AOP在不同数据源之间动态切换
* @return
*/
@Primary
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource() {
DynamicDS dynamicDS = new DynamicDS();
// 默认数据源
// dynamicDataSource.setDefaultTargetDataSource(dataSource1());
// 配置多数据源
Map<Object, Object> dsMap = new HashMap();
dsMap.put("dbcp1", dataSource1());
dsMap.put("dbcp2", dataSource2());
dsMap.put("dbcp3", dataSource3());
dynamicDS.setTargetDataSources(dsMap);
return dynamicDS;
}
/**
* 配置@Transactional注解事物
* @return
*/
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
}
DataSourceContextHolder用来获取及设置当前数据源
package com.shop.config;
import com.shop.codes.DSEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
*用来获取及设置当前数据源
*/
public class DataSourceContextHolder {
static Logger logger = LoggerFactory.getLogger("DataSourceContextHolder");
/**
* 默认数据源
*/
public static final String DEFAULT_DS = DSEnum.DB1.getValue();
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
// 设置数据源名
public static void setDB(String dbType) {
contextHolder.set(dbType);
}
// 获取数据源名
public static String getDB() {
String dbsource = contextHolder.get();
if(dbsource == null ){
dbsource = "dbcp1";
logger.info("数据源为NULL 返回默认数据源"+ dbsource);
}
return dbsource;
}
// 清除数据源名
public static void clearDB() {
contextHolder.remove();
}
}
枚举类
package com.shop.codes;
public enum DSEnum {
DB1("dbcp1"),
DB2("dbcp2"),
DB3("dbcp3");
private String value;
DSEnum(String value){
this.value=value;
}
public String getValue() {
return value;
}
}
自定义DSAnno注解
package com.shop.annos;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DSAnno {
String value() default "dbcp1";
}
定义切面类,根据接口上的注解动态切换数据源
package com.shop.config;
import com.shop.annos.DSAnno;
import com.shop.util.GetPrivateFieldUtil;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
/**
* 自定义注解 + AOP的方式实现数据源动态切换。
*/
@Aspect
@Component
public class AspectDynamicDS {
private Logger logger = LoggerFactory.getLogger(AspectDynamicDS.class);
@Before("@annotation(com.shop.annos.DSAnno)")
public void beforeSwitchDS(JoinPoint point) {
try {
Object methodInvocation = GetPrivateFieldUtil.getPrivateField(point, "methodInvocation", point.getClass());
Method method = (Method) GetPrivateFieldUtil.getPrivateField(methodInvocation, "method", methodInvocation.getClass());
DSAnno annotation = method.getAnnotation(DSAnno.class);
DataSourceContextHolder.setDB(annotation.value());
} catch (Exception e) {
e.printStackTrace();
}
logger.info("当前数据源为" + DataSourceContextHolder.getDB());
}
@After("@annotation(com.shop.annos.DSAnno)")
public void afterSwitchDS(JoinPoint point) {
DataSourceContextHolder.clearDB();
}
}
动态数据源,重载函数用于返回当前数据源
package com.shop.config;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDS extends AbstractRoutingDataSource {
private Logger logger = LoggerFactory.getLogger(getClass().getName());
@Override
protected Object determineCurrentLookupKey() {
String dbsource = DataSourceContextHolder.getDB();
if(dbsource == null ){
dbsource = "dbcp1";
logger.info("数据源为NULL 返回数据源"+DataSourceContextHolder.getDB());
}
return dbsource;
}
}
被切面引用的工具类
package com.shop.util;
import java.lang.reflect.Field;
public class GetPrivateFieldUtil {
public static Object getPrivateField(Object obj, String fieldName, Class classs) {
try {
Field field = classs.getDeclaredField(fieldName);
field.setAccessible(true);
return field.get(obj);
} catch (Exception e) {
try {
classs = classs.getSuperclass();
Field field = classs.getDeclaredField(fieldName);
field.setAccessible(true);
return field.get(obj);
} catch (Exception ex) {
return null;
}
}
}
}
第三步:禁止springboot自动注入数据源配置
package com.shop;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan("com.shop.mapper")
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
使用方法
Mapper接口文件定义3个接口,分别对应3个数据源的查询操作(为了省事儿,用了注解)
分别对应 第一个数据源 第二个数据源 第三个数据源
对了三个数据源里面的数据都是一样的 可以自己修改用户名密码去区别
//第一个数据源
@DSAnno("dbcp1")
@Select("SELECT * from oc_current_day_revenue where polylineId=#{polylineId} GROUP BY date ")
List<NowDayRevenue> getAllDataByTypeId(@Param("polylineId") int polylineId);
//第二个数据源
@DSAnno("dbcp2")
@Select("SELECT * FROM oc_polylinedata")
List<OcPolylineData> selectAllType();
//三个数据源
@DSAnno("dbcp3")
@Select("SELECT * FROM oc_users WHERE name = #{name} AND password = #{password}")
User getInfo(@Param("name")String name,@Param("password")String password);
controller:
@RequestMapping(value = "/loginIn",method = RequestMethod.POST)
public String login(String name,String password){
User user = tidDSUserService.loginIn(name,password);
String s = weatherController.QueryWeather();
if(user !=null){
return "success";
}else {
return "error";
}
}
@PostMapping("/getAll")
@ResponseBody
public Map getAll(){
Map map = firDSNowDayRevenueService.getAll();
return map;
}
@PostMapping("/getAllPolyline")
@ResponseBody
public Map getAllPolyline(){
return polylineService.getAllPolyline();
}
console控制台打印:
从登陆页面登陆成功时,会自动查询所有数据分别调用三个数据源
控制台可以看到打印的数据
登录用数据源3 查询类别用数据源2 查询所有当日数据用数据源1
2021-11-01 23:04:17.498 INFO 23652 --- [nio-8090-exec-3] c.s.service.impl.TidDSUserServiceImpl : admin1:::::1
2021-11-01 23:04:17.507 INFO 23652 --- [nio-8090-exec-3] com.shop.config.AspectDynamicDS : 当前数据源为dbcp3
2021-11-01 23:04:17.530 INFO 23652 --- [nio-8090-exec-3] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-11-01 23:04:17.859 INFO 23652 --- [nio-8090-exec-3] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2021-11-01 23:04:20.804 INFO 23652 --- [nio-8090-exec-3] c.s.service.impl.TidDSUserServiceImpl : {"tem_day":"22","update_time":"22:55","wea":"多云","win_meter":"0km/h","city":"上海","wea_img":"yun","tem_night":"16","win_speed":"0级","cityid":"101020100","air":"118","tem":"17","win":"南风"}
2021-11-01 23:04:20.804 INFO 23652 --- [nio-8090-exec-3] c.s.service.impl.TidDSUserServiceImpl : 22
2021-11-01 23:04:26.361 INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS : 当前数据源为dbcp2
2021-11-01 23:04:26.362 INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2021-11-01 23:04:26.392 INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
2021-11-01 23:04:26.396 INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS : 当前数据源为dbcp1
2021-11-01 23:04:26.396 INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Starting...
2021-11-01 23:04:26.427 INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Start completed.
2021-11-01 23:04:26.434 INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS : 当前数据源为dbcp1
2021-11-01 23:04:26.436 INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS : 当前数据源为dbcp1
项目地址(包含数据库):
https://github.com/DNYDYS/SpringBootStudy