前言
Springboot多数据源整合有许多显著的好处和作用，尤其在SaaS（软件及服务），还有以数据为驱动的决策和分析中非常重要。
一、多数据源是什么？
示例：多数据源是指在一个应用程序中配置和使用多个不同的数据库连接池，以便连接和操作多个数据库。这在需要从不同数据库中获取数据或将数据写入不同数据库的场景中非常有用。例如，一个应用程序可能需要从一个数据库中读取数据，同时将处理后的数据写入另一个数据库。
二、使用步骤
1.配置
其中使用的数据库连接池为druid连接池使用redis存储数据库的名称，用户名和密码
在这里测试使用（test）主库作为主库，在主库中维护一张数据库表。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| create table sys_ds_manage
(
id bigint auto_increment
primary key,
port_number varchar(255) not null comment '端口号',
port_ds_name varchar(255) not null comment '数据库名',
port_name_exegesis varchar(255) not null comment '数据库注释',
port_ds_username varchar(255) not null comment '数据库用户',
port_ds_password varchar(255) not null comment '数据库密码',
create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
status tinyint default 0 not null comment '0表示正常，1表示停用',
build_ds tinyint default 0 not null comment '0表示未建库，1表示已建库',
is_deleted tinyint default 0 not null comment '删除标记（0:可用 1:不可用）',
constraint ds_manage_pk2
unique (id)
)
comment '数据库表';
|
Maven导入：
dynamic： 多数据源配置
redis：做键值存储
druid：为数据库连接池
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.6.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.15</version>
</dependency>
|
yml配置：
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
| mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
spring:
redis:
host: localhost
port: 6379
database: 0
timeout: 1800000
password:
jedis:
pool:
max-active: 20
max-wait: -1
max-idle: 5
min-idle: 0
datasource:
druid:
statViewServlet:
enabled: true
login-username: admin
login-password: 123456
url-pattern: /druid/*
web-stat-filter:
enabled: true
session-stat-enable: true
session-stat-max-count: 1000
filter:
stat:
enabled: true
log-slow-sql: true
slow-sql-millis: 10
dynamic:
primary: master
strict: false
datasource:
master:
driver-class-name: com.mysql.cj.jdbc.Driver
username: ***
password: ***
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
type: com.alibaba.druid.pool.DruidDataSource
|
2.java代码实现
代码如下：
设置一个自定义注解，该注解标注的位置在(service实现类)serviceImpl的方法上
1
2
3
4
| @Target(value = ElementType.METHOD)
@Retention(value = RetentionPolicy.RUNTIME)
public @interface SaaS {
}
|
注解配置类如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| @Component
@Aspect
public class SaasAspect {
@Resource
private RedisTemplate<String ,Object> redisTemplate;
@Pointcut(value = "@annotation(com.demo.config.aop.saas.SaaS)")
private void sa(){}
@Before(value = "sa()")
public Object SaasAs(){
ServletRequestAttributes requestAttributes = (ServletRequestAttributes)
RequestContextHolder.getRequestAttributes();
if (Objects.isNull(requestAttributes)){
throw new OaException(ResultCodeEnum.FAIL);
}
HttpServletRequest request = requestAttributes.getRequest();
String keyToken = request.getSession().getServletContext().getAttribute("dsManageKeyToken").toString();
String dskey = Objects.requireNonNull(redisTemplate.opsForValue().get("dsKey:"+keyToken)).toString();
request.getSession().setAttribute("test",dskey);
return true;
}
}
|
创建一个初始化数据库连接工具类:
1
2
3
4
5
6
7
8
9
10
11
12
13
| public class DsManageUtil {
public static void chooseDsMange(String dsManage) {
ServletRequestAttributes servletRequestAttributes =
(ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
assert servletRequestAttributes != null;
HttpServletRequest request = servletRequestAttributes.getRequest();
request.getSession().removeAttribute("test");
request.getSession().setAttribute("test", dsManage);
}
}
|
初始化数据库连接工具
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
| public class InitDBUtil {
public static DruidDataSource getInitDBUtil() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
return dataSource;
}
public static Boolean createDataSourceUtil(String username, String password, String portNumber, String DBName) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String oldUrl = "jdbc:mysql://#{url}/";
String replaceUrl = oldUrl.replace("#{url}", portNumber);
Connection conn = DriverManager.getConnection(
replaceUrl,
username,
password);
Statement st = conn.createStatement();
String oldDataCraeteSql = "create database #{name}";
String oldDataUseSql = "use #{name}";
String replaceOldDataCraeteSql = oldDataCraeteSql.replace("#{name}", DBName);
st.executeUpdate(replaceOldDataCraeteSql);
String replaceOldDataUseSql = oldDataUseSql.replace("#{name}", DBName);
st.executeUpdate(replaceOldDataUseSql);
ScriptRunner scriptRunner = new ScriptRunner(conn);
scriptRunner.setStopOnError(true);
FileInputStream fileInputStream = new FileInputStream(
"admin/src/main/resources/***.sql");
InputStreamReader inputStreamReader = new InputStreamReader(fileInputStream);
scriptRunner.runScript(inputStreamReader);
inputStreamReader.close();
fileInputStream.close();
st.close();
conn.close();
return true;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Boolean getConnection(DsManageDTO dsManageDTO) {
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://" +
dsManageDTO.getIpNum() +
":" +
dsManageDTO.getPortNum() +
"/" +
dsManageDTO.getPortDsName() +
"?user=" +
dsManageDTO.getPortDsUsername() +
"&password=" +
dsManageDTO.getPortDsPassword();
conn = DriverManager.getConnection(url);
System.out.println("成功连接数据库！！！");
} catch (Exception ex1) {
throw new OaException(ResultCodeEnum.FAIL);
} finally {
assert conn != null;
try {
System.out.println("断开数据库连接");
conn.close();
} catch (SQLException e) {
log.error(e.toString());
}
}
return true;
}
}
|
多数据源配置如下：
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
| @Component
@Slf4j
public class DynamicDataSourceConfig {
@Resource
private DynamicRoutingDataSource dataSource;
@Resource
private DsManageService dsManageService;
@PostConstruct
public void LoadAllDB() {
try {
DruidDataSource druidDataSource=null;
List<DsManage> dsManageList = dsManageService.list(
new LambdaQueryWrapper<DsManage>()
.eq(DsManage::getStatus, 0));
for (DsManage dsManage : dsManageList) {
druidDataSource = InitDBUtil.getInitDBUtil();
druidDataSource.setUrl("jdbc:mysql://" + dsManage.getPortNumber() + "/" + dsManage.getPortDsName());
druidDataSource.setUsername(dsManage.getPortDsUsername());
druidDataSource.setPassword(dsManage.getPortDsPassword());
dataSource.addDataSource(dsManage.getPortDsName(), druidDataSource);
log.info(dsManage.getPortDsName()+">>>>>>>>>>连接成功");
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
|
Mapper接口如下
1
2
3
4
| @DS("test")
public interface DsManageMapper extends BaseMapper<DsManage> {
}
|
Service接口
1
2
3
4
5
6
7
8
9
10
| public interface DsManageService extends IService<DsManage> {
boolean delete(Long id);
Map<String,Object> selectByPage(Page<DsManage> page, SearchDsManageDTO searchDsManageDTO);
DsManageVO selectByOne(Long id);
Boolean initMySql(Long id);
}
|
serviceImpl实现类
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
| @Service("dsManageService")
@DSTransactional
public class DsManageServiceImpl extends ServiceImpl<DsManageMapper, DsManage> implements DsManageService {
@Resource
private EmailService emailService;
@Resource
private RedisTemplate<String,Object> redisTemplate;
@Override
public Map<String, Object> selectByPage(Page<DsManage> page, SearchDsManageDTO searchDsManageDTO) {
LambdaQueryWrapper<DsManage> wrapper = new LambdaQueryWrapper<>();
if (!Objects.isNull(searchDsManageDTO.getPortNameExegesis())) {
wrapper.like(DsManage::getPortNameExegesis,
searchDsManageDTO.getPortNameExegesis()
);
}
if (!Objects.equals(searchDsManageDTO.getCreateTimeBegin(),null)){
wrapper.ge(DsManage::getCreateTime,searchDsManageDTO.getCreateTimeBegin());
}
if (!Objects.equals(searchDsManageDTO.getCreateTimeEnd(),null)){
wrapper.le(DsManage::getCreateTime,searchDsManageDTO.getCreateTimeEnd());
}
wrapper.eq(DsManage::getIsDeleted, 0);
Page<DsManage> selectPage = baseMapper.selectPage(page, wrapper);
List<DsManage> dsManageList = selectPage.getRecords();
List<DsManageVO> dsManageVOList = new ArrayList<>();
for (DsManage dsManage : dsManageList) {
DsManageVO dsManageVO = getDsManageVO(dsManage);
dsManageVOList.add(dsManageVO);
}
Map<String, Object> map = new HashMap<>();
map.put("total", page.getTotal());
map.put("rows", dsManageVOList);
return map;
}
@Override
public DsManageVO selectByOne(Long id) {
DsManage dsManage = baseMapper.selectById(id);
return getDsManageVO(dsManage);
}
@Override
public boolean delete(Long id) {
return baseMapper.deleteById(id)>0;
}
@Override
public Boolean initMySql(Long id) {
DsManage dsManage = baseMapper.selectById(id);
if (Objects.equals(dsManage.getBuildDs(), 1)) {
return false;
}
dsManage.setBuildDs(1);
baseMapper.updateById(dsManage);
ServletRequestAttributes requestAttributes = (ServletRequestAttributes)
RequestContextHolder.getRequestAttributes();
assert requestAttributes != null;
String token = requestAttributes.getRequest().getHeader("token");
Object object = redisTemplate.opsForValue().get("user:" + token);
User user = JSONUtil.toBean(JSONUtil.toJsonStr(object), User.class);
EmailVo emailVo = new EmailVo();
emailVo.setFromMail(user.getEmail());
emailVo.setSubject("数据库建库操作");
emailVo.setText(dsManage.getPortDsName() + ">>>>>>>>>>>已经完成建立数据库和数据库初始化");
emailService.sendMail(emailVo);
return InitDBUtil.createDataSourceUtil(
dsManage.getPortDsUsername(),
dsManage.getPortDsPassword(),
dsManage.getPortNumber(),
dsManage.getPortDsName());
}
private static DsManageVO getDsManageVO(DsManage dsManage) {
DsManageVO dsManageVO = BeanUtil.copyProperties(dsManage, DsManageVO.class);
String dsPortDsName = dsManage.getPortNumber();
String[] split = dsPortDsName.split(":");
dsManageVO.setIpNum(split[0]);
dsManageVO.setPortNum(split[1]);
return dsManageVO;
}
}
|
数据库映射实体类
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
| @Data
@AllArgsConstructor
@NoArgsConstructor
public class DsManageVO implements Serializable {
private Long id;
private String ipNum;
private String portNum;
private String portDsName;
private String portNameExegesis;
private String portDsUsername;
private String portDsPassword;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date updateTime;
private Integer buildDs;
private Integer status;
}
|
前端传值实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| @Data
@AllArgsConstructor
@NoArgsConstructor
public class DsManageDTO implements Serializable {
private Integer id;
private String ipNum;
private String portNum;
private String portDsName;
private String portNameExegesis;
private String portDsUsername;
private String portDsPassword;
private Integer status;
private Integer buildDs;
}
|
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
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
| @RestController
@RequestMapping("dsManage")
@CrossOrigin
public class DsManageController{
@Resource
private DsManageService dsManageService;
@GetMapping()
public Result<?> selectAll(){
List<DsManage> dsManageList = dsManageService.list(new LambdaQueryWrapper<DsManage>()
.eq(DsManage::getIsDeleted, 0)
.eq(DsManage::getStatus,0)
);
return Result.ok(dsManageList);
}
@GetMapping("selectBuildAll")
public Result<?> selectBuildAll(){
List<DsManage> dsManageList = dsManageService.list(new LambdaQueryWrapper<DsManage>()
.eq(DsManage::getStatus, 0)
.eq(DsManage::getBuildDs, 1)
);
ArrayList<Object> list = new ArrayList<>();
for (DsManage dsManage : dsManageList) {
Map<String,Object> map=new HashMap<>();
map.put("text",dsManage.getPortNameExegesis());
map.put("value",dsManage.getPortDsName());
list.add(map);
}
return Result.ok(list);
}
@GetMapping("{id}")
@LogPoint(businessName = "根据数据库id查询当前用户数据",businessAction = "selectByOne")
public Result<?> selectByOne(@PathVariable("id") Long id){
DsManageVO dsManageVO=dsManageService.selectByOne(id);
return Result.ok(dsManageVO);
}
@PostMapping("/{pageNum}/{pageSize}")
@LogPoint(businessName = "分页查询数据库信息",businessAction = "selectByPage")
public Result<?> selectByPage(
@RequestBody SearchDsManageDTO searchDsManageDTO,
@PathVariable("pageNum")Long pageNum,
@PathVariable("pageSize")Long pageSize
){
Page<DsManage> page = new Page<>(pageNum, pageSize);
Map<String,Object> dsManageMap=dsManageService.selectByPage(page, searchDsManageDTO);
return Result.ok(dsManageMap);
}
@PostMapping()
@LogPoint(businessName = "增加数据库信息",businessAction = "save")
public Result<?> save(@RequestBody DsManageDTO dsManageDTO){
DsManage dsManage = BeanCopyDsManage(dsManageDTO);
List<String> portDsNameList = dsManageService
.list(new LambdaQueryWrapper<DsManage>()
.eq(DsManage::getIsDeleted, 0))
.stream()
.map(DsManage::getPortDsName)
.collect(Collectors.toList());
if (portDsNameList.contains(dsManage.getPortDsName())){
throw new OaException(ResultCodeEnum.DSMANAGE_ON);
}
return dsManageService.save(dsManage)?
Result.ok():
Result.fail();
}
@PutMapping("updateStatus")
@LogPoint(businessName = "修改数据库状态",businessAction = "updateStatus")
public Result<?> updateStatus(@RequestBody DsManageDTO dsManageDTO){
DsManage dsManage = BeanUtil.copyProperties(dsManageDTO, DsManage.class);
return dsManageService.updateById(dsManage)?
Result.ok():
Result.fail();
}
@PutMapping()
@LogPoint(businessName = "修改数据库信息",businessAction = "update")
public Result<?> update(@RequestBody DsManageDTO dsManageDTO){
DsManage dsManage = BeanCopyDsManage(dsManageDTO);
return dsManageService.updateById(dsManage)?
Result.ok():
Result.fail();
}
@DeleteMapping("/{id}")
@LogPoint(businessName = "逻辑删除数据库表的信息",businessAction = "remove")
public Result<?> remove(@PathVariable("id") Long id){
return dsManageService.delete(id)?
Result.ok():
Result.fail();
}
private static DsManage BeanCopyDsManage(DsManageDTO dsManageDTO) {
DsManage dsManage = BeanUtil.copyProperties(dsManageDTO, DsManage.class);
String ipNum = dsManageDTO.getIpNum();
String portNum = dsManageDTO.getPortNum();
dsManage.setPortNumber(ipNum+":"+portNum);
return dsManage;
}
@GetMapping("/init/{id}")
public Result<?> initMySql(@PathVariable("id") Long id){
return dsManageService.initMySql(id)?
Result.ok():
Result.fail();
}
@PostMapping("testConnection")
@LogPoint(businessName = "测试数据库连接",businessAction = "testConnection")
public Result<?> testConnection(@RequestBody DsManageDTO dsManageDTO){
return InitDBUtil.getConnection(dsManageDTO)?
Result.ok():
Result.fail();
}
}
|
总结
以上就是今天要讲的内容，本文解决了多数据源前端动态切换问题。
不需要封装在代码中只需要配置在主数据库中的一张表中就可以实现动态切换。
