目录
一、简介
在一些中小企业里,由于业务的原因,可能数据会存放在不同的数据库中,比如存交易的库,存基础信息的库,有时候,交易记录的存储只是存了基础信息的一个id或者编号,比如商品id,但是实际在交易列表查询的时候又希望查出具体的商品信息,这是一种多数据源的情况,我们也来看看怎么使用多数据源。本文中Spring Boot 版本为2.5.2,JDK环境为 1.8,Druid 版本为1.2.6,mysql 版本为8.0.26,本文中使用到的依赖如下:
二、maven依赖
pom.xml
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.alian</groupId>
<artifactId>datasource</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>datasource</name>
<description>Spring Boot多数据源</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.5.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>2.5.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>2.5.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
三、配置
3.1、数据源操作类
MultiDataSourceContextHolder.java
package com.alian.datasource.common;
/**
* 多数据源类型 操作
*/
public class MultiDataSourceContextHolder {
/**
* ThreadLocal 线程局部变量,多线程环境环境下每个线程一份
*/
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
/**
* 设置当前线程数据源类型
*
* @param dataSourceType
*/
public static void setDataSourceType(String dataSourceType) {
contextHolder.set(dataSourceType);
}
/**
* 获取当前线程数据源类型
*
* @return string
*/
public static String getDataSourceType() {
return contextHolder.get();
}
/**
* 清除当前线程数据源类型
*/
public static void removeDataSourceType() {
contextHolder.remove();
}
}
本类主要是对数据源进行操作,比如获取,修改,移除等,单独出来这样尽量避免MultiDataSource耦合,便于我们维护。
3.2、继承AbstractRoutingDataSource(核心)
MultiDataSource 扩展Spring的org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource抽象类,重写 determineCurrentLookupKey()方法。
MultiDataSource.java
package com.alian.datasource.common;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
public class MultiDataSource extends AbstractRoutingDataSource {
public MultiDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
//数据源设置(核心操作)
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return MultiDataSourceContextHolder.getDataSourceType();
}
}
实现流程:
- 我们定义的 MultiDataSource 类继承抽象类 AbstractRoutingDataSource ,并实现了determineCurrentLookupKey()方法
- 通过构造方法接收配置的数据源对象 defaultTargetDataSource 和 targetDataSources ,然后通过afterPropertiesSet()方法,将他们分别进行复制到 AbstractRoutingDataSource 对象的 resolvedDataSources 和 resolvedDataSources 中
- 当AbstractRoutingDataSource的getConnection()的方法被调用的时候,会先调用determineTargetDataSource()方法,返回我们指定的DataSource,然后再进行getConnection()达到切换数据源的目的
3.3、自动配置类(核心)
MultiDataSourceConfig.java
package com.alian.datasource.common;
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.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.annotation.Order;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Order(-1)
@ComponentScan("com.alian.datasource")
@Configuration
public class MultiDataSourceConfig {
public static final String MASTER = "master";
public static final String MPS = "mps";
public static final String EIS = "eis";
@Bean
@ConfigurationProperties("spring.datasource.druid." + MASTER)
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.druid." + MPS)
public DataSource mpsDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.druid." + EIS)
public DataSource eisDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@Primary
public MultiDataSource dataSource(DataSource masterDataSource, DataSource mpsDataSource, DataSource eisDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>(5);
targetDataSources.put(MultiDataSourceConfig.MASTER, masterDataSource);
targetDataSources.put(MultiDataSourceConfig.MPS, mpsDataSource);
targetDataSources.put(MultiDataSourceConfig.EIS, eisDataSource);
return new MultiDataSource(masterDataSource, targetDataSources);
}
}
关于 @ConfigurationProperties:
- @ConfigurationProperties 和 @value 有着相同的功能,但是 @ConfigurationProperties 有时更方便,比如映射到pojo
- 配置文件编写的时候,除去前缀的key的名称需要与对应配置类的字段名称相同(如果是yml文件,驼峰模式用短横线)
3.4、配置文件
application.yml
server:
port: 8081
servlet:
context-path: /datasource
spring:
datasource:
druid:
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: test
password: Test1234
url: jdbc:mysql://192.168.0.139:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=3
initialSize: 5
minIdle: 5
maxActive: 20
mps:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: mps
password: mps1234
url: jdbc:mysql://192.168.0.157:3306/mps?characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=3
initialSize: 5
minIdle: 5
maxActive: 20
eis:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: eis
password: eis1234
url: jdbc:mysql://192.168.0.218:3306/eis?characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=3
initialSize: 5
minIdle: 5
maxActive: 20
四、AOP编程
4.1、注解声明
TargetDataSource.java
package com.alian.datasource.common;
import java.lang.annotation.*;
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
String value();
}
4.2、AOP切面编程(核心)
MultiDataSourceAspect.java
package com.alian.datasource.common;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Slf4j
@Aspect
@Order(-1)// 保证该AOP在@Transactional(默认:Integer.MAX_VALUE)之前执行
@Component
public class MultiDataSourceAspect {
@Around("@annotation(ds)")
public Object around(ProceedingJoinPoint point, TargetDataSource ds) throws Throwable {
if (ds == null) {
//数据源为空则设置默认的数据源
MultiDataSourceContextHolder.setDataSourceType(MultiDataSourceConfig.MASTER);
} else {
//根据@TargetDataSource的value值去指定
MultiDataSourceContextHolder.setDataSourceType(ds.value());
}
try {
//继续执行原方法
return point.proceed();
} finally {
//删除数据源类型
MultiDataSourceContextHolder.removeDataSourceType();
}
}
}
如果该方法上加了@Transactional,这时加上注解@Order(-1)(其他数字都可以,只要比Integer.MAX_VALUE小),会保证该AOP在@Transactional之前执行(@Transactional 默认:Integer.MAX_VALUE)
关于 ProceedingJoinPoint 的 proceed 方法,正常情况使用不带参的执行就行
Object proceed() throws Throwable;
若是在AOP中参数值有替换的情况下需要将新的参数列表更新,则使用
Object proceed(Object[] var1) throws Throwable;
至于被调用方法的原参数的获取,可以通过调用 ProceedingJoinPoint 的getArgs()方法。
五、具体使用
我们的目的是为了验证我们能否一个系统能查不同的数据库,所以我们采用最简单的方式进行查询验证(数据对象都没有封装,连接口都省略了,实际中大家千万不要这么做,我这么做是为了省篇幅),这里有三个数据库,其中一个数默认的,我们的实现类分别做员工信息查询、账户信息查询、及发票信息查询。
5.1、员工信息查询
EmployeeService.java
package com.alian.datasource.service;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.Map;
@Slf4j
@Service
public class EmployeeService {
@Autowired
private JdbcTemplate jdbcTemplate;
public Map<String, Object> queryEmployeeById(String id) {
String sql = "SELECT * FROM test.tb_inf_employee where id='" + id + "'";
Map<String, Object> map = jdbcTemplate.queryForMap(sql);
log.info("查询员工信息返回:{}", map);
return map;
}
}
因为员工表是我们默认的数据源,什么都不用处理,像正常使用即可。
5.2、账户信息查询
AccountService.java
package com.alian.datasource.service;
import com.alian.datasource.common.MultiDataSourceConfig;
import com.alian.datasource.common.TargetDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.Map;
@Slf4j
@Service
public class AccountService {
@Autowired
private JdbcTemplate jdbcTemplate;
@TargetDataSource(MultiDataSourceConfig.MPS)
public Map<String, Object> queryAccountByAccountId(String accountId) {
String sql = "SELECT * FROM mps.tb_inf_account where account_id='" + accountId + "'";
Map<String, Object> map = jdbcTemplate.queryForMap(sql);
log.info("查询账户信息返回:{}", map);
return map;
}
}
数据库mps是非默认数据源,也就是非主数据源,我们需要加上注解@TargetDataSource(MultiDataSourceConfig.MPS),其中MultiDataSourceConfig.MPS是我们定义的一个常量和数据源进行对应。
5.3、发票信息查询
InvoiceService.java
package com.alian.datasource.service;
import com.alian.datasource.common.MultiDataSourceConfig;
import com.alian.datasource.common.TargetDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.Map;
@Slf4j
@Service
public class InvoiceService {
@Autowired
private JdbcTemplate jdbcTemplate;
@TargetDataSource(MultiDataSourceConfig.EIS)
public Map<String, Object> queryInvoiceByPayTranSeq(String payTranSeq) {
String sql = "SELECT INVOICECODE,MONEY,INVOICETITLE,INVOICEURL FROM eis.tb_biz_invoice where PAYTRANSEQ='" + payTranSeq + "'";
Map<String, Object> map = jdbcTemplate.queryForMap(sql);
log.info("查询发票信息返回:{}", map);
return map;
}
}
数据库eis是非默认数据源,也就是非主数据源,我们需要加上注解@TargetDataSource(MultiDataSourceConfig.EIS),其中MultiDataSourceConfig.EIS是我们定义的一个常量和数据源进行对应。
六、测试
6.1、测试接口
MultiDataSourceController.java
package com.alian.datasource.controller;
import com.alian.datasource.service.AccountService;
import com.alian.datasource.service.EmployeeService;
import com.alian.datasource.service.InvoiceService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Map;
@Slf4j
@RestController
@RequestMapping("/test")
public class MultiDataSourceController {
@Autowired
private AccountService accountService;
@Autowired
private EmployeeService employeeService;
@Autowired
private InvoiceService invoiceService;
@RequestMapping(value = "/queryAccountByAccountId/{accountId}")
public Map<String, Object> queryAccountByAccountId(@PathVariable String accountId) {
return accountService.queryAccountByAccountId(accountId);
}
@RequestMapping(value = "/queryEmployeeById/{id}")
public Map<String, Object> queryEmployeeById(@PathVariable String id) {
return employeeService.queryEmployeeById(id);
}
@RequestMapping(value = "/queryInvoiceByPayTranSeq/{payTranSeq}")
public Map<String, Object> queryInvoiceByPayTranSeq(@PathVariable String payTranSeq) {
return invoiceService.queryInvoiceByPayTranSeq(payTranSeq);
}
}
6.2、数据准备
6.2.1、test数据库表(tb_inf_employee)
6.2.2、mps数据库表(tb_inf_account)
6.2.3、eis数据库表(tb_biz_invoice)
6.3、运行结果
请求地址:http://localhost:8081/datasource/test/queryEmployeeById/BAT10001
{"id":"BAT10001","emp_name":"梁南生","age":32,"salary":31000.0,"department":"研发部","hire_date":"2020-05-20"}
请求地址:http://localhost:8081/datasource/test/queryAccountByAccountId/2088911600919882
{"id":2,"account_id":"2088911600919882","account_name":"支付宝账户","account_type":"03"}
请求地址:http://localhost:8081/datasource/test/queryInvoiceByPayTranSeq/180017559001
{"INVOICECODE":"044031600111","MONEY":5000,"INVOICETITLE":"个人","INVOICEURL":"http://down.szhtxx.cn:10000/downInvoice/download/914403007703110594/billCode/mu5epeuq9utt0sq3wzor"}
结语
基于 AbstractRoutingDataSource 的多数据源动态切换,可以实现读写分离,但是缺点也很明显,无法动态的增加数据源。
- 多数据源的情况下,一般是主数据源有读写功能,其他的库都只有读的功能,一般在清分对账的场景下使用多
- 即算是读的功能都是分账户权限和表权限的
- 为了数据的一致性,所以尽量不要一个库多个不同的系统都可以去操作
- 其实在大行其道的微服务架构的环境下,多数据源模式不是那么的推荐,一般都是通过接口进行数据的访问