springboot+mybatis多数据源解决方案

最新推荐文章于 2024-08-09 18:07:53 发布

Choimeyu

最新推荐文章于 2024-08-09 18:07:53 发布

阅读量1.1k

点赞数 1

分类专栏： Java 文章标签：数据源 springboot+boot

本文链接：https://blog.csdn.net/JINYUANCAI/article/details/80514979

版权

Java 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

s pringboot+mybatis多数据源自动切换解决方案

在项目开发中，难免会遇到需要查询多个数据源的数据。

简单点，举个例子：需要写一个查找用户订单详情的接口。此时，用户表是存储在mysql，而订单表是存储在sql-server

查询流程：1、查询用户的基本信息

2、查询该用户的订单信息

3、组装数据返回

这里就需要用到数据源自动切换了，使用mysql数据源查询出用户基本信息后，需要切换成sql-server数据
源查询该用户的订单信息。

有两种解决方案：

// =========================== 方案一

在这里，我选择在DAO层(数据交换层)进行数据源切换。设置默认的数据源是：mysql

那么问题来了，怎么进行数据源切换呢？

Spring AOP；既然，选择了在DAO层进行切换，那切入点就是mybatis的mapper类。单独为sql-server的mapper创建一个package msmapper

调用sql-server的mapper前，切换成sql-server的数据源；查询完数据后，再切换为mysql的数据源。

没错，就这么简单，上代码。

/**
* 数据源
* @author  choimeyu
* @date 2018/04/20
*/
public enum DataSourceKey {
    MYSQL,
    SQLSERVER
}

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
* 保存当前线程使用的数据源名
* @author choimeyu
* @date 2018/04/21
*/
public class DataSourceContextHolder {

    public static final Logger LOGGER = LoggerFactory.getLogger(DataSourceContextHolder.class);

    private static final ThreadLocal<Object> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
    * 设置数据源名
    * @param dbName 数据源名
    */
    public static void setDB(Object dbName) {
        LOGGER.debug("切换到{}数据源", dbName);
        CONTEXT_HOLDER.set(dbName);
    }

    /**
    * 获取数据源名
    * @return
    */
    public static Object getDB() {
        return CONTEXT_HOLDER.get();
    }

    /**
    * 清除数据源名
    */
    public static void clearDB() {
    CONTEXT_HOLDER.remove();
    }
}

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
* dao层数据源切换切面
* @author choimeyu
* @date 2018/04/21
*/
@Aspect
@Component
public class DynamicDataSourceAspect {

    private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    @Pointcut("execution(* com.choimeyu.tech.common.mssqlmapper.*.*(..))")
    public void daoAop() {
    }

    @Before("daoAop()")
    public void beforeSwitchDS(JoinPoint joinPoint) {
        // 切换数据源
        DataSourceContextHolder.setDB(DataSourceKey.SQLSERVER);
    }


    @After("daoAop()")
    public void afterSwitchDS(JoinPoint joinPoint) {
        DataSourceContextHolder.clearDB();
        LOGGER.info("Restore DataSource to [{}] in Method [{}]",
        DataSourceContextHolder.getDB(), joinPoint.getSignature());
    }
}

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
* @author choimeyu
* @date 2018/04/21
*/
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    private static final Logger LOGGER = LoggerFactory.getLogger(DynamicRoutingDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {
        LOGGER.debug("数据源为{}", DataSourceContextHolder.getDB());
        return DataSourceContextHolder.getDB();
    }
}

* 数据源配置
* @author choimeyu
* @date 2018/04/21
*/
@Configuration
public class DataSourceConfigurer {

    /**
    * mysql DataSource
    *
    * @return dataSource
    * @Primary 注解用于标识默认使用的 DataSource Bean，因为有多个 DataSource Bean，该注解可用于 dev
    * 或 source DataSource Bean, 但不能用于 dynamicDataSource Bean, 否则会产生循环调用
    * @ConfigurationProperties 注解用于从 application.properties 文件中读取配置，为 Bean 设置属性
    */
    @Bean("mysqlDS")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDS() {
        // 使用Druid连接池
        return DruidDataSourceBuilder.create().build();
    }

    /**
    * sqlServer DataSource
    * @return
    */
    @Bean("sqlServerDS")
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource sqlServerDS() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
    * dynamic DataSource
    * @return
    */
    @Bean("dynamicDS")
    public DataSource dynamicDataSource() {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put(DataSourceKey.MYSQL, mysqlDS());
        dataSourceMap.put(DataSourceKey.SQLSERVER, sqlServerDS());
        // 设置默认数据源为mysql
        dynamicRoutingDataSource.setDefaultTargetDataSource(mysqlDS());
        // 设置mysql和sqlServer为指定数据源
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
        return dynamicRoutingDataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(mysqlDS());
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath:com/choimeyu/tech/common/mapper/*.xml"));
        factoryBean.setTypeAliasesPackage("com.choimeyu.tech.common.model");
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionFactory sqlServerSessionFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(sqlServerDS());
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath:com/choimeyu/tech/common/mssqlmapper/*.xml"));
        factoryBean.setTypeAliasesPackage("com.choimeyu.tech.common.model");
        return factoryBean.getObject();
    }

}

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.SqlServerMapper;

public interface BaseSqlServerDao<T> extends Mapper<T>, SqlServerMapper<T> {

}

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface BaseDao<T> extends Mapper<T>,MySqlMapper<T>{

}

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import tk.mybatis.spring.mapper.MapperScannerConfigurer;

import java.util.Properties;

/**
* mybatis配置
* @author choimeyu
*
*/
@Configuration
public class MyBatisMapperScannerConfig {

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.choimeyu.tech.common.mapper");
        Properties properties = new Properties();
        properties.setProperty("mappers", BaseDao.class.getName());
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "MYSQL");
        mapperScannerConfigurer.setProperties(properties);
        return mapperScannerConfigurer;
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer1() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlServerSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.choimeyu.tech.common.mssqlmapper");
        Properties properties = new Properties();
        properties.setProperty("mappers", BaseSqlServerDao.class.getName());
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "SQLSERVER");
        mapperScannerConfigurer.setProperties(properties);
        return mapperScannerConfigurer;
    }
}

// =========================== 方案二

第二种方案。更加简单，无需我们手写切换数据源代码，只需要配置Druid连接池即可进行切换，需要注意的是：使用了mybatis Pagehelper分页插件时，需要在spring.yml配置文件中多加个配置，才能生效。如下

spring:
    省略 . . .
pagehelper:
auto-runtime-dialect: true

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.choimeyu.tech.common.constant.DataSourceKey;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
* 数据源配置
* @author choimeyu
* @date 2018/04/21
*/
@Configuration
public class DataSourceConfigurer {

    /**
    * mysql DataSource
    *
    * @return dataSource
    * @Primary 注解用于标识默认使用的 DataSource Bean，因为有多个 DataSource Bean，该注解可用于 dev
    * 或 source DataSource Bean, 但不能用于 dynamicDataSource Bean, 否则会产生循环调用
    * @ConfigurationProperties 注解用于从 application.properties 文件中读取配置，为 Bean 设置属性
    */
    @Bean("mysqlDS")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDS() {
        // 使用Druid连接池
        return DruidDataSourceBuilder.create().build();
    }

    /**
    * sqlServer DataSource
    * @return
    */
    @Bean("sqlServerDS")
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource sqlServerDS() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
    * dynamic DataSource
    * @return
    */
    @Bean("dynamicDS")
    public DataSource dynamicDataSource() {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put(DataSourceKey.MYSQL, mysqlDS());
        dataSourceMap.put(DataSourceKey.SQLSERVER, sqlServerDS());
        // 设置默认数据源为mysql
        dynamicRoutingDataSource.setDefaultTargetDataSource(mysqlDS());
        // 设置mysql和sqlServer为指定数据源
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
        return dynamicRoutingDataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(mysqlDS());
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath:com/choimeyu/tech/common/mapper/*.xml"));
        factoryBean.setTypeAliasesPackage("com.choimeyu.tech.common.model");
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionFactory sqlServerSessionFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(sqlServerDS());
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("classpath:com/choimeyu/tech/common/mssqlmapper/*.xml"));
        factoryBean.setTypeAliasesPackage("com.choimeyu.tech.common.model");
        return factoryBean.getObject();
    }

}

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.SqlServerMapper;

public interface BaseSqlServerDao<T> extends Mapper<T>, SqlServerMapper<T> {

}

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface BaseDao<T> extends Mapper<T>,MySqlMapper<T>{

}

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import tk.mybatis.spring.mapper.MapperScannerConfigurer;

import java.util.Properties;

/**
* mybatis配置
* @author choimeyu
*
*/
@Configuration
public class MyBatisMapperScannerConfig {

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.choimeyu.tech.common.mapper");
        Properties properties = new Properties();
        properties.setProperty("mappers", BaseDao.class.getName());
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "MYSQL");
        mapperScannerConfigurer.setProperties(properties);
        return mapperScannerConfigurer;
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer1() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlServerSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.choimeyu.tech.common.mssqlmapper");
        Properties properties = new Properties();
        properties.setProperty("mappers", BaseSqlServerDao.class.getName());
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "SQLSERVER");
        mapperScannerConfigurer.setProperties(properties);
        return mapperScannerConfigurer;
    }
}