SpringBoot动态数据源
1、原理图
、
2、创建枚举类
package com.yeqm.demo.sys;
/**
*@Description 存数据源key值
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:43
*/
public enum DataSourceKey {
master,salve,migration
}
3、创建自定义注解类
package com.yeqm.demo.sys;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*@Description 自定义注解
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:45
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TargetDataSource {
String value() default "master";
}
4、切换数据源类
package com.yeqm.demo.sys;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
/**
*@Description 根据线程动态切换数据源
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:47
*/
@Configuration
public class DynamicDataSourceContextHolder {
private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/**
* 设置默认数据源
*/
public static String DEFAULT_DS = "master";
/**
*用于轮训计数
*/
private static int counter = 0;
/*
* 当使用ThreadLocal维护变量时,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> contextHolder = ThreadLocal.withInitial(() -> DataSourceKey.master.name());
/**
*用于在切换数据源时保证不会被其他线程修改
*/
public static Lock lock = new ReentrantLock();
/**
* 设置数据源
*/
public static void setDB(String dbType){
log.info("切换到{" + dbType + "}数据源");
contextHolder.set(dbType);
}
/**
* 得到数据源
*
*/
public static String getDB(){
return contextHolder.get();
}
/**
* 使用主数据源
*/
public static void useMasterDataSource() {
contextHolder.set(DataSourceKey.master.name());
}
/**
* 移除数据源
*/
public static void removeDB(){
contextHolder.remove();
}
/**
* The constant slaveDataSourceKeys.
*/
public static List<Object> slaveDataSourceKeys = new ArrayList<>();
/**
* 当使用只读数据源时通过轮循方式选择要使用的数据源
*/
public static String getSlaveDB(){
lock.lock();
try {
int datasourceKeyIndex = counter % slaveDataSourceKeys.size();
counter++;
return String.valueOf(slaveDataSourceKeys.get(datasourceKeyIndex));
} catch (Exception e) {
log.error(e.getMessage(), e);
e.printStackTrace();
return "master";
} finally {
lock.unlock();
}
}
}
5、获取数据源类
package com.yeqm.demo.sys;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
*@Description 多数据源的选择
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:48
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger log = LoggerFactory.getLogger(DynamicDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
log.info("Current DataSource is " + DynamicDataSourceContextHolder.getDB());
return DynamicDataSourceContextHolder.getDB();
}
}
6、Aop类
package com.yeqm.demo.sys;
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.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
*@Description 自定义注解 + AOP的方式实现数据源动态切换。
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:50
*/
@Aspect
@Component
public class DynamicDataSourceAspect {
private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
@Before("@annotation(TargetDataSource)")
public void beforeSwitchDB(JoinPoint joinPoint, TargetDataSource TargetDataSource){
//获取目标类的方法
Class<?> aClass = joinPoint.getTarget().getClass();
//获得访问的方法名
String methodName = joinPoint.getSignature().getName();
//得到方法的参数类型
Class[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getParameterTypes();
String dataSource = DynamicDataSourceContextHolder.DEFAULT_DS;
try {
Method method = aClass.getMethod(methodName, parameterTypes);
if(method.isAnnotationPresent(TargetDataSource.class)){
TargetDataSource db = method.getAnnotation(TargetDataSource.class);
//指定数据源
dataSource = db.value();
}else{
//轮训设置数据源
dataSource = DynamicDataSourceContextHolder.getSlaveDB();
}
} catch (NoSuchMethodException e) {
log.error(e.getMessage(), e);
}
//设置数据源
DynamicDataSourceContextHolder.setDB(dataSource);
}
@After("@annotation(TargetDataSource)")
public void afterSwitchDB(TargetDataSource TargetDataSource){
DynamicDataSourceContextHolder.removeDB();
}
}
7、application.yml文件
spring:
datasource:
druid:
# 数据库访问配置, 使用druid数据源 1
master:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://62.234.72.109:3306/test11
username: root
password: ******
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
# MySQL 8.x: com.mysql.cj.jdbc.Driver
# 数据库访问配置, 使用druid数据源 1
salve:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://62.234.72.109:3306/test12
username: root
password: ******
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
# MySQL 8.x: com.mysql.cj.jdbc.Driver
# 数据库访问配置, 使用druid数据源 1
migration:
# MySQL 8.x: com.mysql.cj.jdbc.Driver
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://62.234.72.109:3306/test13
username: root
password: ******
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
9、数据源配置类
package com.yeqm.demo.config;
import com.yeqm.demo.sys.DataSourceKey;
import com.yeqm.demo.sys.DynamicDataSource;
import com.yeqm.demo.sys.DynamicDataSourceContextHolder;
import org.mybatis.spring.SqlSessionFactoryBean;
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;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
/**
*@Description 数据源配置类
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:57
*/
@Configuration
public class DataSourceConfig {
/**
* 主数据
*
* @return data source
*/
@Bean("master")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.druid.master")
public DataSource master() {
return DataSourceBuilder.create().build();
}
/**
* 从数据库
*
* @return data source
*/
@Bean("salve")
@ConfigurationProperties(prefix ="spring.datasource.druid.salve")
public DataSource slave() {
return DataSourceBuilder.create().build();
}
/**
* 从数据库
*
* @return data source
*/
@Bean("migration")
@ConfigurationProperties(prefix ="spring.datasource.druid.migration")
public DataSource migration() {
return DataSourceBuilder.create().build();
}
/**
* 配置动态数据源
*
* @return
*/
@Bean("dynamicDataSource")
public DataSource dynamicDataSource() {
DynamicDataSource dynamicRoutingDataSource = new DynamicDataSource();
Map<Object, Object> dataSourceMap = new HashMap<>(4);
dataSourceMap.put(DataSourceKey.master.name(), master());
dataSourceMap.put(DataSourceKey.salve.name(), slave());
dataSourceMap.put(DataSourceKey.migration.name(), migration());
//设置默认的数据源
dynamicRoutingDataSource.setDefaultTargetDataSource(master());
// 多个slave数据源在此添加,自定义key,用于轮询
dataSourceMap.put(DataSourceKey.salve.name() + "1", slave());
//设置目标数据源
dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
//将数据源的key放在集合中判断是否正常
DynamicDataSourceContextHolder.slaveDataSourceKeys.addAll(dataSourceMap.keySet());
//实现负载均衡算法 将 Slave 数据源的 key 放在集合中,用于轮循
DynamicDataSourceContextHolder.slaveDataSourceKeys.addAll(dataSourceMap.keySet());
DynamicDataSourceContextHolder.slaveDataSourceKeys.remove(DataSourceKey.migration.name());
return dynamicRoutingDataSource;
}
/**
* 设置工厂类
*/
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean() {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dynamicDataSource());
//此处设置为了解决找不到mapper文件的问题
try {
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*Mapper.xml"));
} catch (IOException e) {
e.printStackTrace();
}
return sqlSessionFactoryBean;
}
/**
* 事物管理器
*/
@Bean("transactionManager")
public DataSourceTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
}
10、启动类
package com.yeqm.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
*@Description springboot入口类,此类需要在所有用到的package上层 exclude ={DataSourceAutoConfiguration.class}
*@Param 禁用springboot默认加载的application.properties单数据源配置
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 17:57
*/
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
11、测试
controller类
package com.yeqm.demo.controller;
import com.yeqm.demo.biz.TBdUserBiz;
import com.yeqm.demo.entity.TBdUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.List;
/**
* @description:
* @author: 骑牛小道士
* @date: 2019-05-30 11:48
*/
@Controller
public class TBdUserController {
@Autowired
private TBdUserBiz tBdUserBiz;
@RequestMapping("/999")
public List<TBdUser> selectList1 (TBdUser param){
List<TBdUser> list1 =tBdUserBiz.selectList1(param);
List<TBdUser> list2 =tBdUserBiz.selectList2(param);
List<TBdUser> list3 =tBdUserBiz.selectList3(param);
return list1;
}
}
biz 类
package com.yeqm.demo.biz;
import com.yeqm.demo.entity.TBdUser;
import com.yeqm.demo.mapper.TBdUserMapper;
import com.yeqm.demo.sys.TargetDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @description:
* @author: 骑牛小道士
* @date: 2019-05-30 11:49
*/
@Service
public class TBdUserBiz {
@Autowired
private TBdUserMapper tBdUserMapper;
@TargetDataSource("migration")
public List<TBdUser> selectList3(TBdUser param) {
List<TBdUser> list= tBdUserMapper.selectList(param);
System.out.println("migration");
System.out.println(list.get(0).getUserCode());
return list;
}
@TargetDataSource("salve")
public List<TBdUser> selectList2(TBdUser param) {
List<TBdUser> list= tBdUserMapper.selectList(param);
System.out.println("salve");
System.out.println(list.get(0).getUserCode());
return list;
}
@TargetDataSource("master")
public List<TBdUser> selectList1(TBdUser param) {
List<TBdUser> list= tBdUserMapper.selectList(param);
System.out.println("master");
System.out.println(list.get(0).getUserCode());
return list;
}
}
mapper 类
package com.yeqm.demo.mapper;
import com.yeqm.demo.entity.TBdUser;
import com.yeqm.utils.MyMapper;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
*@Description
*@Param
*@Return
*@Author 骑牛小道士
*@Date 2019/5/30
*@Time 18:04
*/
@Mapper
public interface TBdUserMapper extends MyMapper<TBdUser> {
List<TBdUser> selectList(TBdUser param);
}
xml 文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yeqm.demo.mapper.TBdUserMapper">
<resultMap id="BaseResultMap" type="com.yeqm.demo.entity.TBdUser">
<!--
WARNING - @mbg.generated
-->
<id column="row_id" jdbcType="INTEGER" property="rowId" />
<result column="user_code" jdbcType="VARCHAR" property="userCode" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="pass_word" jdbcType="VARCHAR" property="passWord" />
<result column="created_by" jdbcType="VARCHAR" property="createdBy" />
<result column="creation_date" jdbcType="TIMESTAMP" property="creationDate" />
<result column="last_updated_by" jdbcType="VARCHAR" property="lastUpdatedBy" />
<result column="last_update_date" jdbcType="TIMESTAMP" property="lastUpdateDate" />
</resultMap>
<select id="selectList" resultMap="BaseResultMap"
parameterType="com.yeqm.demo.entity.TBdUser">
select t.* from t_bd_user t
</select>
</mapper>
效果
2019-05-30 14:25:39.788 INFO 12228 --- [nio-9990-exec-1] c.y.d.s.DynamicDataSourceContextHolder : 切换到{master}数据源
2019-05-30 14:25:39.915 INFO 12228 --- [nio-9990-exec-1] com.yeqm.demo.sys.DynamicDataSource : Current DataSource is master
2019-05-30 14:25:39.917 INFO 12228 --- [nio-9990-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2019-05-30 14:25:42.189 INFO 12228 --- [nio-9990-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
master
zs
2019-05-30 14:25:43.361 INFO 12228 --- [nio-9990-exec-1] c.y.d.s.DynamicDataSourceContextHolder : 切换到{salve}数据源
2019-05-30 14:25:43.362 INFO 12228 --- [nio-9990-exec-1] com.yeqm.demo.sys.DynamicDataSource : Current DataSource is salve
2019-05-30 14:25:43.363 INFO 12228 --- [nio-9990-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2019-05-30 14:25:44.011 INFO 12228 --- [nio-9990-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
salve
we
2019-05-30 14:25:48.393 INFO 12228 --- [nio-9990-exec-1] c.y.d.s.DynamicDataSourceContextHolder : 切换到{migration}数据源
2019-05-30 14:25:48.394 INFO 12228 --- [nio-9990-exec-1] com.yeqm.demo.sys.DynamicDataSource : Current DataSource is migration
2019-05-30 14:25:48.394 INFO 12228 --- [nio-9990-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Starting...
2019-05-30 14:25:49.040 INFO 12228 --- [nio-9990-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Start completed.
migration
sd