动态数据源切换
通过一个注解实现动态数据源切换
- 将要用的数据源名字,记录在threadLocal中
- 通过AbstractRoutingDataSource,重写方法,将threadLocal中的数据源名字设置进去 。(当这个系统需要获取数据源的时候,会自动调用AbstractRoutingDataSource中的determineCurrentLookupKey()方法,获取要用的数据源名字)
- aop拦截注解
- 网页上,传递你要使用的数据源名字,aop拦截修改threaLocal中的值
- 多个aop调用顺序
// 创建DruidDataSource
DruidDataSourceFactory.createDataSource()
// 查找方法,类上的注解
AnnotationUtils.findAnnotation();
// @ConfigurationProperties @EnableConfigurationProperties
依赖
aop+druid+mysql+mybatis+web
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.9</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.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
配置文件
# 数据源配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
ds:
# 主库数据源
master:
url: jdbc:mysql://localhost:3306/test09?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled: false
url: jdbc:mysql://localhost:3306/test08?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
实体类+service+mapper
@Service
@DataSource("slave")
public class UserService {
@Autowired
UserMapper userMapper;
@DataSource("master")
public List<User> getAllUsers(){
return userMapper.getAllUsers();
}
}
@Mapper
public interface UserMapper {
@Select("select * from user")
List<User> getAllUsers();
}
public class User {
private Integer id;
private String name;
private String address;
}
将数据源名字写入threadLocal中:
public class DynamicDataSourceHolder {
private static ThreadLocal<String> threadLocal = new ThreadLocal<>();
public static String getDataSourceName(){
return threadLocal.get();
}
public static void setDataSourceName(String dataSourceName){
threadLocal.set(dataSourceName);
}
public static void removeDataSourceName(){
threadLocal.remove();
}
}
继承AbstractRoutingDataSource
,表名你要使用哪个数据源(从threadLocal中获取)
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.getDataSourceName();
}
}
注入你的数据库配置
@Configuration
public class DataSourceConfiguration {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.ds.master")
public DataSource dataSource1(){
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.ds.slave")
public DataSource dataSource2(){
return DruidDataSourceBuilder.create().build();
}
@Bean
DynamicDataSource dynamicDataSource(){
DynamicDataSource dynamicDataSource = new DynamicDataSource();
HashMap<Object, Object> map = new HashMap<>();
map.put("master",dataSource1());
map.put("slave",dataSource2());
dynamicDataSource.setTargetDataSources(map);
dynamicDataSource.setDefaultTargetDataSource(map.get("master"));
return dynamicDataSource;
}
/**
* 为什么要注入SqlSessionFactory: 我觉得是因为注入了多个数据源bean, 他不知道用哪个,要设置一下
* @return
*/
@Bean
SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dynamicDataSource());
return factoryBean.getObject();
}
}
写一个注解,拦截他,设置threadLocal值
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD,ElementType.TYPE})
public @interface DataSource {
String value() default "master";
}
@Aspect
@Component
public class DataSourceAspect {
@Pointcut("@annotation(com.cj.DataSource) || @within(com.cj.DataSource)")
public void pointcut(){}
@Before("pointcut()")
public void before(JoinPoint joinPoint){
// 获取注解
DataSource dataSource = getDataSource(joinPoint);
// 根据@DataSource拦截的,dataSource一定存在
String dataSourceName = dataSource.value();
DynamicDataSourceHolder.setDataSourceName(dataSourceName);
}
private DataSource getDataSource(JoinPoint joinPoint) {
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
Method method = signature.getMethod();
// 判断方法上有没有 @DataSource
DataSource annotation = AnnotationUtils.findAnnotation(method, DataSource.class);
if(annotation != null){
return annotation;
}
return AnnotationUtils.findAnnotation(signature.getDeclaringType(),DataSource.class);
}
@After("pointcut()")
public void after(){
DynamicDataSourceHolder.removeDataSourceName();
}
}
写法二:在AbstractRoutingDataSource的实现类中,直接设置数据源并注入spring
加载所有的数据源
@Component
@EnableConfigurationProperties(DruidProperties.class)
public class LoadDataSource {
@Autowired
DruidProperties druidProperties;
public Map<Object, Object> getAllDataSource() {
Map<String, Map<String, String>> ds = druidProperties.getDs();
// 要传入AbstractRoutingDataSource的map
HashMap<Object, Object> map = new HashMap<>();
try {
Set<String> keySet = ds.keySet();
for (String key : keySet) {
// DruidDataSourceBuilder.create().build()
// 创建 druidDataSource 核心属性
DruidDataSource dataSource = ((DruidDataSource) DruidDataSourceFactory.createDataSource(ds.get(key)));
// 设置 druidDataSource 其他属性
map.put(key, druidProperties.getDataSource(dataSource));
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
}
常量类:
public interface DataSourceType {
String DEFAULT_DATASOURCE_TYPE = "master";
String DS_SESSION_KEY = "dsType";
}
AbstractRoutingDataSource
实体类注入spring
@Component
public class DynamicDataSource extends AbstractRoutingDataSource {
public DynamicDataSource(LoadDataSource loadDataSource) {
// 设置所有的数据源
Map<Object, Object> allDataSource = loadDataSource.getAllDataSource();
super.setTargetDataSources(allDataSource);
// 设置默认的 那些没有@DataSource的也需要数据源
super.setDefaultTargetDataSource(allDataSource.get(DataSourceType.DEFAULT_DATASOURCE_TYPE));
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.getDataSourceName();
}
}
全局切面,切点是service方法…请求都会经过这个切面
@Component
@Aspect
@Order(10)
public class GlobalDataSourceAspect {
@Autowired
HttpSession httpSession;
@Pointcut("execution(* com.cj.service.*.*(..))")
public void pointcut(){}
@Around("pointcut()")
public Object around(ProceedingJoinPoint pjp){
DynamicDataSourceHolder.setDataSourceName((String)httpSession.getAttribute(DataSourceType.DS_SESSION_KEY));
try {
return pjp.proceed();
} catch (Throwable throwable) {
throwable.printStackTrace();
} finally {
DynamicDataSourceHolder.removeDataSourceName();
}
return null;
}
}
修改数据源接口:
@RestController
public class DataSourceController {
private static final Logger logger = LoggerFactory.getLogger(DataSourceController.class);
@Autowired
UserService userService;
/**
* 修改数据源 使用切面修改,,可以控制切面的顺序。直接修改可能会被另一个切面覆盖
* @param dsType
*/
@PostMapping("/dsType")
public void setDsType(String dsType, HttpSession httpSession){
// DynamicDataSourceHolder.setDataSourceName(dsType);
httpSession.setAttribute(DataSourceType.DS_SESSION_KEY,dsType);
logger.info("数据源切换为 {}",dsType);
}
@GetMapping("getAllUsers")
public List<User> getAllUsers(){
return userService.getAllUsers();
}
}
前端:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="jquery.min.js"></script>
</head>
<body>
<select name="" id="" onchange="dsChange(this.options[this.options.selectedIndex].value)">
<option value="master">master</option>
<option value="slave">slave</option>
</select>
<button onclick="loadAllUsers()">loadAllUsers</button>
<div id="result"></div>
<script>
function dsChange(value) {
$.post("/dsType",{dsType:value})
}
function loadAllUsers() {
$.get("/getAllUsers",function (data) {
$("#result").html(JSON.stringify(data))
})
}
</script>
</body>
</html>
遇到的问题:
DruidDataSourceBuilder.create().build()
创建druid数据源,为什么不能直接newDataSourceBuilder.create().build()
返回的是一个什么数据源,为什么配置文件中的url属性,要写成jdbc-url
- 注入多个数据源时,要注入
SqlSessionFactory
并指定你要使用的数据源 - 创建Druid数据源
DruidDataSourceFactory.createDataSource(map)
- 传入选中的option的值
git:https://github.com/water-kid/code/tree/master/dynamic-datasource00