SpringBoot+MyBatisPlus+AOP实现多数据源切换(经典)

桃扇骨 2024-02-22 09:55 28阅读 0赞

1、背景  
在实际开发过程中，我们可能会出现需要用到多数据源的情况，就是应用中可能需要根据场景的不同，对不同的数据库进行操作，此时需要进行多数据源的配置和开发了，在网上看到了很多案例，实现方式各不一样，我这里给大家梳理出来的实现方案是基于AOP+自定义注解实现的，为什么这里介绍这种方案呢？因为这种方案使用起来非常方便快捷，可扩展性和可维护性高

2、环境准备  
https://gitee.com/colinWu\_java/spring-boot-base.git

大家可以先把上面这个这个项目拉下来，我会基于这个主干项目之上进行开发

3、编码实战  
3.1、建库脚本  
我们现在做多数据源的测试，所以我在本地新建了两个数据库，分别取名叫做test1和test2，两个库新建同一张表，叫做t\_user，建表语句如下：

DROP TABLE IF EXISTS \`t\_user\`;  
CREATE TABLE \`t\_user\` (  
\`id\` int(11) NOT NULL AUTO\_INCREMENT COMMENT '主键ID',  
\`name\` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL COMMENT '姓名',  
\`age\` int(11) NULL DEFAULT NULL COMMENT '年龄',  
\`phone\` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL COMMENT '手机号',  
PRIMARY KEY (\`id\`) USING BTREE  
) ENGINE = InnoDB AUTO\_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4\_general\_ci COMMENT = '用户表' ROW\_FORMAT = Dynamic;  
1  
2  
3  
4  
5  
6  
7  
8  
3.2、pom依赖  
主要是需要导入aop的依赖

<?xml version="1.0" encoding="UTF-8"?> 
<project xmlns="http://maven.apache.org/POM/4.0.0" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 
<modelVersion>4.0.0</modelVersion>

<groupId>org.example</groupId> 
<artifactId>SpringBootBase</artifactId> 
<version>1.0-SNAPSHOT</version>

<parent> 
<groupId>org.springframework.boot</groupId> 
<artifactId>spring-boot-starter-parent</artifactId> 
<version>2.0.5.RELEASE</version> 
</parent>

<dependencies> 
 
<dependency> 
<groupId>org.springframework.boot</groupId> 
<artifactId>spring-boot-starter-web</artifactId> 
</dependency>

<dependency> 
<groupId>junit</groupId> 
<artifactId>junit</artifactId> 
<scope>test</scope> 
</dependency>

<dependency> 
<groupId>org.springframework.boot</groupId> 
<artifactId>spring-boot-starter-test</artifactId> 
<scope>test</scope> 
</dependency>

<dependency> 
<groupId>com.baomidou</groupId> 
<artifactId>mybatis-plus-boot-starter</artifactId> 
<version>3.0.5</version> 
</dependency>

<dependency> 
<groupId>mysql</groupId> 
<artifactId>mysql-connector-java</artifactId> 
</dependency>

<dependency> 
<groupId>com.alibaba</groupId> 
<artifactId>druid-spring-boot-starter</artifactId> 
<version>1.1.18</version> 
</dependency>

<dependency> 
<groupId>org.apache.velocity</groupId> 
<artifactId>velocity-engine-core</artifactId> 
<version>2.0</version> 
</dependency>

<dependency> 
<groupId>org.projectlombok</groupId> 
<artifactId>lombok</artifactId> 
</dependency>

<dependency> 
<groupId>com.alibaba</groupId> 
<artifactId>fastjson</artifactId> 
<version>1.2.50</version> 
</dependency>

<dependency> 
<groupId>org.springframework.boot</groupId> 
<artifactId>spring-boot-starter-aop</artifactId> 
</dependency>

</dependencies>

<build> 
<plugins> 
<plugin> 
<groupId>org.springframework.boot</groupId> 
<artifactId>spring-boot-maven-plugin</artifactId> 
<version>2.3.5.RELEASE</version> 
</plugin> 
</plugins> 
<finalName>SpringBootBase</finalName> 
</build>

</project> 
 
3.3、yml配置 
server: 
port: 8001 
spring: 
\#配置数据库信息 
datasource: 
druid: \# 全局druid参数 
\# 连接池的配置信息 
db1: 
url: jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=UTF-8&useUnicode=true&serverTimezone=UTC&useSSL=false 
username: root 
password: 123456 
db2: 
url: jdbc:mysql://127.0.0.1:3306/test2?characterEncoding=UTF-8&useUnicode=true&serverTimezone=UTC&useSSL=false 
username: root 
password: 123456 
driver-class-name: com.mysql.jdbc.Driver 
\# 初始化大小，最小，最大 
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 
stat-view-servlet: 
enabled: true 
\#网页访问地址：http://localhost:8001/druid/login.html 
url-pattern: /druid/\* 
login-username: admin 
login-password: 123456 
application: 
name: SpringBootBase \#服务名

\#MyBatis-Plus相关配置  
mybatis-plus:  
\#指定Mapper.xml路径，如果与Mapper路径相同的话，可省略  
mapper-locations: classpath:org/wujiangbo/mapper/\*Mapper.xml  
configuration:  
map-underscore-to-camel-case: true \#开启驼峰大小写自动转换  
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl \#开启控制台sql输出  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
3.4、实体类  
package org.wujiangbo.domain;

import com.baomidou.mybatisplus.annotation.IdType;  
import com.baomidou.mybatisplus.annotation.TableId;  
import com.baomidou.mybatisplus.annotation.TableName;  
import com.baomidou.mybatisplus.extension.activerecord.Model;  
import lombok.AllArgsConstructor;  
import lombok.Data;  
import lombok.NoArgsConstructor;  
import lombok.ToString;  
import java.io.Serializable;

/\*\* 
\* 
\* 用户表 
\* 
\* 
\* @author 波波老师(weixin：javabobo0513) 
\*/ 
@TableName("t\_user") 
@ToString 
@Data 
@NoArgsConstructor 
@AllArgsConstructor 
public class User extends Model<User> \{

private static final long serialVersionUID = 1L;

/\*\*  
\* 主键ID  
\*/  
@TableId(value = "id", type = IdType.AUTO)  
private Integer id;  
/\*\*  
\* 姓名  
\*/  
private String name;  
/\*\*  
\* 年龄  
\*/  
private Integer age;  
/\*\*  
\* 手机号  
\*/  
private String phone;

@Override  
protected Serializable pkVal() \{  
return this.id;  
\}  
\}  
  
3.5、controller层  
package org.wujiangbo.controller;

import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.web.bind.annotation.PostMapping;  
import org.springframework.web.bind.annotation.RequestBody;  
import org.springframework.web.bind.annotation.RequestMapping;  
import org.springframework.web.bind.annotation.RestController;  
import org.wujiangbo.annotation.DataSource;  
import org.wujiangbo.domain.User;  
import org.wujiangbo.result.JSONResult;  
import org.wujiangbo.service.IUserService;

/\*\*  
\* @desc UserController  
\* @author 波波老师(weixin：javabobo0513)  
\*/  
@RestController  
@RequestMapping("/user")  
public class UserController \{

@Autowired  
public IUserService userService;

/\*\*  
\* 保存数据到ds1数据源中，可以不写，那就是默认选择数据源1  
\*/  
@PostMapping(value="/saveDb1")  
@DataSource(name = "db1")  
public JSONResult saveDb1(@RequestBody User user)\{  
userService.saveUser1(user);  
return JSONResult.success();  
\}

/\*\*  
\* 保存数据到ds2数据源中  
\*/  
@PostMapping(value="/saveDb2")  
@DataSource(name = "db2")  
public JSONResult saveDb2(@RequestBody User user)\{  
userService.saveUser2(user);  
return JSONResult.success();  
\}

/\*\*  
\* 从ds1数据源中查询数据  
\*/  
@PostMapping(value="/queryDb1")  
@DataSource(name = "db1")  
public JSONResult queryDb1()\{  
return JSONResult.success(userService.list(null));  
\}  
\}  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
3.6、service层  
接口：

package org.wujiangbo.service;

import com.baomidou.mybatisplus.extension.service.IService;  
import org.wujiangbo.domain.User;

/\*\* 
\* 
\* 用户表 服务类 
\* 
\* 
\* @author 波波老师(weixin：javabobo0513) 
\*/ 
public interface IUserService extends IService<User> \{ 
void saveUser1(User user);

void saveUser2(User user);  
\}  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
实现类：

package org.wujiangbo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;  
import org.springframework.stereotype.Service;  
import org.wujiangbo.domain.User;  
import org.wujiangbo.mapper.UserMapper;  
import org.wujiangbo.service.IUserService;

/\*\* 
\* 
\* 用户表 服务实现类 
\* 
\* 
\* @author 波波老师(weixin：javabobo0513) 
\* @since 2022-11-01 
\*/ 
@Service 
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService \{

@Override  
public void saveUser1(User user) \{  
super.save(user);  
\}

@Override  
public void saveUser2(User user) \{  
super.save(user);  
\}  
\}  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
3.7、mapper层  
package org.wujiangbo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;  
import org.wujiangbo.domain.User;

/\*\* 
\* 
\* 用户表 Mapper 接口 
\* 
\* 
\* @author 波波老师(weixin：javabobo0513) 
\*/ 
public interface UserMapper extends BaseMapper<User> \{ 
\} 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
3.8、自定义注解 
package org.wujiangbo.annotation;

import java.lang.annotation.\*;

/\*\*  
\* @desc 自定义注解：实现多数据源  
\* @author 波波老师(weixin：javabobo0513)  
\*/  
@Target(\{ElementType.TYPE,ElementType.METHOD\})  
@Retention(RetentionPolicy.RUNTIME)  
@Documented  
public @interface DataSource \{  
String name() default "";  
\}  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
3.9、切面类  
package org.wujiangbo.aspect;

import lombok.extern.slf4j.Slf4j;  
import org.aspectj.lang.ProceedingJoinPoint;  
import org.aspectj.lang.annotation.Around;  
import org.aspectj.lang.annotation.Aspect;  
import org.aspectj.lang.annotation.Pointcut;  
import org.aspectj.lang.reflect.MethodSignature;  
import org.springframework.core.Ordered;  
import org.springframework.stereotype.Component;  
import org.wujiangbo.annotation.DataSource;  
import org.wujiangbo.config.datasource.DynamicDataSource;  
import java.lang.reflect.Method;

/\*\*  
\* 切面处理类，处理多数据源相关功能  
\* @author 波波老师(weixin：javabobo0513)  
\*/  
@Slf4j  
@Aspect  
@Component  
public class DataSourceAspect implements Ordered \{

//定义切入点  
@Pointcut("@annotation(org.wujiangbo.annotation.DataSource)")  
public void pointCut() \{  
\}

//环绕通知  
@Around("pointCut()")  
public Object around(ProceedingJoinPoint point) throws Throwable \{  
MethodSignature signature = (MethodSignature) point.getSignature();  
Method method = signature.getMethod();  
DataSource ds = method.getAnnotation(DataSource.class);  
//开始设置数据源  
if (ds == null) \{  
//设置默认数据源  
DynamicDataSource.setDataSource("db1");  
log.info("set datasource is " + ds.name());  
\} else \{  
DynamicDataSource.setDataSource(ds.name());  
log.info("set datasource is " + ds.name());  
\}  
try \{  
return point.proceed();  
\} finally \{  
DynamicDataSource.clearDataSource();  
log.info("clean datasource");  
\}  
\}

@Override  
public int getOrder() \{  
return 1;  
\}  
\}  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
3.10、多数据源配置类  
package org.wujiangbo.config.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  
import javax.sql.DataSource;  
import java.util.Map;

/\*\* 
\* @desc 动态数据源 
\* @author 波波老师(weixin：javabobo0513) 
\*/ 
public class DynamicDataSource extends AbstractRoutingDataSource \{ 
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) \{ 
super.setDefaultTargetDataSource(defaultTargetDataSource); 
super.setTargetDataSources(targetDataSources); 
super.afterPropertiesSet(); 
\}

@Override  
protected Object determineCurrentLookupKey() \{  
return getDataSource();  
\}

public static void setDataSource(String dataSource) \{  
contextHolder.set(dataSource);  
\}

public static String getDataSource() \{  
return contextHolder.get();  
\}

public static void clearDataSource() \{  
contextHolder.remove();  
\}

\}  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
配置类：

package org.wujiangbo.config.datasource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;  
import org.springframework.boot.context.properties.ConfigurationProperties;  
import org.springframework.context.annotation.Bean;  
import org.springframework.context.annotation.Configuration;  
import org.springframework.context.annotation.Primary;  
import javax.sql.DataSource;  
import java.util.HashMap;  
import java.util.Map;

/\*\*  
\* @desc 动态数据源配置类  
\* @author 波波老师(weixin：javabobo0513)  
\*/  
@Configuration  
public class DynamicDataSourceConfig \{

@Bean  
@ConfigurationProperties("spring.datasource.druid.db1")  
public DataSource firstDataSource() \{  
return DruidDataSourceBuilder.create().build();  
\}

@Bean  
@ConfigurationProperties("spring.datasource.druid.db2")  
public DataSource secondDataSource() \{  
return DruidDataSourceBuilder.create().build();  
\}

@Bean 
@Primary 
public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) \{ 
Map<Object, Object> targetDataSources = new HashMap<>(); 
targetDataSources.put("db1", firstDataSource); 
targetDataSources.put("db2", secondDataSource); 
return new DynamicDataSource(firstDataSource, targetDataSources); 
\} 
\} 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
4、测试 
用postman工具分别访问接口：

http://localhost:8001/user/saveDb1  
http://localhost:8001/user/saveDb2

![3acc6829d0684d3191d0329c155496f8.png][]

![3b114a1f77b64c2ea978e60d697b650f.png][]

调用两次后，再去看数据库，就会发现test1和test2数据库就有数据了，测试成功

以上代码都已经提交到git中的【MultiDataSource1】分支中了

5、总结  
自定义注解+AOP这种方式实现多数据源的切换，体验起来非常丝滑，很方便  
希望对大家有点帮助，赶紧动手测试一下吧  
————————————————  
版权声明：本文为CSDN博主「小学生波波」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/wujiangbo520/article/details/127651949

[3acc6829d0684d3191d0329c155496f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/737d885967de47239dce8fcce25d9b06.png
[3b114a1f77b64c2ea978e60d697b650f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/8ea6d55faa1a48c997b83efd588b7ec7.png