在 SpringBoot 中实现多数据源访问的最佳实践
1 概述
在实际业务开发中通常会在单个应用中通过 分库分表 或者 读写分离的方式来提供应用的读写性能。
在具体的开发中有很多方式:
- 通过不同的 mapper,映射到不同的 mybatis 源的方式
- 通过继承 Spring 的 AbstractRoutingDataSource 抽象类并重写 determineCurrentLookupKey 方法来管理多个数据源的方式
本文将详细介绍在 SpringBoot 应用中如何通过 自定义注解 和 aop 的方式实现多数据源的访问,采用了第二种的方式。
2 关键点
- 通过 @Aspect 注解来解析自定义注解
- 通过继承 Spring 的 AbstractRoutingDataSource 抽象类,重写 determineCurrentLookupKey 方法来管理多个数据源
- 通过 自定义注解 中的参数用来访问不同的数据源
- 由于 mybatis 的事务 和 sqlSession 的打开和关闭 也是通过 aop 来实现的,因此这里必须通过 @Order 注解来提高自定义注解的优先级
3 使用场景
- 分库分表,根据业务来划分不同的库,比如与用户相关的表在 db_user 库,与订单相关的表在 db_order 库。
- 读写分离,master 和 slave 模式,master 库只用来写入数据,slave 库只用来读取数据。
这里根据场景 1 来实现具体的例子。
4 具体的例子
4.1 开发环境
- SpringBoot: 2.2.2.RELEASE
- mybatis-spring-boot-starter: 2.1.1
- HikariCP: 3.4.1
4.2 数据库和表
- db_user 库 的 t_user 表如下
CREATE TABLE `t_user` (
`id` int(18) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`name` varchar(25) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
`age` int(10) DEFAULT NULL COMMENT '年龄',
`sex` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '性别',
`remarks` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`create_user` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
`update_date` datetime DEFAULT NULL COMMENT '更新时间',
`update_user` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '更新人',
`del_flag` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '删除标记(0:正常;1:删除)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户信息表'
- db_order 库 的 t_order 表如下
CREATE TABLE `t_order` (
`id` int(18) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`user_id` int(18) DEFAULT NULL COMMENT '用户id',
`order_date` datetime DEFAULT NULL COMMENT '订单时间',
`order_amount` decimal(10,0) DEFAULT NULL COMMENT '订单金额',
`remarks` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`create_user` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人',
`update_date` datetime DEFAULT NULL COMMENT '更新时间',
`update_user` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新人',
`del_flag` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '删除标记(0:正常;1:删除)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表'
4.3 代码结构如下
4.4 自定义注解和 AOP 实现
- MultiDataSource
import com.ckjava.entity.DbEnum;
import java.lang.annotation.*;
/**
* 数据库切换的注解,只作用在方法上
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MultiDataSource {
// 用于指定数据库名称的
DbEnum value() default DbEnum.user;
}
- 通过 aop 来读取注解的配置,并在方法前后进行数据库的切换
import com.ckjava.aop.annotation.MultiDataSource;
import com.ckjava.config.MultiDataSourceHolder;
import com.ckjava.entity.DbEnum;
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 org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* 通过 aop 来读取注解的配置,并在方法前后进行数据库的切换
*/
@Aspect
@Component
@Order(1)
public class MultiDataSourceAspect {
@Pointcut("@annotation(com.ckjava.aop.annotation.MultiDataSource)")
public void dataSourcePointCut() {
}
/**
* 在方法执行前设置数据库 key
*
* @param point
*/
@Before("dataSourcePointCut()")
public void before(JoinPoint point) {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
// 在含有 MultiDataSource 注解的方法执行前,设置线程的数据库源变量
MultiDataSource dataSource = method.getAnnotation(MultiDataSource.class);
if (dataSource == null) {
MultiDataSourceHolder.setDataSource(DbEnum.user);
} else {
MultiDataSourceHolder.setDataSource(dataSource.value());
}
}
/**
* 在方法执行后移除 数据库 key
*/
@After("dataSourcePointCut()")
public void after() {
// 移除线程本地数据库源变量
MultiDataSourceHolder.clearDataSource();
}
}
4.5 继承 AbstractRoutingDataSource 抽象类,重写 determineCurrentLookupKey 方法
具体如下
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* AbstractRoutingDataSource 在获取 Connection 前会通过 determineTargetDataSource 来从多个数据源中根据 key 来
*
* 获取一个 DataSource 对象,这里 determineCurrentLookupKey 由子类来实现
*/
public class MultiRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return MultiDataSourceHolder.getDataSource();
}
}
- MultiRoutingDataSource 将作为 SqlSessionFactory 和 DataSourceTransactionManager 的数据源
4.6 多数据源配置以及 mybatis 配置
- MultiDataSourceConfig
import com.ckjava.entity.DbEnum;
import com.ckjava.properties.OrderDataSourceProperties;
import com.ckjava.properties.UserDataSourceProperties;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = {"com.ckjava.dao"}, sqlSessionFactoryRef = "sqlSessionFactory") // 扫描 Mapper 接口并容器管理
public class MultiDataSourceConfig {
// 精确到 master 目录,以便跟其他数据源隔离
public static final String MAPPER_LOCATION = "classpath:mapper/data/*Mapper.xml";
public static final String CONFIG_LOCATION = "classpath:mapper/data/config.xml";
@Autowired
private UserDataSourceProperties userSourceProperties;
@Autowired
private OrderDataSourceProperties orderDataSourceProperties;
@Bean("userDataSource")
public DataSource userDataSource() {
HikariDataSource dataSource = DataSourceBuilder
.create()
.driverClassName(userSourceProperties.getDriverClassName())
.url(userSourceProperties.getUrl())
.username(userSourceProperties.getUsername())
.password(userSourceProperties.getPassword())
.type(HikariDataSource.class)
.build();
dataSource.setMaximumPoolSize(userSourceProperties.getMaxPoolSize());
dataSource.setAutoCommit(true);
return dataSource;
}
@Bean("orderDataSource")
public DataSource orderDataSource() {
HikariDataSource dataSource = DataSourceBuilder
.create()
.driverClassName(orderDataSourceProperties.getDriverClassName())
.url(orderDataSourceProperties.getUrl())
.username(orderDataSourceProperties.getUsername())
.password(orderDataSourceProperties.getPassword())
.type(HikariDataSource.class)
.build();
dataSource.setMaximumPoolSize(orderDataSourceProperties.getMaxPoolSize());
dataSource.setAutoCommit(true);
return dataSource;
}
@Bean
@Primary
public DataSource multiRoutingDataSource(@Qualifier("userDataSource") DataSource userDataSource,
@Qualifier("orderDataSource") DataSource orderDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DbEnum.user, userDataSource);
targetDataSources.put(DbEnum.order, orderDataSource);
MultiRoutingDataSource myRoutingDataSource = new MultiRoutingDataSource();
myRoutingDataSource.setDefaultTargetDataSource(userDataSource);
myRoutingDataSource.setTargetDataSources(targetDataSources);
return myRoutingDataSource;
}
@Bean(name = "transactionManager")
@Primary
public PlatformTransactionManager transactionManager(@Qualifier("userDataSource") DataSource userDataSource,
@Qualifier("orderDataSource") DataSource orderDataSource) throws Exception {
return new DataSourceTransactionManager(multiRoutingDataSource(userDataSource, orderDataSource));
}
@Bean(name = "sqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("userDataSource") DataSource userDataSource,
@Qualifier("orderDataSource") DataSource orderDataSource) throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(multiRoutingDataSource(userDataSource, orderDataSource));
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MultiDataSourceConfig.MAPPER_LOCATION));
sessionFactory.setConfigLocation(new PathMatchingResourcePatternResolver().getResource(MultiDataSourceConfig.CONFIG_LOCATION));
return sessionFactory.getObject();
}
}
4.7 通过 ThreadLocal 来存储当前线程的 数据库 key 变量
具体如下
import com.ckjava.entity.DbEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 通过 ThreadLocal 来存储当前线程的 数据库 key 变量
*/
public class MultiDataSourceHolder {
private static final Logger logger = LoggerFactory.getLogger(MultiDataSourceHolder.class);
/**
* 通过 ThreadLocal 来存储当前线程的 dataSource key
*/
private static final ThreadLocal<DbEnum> contextHolder = new ThreadLocal<>();
public static void setDataSource(DbEnum dataSource) {
logger.info("Thread {} set datasource {}", Thread.currentThread().getName(), dataSource);
contextHolder.set(dataSource);
}
public static DbEnum getDataSource() {
return contextHolder.get();
}
public static void clearDataSource() {
logger.info("Thread {} unset datasource {}", Thread.currentThread().getName(), contextHolder.get());
contextHolder.remove();
}
}
4.8 在 TUserService 和 TOrderService 上使用
- TUserService
import com.ckjava.aop.annotation.MultiDataSource;
import com.ckjava.dao.TUserDao;
import com.ckjava.entity.DbEnum;
import com.ckjava.entity.TUserEntity;
import com.ckjava.entity.base.PageParamer;
import com.ckjava.service.base.BaseMybatisCrudService;
import com.ckjava.xutils.http.Page;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Optional;
@Service
public class TUserService extends BaseMybatisCrudService<TUserEntity, TUserDao> {
@Override
public Class<TUserEntity> getClassType() {
return TUserEntity.class;
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<TUserEntity> get(long id) {
return super.get(id);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<List<TUserEntity>> getAll() {
return super.getAll();
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<List<TUserEntity>> getByParam(TUserEntity entity) {
return super.getByParam(entity);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<Page<TUserEntity>> getPage(PageParamer pageParamer) {
return super.getPage(pageParamer);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<Long> save(TUserEntity entity) {
return super.save(entity);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<Long> update(TUserEntity entity) {
return super.update(entity);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<Long> saveOrUpdate(TUserEntity entity) {
return super.saveOrUpdate(entity);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<Integer> delete(Long id) {
return super.delete(id);
}
@Override
@MultiDataSource(DbEnum.user)
public Optional<Integer> deletePhysical(Long id) {
return super.deletePhysical(id);
}
}
- TOrderService
import com.ckjava.aop.annotation.MultiDataSource;
import com.ckjava.dao.TOrderDao;
import com.ckjava.entity.DbEnum;
import com.ckjava.entity.TOrderEntity;
import com.ckjava.entity.base.PageParamer;
import com.ckjava.service.base.BaseMybatisCrudService;
import com.ckjava.xutils.http.Page;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Optional;
@Service
public class TOrderService extends BaseMybatisCrudService<TOrderEntity, TOrderDao> {
@Override
public Class<TOrderEntity> getClassType() {
return TOrderEntity.class;
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<TOrderEntity> get(long id) {
return super.get(id);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<List<TOrderEntity>> getAll() {
return super.getAll();
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<List<TOrderEntity>> getByParam(TOrderEntity entity) {
return super.getByParam(entity);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<Page<TOrderEntity>> getPage(PageParamer pageParamer) {
return super.getPage(pageParamer);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<Long> save(TOrderEntity entity) {
return super.save(entity);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<Long> update(TOrderEntity entity) {
return super.update(entity);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<Long> saveOrUpdate(TOrderEntity entity) {
return super.saveOrUpdate(entity);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<Integer> delete(Long id) {
return super.delete(id);
}
@Override
@MultiDataSource(DbEnum.order)
public Optional<Integer> deletePhysical(Long id) {
return super.deletePhysical(id);
}
}
5 测试
- TestOrderService 测试如下
import com.ckjava.entity.TOrderEntity;
import com.ckjava.service.TOrderService;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.Date;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestOrderService {
@Autowired
private TOrderService tOrderService;
@Test
public void test_get() {
tOrderService.get(1L).ifPresent(entity -> {
System.out.println(entity.getId());
});
}
}
- 测试 test_get 方法,输出如下
12:43:06.033 [main] INFO c.c.config.MultiDataSourceHolder - Thread main set datasource order
12:43:06.042 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
12:43:06.359 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
12:43:06.516 [main] INFO c.c.config.MultiDataSourceHolder - Thread main unset datasource order
1
从上面可见,在数据源加载前,先修改了 datasource key 为 order 数据库。
- TestUserService 测试如下
import com.ckjava.service.TUserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestUserService {
@Autowired
private TUserService tUserService;
@Test
public void test_get() {
tUserService.get(1L).ifPresent(entity -> {
System.out.println(entity.getId());
});
}
}
- 执行 test_get 方法,输出如下
12:45:30.389 [main] INFO c.c.config.MultiDataSourceHolder - Thread main set datasource user
12:45:30.403 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
12:45:30.906 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
12:45:31.159 [main] INFO c.c.config.MultiDataSourceHolder - Thread main unset datasource user
1
从输出结果看,在数据源加载前,先修改了 datasource key 为 user 数据库。
6 代码
例子的完整代码:gitee spring-boot-multidb