1. 前言

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

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

2. 数据准备

• db1创建表并准备数据

  create table goods_info(    id bigint auto_increment comment '主键' primary key,name varchar(50) not null)collate=utf8mb4_bin;insert into db1.goods_info (id, name)values  (1, '商品1'),        (2, '商品2'),        (3, '商品3');

• db1创建表并准备数据

  create table user_info(    id bigint auto_increment comment '主键' primary key,    name varchar(50) not null)collate=utf8mb4_bin;insert into db2.user_info (id, name)values  (1, '用户1'),        (2, '用户2'),        (3, '用户3');

• same_db_private创建表并准备数据

  create table brand_info(    id bigint auto_increment comment '主键' primary key,    name varchar(50) not null)collate=utf8mb4_bin;insert into brand_info (id, name)values  (1, '内部品牌1');

• same_db_public创建表并准备数据

  create table brand_info(    id bigint auto_increment comment '主键' primary key,    name varchar(50) not null)collate=utf8mb4_bin;insert into brand_info (id, name)values  (1, '外部品牌1');

3. 代码细节

3.1 工程目录

3.2 配置文件

spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driverspring.datasource.db1.username=rootspring.datasource.db1.password=xxxxxxspring.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driverspring.datasource.db2.username=rootspring.datasource.db2.password=xxxxxxspring.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2spring.datasource.same-db-private.driver-class-name=com.mysql.jdbc.Driverspring.datasource.same-db-private.username=rootspring.datasource.same-db-private.password=xxxxxxspring.datasource.same-db-private.jdbc-url=jdbc:mysql://localhost:3306/same_db_privatespring.datasource.same-db-public.driver-class-name=com.mysql.jdbc.Driverspring.datasource.same-db-public.username=rootspring.datasource.same-db-public.password=xxxxxxspring.datasource.same-db-public.jdbc-url=jdbc:mysql://localhost:3306/same_db_public

3.3 静态配置数据源

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

package com.aresbf.multi.config;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.SqlSessionTemplate;import org.mybatis.spring.annotation.MapperScan;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration@MapperScan(basePackages = "com.aresbf.multi.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")public class DataSource1 {        @Bean(name = "db1Datasource")    @ConfigurationProperties(prefix = "spring.datasource.db1")    public DataSource testDatasource() {        return DataSourceBuilder.create().build();    }        @Bean(name = "db1SqlSessionFactory")    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1Datasource") DataSource dataSource)            throws Exception {        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();        bean.setDataSource(dataSource);        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);        return bean.getObject();    }        @Bean(name = "db1TransactionManager")    public DataSourceTransactionManager testTransactionManager(            @Qualifier("db1Datasource") DataSource dataSource) {        return new DataSourceTransactionManager(dataSource);    }    @Bean(name = "db1SqlSessionTemplate")    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory")     SqlSessionFactory sqlSessionFactory) {        return new SqlSessionTemplate(sqlSessionFactory);    }}

package com.aresbf.multi.config;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.SqlSessionTemplate;import org.mybatis.spring.annotation.MapperScan;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration@MapperScan(basePackages = "com.aresbf.multi.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")public class DataSource2 {        @Bean(name = "db2Datasource")    @ConfigurationProperties(prefix = "spring.datasource.db2")    public DataSource testDatasource() {        return DataSourceBuilder.create().build();    }        @Bean(name = "db2SqlSessionFactory")    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db2Datasource") DataSource dataSource)            throws Exception {        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();        bean.setDataSource(dataSource);        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);        return bean.getObject();    }        @Bean(name = "db2TransactionManager")    public DataSourceTransactionManager testTransactionManager(            @Qualifier("db2Datasource") DataSource dataSource) {        return new DataSourceTransactionManager(dataSource);    }    @Bean(name = "db2SqlSessionTemplate")    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory")     SqlSessionFactory sqlSessionFactory) {        return new SqlSessionTemplate(sqlSessionFactory);    }}

3.4 动态切换数据源

1. 创建动态数据源配置

   package com.aresbf.multi.config;import com.aresbf.multi.dynamicdatasource.DataSourceRouting;import lombok.extern.slf4j.Slf4j;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.SqlSessionTemplate;import org.mybatis.spring.annotation.MapperScan;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.boot.context.properties.ConfigurationProperties;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.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;@Configuration@Slf4j@MapperScan(basePackages = "com.aresbf.multi.mapper.same_db", sqlSessionTemplateRef = "sameDbDynamicSqlSessionTemplate")public class SameDbDynamicDataSourceConfig {        @Bean(name = "sameDbDynamicDataSource")    @Primary    public DataSource dataSource(@Qualifier("sameDbPrivateDataSource") DataSource sameDbPrivateDataSource, @Qualifier("sameDbPublicDataSource") DataSource sameDbPublicDataSource) {        DataSourceRouting routingDataSource = new DataSourceRouting();        routingDataSource.initDatasource(sameDbPrivateDataSource,                sameDbPublicDataSource);        return routingDataSource;    }        @Bean(name = "sameDbPrivateDataSource")    @ConfigurationProperties(prefix = "spring.datasource.same-db-private")    public DataSource sameDbPrivateDataSource() {        return DataSourceBuilder.create().build();    }        @Bean(name = "sameDbPublicDataSource")    @ConfigurationProperties(prefix = "spring.datasource.same-db-public")    public DataSource sameDbPublicDataSource() {        return DataSourceBuilder.create().build();    }        @Bean(name = "sameDbDynamicSqlSessionFactory")    public SqlSessionFactory customSqlSessionFactory(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) throws Exception {        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();        bean.setDataSource(dataSource);        //实现数据库下划线字段到POJO类驼峰形式的自动映射        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);        return bean.getObject();    }        @Bean(name = "sameDbDynamicTransactionManager")    @Primary    public DataSourceTransactionManager customTransactionManager(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) {        return new DataSourceTransactionManager(dataSource);    }        @Bean(name = "sameDbDynamicSqlSessionTemplate")    @Primary    public SqlSessionTemplate customSqlSessionTemplate(@Qualifier("sameDbDynamicSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {        return new SqlSessionTemplate(sqlSessionFactory);    }}

2. 创建区分动态数据源枚举项

   package com.aresbf.multi.dynamicdatasource;import lombok.Getter;public enum DynamicDataSourceEnum {        SAME_DB_PRIVATE("SAME_DB_PRIVATE", "对内系统数据库"),        SAME_DB_PUBLIC("SAME_DB_PUBLIC", "对外系统数据库"),    ;        @Getter    private final String dataSource;        private final String description;        DynamicDataSourceEnum(String dataSource, String description) {        this.dataSource = dataSource;        this.description = description;    }}

3. 动态数据源切换上下文

   package com.aresbf.multi.dynamicdatasource;import lombok.extern.slf4j.Slf4j;@Slf4jpublic class DataSourceContextHolder {        private static ThreadLocal<DynamicDataSourceEnum> datasourceContext = new ThreadLocal<>();        public static void switchDataSource(DynamicDataSourceEnum dynamicDataSourceEnum) {        log.debug("switchDataSource: {}", dynamicDataSourceEnum.getDataSource());        datasourceContext.set(dynamicDataSourceEnum);    }        public static DynamicDataSourceEnum getDataSource() {        return datasourceContext.get();    }        public static void clear() {        datasourceContext.remove();    }}

4. 动态路由

   package com.aresbf.multi.dynamicdatasource;import lombok.extern.slf4j.Slf4j;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;import javax.sql.DataSource;import java.util.HashMap;import java.util.Map;@Slf4jpublic class DataSourceRouting extends AbstractRoutingDataSource {        @Override    protected Object determineCurrentLookupKey() {        DynamicDataSourceEnum dynamicDataSourceEnum = DataSourceContextHolder.getDataSource();        //如果没有设置数据源标识，默认使用对内数据源标识        if (dynamicDataSourceEnum == null) {            dynamicDataSourceEnum = DynamicDataSourceEnum.SAME_DB_PRIVATE;        }        log.debug("use{}", dynamicDataSourceEnum.getDataSource());        return dynamicDataSourceEnum;    }        public void initDatasource(DataSource sameDbPrivate,   DataSource sameDbPublic) {        Map<Object, Object> dataSourceMap = new HashMap<>();        dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PRIVATE, sameDbPrivate);        dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PUBLIC, sameDbPublic);        this.setTargetDataSources(dataSourceMap);        this.setDefaultTargetDataSource(sameDbPrivate);    }}

5. 自定义动态数据源注解

   package com.aresbf.multi.dynamicdatasource;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target({ElementType.TYPE, ElementType.METHOD})@Retention(RetentionPolicy.RUNTIME)public @interface SameDbDynamicDataSource {        DynamicDataSourceEnum name() default DynamicDataSourceEnum.SAME_DB_PRIVATE;}

6. 动态数据源拦截

   package com.aresbf.multi.dynamicdatasource;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.stereotype.Component;import java.lang.reflect.Method;@Aspect@Componentpublic class HandleDatasourceAspect {        @Pointcut("@annotation(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)||@within(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)")    public void pointcut() {    }        @Before("pointcut()")    public void beforeExecute(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()")    public void afterExecute() {        DataSourceContextHolder.clear();    }}

3.5 编写测试类

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

package com.aresbf.multi.controller;import com.aresbf.multi.dynamicdatasource.DynamicDataSourceEnum;import com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource;import com.aresbf.multi.entity.BrandInfoPO;import com.aresbf.multi.entity.GoodsInfoPO;import com.aresbf.multi.entity.UserInfoPO;import com.aresbf.multi.service.BrandInfoService;import com.aresbf.multi.service.GoodsInfoService;import com.aresbf.multi.service.UserInfoService;import com.fasterxml.jackson.core.JsonProcessingException;import com.fasterxml.jackson.databind.ObjectMapper;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.HashMap;import java.util.List;@RestController@RequestMapping@Slf4jpublic class TestController {    @Autowired    private GoodsInfoService goodsInfoService;    @Autowired    private UserInfoService userInfoService;    @Autowired    private BrandInfoService brandInfoService;        @GetMapping("/queryPrivate")    @SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PRIVATE)    public String queryPrivate() throws JsonProcessingException {        List<GoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();        List<UserInfoPO> userInfoPOS = userInfoService.queryAll();        List<BrandInfoPO> brandInfoPOS = brandInfoService.queryAll();        HashMap<String, List> resultMap = new HashMap<>();        resultMap.put("goodsInfo", goodsInfoPOS);        resultMap.put("userInfo", userInfoPOS);        resultMap.put("brandInfo", brandInfoPOS);        ObjectMapper objectMapper = new ObjectMapper();        return objectMapper.writeValueAsString(resultMap);    }        @GetMapping("/queryPublic")    @SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PUBLIC)    public String queryPublic() throws JsonProcessingException {        List<GoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();        List<UserInfoPO> userInfoPOS = userInfoService.queryAll();        List<BrandInfoPO> brandInfoPOS = brandInfoService.queryAll();        HashMap<String, List> resultMap = new HashMap<>();        resultMap.put("goodsInfo", goodsInfoPOS);        resultMap.put("userInfo", userInfoPOS);        resultMap.put("brandInfo", brandInfoPOS);        ObjectMapper objectMapper = new ObjectMapper();        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"}]}

来源地址：https://blog.csdn.net/qq_32603429/article/details/127739634