我们在项目中，有时候会碰到多个数据源切换的场景，这里从零开始说一下步骤！！也是做一个笔记备忘。
准备工作
事先准备好一个能走通curd的springboot+mybatis的web项目,我的结构如下图
基本代码和之前一样，我这里贴一下
- 新建两个库 biubiu(主) 和 biubiu_slave(从)，新建一个tb_user表
![4JU0G4.png]()
1 | SET NAMES utf8mb4; |
UserEntity 实体
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
public class UserEntity implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键id
*/
private Integer userId;
/**
* 名称
*/
private String name;
/**
* 名称
*/
private String nickname;
/**
* 年龄
*/
private Integer age;
/**
* 头像
*/
private String avatar;
/**
* 电话
*/
private String phone;
/**
* 地址
*/
private String address;
/**
* 备注
*/
private String remark;
/**
* 创建时间
*/
private Date createdTime;
/**
* 更新时间
*/
private Date updatedTime;
}UserMapper
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
50public interface UserMapper {
/**
* 新增
*
* @author biubiu
* @date 2021/08/08
**/
int insert(UserEntity userEntity);
/**
* 刪除
*
* @author biubiu
* @date 2021/08/08
**/
int delete(int id);
/**
* 更新
*
* @author biubiu
* @date 2021/08/08
**/
int update(UserEntity userEntity);
/**
* 查询 根据主键 id 查询
*
* @author biubiu
* @date 2021/08/08
**/
UserEntity load(int id);
/**
* 查询 分页查询
*
* @author biubiu
* @date 2021/08/08
**/
List<UserEntity> pageList(int pageNum, int pageSize);
/**
* 查询 分页查询 count
*
* @author biubiu
* @date 2021/08/08
**/
int pageListCount(int pageNum, int pageSize);
}UserMapper.xml
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
<mapper namespace="com.biubiu.multidatabase.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.biubiu.multidatabase.entity.UserEntity" >
<result column="user_id" property="userId" />
<result column="name" property="name" />
<result column="nickname" property="nickname" />
<result column="age" property="age" />
<result column="avatar" property="avatar" />
<result column="phone" property="phone" />
<result column="address" property="address" />
<result column="remark" property="remark" />
<result column="created_time" property="createdTime" />
<result column="updated_time" property="updatedTime" />
</resultMap>
<sql id="Base_Column_List">
user_id,
name,
nickname,
age,
avatar,
phone,
address,
remark,
created_time,
updated_time
</sql>
<insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id"
parameterType="com.biubiu.multidatabase.entity.UserEntity">
INSERT INTO tb_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="null != userId and '' != userId">
user_id,
</if>
<if test="null != name and '' != name">
name,
</if>
<if test="null != nickname and '' != nickname">
nickname,
</if>
<if test="null != age and '' != age">
age,
</if>
<if test="null != avatar and '' != avatar">
avatar,
</if>
<if test="null != phone and '' != phone">
phone,
</if>
<if test="null != address and '' != address">
address,
</if>
<if test="null != remark and '' != remark">
remark,
</if>
<if test="null != createdTime and '' != createdTime">
created_time,
</if>
<if test="null != updatedTime and '' != updatedTime">
updated_time
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="null != userId and '' != userId">
#{userId},
</if>
<if test="null != name and '' != name">
#{name},
</if>
<if test="null != nickname and '' != nickname">
#{nickname},
</if>
<if test="null != age and '' != age">
#{age},
</if>
<if test="null != avatar and '' != avatar">
#{avatar},
</if>
<if test="null != phone and '' != phone">
#{phone},
</if>
<if test="null != address and '' != address">
#{address},
</if>
<if test="null != remark and '' != remark">
#{remark},
</if>
<if test="null != createdTime and '' != createdTime">
#{createdTime},
</if>
<if test="null != updatedTime and '' != updatedTime">
#{updatedTime}
</if>
</trim>
</insert>
<delete id="delete" >
DELETE FROM tb_user
WHERE user_id = #{id}
</delete>
<update id="update" parameterType="com.biubiu.multidatabase.entity.UserEntity">
UPDATE tb_user
<set>
<if test="null != userId and '' != userId">user_user_id = #{userId},</if>
<if test="null != name and '' != name">name = #{name},</if>
<if test="null != nickname and '' != nickname">nickname = #{nickname},</if>
<if test="null != age and '' != age">age = #{age},</if>
<if test="null != avatar and '' != avatar">avatar = #{avatar},</if>
<if test="null != phone and '' != phone">phone = #{phone},</if>
<if test="null != address and '' != address">address = #{address},</if>
<if test="null != remark and '' != remark">remark = #{remark},</if>
<if test="null != createdTime and '' != createdTime">created_time = #{createdTime},</if>
<if test="null != updatedTime and '' != updatedTime">updated_time = #{updatedTime}</if>
</set>
WHERE user_id = #{id}
</update>
<select id="load" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM tb_user
WHERE user_id = #{id}
</select>
<select id="pageList" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM tb_user
LIMIT #{pageNum}, #{pageSize}
</select>
<select id="pageListCount" resultType="java.lang.Integer">
SELECT count(1)
FROM tb_user
</select>
</mapper>service层
UserService
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
26public interface UserService {
/**
* 新增
*/
public Object insert(UserEntity tbUserEntity);
/**
* 删除
*/
public ReturnT<String> delete(int id);
/**
* 更新
*/
public ReturnT<String> update(UserEntity tbUserEntity);
/**
* 根据主键 id 查询
*/
public UserEntity load(int id);
/**
* 分页查询
*/
public Map<String, Object> pageList(int offset, int pagesize);
}UserServiceImpl
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
public class UserServiceImpl implements UserService {
private UserMapper userMapper;
public Object insert(UserEntity tbUserEntity) {
// valid
if (tbUserEntity == null) {
return ReturnT.error("必要参数缺失");
}
userMapper.insert(tbUserEntity);
return ReturnT.success();
}
public ReturnT<String> delete(int id) {
int ret = userMapper.delete(id);
return ret > 0 ? ReturnT.success() : ReturnT.error();
}
public ReturnT<String> update(UserEntity tbUserEntity) {
int ret = userMapper.update(tbUserEntity);
return ret > 0 ? ReturnT.success() : ReturnT.error();
}
public UserEntity load(int id) {
return userMapper.load(id);
}
public Map<String, Object> pageList(int pageNum, int pageSize) {
List<UserEntity> pageList = userMapper.pageList(pageNum, pageSize);
int totalCount = userMapper.pageListCount(pageNum, pageSize);
// result
Map<String, Object> result = new HashMap<String, Object>();
result.put("pageList", pageList);
result.put("totalCount", totalCount);
return result;
}
}
controller
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
57
58
59
60
61
62
public class UserController {
private UserService userService;
/**
* 新增
*
* @author biubiu
* @date 2021/08/08
**/
public Object insert(UserEntity tbUser) {
return userService.insert(tbUser);
}
/**
* 刪除
*
* @author biubiu
* @date 2021/08/08
**/
public ReturnT<String> delete(int id) {
return userService.delete(id);
}
/**
* 更新
*
* @author biubiu
* @date 2021/08/08
**/
public ReturnT<String> update(UserEntity tbUser) {
return userService.update(tbUser);
}
/**
* 查询 根据主键 id 查询
*
* @author biubiu
* @date 2021/08/08
**/
public Object load(int id) {
return userService.load(id);
}
/**
* 查询 分页查询
*
* @author biubiu
* @date 2021/08/08
**/
public Map<String, Object> pageList( int pageNum,
int pageSize) {
return userService.pageList(pageNum, pageSize);
}
}统一返回实体 common ReturnT
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
36public class ReturnT<T> implements Serializable {
private static final long serialVersionUID = 1L;
private int code;
private String message;
private T data;
private ReturnT(int code, String message, T data) {
this.code = code;
this.message = message;
this.data = data;
}
public static <T> ReturnT<T> success(T data) {
return new ReturnT<>(0, "success", data);
}
public static <T> ReturnT<T> success() {
return success(null);
}
public static <T> ReturnT<T> error(int code, String message) {
return new ReturnT<>(code, message, null);
}
public static <T> ReturnT<T> error(String message) {
return error(-1, message);
}
public static <T> ReturnT<T> error() {
return error("error");
}
}配置
Mapper接口扫描
1
2
3
4
public class MyBatisConfig {
}数据源配置
1
2
3
4
5
6
7
8
9
10
11
12spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/biubiu?useUnicode=true&characterEncoding=UTF-8&useSSL=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:mapper/*.xmlpom.xml
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
<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">
<parent>
<artifactId>springboot-demo</artifactId>
<groupId>com.biubiu</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>springboot-multidatabase-demo</artifactId>
<properties>
<java.version>1.8</java.version>
<mybatis.version>2.2.0</mybatis.version>
<fastjson.version>1.2.76</fastjson.version>
<druid.version>1.2.6</druid.version>
</properties>
<dependencies>
<!--web启动器-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 添加AOP坐标 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!--MyBatis框架-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!--MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--Druid数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!--lombok工具-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- 测试没问题
![4JdUCF.png]()
多数据源切换
我们目前已经有一个基础项目，接下来正式开始进入正题。所谓多数据源切换，无非就是对Druid，HikriCP或者其他数据源上做手脚
废话不多说，先把我们的数据库连接字符串配上去
- 配置主从数据库连接 application.yml
1 | spring: |
- 把配置读到Java里，初始化数据源
- DataSourceName 数据源名称
1
2
3
4
5
6
7
8public class DataSourceName {
private DataSourceName(){}
public static final String MASTER = "biubiu";
public static final String SLAVE = "biubiu_slave";
} - DynamicDataSource 动态数据源基类
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
27public 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();
}
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();
}
} - DynamicDataSourceConfig 多数据源配置类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public class DynamicDataSourceConfig {
public DataSource masterDataSource() throws SQLException {
return DruidDataSourceBuilder.create().build();
}
public DataSource slaveDataSource() {
return DruidDataSourceBuilder.create().build();
}
public DynamicDataSource dataSource(DataSource masterDataSource, DataSource slaveDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceName.MASTER, masterDataSource);
targetDataSources.put(DataSourceName.SLAVE, slaveDataSource);
return new DynamicDataSource(masterDataSource, targetDataSources);
}
}
到此我们数据源配置，初始化完成！！接下来，开始做切换，我们采用Aop方式做数据源切换，大致思路是 Aop拦截注解，通过注解指定的数据源做切换
- 自定义注解 AOP拦截
DataSource注解
1
2
3
4
5
6
public DataSource {
String name() default "";
}DataSourceAspect Aop拦截
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
public class DataSourceAspect {
public void dataSourcePointCut() {
}
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource dataSource = method.getAnnotation(DataSource.class);
if (dataSource == null) {
DynamicDataSource.setDataSource(DataSourceName.MASTER);
} else {
DynamicDataSource.setDataSource(dataSource.name());
}
try {
return point.proceed();
} finally {
DynamicDataSource.clearDataSource();
}
}
}
- 开始使用
在Controller/Service/Mapper里可以都可以试试1
2
3
4
5
6
7
8
9
10
11
public Object loadMaster(int id) {
return userService.load(id);
}hex
public Object loadSlave(int id) {
return userService.load(id);
}
结论
