springboot+mybatis实现多数据源

1. 前言

最近做项目碰到了一个需要连4个不同数据库的需求，其中db1、db2表结构都不相同；另外两个数据库same_db_private、same_db_public表结构完全相同，一个对内一个对外，只是从物理上隔离了数据而已。

所以打算通过静态配置包路径的方式来实现db1、db2的操作，并且通过扩展Spring的AbstractRoutingDataSource的方式来实现same_db_private、same_db_public的动态切换数据源。

2. 数据准备

• db1创建表并准备数据createtable goods_info( id bigintauto_incrementcomment'主键'primarykey, name varchar(50)notnull)collate=utf8mb4_bin;insertinto db1.goods_info (id, name)values(1,'商品1'),(2,'商品2'),(3,'商品3');
• db1创建表并准备数据createtable user_info( id bigintauto_incrementcomment'主键'primarykey, name varchar(50)notnull)collate=utf8mb4_bin;insertinto db2.user_info (id, name)values(1,'用户1'),(2,'用户2'),(3,'用户3');
• same_db_private创建表并准备数据createtable brand_info( id bigintauto_incrementcomment'主键'primarykey, name varchar(50)notnull)collate=utf8mb4_bin;insertinto brand_info (id, name)values(1,'内部品牌1');
• same_db_public创建表并准备数据createtable brand_info( id bigintauto_incrementcomment'主键'primarykey, name varchar(50)notnull)collate=utf8mb4_bin;insertinto brand_info (id, name)values(1,'外部品牌1');

3. 代码细节

3.1 工程目录

3.2 配置文件

spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db1.username=root
spring.datasource.db1.password=xxxxxx
spring.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1

spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.username=root
spring.datasource.db2.password=xxxxxx
spring.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2

spring.datasource.same-db-private.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.same-db-private.username=root
spring.datasource.same-db-private.password=xxxxxx
spring.datasource.same-db-private.jdbc-url=jdbc:mysql://localhost:3306/same_db_private

spring.datasource.same-db-public.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.same-db-public.username=root
spring.datasource.same-db-public.password=xxxxxx
spring.datasource.same-db-public.jdbc-url=jdbc:mysql://localhost:3306/same_db_public

3.3 静态配置数据源

在config中创建db1和db2的静态数据源配置

packagecom.aresbf.multi.config;importorg.apache.ibatis.session.SqlSessionFactory;importorg.mybatis.spring.SqlSessionFactoryBean;importorg.mybatis.spring.SqlSessionTemplate;importorg.mybatis.spring.annotation.MapperScan;importorg.springframework.beans.factory.annotation.Qualifier;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.boot.jdbc.DataSourceBuilder;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importjavax.sql.DataSource;@Configuration@MapperScan(basePackages ="com.aresbf.multi.mapper.db1", sqlSessionFactoryRef ="db1SqlSessionFactory")publicclassDataSource1{/**
     * 配置db1数据库
     *
     * @return
     */@Bean(name ="db1Datasource")@ConfigurationProperties(prefix ="spring.datasource.db1")publicDataSourcetestDatasource(){returnDataSourceBuilder.create().build();}/**
     * 创建SqlSessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */@Bean(name ="db1SqlSessionFactory")publicSqlSessionFactorytestSqlSessionFactory(@Qualifier("db1Datasource")DataSource dataSource)throwsException{SqlSessionFactoryBean bean =newSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);return bean.getObject();}/**
     * 配置事务管理
     *
     * @param dataSource
     * @return
     */@Bean(name ="db1TransactionManager")publicDataSourceTransactionManagertestTransactionManager(@Qualifier("db1Datasource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}@Bean(name ="db1SqlSessionTemplate")publicSqlSessionTemplatetestSqlSessionTemplate(@Qualifier("db1SqlSessionFactory")SqlSessionFactory sqlSessionFactory){returnnewSqlSessionTemplate(sqlSessionFactory);}}

packagecom.aresbf.multi.config;importorg.apache.ibatis.session.SqlSessionFactory;importorg.mybatis.spring.SqlSessionFactoryBean;importorg.mybatis.spring.SqlSessionTemplate;importorg.mybatis.spring.annotation.MapperScan;importorg.springframework.beans.factory.annotation.Qualifier;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.boot.jdbc.DataSourceBuilder;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importjavax.sql.DataSource;@Configuration@MapperScan(basePackages ="com.aresbf.multi.mapper.db2", sqlSessionFactoryRef ="db2SqlSessionFactory")publicclassDataSource2{/**
     * 配置db2数据库
     *
     * @return
     */@Bean(name ="db2Datasource")@ConfigurationProperties(prefix ="spring.datasource.db2")publicDataSourcetestDatasource(){returnDataSourceBuilder.create().build();}/**
     * 创建SqlSessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */@Bean(name ="db2SqlSessionFactory")publicSqlSessionFactorytestSqlSessionFactory(@Qualifier("db2Datasource")DataSource dataSource)throwsException{SqlSessionFactoryBean bean =newSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);return bean.getObject();}/**
     * 配置事务管理
     *
     * @param dataSource
     * @return
     */@Bean(name ="db2TransactionManager")publicDataSourceTransactionManagertestTransactionManager(@Qualifier("db2Datasource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}@Bean(name ="db2SqlSessionTemplate")publicSqlSessionTemplatetestSqlSessionTemplate(@Qualifier("db2SqlSessionFactory")SqlSessionFactory sqlSessionFactory){returnnewSqlSessionTemplate(sqlSessionFactory);}}

3.4 动态切换数据源

1. 创建动态数据源配置packagecom.aresbf.multi.config;importcom.aresbf.multi.dynamicdatasource.DataSourceRouting;importlombok.extern.slf4j.Slf4j;importorg.apache.ibatis.session.SqlSessionFactory;importorg.mybatis.spring.SqlSessionFactoryBean;importorg.mybatis.spring.SqlSessionTemplate;importorg.mybatis.spring.annotation.MapperScan;importorg.springframework.beans.factory.annotation.Qualifier;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.boot.jdbc.DataSourceBuilder;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Primary;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importjavax.sql.DataSource;/** * @description:sameDb动态数据源 * @author: aresbf * @createDate: 2022/10/21 */@Configuration@Slf4j@MapperScan(basePackages ="com.aresbf.multi.mapper.same_db", sqlSessionTemplateRef ="sameDbDynamicSqlSessionTemplate")publicclassSameDbDynamicDataSourceConfig{/** * 自定义动态datasource * * @param sameDbPrivateDataSource 对内datasource * @param sameDbPublicDataSource 对外datasource * @return DataSource */@Bean(name ="sameDbDynamicDataSource")@PrimarypublicDataSourcedataSource(@Qualifier("sameDbPrivateDataSource")DataSource sameDbPrivateDataSource,@Qualifier("sameDbPublicDataSource")DataSource sameDbPublicDataSource){DataSourceRouting routingDataSource =newDataSourceRouting(); routingDataSource.initDatasource(sameDbPrivateDataSource, sameDbPublicDataSource);return routingDataSource;}/** * 对内datasource * * @return DataSource */@Bean(name ="sameDbPrivateDataSource")@ConfigurationProperties(prefix ="spring.datasource.same-db-private")publicDataSourcesameDbPrivateDataSource(){returnDataSourceBuilder.create().build();}/** * 对外datasource * * @return DataSource */@Bean(name ="sameDbPublicDataSource")@ConfigurationProperties(prefix ="spring.datasource.same-db-public")publicDataSourcesameDbPublicDataSource(){returnDataSourceBuilder.create().build();}/** * 自定义SqlSessionFactory * * @param dataSource 自定义datasource * @return SqlSessionFactory * @throws Exception */@Bean(name ="sameDbDynamicSqlSessionFactory")publicSqlSessionFactorycustomSqlSessionFactory(@Qualifier("sameDbDynamicDataSource")DataSource dataSource)throwsException{SqlSessionFactoryBean bean =newSqlSessionFactoryBean(); bean.setDataSource(dataSource);//实现数据库下划线字段到POJO类驼峰形式的自动映射 bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);return bean.getObject();}/** * 自定义DataSourceTransactionManager * * @param dataSource 自定义datasource * @return DataSourceTransactionManager */@Bean(name ="sameDbDynamicTransactionManager")@PrimarypublicDataSourceTransactionManagercustomTransactionManager(@Qualifier("sameDbDynamicDataSource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}/** * 自定义SqlSessionTemplate * * @param sqlSessionFactory 自定义SqlSessionFactory * @return SqlSessionTemplate */@Bean(name ="sameDbDynamicSqlSessionTemplate")@PrimarypublicSqlSessionTemplatecustomSqlSessionTemplate(@Qualifier("sameDbDynamicSqlSessionFactory")SqlSessionFactory sqlSessionFactory){returnnewSqlSessionTemplate(sqlSessionFactory);}}
2. 创建区分动态数据源枚举项packagecom.aresbf.multi.dynamicdatasource;importlombok.Getter;/** * @description: 动态数据源枚举 * @author: aresbf * @createDate: 2022/10/26 */publicenumDynamicDataSourceEnum{/** * 对内系统数据库 */SAME_DB_PRIVATE("SAME_DB_PRIVATE","对内系统数据库"),/** * 对外系统数据库 */SAME_DB_PUBLIC("SAME_DB_PUBLIC","对外系统数据库"),;/** * 数据源code */@GetterprivatefinalString dataSource;/** * 描述 */privatefinalString description;/** * 构造器 * * @param dataSource 数据源标识 * @param description 描述 */DynamicDataSourceEnum(String dataSource,String description){this.dataSource = dataSource;this.description = description;}}
3. 动态数据源切换上下文packagecom.aresbf.multi.dynamicdatasource;importlombok.extern.slf4j.Slf4j;/** * @description: 动态数据源切换用上下文 * @author: aresbf * @createDate: 2022/10/26 */@Slf4jpublicclassDataSourceContextHolder{/** * 用于存在数据源切换标识 */privatestaticThreadLocal<DynamicDataSourceEnum> datasourceContext =newThreadLocal<>();/** * 切换数据源 * * @param dynamicDataSourceEnum 要切换的数据源标识 */publicstaticvoidswitchDataSource(DynamicDataSourceEnum dynamicDataSourceEnum){ log.debug("switchDataSource: {}", dynamicDataSourceEnum.getDataSource()); datasourceContext.set(dynamicDataSourceEnum);}/** * 获取当前的数据源标识 * * @return */publicstaticDynamicDataSourceEnumgetDataSource(){return datasourceContext.get();}/** * 清理上下文中的数据源标识 */publicstaticvoidclear(){ datasourceContext.remove();}}
4. 动态路由packagecom.aresbf.multi.dynamicdatasource;importlombok.extern.slf4j.Slf4j;importorg.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;importjavax.sql.DataSource;importjava.util.HashMap;importjava.util.Map;/** * @description:动态数据源路由 * @author: aresbf * @createDate: 2022/10/26 */@Slf4jpublicclassDataSourceRoutingextendsAbstractRoutingDataSource{/** * 决定使用哪个数据源标识 * * @return 数据源标识 */@OverrideprotectedObjectdetermineCurrentLookupKey(){DynamicDataSourceEnum dynamicDataSourceEnum =DataSourceContextHolder.getDataSource();//如果没有设置数据源标识，默认使用对内数据源标识if(dynamicDataSourceEnum ==null){ dynamicDataSourceEnum =DynamicDataSourceEnum.SAME_DB_PRIVATE;} log.debug("use{}", dynamicDataSourceEnum.getDataSource());return dynamicDataSourceEnum;}/** * 初始化数据源列表 * * @param sameDbPrivate 对内数据源 * @param sameDbPublic 对外数据源 */publicvoidinitDatasource(DataSource sameDbPrivate,DataSource sameDbPublic){Map<Object,Object> dataSourceMap =newHashMap<>(); dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PRIVATE, sameDbPrivate); dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PUBLIC, sameDbPublic);this.setTargetDataSources(dataSourceMap);this.setDefaultTargetDataSource(sameDbPrivate);}}
5. 自定义动态数据源注解packagecom.aresbf.multi.dynamicdatasource;importjava.lang.annotation.ElementType;importjava.lang.annotation.Retention;importjava.lang.annotation.RetentionPolicy;importjava.lang.annotation.Target;/** * @description:自定义动态数据源注解 * @author: aresbf * @createDate: 2022/10/26 */@Target({ElementType.TYPE,ElementType.METHOD})@Retention(RetentionPolicy.RUNTIME)public@interfaceSameDbDynamicDataSource{/** * 数据源标识 * * @return DynamicDataSourceEnum */DynamicDataSourceEnumname()defaultDynamicDataSourceEnum.SAME_DB_PRIVATE;}
6. 动态数据源拦截packagecom.aresbf.multi.dynamicdatasource;importorg.aspectj.lang.JoinPoint;importorg.aspectj.lang.annotation.After;importorg.aspectj.lang.annotation.Aspect;importorg.aspectj.lang.annotation.Before;importorg.aspectj.lang.annotation.Pointcut;importorg.aspectj.lang.reflect.MethodSignature;importorg.springframework.stereotype.Component;importjava.lang.reflect.Method;/** * @description:动态数据源拦截 * @author: aresbf * @createDate: 2022/10/26 */@Aspect@ComponentpublicclassHandleDatasourceAspect{/** * 所有标识sameDbDynamicDataSource注解的类和方法 */@Pointcut("@annotation(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)||@within(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)")publicvoidpointcut(){}/** * 方法执行前 * * @param joinPoint 拦截点 */@Before("pointcut()")publicvoidbeforeExecute(JoinPoint joinPoint){Method method =((MethodSignature) joinPoint.getSignature()).getMethod();SameDbDynamicDataSource annotation = method.getAnnotation(SameDbDynamicDataSource.class);if(null== annotation){ annotation = joinPoint.getTarget().getClass().getAnnotation(SameDbDynamicDataSource.class);}if(null!= annotation){// 切换数据源DataSourceContextHolder.switchDataSource(annotation.name());}}/** * 方法执行后 */@After("pointcut()")publicvoidafterExecute(){DataSourceContextHolder.clear();}}

3.5 编写测试类

通过SameDbDynamicDataSource动态指定需要访问的数据源，避免相同的mapper代码重复写

packagecom.aresbf.multi.controller;importcom.aresbf.multi.dynamicdatasource.DynamicDataSourceEnum;importcom.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource;importcom.aresbf.multi.entity.BrandInfoPO;importcom.aresbf.multi.entity.GoodsInfoPO;importcom.aresbf.multi.entity.UserInfoPO;importcom.aresbf.multi.service.BrandInfoService;importcom.aresbf.multi.service.GoodsInfoService;importcom.aresbf.multi.service.UserInfoService;importcom.fasterxml.jackson.core.JsonProcessingException;importcom.fasterxml.jackson.databind.ObjectMapper;importlombok.extern.slf4j.Slf4j;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;importjava.util.HashMap;importjava.util.List;/**
 * @description: 测试
 * @author: aresbf
 * @createDate: 2022/10/24
 */@RestController@RequestMapping@Slf4jpublicclassTestController{@AutowiredprivateGoodsInfoService goodsInfoService;@AutowiredprivateUserInfoService userInfoService;@AutowiredprivateBrandInfoService brandInfoService;/**
     * 查内部
     *
     * @return ok
     */@GetMapping("/queryPrivate")@SameDbDynamicDataSource(name =DynamicDataSourceEnum.SAME_DB_PRIVATE)publicStringqueryPrivate()throwsJsonProcessingException{List<GoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();List<UserInfoPO> userInfoPOS = userInfoService.queryAll();List<BrandInfoPO> brandInfoPOS = brandInfoService.queryAll();HashMap<String,List> resultMap =newHashMap<>();
        resultMap.put("goodsInfo", goodsInfoPOS);
        resultMap.put("userInfo", userInfoPOS);
        resultMap.put("brandInfo", brandInfoPOS);ObjectMapper objectMapper =newObjectMapper();return objectMapper.writeValueAsString(resultMap);}/**
     * 查外部
     *
     * @return ok
     */@GetMapping("/queryPublic")@SameDbDynamicDataSource(name =DynamicDataSourceEnum.SAME_DB_PUBLIC)publicStringqueryPublic()throwsJsonProcessingException{List<GoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();List<UserInfoPO> userInfoPOS = userInfoService.queryAll();List<BrandInfoPO> brandInfoPOS = brandInfoService.queryAll();HashMap<String,List> resultMap =newHashMap<>();
        resultMap.put("goodsInfo", goodsInfoPOS);
        resultMap.put("userInfo", userInfoPOS);
        resultMap.put("brandInfo", brandInfoPOS);ObjectMapper objectMapper =newObjectMapper();return objectMapper.writeValueAsString(resultMap);}}

3.6 测试结果

3.6.1 请求http://localhost:8080/queryPublic

{"userInfo":[{"id":1,"name":"用户1"},{"id":2,"name":"用户2"},{"id":3,"name":"用户3"}],"brandInfo":[{"id":1,"name":"外部品牌1"}],"goodsInfo":[{"id":1,"name":"商品1"},{"id":2,"name":"商品2"},{"id":3,"name":"商品3"}]}

3.6.2 请求http://localhost:8080/queryPrivate

{"userInfo":[{"id":1,"name":"用户1"},{"id":2,"name":"用户2"},{"id":3,"name":"用户3"}],"brandInfo":[{"id":1,"name":"内部品牌1"}],"goodsInfo":[{"id":1,"name":"商品1"},{"id":2,"name":"商品2"},{"id":3,"name":"商品3"}]}