springboot-multi-datasource

springboot多数据源的Starter

简介

主要代码都来自于dynamic-datasource-spring-boot-starter

主要改动：

application.properties 前缀: spring.datasource.multi
数据库分组，比如订单库组 spring.datasource.multi.group.order

每个数据库组下分一个主库（master）和多个从库（slaves）

   spring.datasource.multi.group.order.master.url=xxx
   spring.datasource.multi.group.order.slaves[0].url=xxx
   spring.datasource.multi.group.order.slaves[1].url=xxx

通过注解@DsGroup动态指定数据库组
通过注解@Slave动态指定是否使用从库
在事务中只会使用主库

使用方法

依赖multi-datasource-spring-boot-starter

<dependency>
  <groupId>io.github.loushi135</groupId>
  <artifactId>multi-datasource-spring-boot-starter</artifactId>
  <version>${version}</version>
</dependency>

配置数据源组

#mybatis starter配置
mybatis.check-config-location=true
mybatis.config-location=classpath:mybatis-settings.xml
mybatis.mapper-locations=classpath*:sqlmap/**/*.xml

#全局配置druid配置
sppring.datasource.multi.druid.initial-size=2
sppring.datasource.multi.druid.max-active=10
sppring.datasource.multi.druid.query-timeout=30
sppring.datasource.multi.p6spy=true

#组testorder master连接池配置
sppring.datasource.multi.group.testorder.master.primary=true
sppring.datasource.multi.group.testorder.master.driver-class-name=org.h2.Driver
sppring.datasource.multi.group.testorder.master.username=sa
sppring.datasource.multi.group.testorder.master.password=""
sppring.datasource.multi.group.testorder.master.url=jdbc:h2:mem:testorder
#master连接池参数initial-size,将使用此值
sppring.datasource.multi.group.testorder.master.druid.initial-size=5

#组testorder下druid配置
sppring.datasource.multi.group.testorder.druid.initial-size=3
sppring.datasource.multi.group.testorder.druid.min-idle=1
sppring.datasource.multi.group.testorder.druid.max-active=10
sppring.datasource.multi.group.testorder.druid.max-wait=60000
sppring.datasource.multi.group.testorder.druid.time-between-eviction-runs-millis=60000
sppring.datasource.multi.group.testorder.druid.min-evictable-idle-time-millis=300000
sppring.datasource.multi.group.testorder.druid.validation-query=select 'x'
sppring.datasource.multi.group.testorder.druid.test-while-idle=true
sppring.datasource.multi.group.testorder.druid.test-on-borrow=false
sppring.datasource.multi.group.testorder.druid.validation-query-timeout=30
sppring.datasource.multi.group.testorder.druid.transaction-query-timeout=60

#组testorder下slave连接池
sppring.datasource.multi.group.testorder.slaves[0].driver-class-name=org.h2.Driver
sppring.datasource.multi.group.testorder.slaves[0].username=sa
sppring.datasource.multi.group.testorder.slaves[0].password=""
sppring.datasource.multi.group.testorder.slaves[0].url=jdbc:h2:mem:testorder-slave0

#组testorder1 master连接池配置
sppring.datasource.multi.group.testorder1.master.driver-class-name=org.h2.Driver
sppring.datasource.multi.group.testorder1.master.username=sa
sppring.datasource.multi.group.testorder1.master.password=""
sppring.datasource.multi.group.testorder1.master.url=jdbc:h2:mem:testorder1
sppring.datasource.multi.group.testorder1.master.druid.initial-size=5

#组testorder1 slave连接池配置
sppring.datasource.multi.group.testorder1.slaves[0].driver-class-name=org.h2.Driver
sppring.datasource.multi.group.testorder1.slaves[0].username=sa
sppring.datasource.multi.group.testorder1.slaves[0].password=""
sppring.datasource.multi.group.testorder1.slaves[0].url=jdbc:h2:mem:testorder1-slave0

前缀: sppring.datasource.multi

一级配置（MultiDataSourceProperties类下属性）：

p6spy:  是否使用p6spy输出，默认不输出, 开发测试环境可配置为true,用于打印可执行的sql,
eg:    p6spy KEY[] - 1545882694616|0|statement|connection 0|url|select id, name, title, user_id from tb_order where id = ?|select id, name, title, user_id from tb_order where id = 1    
druid:  全局druid配置，可以统一配置连接池参数，具体参数配置详见DruidConfig

group:   数据源分组 Map ,key为组名（一般设置为数据库名） value为类型： DataSourceGroupProperties

二级配置（DataSourceGroupProperties类下属性）：

master:  master数据库配置，类型：DataSourceProperty

slaves:  多个slave数据库配置

druid:   此组下druid默认配置

三级配置（DataSourceProperty类下属性）：

primary: 主否作为默认数据库连接池（用于没有配置@DsGroup）默认使用的连接池，可不配（默认使用第一个组的master）
driverClassName: JDBC DRIVER 
url:             JDBC URL地址
username:        JDBC 用户名
password:        JDBC 密码
druid:           DruidConfig 此连接池参数配置，优先使用此配置，
                 如果对应属性没有配置 则使用此group下druid配置 ,
                 group没有配置则使用全局druid配置，
                 再没有的话使用 默认参数配置,默认配置如下DataSourceSettingEnum

public enum DataSourceSettingEnum {

    initialSize("20"),

    minIdle("5"),

    maxActive("100"),

    /**
     * 配置获取连接等待超时的时间
     */
    maxWait("60000"),
    /**
     * 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
     */
    timeBetweenEvictionRunsMillis("60000"),

    /**
     * 配置一个连接在池中最小生存的时间，单位是毫秒
     */
    minEvictableIdleTimeMillis("300000"),

    validationQuery("SELECT 'x'"),

    testWhileIdle("true"),

    testOnBorrow("false"),
    /**
     * datasource 单位秒
     * 查询timeout throws SQLException;
     */
    queryTimeout("30");
}

注解定义

DsGroup 用于确定使用什么数据库组

/**
 * 可用于类和方法
 * 设置数据库组
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DsGroup {

    /**
     * database group name，support spel determine by params args
     * <pre> {@code
     * @DsGroup("testorder")
     * public class OrderServiceImpl extends OrderService {
     *
     *   using slave for group testorder1
     *   @DsGroup("testorder1")
     *   @Slave
     *   @Override
     *   public OrderPo addOrderForSlave() {
     *     return insert();
     *   }
     *
     *   default using master for group testorder
     *   @Override
     *   public OrderPo addOrder() {
     *     return insert();
     *   }
     *
     *   depends on params args to determine group and master or slave
     *   @DsGroup("#dsGroup")
     *   @Slave("#slave")
     *   @Override
     *   public OrderPo addOrderForSpel(String dsGroup,boolean slave) {
     *     return insert();
     *   }
     *
     * }}</pre>
     *
     * @return 数据源名称
     */
    String value();
}

Slave 用于确定是否使用从库

/**
 * 可用于类和方法
 * 是否使用从库
 * 不带@Slave注解使用主库，
 * 带@Slave注解如果 isSlave为false也使用主库
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Slave {
    boolean isSlave() default true;

    /**
     * 通过参数来定是否slave
     *
     * @return
     */
    String spel() default "";
}

// spel文档如下：
// https://docs.spring.io/spring-framework/docs/5.1.3.RELEASE/spring-framework-reference/core.html#expressions-evaluation
// https://docs.spring.io/spring-framework/docs/5.1.3.RELEASE/spring-framework-reference/core.html#expressions-language-ref

AOP,通过不同包路径设置不同数据库组

    @Bean
    public MultiDataSourceMatcherConfig multiDataSourceMatcherConfig() {
        return MultiDataSourceMatcherConfig.newInstance()
                .expressionMatcher("execution(* com.lsq.springboot.test.mybatis.aop.aspectj.facade.*Facade.*(..))", "testorder")
                .expressionMatcher("execution(* com.lsq.springboot.test.mybatis.aop.aspectj.repository.*Repository.*(..))", "testorder")
                .expressionMatcher("execution(* com.lsq.springboot.test.mybatis.aop.aspectj.repository1.*Repository.*(..))", "testorder1")
                ;
    }
    //如上所示 facade类和repository下都使用testorder组, repository1下都使用testorder1组

###Sample 详见test目录下单元测试

Mybatis-Test

测试准备

OrderFacade、OrderRepository 、Order1Repository定义

 @Component
 public class OrderFacadeImpl implements OrderFacade {
     @Autowired
     private OrderMapper orderMapper;
     public OrderPo findByOrderIdForMaster(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
     @Slave
     public OrderPo findByOrderIdForSlave(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
 }
   
 @Component
 public class OrderRepository {
     @Autowired
     private OrderMapper orderMapper;
     public OrderPo findByOrderIdForMaster(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
     @Slave
     public OrderPo findByOrderIdForSlave(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
 }
 @Component
 public class Order1Repository {
     @Autowired
     private OrderMapper orderMapper;
     public OrderPo findByOrderIdForMaster(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
     @Slave
     public OrderPo findByOrderIdForSlave(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
 }

OrderService默认使用testorder数据库

 @Service
 @DsGroup("testorder")
 public class OrderServiceImpl implements OrderService {
     @Autowired
     private OrderMapper orderMapper;
     @Autowired
     private OrderQuery orderQuery;
     private OrderPo insert() {
         OrderPo orderPo = new OrderPo();
         orderPo.setName(RandomUtil.randomStringFixLength(10));
         orderPo.setTitle(RandomUtil.randomStringFixLength(10));
         orderPo.setUserId(RandomUtil.nextInt(20));
         orderMapper.insert(orderPo);
         return orderPo;
     }
     @Override
     public OrderPo addOrderForMaster() {
         return insert();
     }
     @Override
     @Slave
     public OrderPo addOrderForSlave() {
         return insert();
     }
     @Override
     public OrderPo findByOrderIdForMaster(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
     @Slave
     @Override
     public OrderPo findByOrderIdForSlave(long orderId) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
     @DsGroup("testorder1")
     @Override
     public OrderPo addOrder1ForMaster() {
         return insert();
     }
     @DsGroup("testorder1")
     @Slave
     @Override
     public OrderPo addOrder1ForSlave() {
         return insert();
     }
     @Override
     public OrderPo findByOrderId1ForMaster(long orderId) {
         return orderQuery.findAllForMaster().get(0);
     }
     @Override
     public OrderPo findByOrderId1ForSlave(long orderId) {
         return orderQuery.findAllForSlave().get(0);
     }
     @DsGroup("#dsGroup")
     @Slave(spel = "#slave")
     @Override
     public OrderPo findByOrderIdForSpel(long orderId, String dsGroup, boolean slave) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
     @DsGroup("#spelModel.dsGroup")
     @Slave(spel = "#spelModel.slave")
     @Override
     public OrderPo findByOrderIdForSpel(long orderId, SpelModel spelModel) {
         return orderMapper.selectByPrimaryKey(orderId);
     }
 }

OrderMapper为mybatis generator自动生成
OrderQuery 使用testorder1 数据库

 public interface OrderQuery {
     @DsGroup("testorder1")
     @Slave(isSlave = false)
     List<OrderPo> findAllForMaster();
  
     @DsGroup("testorder1")
     @Slave
     List<OrderPo> findAllForSlave();
 }

测试主从多数据源新增查询

 @Test
 public void addOrder() {
  
     //testorder主库新增orderpo
     OrderPo orderPoMaster = orderService.addOrderForMaster();
     //testorder从库新增orderpo
     OrderPo orderPoSlave = orderService.addOrderForSlave();
  
     //由于两个不同库，h2 db，自增长 ，id相同
     Assert.assertEquals(orderPoMaster.getId(), orderPoSlave.getId());
  
     //testorder主库查询orderpo
     OrderPo poMaster = orderService.findByOrderIdForMaster(orderPoMaster.getId());
     //testorder从库查询orderpo
     OrderPo poSlave = orderService.findByOrderIdForSlave(orderPoSlave.getId());
  
     //查询结果与新增一致
     Assert.assertEquals(orderPoMaster.getName(), poMaster.getName());
     Assert.assertEquals(orderPoSlave.getName(), poSlave.getName());
  
     //testorder1主库新增orderpo
     orderPoMaster = orderService.addOrder1ForMaster();
     //testorder1从库新增orderpo
     orderPoSlave = orderService.addOrder1ForSlave();
     Assert.assertEquals(orderPoMaster.getId(), orderPoSlave.getId());
  
     poMaster = orderService.findByOrderId1ForMaster(orderPoMaster.getId());
     poSlave = orderService.findByOrderId1ForSlave(orderPoSlave.getId());
  
     //查询结果与新增一致
     Assert.assertEquals(orderPoMaster.getName(), poMaster.getName());
     Assert.assertEquals(orderPoSlave.getName(), poSlave.getName());
 }

测试事务事务中只会用master

 //事务中 只会用master
 @Test
 public void testTransaction() {
  
     transactionTemplate.execute(transactionStatus -> {
         //testorder主库新增orderpo
         OrderPo orderPoMaster = orderService.addOrderForMaster();
         //testorder从库新增orderpo，由于在事务中，将使用主库
         OrderPo orderPoSlave = orderService.addOrderForSlave();
  
         //在主库新增，所以产生两个id
         Assert.assertEquals(1L, orderPoMaster.getId().longValue());
         Assert.assertEquals(2L, orderPoSlave.getId().longValue());
  
         //查询
         OrderPo poMaster = orderService.findByOrderIdForMaster(orderPoMaster.getId());
         OrderPo poSlave = orderService.findByOrderIdForSlave(orderPoSlave.getId());
  
         Assert.assertEquals(orderPoMaster.getName(), poMaster.getName());
         Assert.assertEquals(orderPoSlave.getName(), poSlave.getName());
  
         return null;
     });
 }

spel表达式测试，通过动态参数选择不同数据库组和主从库

 @Test
 public void testSpel() {
     //testorder主库新增orderpo
     OrderPo orderPoMaster = orderService.addOrderForMaster();
     //testorder从库新增orderpo
     OrderPo orderPoSlave = orderService.addOrderForSlave();
     //testorder1主库新增orderpo
     OrderPo orderPoMaster1 = orderService.addOrder1ForMaster();
     //testorder1从库新增orderpo
     OrderPo orderPoSlave1 = orderService.addOrder1ForSlave();
  
     //spel 查询testorder组 master库
     OrderPo poMaster = orderService.findByOrderIdForSpel(orderPoMaster.getId(), "testorder", false);
     Assert.assertEquals(orderPoMaster.getId(), poMaster.getId());
     Assert.assertEquals(orderPoMaster.getName(), poMaster.getName());
  
     //spel 查询testorder组 slave库
     OrderPo poSlave = orderService.findByOrderIdForSpel(orderPoSlave.getId(), "testorder", true);
     Assert.assertEquals(orderPoSlave.getId(), poSlave.getId());
     Assert.assertEquals(orderPoSlave.getName(), poSlave.getName());
  
  
     //spel model 查询testorder1组 master库
     SpelModel spelModel = new SpelModel();
     spelModel.setDsGroup("testorder1");
     OrderPo poMaster1 = orderService.findByOrderIdForSpel(orderPoMaster1.getId(), spelModel);
     Assert.assertEquals(orderPoMaster1.getId(), poMaster1.getId());
     Assert.assertEquals(orderPoMaster1.getName(), poMaster1.getName());
  
     //spel model 查询testorder1组 slave库
     spelModel.setSlave(true);
     OrderPo poSlave1 = orderService.findByOrderIdForSpel(orderPoSlave1.getId(), spelModel);
     Assert.assertEquals(orderPoSlave1.getId(), poSlave1.getId());
     Assert.assertEquals(orderPoSlave1.getName(), poSlave1.getName());
 }

AOP测试，不同包对应不同数据库组

 @Test
 public void testAop() {
     //testorder主库新增orderpo
     OrderPo orderPoMaster = orderService.addOrderForMaster();
     //testorder从库新增orderpo
     OrderPo orderPoSlave = orderService.addOrderForSlave();
     //testorder1主库新增orderpo
     OrderPo orderPoMaster1 = orderService.addOrder1ForMaster();
     //testorder1从库新增orderpo
     OrderPo orderPoSlave1 = orderService.addOrder1ForSlave();
  
  
     //testorder主库查询
     OrderPo poMaster = orderFacade.findByOrderIdForMaster(orderPoMaster.getId());
     Assert.assertEquals(orderPoMaster.getId(), poMaster.getId());
     Assert.assertEquals(orderPoMaster.getName(), poMaster.getName());
  
     //testorder从库查询
     OrderPo poSlave = orderFacade.findByOrderIdForSlave(orderPoMaster.getId());
     Assert.assertEquals(orderPoSlave.getId(), poSlave.getId());
     Assert.assertEquals(orderPoSlave.getName(), poSlave.getName());
  
     //testorder主库查询
     poMaster = orderRepository.findByOrderIdForMaster(orderPoMaster.getId());
     Assert.assertEquals(orderPoMaster.getId(), poMaster.getId());
     Assert.assertEquals(orderPoMaster.getName(), poMaster.getName());
  
     //testorder从库查询
     poSlave = orderRepository.findByOrderIdForSlave(orderPoMaster.getId());
     Assert.assertEquals(orderPoSlave.getId(), poSlave.getId());
     Assert.assertEquals(orderPoSlave.getName(), poSlave.getName());
  
     //testorder1主库查询
     poMaster = order1Repository.findByOrderIdForMaster(orderPoMaster.getId());
     Assert.assertEquals(orderPoMaster1.getId(), poMaster.getId());
     Assert.assertEquals(orderPoMaster1.getName(), poMaster.getName());
  
     //testorder1从库查询
     poSlave = order1Repository.findByOrderIdForSlave(orderPoMaster.getId());
     Assert.assertEquals(orderPoSlave1.getId(), poSlave.getId());
     Assert.assertEquals(orderPoSlave1.getName(), poSlave.getName());
 }

Mybatis-Plus-Test

loushi135 / springboot-multi-datasource Goto Github PK

springboot-multi-datasource's Introduction

springboot-multi-datasource

简介

使用方法

注解定义

springboot-multi-datasource's People

Contributors

Watchers

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent