spring实现多数据源切换示例讲解
多数据源切换的使用场景
多数据源的使用场景可能有很多,比如我们的业务数量很比较多,需要做分库的时候,多个数据库的读写操作就是一个多数据源的读写操作。自己单位的项目由于数据量较大,起初就选择了阿里的DRDS分库系统,但由于sql语句的兼容性问题并且阿里是收费的,CTO要求我们自己实现分库系统。因为我们的产品是服务于企业的,所以就使用企业的ID作为一个key,然后对key取余这个一个常规算法,将企业的数据分散在不同的数据库实例上,从而实现数据分库。接下来,写一个如何实现动态切换数据的项目,来学习一下AbstractRoutingDataSource抽象类的使用。
以下所说的“连接池”等价于“数据源” ,两种说法可以互相替换理解
maven坐标
<?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>com.alex</groupId>
<artifactId>spring</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<jweb.version>1.1.3</jweb.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.49</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.18</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.1.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.22</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.5</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
整体项目是这样实现思路:先将各个服务实体完成吗,然后交给spring管理,手动创建出spring容器,接下来开始操作
数据库准备
sql脚本(两个库各一个user表)
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`cup` varchar(50) DEFAULT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '白骨精', 'beef', '300');
INSERT INTO `user` VALUES ('2', '琵琶精', 'coffee', '260');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`cup` varchar(50) DEFAULT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '哪吒', 'tea', '800');
INSERT INTO `user` VALUES ('2', '悟空', 'fruit', '510');
数据库配置文件jdbc.properties
jdbc_driver=com.mysql.jdbc.Driver
jdbc_url=jdbc:mysql://127.0.0.1:3306/girl
jdbc_user=root
jdbc_password=root
jdbc_url_s=jdbc:mysql://127.0.0.1:3306/boy
jdbc_user_s=root
jdbc_password_s=root
实体数据模型
package com.alex.entity;
import lombok.Data;
import java.io.Serializable;
@Data
public class UserEntity implements Serializable {
private static final long serialVersionUID = -8233400492862988387L;
private int id;
private String name;
private int age;
private String cup;
}
mapper准备
mapper接口UserMapper
package com.alex.mapper;
import com.alex.entity.UserEntity;
public interface UserMapper {
UserEntity queryUserById(int id);
}
mapper配置文件UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.alex.mapper.UserMapper">
<select id="queryUserById" parameterType="int" resultType="com.alex.entity.UserEntity">
select id,name,cup,age from user where id=#{id}
</select>
</mapper>
切换数据源实现
连接池名称枚举DataSourceType
用于获取连接池的名称
package com.alex.dao;
public enum DataSourceType {
DATASOURCE1("dataSource1"),DATASOURCE2("dataSource2");
private String name;
private DataSourceType(String name){
this.name=name;
}
public String getDataSource(){
return this.getName();
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
连接池主键名称存取对象DynamicDataSourceHolder
由于每一次请求都是一个新的线程,通过将本次请求使用的连接池名称存储于线程中来确定请求的数据源连接池名称作为了key,下面在beans.xml中可以看出
。
package com.alex.dao;
/**
* @author alex
* @Title: DynamicDataSourceHolder
* @ProjectName spring
* @Description: TODO
* @date 2019/8/14 22:26
*/
public class DynamicDataSourceHolder {
//解决线程安全问题
private static final ThreadLocal<String> holder = new ThreadLocal<String>();
public static void putDataSourceName(String dataName){
holder.set(dataName);
}
public static String getDataSourceName(){
return holder.get();
}
public static void clear(){
holder.remove();
}
public static class DataSourceName{
public final static String BASE = "datasource1";
}
}
动态连接池RoutingDataSource
自定义的动态数据源,需要继承spring提供的动态切换数据源的抽象类AbstractRoutingDataSource.java
,本项目中配置了两个真正的连接池,供其根据key选择见beans.xml
。
package com.alex.dao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author alex
* @Title: RoutingDataSource
* @ProjectName spring
* @Description: TODO
* @date 2019/8/14 22:19
*/
@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
String dataSourceName = DynamicDataSourceHolder.getDataSourceName();
if(dataSourceName == null){
dataSourceName = DataSourceType.DATASOURCE1.getDataSource();
}
log.info("选择的数据库是:"+dataSourceName);
return dataSourceName;
}
}
自定义注解ChooseDataSource
用于在测试的时候通过注解的方式标识使用的数据源
package com.alex.aop;
import java.lang.annotation.*;
@Target({ElementType.PARAMETER, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ChooseDataSource {
/**
* 数据库名称 默认datasource1
* @return 数据库名称
*/
String dataSourceName() default "datasource1";
}
编写切面DataSourceAspect
通过切面,来调整本次请求线程对象中的key(连接池名称)
,从而选择我们选择的数据源(选择数据源是通过注解选择的)
。
package com.alex.aop;
import com.alex.dao.DataSourceType;
import com.alex.dao.DynamicDataSourceHolder;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import java.lang.reflect.Method;
/**
* @author alex
* @Title: DataSourceAspect
* @ProjectName spring
* @Description: TODO
* @date 2019/8/14 23:08
*/
@Slf4j
@Aspect
public class DataSourceAspect {
@Pointcut("@annotation(com.alex.aop.ChooseDataSource)")
public void pointCut() {}
@Before("pointCut()")
public void before(JoinPoint point) {
log.info("...前置增强选择key...");
MethodSignature signa = (MethodSignature) point.getSignature();
Method method = signa.getMethod();
ChooseDataSource annotationClass = method.getAnnotation(ChooseDataSource.class);
if(annotationClass != null){
String sourceName = annotationClass.dataSourceName();
DynamicDataSourceHolder.putDataSourceName(sourceName);
}else{
DynamicDataSourceHolder.putDataSourceName(DataSourceType.DATASOURCE1.getName());
}
}
/**
* 清理掉当前设置的数据源,让默认的数据源不受影响
* */
@After("pointCut()")
public void after(JoinPoint point){
log.info("...后置增强清除key...");
DynamicDataSourceHolder.clear();
}
}
service服务UserService
package com.alex.service;
import com.alex.aop.ChooseDataSource;
import com.alex.entity.UserEntity;
import com.alex.mapper.UserMapper;
import lombok.Data;
/**
* @author alex
* @Title: UserService
* @ProjectName spring
* @Description: TODO
* @date 2019/8/14 23:45
*/
@Data
public class UserService {
private UserMapper userMapper;
@ChooseDataSource(dataSourceName = "dataSource1")
public UserEntity queryUserById(int id){
UserEntity userEntity = this.userMapper.queryUserById(id);
return userEntity;
}
}
spring容器
配置spring容器beans.xml
控制反转,并整合mybatis
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 配置整合mybatis-->
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath*:jdbc.properties</value>
</list>
</property>
</bean>
<!--配置数据库的连接池-->
<bean id="abstractDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc_driver}"></property>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="5" />
<property name="minIdle" value="5" />
<property name="maxActive" value="10" />
<!-- 配置从连接池获取连接等待超时的时间 -->
<property name="maxWait" value="10000" />
</bean>
<!--多数据源配置 parent指向上面的配置数据库的连接池abstractDataSource 这里配置了两个数据源-->
<bean id="dataSource1" parent="abstractDataSource">
<property name="url" value="${jdbc_url}" />
<property name="username" value="${jdbc_user}" />
<property name="password" value="${jdbc_password}" />
</bean>
<bean id="dataSource2" parent="abstractDataSource">
<property name="url" value="${jdbc_url_s}" />
<property name="username" value="${jdbc_user_s}" />
<property name="password" value="${jdbc_password_s}" />
</bean>
<!-- 在RoutingDataSource类中读取 当前的返回值 并匹配key值 选择你的数据库源-->
<bean id="dataSource" class="com.alex.dao.RoutingDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource1" value-ref="dataSource1"></entry>
<entry key="dataSource2" value-ref="dataSource2"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSource1"></property>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 数据源 -->
<property name="dataSource" ref="dataSource"></property>
<!-- 别名 -->
<property name="typeAliasesPackage" value="com.alex.entity"></property>
<!-- sql映射文件路径 -->
<property name="mapperLocations" value="classpath*:mapping/*Mapper.xml"></property>
</bean>
<!--4 自动扫描对象关系映射 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!--指定会话工厂,如果当前上下文中只定义了一个则该属性可省去 -->
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
<!-- 指定要自动扫描接口的基础包,实现接口 -->
<property name="basePackage" value="com.alex.mapper"></property>
</bean>
<!-- 启动@aspectj的自动代理支持-->
<aop:aspectj-autoproxy expose-proxy="true"></aop:aspectj-autoproxy>
<!-- 定义aspect类 -->
<bean id="dataSourceAspect" class="com.alex.aop.DataSourceAspect"></bean>
<bean id="userSerice" class="com.alex.service.UserService" autowire="byType" ></bean>
</beans>
获取spring容器工具Container
package com.alex.context;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Container {
public static ClassPathXmlApplicationContext getContextFromClassPathXmlApplicationContext(){
ClassPathXmlApplicationContext context =
new ClassPathXmlApplicationContext("classpath:beans.xml");
return context;
}
}
测试类Fun
import com.alex.context.Container;
import com.alex.entity.UserEntity;
import com.alex.service.UserService;
import com.alibaba.fastjson.JSON;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Fun {
@Test
public void echo(){
ClassPathXmlApplicationContext context = Container.getContextFromClassPathXmlApplicationContext();
UserService userSerice = context.getBean("userSerice", UserService.class);
UserEntity userEntity = userSerice.queryUserById(1);
String json = JSON.toJSONString(userEntity);
System.out.println(json);
}
}
测试结果
D:\jdk1.8.0_171\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\IntelliJ IDEA 2018.3\lib\idea_rt.jar=57437:D:\IntelliJ IDEA 2018.3\bin" -Dfile.encoding=UTF-8 -classpath "D:\IntelliJ IDEA 2018.3\lib\idea_rt.jar;D:\IntelliJ IDEA 2018.3\plugins\junit\lib\junit-rt.jar;D:\IntelliJ IDEA 2018.3\plugins\junit\lib\junit5-rt.jar;D:\jdk1.8.0_171\jre\lib\charsets.jar;D:\jdk1.8.0_171\jre\lib\deploy.jar;D:\jdk1.8.0_171\jre\lib\ext\access-bridge-64.jar;D:\jdk1.8.0_171\jre\lib\ext\cldrdata.jar;D:\jdk1.8.0_171\jre\lib\ext\dnsns.jar;D:\jdk1.8.0_171\jre\lib\ext\jaccess.jar;D:\jdk1.8.0_171\jre\lib\ext\jfxrt.jar;D:\jdk1.8.0_171\jre\lib\ext\localedata.jar;D:\jdk1.8.0_171\jre\lib\ext\nashorn.jar;D:\jdk1.8.0_171\jre\lib\ext\sunec.jar;D:\jdk1.8.0_171\jre\lib\ext\sunjce_provider.jar;D:\jdk1.8.0_171\jre\lib\ext\sunmscapi.jar;D:\jdk1.8.0_171\jre\lib\ext\sunpkcs11.jar;D:\jdk1.8.0_171\jre\lib\ext\zipfs.jar;D:\jdk1.8.0_171\jre\lib\javaws.jar;D:\jdk1.8.0_171\jre\lib\jce.jar;D:\jdk1.8.0_171\jre\lib\jfr.jar;D:\jdk1.8.0_171\jre\lib\jfxswt.jar;D:\jdk1.8.0_171\jre\lib\jsse.jar;D:\jdk1.8.0_171\jre\lib\management-agent.jar;D:\jdk1.8.0_171\jre\lib\plugin.jar;D:\jdk1.8.0_171\jre\lib\resources.jar;D:\jdk1.8.0_171\jre\lib\rt.jar;F:\spring\target\test-classes;F:\spring\target\classes;D:\repository\org\springframework\spring-beans\5.1.3.RELEASE\spring-beans-5.1.3.RELEASE.jar;D:\repository\org\springframework\spring-context\5.1.3.RELEASE\spring-context-5.1.3.RELEASE.jar;D:\repository\org\springframework\spring-expression\5.1.3.RELEASE\spring-expression-5.1.3.RELEASE.jar;D:\repository\org\springframework\spring-core\5.1.3.RELEASE\spring-core-5.1.3.RELEASE.jar;D:\repository\org\springframework\spring-jcl\5.1.3.RELEASE\spring-jcl-5.1.3.RELEASE.jar;D:\repository\org\springframework\spring-aop\5.1.3.RELEASE\spring-aop-5.1.3.RELEASE.jar;D:\repository\org\springframework\spring-jdbc\5.1.8.RELEASE\spring-jdbc-5.1.8.RELEASE.jar;D:\repository\org\springframework\spring-tx\5.1.8.RELEASE\spring-tx-5.1.8.RELEASE.jar;D:\repository\org\projectlombok\lombok\1.16.20\lombok-1.16.20.jar;D:\repository\junit\junit\4.12\junit-4.12.jar;D:\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;D:\repository\com\alibaba\fastjson\1.2.49\fastjson-1.2.49.jar;D:\repository\org\mybatis\mybatis\3.5.2\mybatis-3.5.2.jar;D:\repository\org\mybatis\mybatis-spring\1.3.2\mybatis-spring-1.3.2.jar;D:\repository\mysql\mysql-connector-java\5.1.46\mysql-connector-java-5.1.46.jar;D:\repository\com\alibaba\druid\1.1.18\druid-1.1.18.jar;D:\repository\org\springframework\spring-aspects\5.1.8.RELEASE\spring-aspects-5.1.8.RELEASE.jar;D:\repository\org\aspectj\aspectjweaver\1.9.3\aspectjweaver-1.9.3.jar;D:\repository\org\slf4j\slf4j-api\1.7.22\slf4j-api-1.7.22.jar;D:\repository\org\slf4j\slf4j-simple\1.7.5\slf4j-simple-1.7.5.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 Fun,echo
[main] INFO com.alex.aop.DataSourceAspect - ...前置增强选择key...
[main] INFO com.alex.dao.RoutingDataSource - 选择的数据库是:dataSource1
[main] INFO com.alibaba.druid.pool.DruidDataSource - {dataSource-1} inited
[main] INFO com.alex.aop.DataSourceAspect - ...后置增强清除key...
{"age":300,"cup":"beef","id":1,"name":"白骨精"}
Process finished with exit code 0
项目目录结构
切换数据源过程详解
- 启动程序,加载spring容器;
- 从容器中获取userService实例;
- 调用userService的queryUserById方法。
@ChooseDataSource(dataSourceName = "dataSource1")
public UserEntity queryUserById(int id){
UserEntity userEntity = this.userMapper.queryUserById(id);
return userEntity;
}
由于配置有切面DataSourceAspect,拦截的切点为添加了ChooseDataSource注解的方法。所以当调用userService的queryUserById方法时会被拦截,织入前置通知。
@Before("pointCut()")
public void before(JoinPoint point) {
log.info("...前置增强选择key...");
MethodSignature signa = (MethodSignature) point.getSignature();
Method method = signa.getMethod();
ChooseDataSource annotationClass = method.getAnnotation(ChooseDataSource.class);
if(annotationClass != null){
String sourceName = annotationClass.dataSourceName();
DynamicDataSourceHolder.putDataSourceName(sourceName);
}else{
DynamicDataSourceHolder.putDataSourceName(DataSourceType.DATASOURCE1.getName());
}
}
在前置通知中,会获取@ChooseDataSource(dataSourceName = "dataSource1")
注解的值dataSource1
,然后将这个数据源的名称放入线程对象容器ThreadLocal中。
随着线程的执行,当到达选择数据源的过程时,由于spring管理的是动态线程池,spring会通过determineCurrentLookupKey()
方法获取执行此任务的线程池的key(这里用的是线程池的名字作为key)。
@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
String dataSourceName = DynamicDataSourceHolder.getDataSourceName();
if(dataSourceName == null){
dataSourceName = DataSourceType.DATASOURCE1.getDataSource();
}
log.info("选择的数据库是:"+dataSourceName);
return dataSourceName;
}
}
获取到key(比如这里是dataSource1)后,然后根据key=dataSource1找到需要用到的具体数据源。
<!-- 在RoutingDataSource类中读取 当前的返回值 并匹配key值 选择你的数据库源-->
<bean id="dataSource" class="com.alex.dao.RoutingDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource1" value-ref="dataSource1"></entry>
<entry key="dataSource2" value-ref="dataSource2"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSource1"></property>
</bean>
** 试想一下**:分库的时候,用ID对一个整数值取余,余数m,现在共有dataSource1…dataSourcem…dataSourcen个数据源实例,是不是就可以找到这个ID对应的分库dataSourcem了呢?