springboot+mybatis实现多数据源 1. 前言 2. 数据准备 3. 代码细节

Posted on by hackdl

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.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的静态数据源配置

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 {

    /**
     * 配置db1数据库
     *
     * @return
     */
    @Bean(name = "db1Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource testDatasource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 创建SqlSessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @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();
    }

    /**
     * 配置事务管理
     *
     * @param dataSource
     * @return
     */
    @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 {

    /**
     * 配置db2数据库
     *
     * @return
     */
    @Bean(name = "db2Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource testDatasource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 创建SqlSessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @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();
    }

    /**
     * 配置事务管理
     *
     * @param dataSource
     * @return
     */
    @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;

/**
 * @description:sameDb动态数据源
 * @author: aresbf
 * @createDate: 2022/10/21
 */
@Configuration
@Slf4j
@MapperScan(basePackages = "com.aresbf.multi.mapper.same_db", sqlSessionTemplateRef = "sameDbDynamicSqlSessionTemplate")
public class SameDbDynamicDataSourceConfig {

    /**
     * 自定义动态datasource
     *
     * @param sameDbPrivateDataSource 对内datasource
     * @param sameDbPublicDataSource  对外datasource
     * @return DataSource
     */
    @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;
    }

    /**
     * 对内datasource
     *
     * @return DataSource
     */
    @Bean(name = "sameDbPrivateDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.same-db-private")
    public DataSource sameDbPrivateDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 对外datasource
     *
     * @return DataSource
     */
    @Bean(name = "sameDbPublicDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.same-db-public")
    public DataSource sameDbPublicDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 自定义SqlSessionFactory
     *
     * @param dataSource 自定义datasource
     * @return SqlSessionFactory
     * @throws Exception
     */
    @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();
    }

    /**
     * 自定义DataSourceTransactionManager
     *
     * @param dataSource 自定义datasource
     * @return DataSourceTransactionManager
     */
    @Bean(name = "sameDbDynamicTransactionManager")
    @Primary
    public DataSourceTransactionManager customTransactionManager(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 自定义SqlSessionTemplate
     *
     * @param sqlSessionFactory 自定义SqlSessionFactory
     * @return SqlSessionTemplate
     */
    @Bean(name = "sameDbDynamicSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate customSqlSessionTemplate(@Qualifier("sameDbDynamicSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

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

    package com.aresbf.multi.dynamicdatasource;

import lombok.Getter;

/**
 * @description: 动态数据源枚举
 * @author: aresbf
 * @createDate: 2022/10/26
 */
public enum DynamicDataSourceEnum {
    /**
     * 对内系统数据库
     */
    SAME_DB_PRIVATE("SAME_DB_PRIVATE", "对内系统数据库"),
    /**
     * 对外系统数据库
     */
    SAME_DB_PUBLIC("SAME_DB_PUBLIC", "对外系统数据库"),
    ;

    /**
     * 数据源code
     */
    @Getter
    private final String dataSource;
    /**
     * 描述
     */
    private final String description;

    /**
     * 构造器
     *
     * @param dataSource  数据源标识
     * @param description 描述
     */
    DynamicDataSourceEnum(String dataSource, String description) {
        this.dataSource = dataSource;
        this.description = description;
    }
}

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

    package com.aresbf.multi.dynamicdatasource;

import lombok.extern.slf4j.Slf4j;

/**
 * @description: 动态数据源切换用上下文
 * @author: aresbf
 * @createDate: 2022/10/26
 */
@Slf4j
public class DataSourceContextHolder {

    /**
     * 用于存在数据源切换标识
     */
    private static ThreadLocalDynamicDataSourceEnum> datasourceContext = new ThreadLocal>();

    /**
     * 切换数据源
     *
     * @param dynamicDataSourceEnum 要切换的数据源标识
     */
    public static void switchDataSource(DynamicDataSourceEnum dynamicDataSourceEnum) {
        log.debug("switchDataSource: {}", dynamicDataSourceEnum.getDataSource());
        datasourceContext.set(dynamicDataSourceEnum);
    }

    /**
     * 获取当前的数据源标识
     *
     * @return
     */
    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;

/**
 * @description:动态数据源路由
 * @author: aresbf
 * @createDate: 2022/10/26
 */
@Slf4j
public class DataSourceRouting extends AbstractRoutingDataSource {

    /**
     * 决定使用哪个数据源标识
     *
     * @return 数据源标识
     */
    @Override
    protected Object determineCurrentLookupKey() {
        DynamicDataSourceEnum dynamicDataSourceEnum = DataSourceContextHolder.getDataSource();
        //如果没有设置数据源标识，默认使用对内数据源标识
        if (dynamicDataSourceEnum == null) {
            dynamicDataSourceEnum = DynamicDataSourceEnum.SAME_DB_PRIVATE;
        }
        log.debug("use{}", dynamicDataSourceEnum.getDataSource());
        return dynamicDataSourceEnum;
    }

    /**
     * 初始化数据源列表
     *
     * @param sameDbPrivate 对内数据源
     * @param sameDbPublic  对外数据源
     */
    public void initDatasource(DataSource sameDbPrivate,
                               DataSource sameDbPublic) {
        MapObject, 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;

/**
 * @description:自定义动态数据源注解
 * @author: aresbf
 * @createDate: 2022/10/26
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface SameDbDynamicDataSource {
    /**
     * 数据源标识
     *
     * @return DynamicDataSourceEnum
     */
    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;

/**
 * @description:动态数据源拦截
 * @author: aresbf
 * @createDate: 2022/10/26
 */
@Aspect
@Component
public class HandleDatasourceAspect {

    /**
     * 所有标识sameDbDynamicDataSource注解的类和方法
     */
    @Pointcut("@annotation(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)||@within(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)")
    public void pointcut() {
    }

    /**
     * 方法执行前
     *
     * @param joinPoint 拦截点
     */
    @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;

/**
 * @description: 测试
 * @author: aresbf
 * @createDate: 2022/10/24
 */
@RestController
@RequestMapping
@Slf4j
public class TestController {
    @Autowired
    private GoodsInfoService goodsInfoService;
    @Autowired
    private UserInfoService userInfoService;
    @Autowired
    private BrandInfoService brandInfoService;

    /**
     * 查内部
     *
     * @return ok
     */
    @GetMapping("/queryPrivate")
    @SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PRIVATE)
    public String queryPrivate() throws JsonProcessingException {
        ListGoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();
        ListUserInfoPO> userInfoPOS = userInfoService.queryAll();
        ListBrandInfoPO> brandInfoPOS = brandInfoService.queryAll();
        HashMapString, List> resultMap = new HashMap>();
        resultMap.put("goodsInfo", goodsInfoPOS);
        resultMap.put("userInfo", userInfoPOS);
        resultMap.put("brandInfo", brandInfoPOS);
        ObjectMapper objectMapper = new ObjectMapper();
        return objectMapper.writeValueAsString(resultMap);
    }

    /**
     * 查外部
     *
     * @return ok
     */
    @GetMapping("/queryPublic")
    @SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PUBLIC)
    public String queryPublic() throws JsonProcessingException {
        ListGoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();
        ListUserInfoPO> userInfoPOS = userInfoService.queryAll();
        ListBrandInfoPO> brandInfoPOS = brandInfoService.queryAll();
        HashMapString, 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"}]}

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: