1. 多数据源解决方案
- 通过中间件ShardingSphere、mycat、mysql-proxy、TDDL等。客户端直接连中间件,由中间件去分发操作哪个数据库
- 自己自定义实现
2. 操作数据库流程
操作数据库流程
- 客户端连接Mybatis等持久层框架
- Mybatis等持久层框架通过spring-data-jdbc获取DataSource
- DataSource通过getConnection获取数据库连接的JDBC Connection
- 由JDBC Connection对数据库进行操作
所以知道了上面的流程,我们可以根据不同的业务情况,提供不同的DataSource,动态的提供DataSource
3. Mysql数据准备
分别创建read_db.user和write_db.user,并向read_db.user写入数据
mysql> create database read_db;
Query OK, 1 row affected (0.14 sec)
mysql> create database write_db;
Query OK, 1 row affected (0.01 sec)
mysql> create table read_db.user (
-> id bigint(20) auto_increment not null comment '主键ID',
-> name varchar(30) null default null comment '姓名',
-> primary key (id)
-> );
Query OK, 0 rows affected, 1 warning (0.29 sec)
mysql>
mysql> insert into read_db.user (id, name) values
-> (1, 'read_name1'),
-> (2, 'read_name2'),
-> (3, 'read_name3'),
-> (4, 'read_name4'),
-> (5, 'read_name5');
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> create table write_db.user (
-> id bigint(20) auto_increment not null comment '主键ID',
-> name varchar(30) null default null comment '姓名',
-> primary key (id)
-> );
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql>
4. 通过AbstractRoutingDataSource自定义多JDBC数据源
4.1 AbstractRoutingDataSource作用和原理
我们可以通过org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource
来帮我们实现动态数据源的切换,而且AbstractRoutingDataSource实现了很多DataSource的方法,稳定性更好
AbstractRoutingDataSource需要给如下三个属性赋值:
- targetDataSources:需要动态切换的所有DataSource
- defaultTargetDataSource:默认DataSource
- resolvedDataSources:内部在afterPropertiesSet方法中自动从targetDataSources传递
动态选择DataSource的实现逻辑如下:
- 根据key从resolvedDataSources获取DataSource
- 如果没获取到,则获取defaultTargetDataSource
- 如果还是没获取到,则抛出异常
......省略部分......
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = this.determineCurrentLookupKey();
DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
} else {
return dataSource;
}
}
......省略部分......
而这个determineCurrentLookupKey是由我们自定义的DynamicDataSourceConfig进行设置的
所以我们的MyDynamicDataSourceConfig只需要继承AbstractRoutingDataSource,并完成上面的三个属性值设置,再做一些简单的配置即可
4.2 pom.xml依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.31</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.15</version>
</dependency>
<!-- 支持spring 2.5.3 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<!-- AOP使用 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
4.3 AbstractRoutingDataSource自定义多数据源实现
4.3.1 application.properties配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 用于读的数据库
spring.datasource.datasource1.url=jdbc:mysql://192.168.28.12:3306/read_db
spring.datasource.datasource1.username=root
spring.datasource.datasource1.password=Root_123
spring.datasource.datasource1.driver-class-name=com.mysql.cj.jdbc.Driver
# 用于写的数据库
spring.datasource.datasource2.url=jdbc:mysql://192.168.28.12:3306/write_db
spring.datasource.datasource2.username=root
spring.datasource.datasource2.password=Root_123
spring.datasource.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver
4.3.2 多个DataSource实现
实现功能:
- 将spring.datasource.datasource1开头的配置,绑定到name = datasource1的DataSource组件上
- 将spring.datasource.datasource2开头的配置,绑定到name = datasource2的DataSource组件上
package com.hh.springboottest.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
/**
* @Author 贺欢
* @Date 2022/11/24
* @Description
*/
@Configuration
public class MyDataSourceConfig {
@ConfigurationProperties(prefix = "spring.datasource.datasource1")
// 向IOC容器添加name = dataSource1的DataSource
@Bean
public DataSource dataSource1() {
DruidDataSource druidDataSource = new DruidDataSource();
return druidDataSource;
}
@ConfigurationProperties(prefix = "spring.datasource.datasource2")
// 向IOC容器添加name = dataSource2的DataSource
@Bean
public DataSource dataSource2() {
DruidDataSource druidDataSource = new DruidDataSource();
return druidDataSource;
}
}
4.3.3 动态数据源DynamicDataSource的实现
不使用AbstractRoutingDataSource。implement DataSource, InitializingBean说明:
- 在afterPropertiesSet方法中,初始化readOrWrite的值
- 重写getConnection方法,根据readOrWrite的值的不同,获取不同的dataSource返回Connection
- 重写DataSource的抽象方法。方法的返回对象测试时返回null等
继承AbstractRoutingDataSource实现的功能:
- 通过determineCurrentLookupKey方法获取当前读取标识
- 通过determineCurrentLookupKey方法设置所有动态切换的DataSource和默认的DataSource
package com.hh.springboottest.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/*
不使用AbstractRoutingDataSource。implement DataSource, InitializingBean说明:
1. 在afterPropertiesSet方法中,初始化readOrWrite的值
2. 重写getConnection方法,根据readOrWrite的值的不同,获取不同的dataSource返回Connection
3. 重写DataSource的抽象方法。方法的返回对象测试时返回null等
*/
@Component
// 当前IOC容器由dataSource1、dataSource2、当前DataSource
// Mapper获取DataSource时,优先获取当前DataSource
@Primary
public class MyDynamicDataSourceConfig extends AbstractRoutingDataSource {
// 用于存放读写标识。ThreadLocal能保证多线程并发安全
public static ThreadLocal<String> readOrWrite = new ThreadLocal<>();
// 从IOC容器获取name = dataSource1的DataSource
@Autowired
DataSource dataSource1;
// 从IOC容器获取name = dataSource2的DataSource
@Autowired
DataSource dataSource2;
// 返回当前的读写标识
@Override
protected Object determineCurrentLookupKey() {
return readOrWrite.get();
}
// 初始化MyDynamicDataSourceConfig后,会调用该方法进行各种属性值的设置
@Override
public void afterPropertiesSet() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("r", dataSource1);
targetDataSources.put("w", dataSource2);
super.setTargetDataSources(targetDataSources);
super.setDefaultTargetDataSource(dataSource1);
super.afterPropertiesSet();
}
}
4.4 使用AOP基于注解实现动态数据源切换
4.4.1 动态数据源注解
说明:
- 声明一个注解:MYDS,参数为字符串,用于指定datasource
- @Target({ElementType.TYPE,ElementType.METHOD}):作用于类上和方法上
- @Retention(RetentionPolicy.RUNTIME):编译后会在Class文件中生成注解,运行时会通过反射进行加载。还有参数SOURCE: 编译后不会在Class文件中;参数CLASS: 编译后会在Class文件中生成注解,运行时不会通过反射进行加载
package com.hh.springboottest.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface MYDS {
// 设置默认值
String value() default "r";
}
4.4.2 AOP实现
说明:
- 对MYDS注解设置的datasource进行拦截,然后设置到MyDynamicDataSourceConfig中
- within指定要拦截的类,execution指定要拦截的方法
- @annotation指定拦截的注解
- 环绕通知包含了前置
package com.hh.springboottest.aspect;
import com.hh.springboottest.annotation.MYDS;
import com.hh.springboottest.config.MyDynamicDataSourceConfig;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;
@Component
@Aspect
public class DynamicDataSourceAspect {
// 前置
@Before("within(com.hh.springboottest.service.impl.*) && @annotation(myds)")
public void before(JoinPoint point, MYDS myds) {
String readOrWrite = myds.value();
MyDynamicDataSourceConfig.readOrWrite.set(readOrWrite);
System.out.println(readOrWrite);
}
// 环绕通知
}
4.5 动态数据源测试
4.5.1 创建User类
package com.hh.springboottest.myController;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class User {
private Long id;
private String name;
}
4.5.2 Mapper接口实现
package com.hh.springboottest.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hh.springboottest.myController.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
4.5.3 Service实现
Service接口实现
package com.hh.springboottest.service;
import com.hh.springboottest.myController.User;
public interface UserService {
public User getUser(Long id);
public void saveUser(User user);
}
ServiceImpl实现类
package com.hh.springboottest.service.impl;
import com.hh.springboottest.mapper.UserMapper;
import com.hh.springboottest.myController.User;
import com.hh.springboottest.service.UserService;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService {
@Autowired
UserMapper userMapper;
@MYDS("r")
public User getUser(Long id) {
return userMapper.selectById(id);
}
@MYDS("w")
public void saveUser(User user) {
userMapper.insert(user);
}
}
4.5.4 测试
package com.hh.springboottest;
import com.hh.springboottest.config.MyDynamicDataSourceConfig;
import com.hh.springboottest.myController.User;
import com.hh.springboottest.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@Slf4j
@SpringBootTest
// 开启AOP功能
@EnableAspectJAutoProxy
public class MyApplicationTest {
@Autowired
UserService userService;
@Test
public void readTest() {
// 不使用AOP基于注解,手动切换数据源
// MyDynamicDataSourceConfig.readOrWrite.set("r");
User user = userService.getById(1);
log.info("获取到的用户为:{}", user);
}
@Test
public void writeTest() {
// 不使用AOP基于注解,手动切换数据源
// MyDynamicDataSourceConfig.readOrWrite.set("w");
User user = new User(1L, "write_name1");
userService.save(user);
}
}
运行测试类,结果如下:
r
2022-11-24 06:22:31.082 INFO 33932 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2022-11-24 06:22:31.082 INFO 33932 --- [ main] com.hh.springboottest.MyApplicationTest : 获取到的用户为:User(id=1, name=read_name1)
2022-11-24 06:22:31.126 INFO 33932 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource : {dataSource-0} closing ...
2022-11-24 06:22:31.126 INFO 33932 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} closing ...
2022-11-24 06:22:31.131 INFO 33932 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} closed
同时查看write_db.user表,数据如下:
mysql> select * from write_db.user;
+----+-------------+
| id | name |
+----+-------------+
| 1 | write_name1 |
+----+-------------+
1 row in set (0.10 sec)
mysql>
4.6 其他方式
我们这里实现的,是使用AOP,适合复杂业务读写多数据源场景。还有另一种通过Mybatis插件的方式,适合读写分离业务