springboot+Mybatis(MybatisPlus)+HikariCP多数据源动态配置(三个数据源)

刺骨的言语ヽ痛彻心扉 2022-11-26 09:45 1149阅读 0赞

我们新做的系统可能是很多个数据源，所以我闲来无事研究一下Mybatis多数据源的配置，然后连接池只要你不配置默认就是HikariCP，被称为最快速的连接池。所以我们使用HikariCP数据库连接池，需要用到aop动态切换，话不多说，上代码了。

1.pom.xml大概要使用的包，仅供参考，我也需要自动生成代码所以也会有MybatisPlus的包

<dependency>
 <groupId>com.github.pagehelper</groupId>
 <artifactId>pagehelper</artifactId>
 <version>4.1.0</version>
 </dependency>
 
 
 <dependency>
 <groupId>com.baomidou</groupId>
 <artifactId>mybatis-plus</artifactId>
 <version>2.1.8</version>
 </dependency>
 
 
 <dependency>
 <groupId>org.apache.velocity</groupId>
 <artifactId>velocity-engine-core</artifactId>
 <version>2.0</version>
 </dependency>
 
 
 <dependency>
 <groupId>org.mybatis.spring.boot</groupId>
 <artifactId>mybatis-spring-boot-starter</artifactId>
 <version>1.3.2</version>
 </dependency>
 
 
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-aop</artifactId>
 </dependency>
 
 <dependency>
 <groupId>org.assertj</groupId>
 <artifactId>assertj-core</artifactId>
 </dependency>

2.然后就是yml的配置。

spring:
      aop:
        proxy-target-class: true
        # proxy-target-class属性值决定是基于接口的还是基于类的代理被创建。
        #如果proxy-target-class 属性值被设置为true，那么基于类的代理将起作用（这时需要cglib库）。如果proxy-target-class属值被设置为false或者这个属性被省略，那么标准的JDK 基于接口的代理将起作用。
        auto: true
      application:
        name: gsa-geographic-system
      datasource:
        ds0:
          jdbc-url: jdbc:mysql://localhost:3306/xdclass?useUnicode=true&characterEncoding=utf-8&useSSL=false
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
        ds1:
          jdbc-url: jdbc:mysql://localhost:3306/mall_0?useUnicode=true&characterEncoding=utf-8&useSSL=false
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
        ds2:
          jdbc-url: jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterEncoding=utf-8&useSSL=false
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver

3.以上准备好了以后就需要配置注入了，创建类MybatisPlusConfig。

package gsa.geographic.system.config.dataSource;
 
 import com.baomidou.mybatisplus.MybatisConfiguration;
 import com.baomidou.mybatisplus.entity.GlobalConfiguration;
 import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
 import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
 import com.github.pagehelper.PageHelper;
 import gsa.geographic.system.enumclass.DBTypeEnum;
 import org.apache.ibatis.plugin.Interceptor;
 import org.apache.ibatis.session.SqlSessionFactory;
 import org.apache.ibatis.type.JdbcType;
 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.core.io.support.PathMatchingResourcePatternResolver;
 
 import javax.sql.DataSource;
 import java.util.HashMap;
 import java.util.Map;
 import java.util.Properties;
 
 /**
 * 分页配置
 *
 * @Author df
 * @Date 2019/8/20 10:09
 * @Version 1.0
 */
 @Configuration
 public class MybatisPlusConfig {
 
 /**
 * mybatis-plus分页插件 
 * 文档：http://mp.baomidou.com 
 */
 @Bean
 public PaginationInterceptor paginationInterceptor() {
 PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
 paginationInterceptor.setDialectType("mysql");
 // paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不受限制);
 //return new PerformanceInterceptor();
 return paginationInterceptor;
 }
 
 /**
 * 配置mybatis的分页插件pageHelper
 *
 * @return
 */
 @Bean
 public PageHelper pageHelper() {
 PageHelper pageHelper = new PageHelper();
 Properties properties = new Properties();
 properties.setProperty("offsetAsPageNum", "true");
 properties.setProperty("rowBoundsWithCount", "true");
 properties.setProperty("reasonable", "true");
 properties.setProperty("dialect", "mysql"); //配置mysql数据库的方言 pageHelper.setProperties(properties); return pageHelper; }}
 return pageHelper;
 }
 
 /**
 * 创建第一个数据源
 * @return
 */
 @Bean(name = "ds0")
 @ConfigurationProperties(prefix = "spring.datasource.ds0")
 public DataSource dataSource0() {
 return DataSourceBuilder.create().build();
 }
 
 /**
 * 创建第二个数据源
 * @return
 */
 @Bean(name = "ds1")
 @ConfigurationProperties(prefix = "spring.datasource.ds1")
 public DataSource dataSource1() {
 return DataSourceBuilder.create().build();
 }
 
 /**
 * 创建第三个数据源
 * @return
 */
 @Bean(name = "ds2")
 @ConfigurationProperties(prefix = "spring.datasource.ds2")
 public DataSource dataSource2() {
 return DataSourceBuilder.create().build();
 }
 
 /**
 * 动态数据源配置
 *
 * @return
 */
 @Bean
 @Primary
 public DataSource multipleDataSource(@Qualifier("ds0") DataSource ds0,
 @Qualifier("ds1") DataSource ds1,
 @Qualifier("ds2") DataSource ds2) {
 DynamicDataSource dynamicDataSource = new DynamicDataSource();
 Map<Object, Object> dataSources = new HashMap<>();
 dataSources.put(DBTypeEnum.DS0.getValue(), ds0);
 dataSources.put(DBTypeEnum.DS1.getValue(), ds1);
 dataSources.put(DBTypeEnum.DS2.getValue(), ds2);
 dynamicDataSource.setTargetDataSources(dataSources);
 dynamicDataSource.setDefaultTargetDataSource(ds0);
 return dynamicDataSource;
 }
 
 @Bean("sqlSessionFactory")
 public SqlSessionFactory sqlSessionFactory() throws Exception {
 // 导入mybatissqlsession配置
 MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
 // 指明数据源
 sessionFactory.setDataSource(multipleDataSource(dataSource0(), dataSource1(), dataSource2()));
 // 指明mapper.xml位置(配置文件中指明的xml位置会失效用此方式代替，具体原因未知)
 sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/**Mapper.xml"));
 // 指明实体扫描(多个package用逗号或者分号分隔)
 sessionFactory.setTypeAliasesPackage("gsa.geographic.system.entity");
 // 导入mybatis配置
 MybatisConfiguration configuration = new MybatisConfiguration();
 configuration.setJdbcTypeForNull(JdbcType.NULL);
 configuration.setMapUnderscoreToCamelCase(true);
 configuration.setCacheEnabled(false);
 sessionFactory.setConfiguration(configuration);
 // 添加分页功能
 sessionFactory.setPlugins(new Interceptor[]{
 paginationInterceptor()
 });
 // 导入全局配置
 sessionFactory.setGlobalConfig(globalConfiguration());
 return sessionFactory.getObject();
 }
 
 /**
 * 在代码中配置MybatisPlus替换掉application.yml中的配置
 *
 * @return
 */
 @Bean
 public GlobalConfiguration globalConfiguration() {
 GlobalConfiguration configuration = new GlobalConfiguration();
 configuration.setLogicDeleteValue("-1");
 configuration.setLogicNotDeleteValue("1");
 // 主键类型 0:数据库ID自增, 1:用户输入ID,2:全局唯一ID (数字类型唯一ID), 3:全局唯一ID UUID
 configuration.setIdType(0);
 // 驼峰下划线转换
 configuration.setDbColumnUnderline(true);
 // 是否动态刷新mapper
 configuration.setRefresh(true);
 return configuration;
 }
 
 }

4.创建动态数据源的获取类DynamicDataSource继承AbstractRoutingDataSource

package gsa.geographic.system.config.dataSource;
    import gsa.geographic.system.config.dataSource.DbContextHolder;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
     
    /**
     * @Author df
     * 扩展Spring的AbstractRoutingDataSource抽象类，实现动态数据源（他的作用就是动态切换数据源）
     * AbstractRoutingDataSource中的抽象方法determineCurrentLookupKey是实现数据源的route的核心，
     * 这里对该方法进行Override。【上下文DbContextHolder为一线程安全的ThreadLocal】
     * @Date 2019/9/3 15:39
     * @Version 1.0
     */
     
     
    public class DynamicDataSource extends AbstractRoutingDataSource {
        /**
         * 取得当前使用哪个数据源
         *
         * @return
         */
        @Override
        protected Object determineCurrentLookupKey() {
            return DbContextHolder.getDbType();
        }
    }

5.创建获取，设置数据源的类DbContextHolder，为了线程安全使用了ThreadLocal。

package gsa.geographic.system.config.dataSource;
    import gsa.geographic.system.enumclass.DBTypeEnum;
     
    /**
     * @Author df
     * @Date 2019/9/3 16:14
     * @Version 1.0
     * @description：设置，获取，清空 当前线程内的数据源变量。
     */
    public class DbContextHolder {
        private static final ThreadLocal contextHolder = new ThreadLocal();
     
        /**
         * 设置数据源
         *
         * @param dbTypeEnum
         */
        public static void setDbType(DBTypeEnum dbTypeEnum) {
            contextHolder.set(dbTypeEnum.getValue());
        }
     
        /**
         * 取得当前数据源
         *
         * @return
         */
        public static String getDbType() {
            return (String) contextHolder.get();
        }
     
        /**
         * 清除上下文数据
         */
        public static void clearDbType() {
            contextHolder.remove();
        }
    }

6.创建枚举类DBTypeEnum，方便添加查看哪些枚举类

package gsa.geographic.system.enumclass;
    /**
     * @Author df
     * @Date 2019/9/3 15:45
     * @Version 1.0
     * 设置数据源
     */
    public enum DBTypeEnum {
        DS0("ds0"), DS1("ds1"), DS2("ds2");
        private String value;
     
        DBTypeEnum(String value) {
            this.value = value;
        }
     
        public String getValue() {
            return value;
        }
     
    }

7.再添加一个aop切面类DataSourceSwitchAspect，这样就可以动态切换数据源了

package gsa.geographic.system.config.dataSource;
     
    import gsa.geographic.system.enumclass.DBTypeEnum;
    import lombok.extern.slf4j.Slf4j;
    import org.aspectj.lang.JoinPoint;
    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.util.Objects;
     
    /**
     * AOP方式动态切换数据源
     *
     * @Author df
     * @Date 2019/9/3 16:26
     * @Version 1.0
     */
     
    @Component
    @Aspect
    @Order(-100) //这是为了保证AOP在事务注解之前生效,Order的值越小,优先级越高
    @Slf4j
    public class DataSourceSwitchAspect {
        @Pointcut("execution(* gsa.geographic.system.service..*.*(..))")
        private void dbAspect() {
     
        }
     
        @Before("dbAspect()")
        public void db(JoinPoint joinPoint) {
            MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
            DataSourceSwitch dataSourceSwitch = methodSignature.getMethod().getAnnotation(DataSourceSwitch.class);
            if (Objects.isNull(dataSourceSwitch) || Objects.isNull(dataSourceSwitch.value())) {
                DbContextHolder.setDbType(DBTypeEnum.DS0);
            } else {
                switch (dataSourceSwitch.value().getValue()) {
                    case "ds0":
                        DbContextHolder.setDbType(DBTypeEnum.DS0);
                        break;
                    case "ds1":
                        DbContextHolder.setDbType(DBTypeEnum.DS1);
                        break;
                    case "ds2":
                        DbContextHolder.setDbType(DBTypeEnum.DS2);
                        break;
                    default:
                        DbContextHolder.setDbType(DBTypeEnum.DS0);
                }
            }
        }
     
    }

8.再添加一个可以供我们用注解形式的调用方式的接口。

package gsa.geographic.system.config.dataSource;
    import gsa.geographic.system.enumclass.DBTypeEnum;
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
     
    /**
     * Service方法调用
     *
     * @Author df
     * @Date 2019/9/3 16:32
     * @Version 1.0
     */
    @Retention(RetentionPolicy.RUNTIME)
    @Target({ElementType.METHOD})
    public @interface DataSourceSwitch {
        DBTypeEnum value() default DBTypeEnum.DS0;
    }

好了配置以上我这边反正是可以启动正常的。

然后开始在service里边使用不同的数据源把，先给大家我的serviceImpl的示例把！这是使用第一个数据源，其实配置默认的数据源以后默认的那个数据源是可以不用写注解表名的，其他两个数据源需要写上注解。

package gsa.geographic.system.service.impl;
 
 import gsa.geographic.system.config.dataSource.DataSourceSwitch;
 import gsa.geographic.system.entity.TOrder0;
 import gsa.geographic.system.dao.TOrder0Mapper;
 import gsa.geographic.system.enumclass.DBTypeEnum;
 import gsa.geographic.system.service.TOrder0Service;
 import com.baomidou.mybatisplus.service.impl.ServiceImpl;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 
 import java.util.List;
 
 /**
 * 
 * 服务实现类
 * 
 *
 * @author df123
 * @since 2019-09-03
 */
 @Service
 public class TOrder0ServiceImpl extends ServiceImpl<TOrder0Mapper, TOrder0> implements TOrder0Service {
 
 @Autowired
 private TOrder0Mapper mapper;
 
 @DataSourceSwitch(DBTypeEnum.DS1)
 @Override
 public List getAll() {
 return mapper.selectList(null);
 }
 }

package gsa.geographic.system.service.impl;
 
 import gsa.geographic.system.config.dataSource.DataSourceSwitch;
 import gsa.geographic.system.entity.TGoods;
 import gsa.geographic.system.dao.TGoodsMapper;
 import gsa.geographic.system.enumclass.DBTypeEnum;
 import gsa.geographic.system.service.TGoodsService;
 import com.baomidou.mybatisplus.service.impl.ServiceImpl;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 
 import java.util.List;
 
 /**
 * 
 * 服务实现类
 * 
 *
 * @author df123
 * @since 2019-09-03
 */
 @Service
 public class TGoodsServiceImpl extends ServiceImpl<TGoodsMapper, TGoods> implements TGoodsService {
 
 @Autowired
 private TGoodsMapper goodsMapper;
 
 @DataSourceSwitch(DBTypeEnum.DS2)
 @Override
 public List getAll() {
 return goodsMapper.selectList(null);
 }
 }

9 然后在写上测试用例。

package gsa.geographic.system.service.impl;
     
    import gsa.geographic.system.service.TGoodsService;
    import gsa.geographic.system.service.TOrder0Service;
    import gsa.geographic.system.service.UserService;
    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.SpringJUnit4ClassRunner;
     
    import java.util.List;
     
    import static org.junit.Assert.*;
     
    /**
     * @Author df
     * @Date 2019/9/3 16:40
     * @Version 1.0
     */
    @SpringBootTest
    @RunWith(SpringJUnit4ClassRunner.class)
    public class UserServiceImplTest {
     
        @Autowired
        private UserService userService;
     
        @Autowired
        private TOrder0Service tOrder0Service;
        @Autowired
        private TGoodsService goodsService;
     
        @Test
        public void getAll() {
            List list = userService.selectList(null);
            List list1=tOrder0Service.getAll();
            List list2=goodsService.getAll();
            System.out.println(list);
        }
     
    }

10.控制台结果，也能看到确实使用了HikariCP的数据库连接池，这样就大公告成了

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RmQmVhdXRpZnVsTGl2ZQ_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RmQmVhdXRpZnVsTGl2ZQ_size_16_color_FFFFFF_t_70]: /images/20221124/32b457435c9048538d017cb8d8a0df6c.png