MyBatis、MyBatisPlus多数据源集成shardingJdbc

1.介绍

本篇的数据源是指，一个核心业务库、一个订单明细库，两个库没有重复的表，核心业务库没有任何分表策略，订单明细库将使用shardingJdbc进行分表管理。

数据访问层采用MybatisPlus，说一下MybatisPlus，它只是一个Mybaits上的插件，具体配置的时候和Mybatis基本一致。

2.依赖

主要依赖就是下面这两个starter

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>

3.配置

针对 sale_comment（评论表）进行分表，分表策略为goods_id（商品id）取模10，将数据分布到10张表，其他分片策略请参照官方文档，“springboot使用JPA集成sharding-jdbc进行分表”这篇文章提到了自定义分片策略。

#主数据库
mybusiness.datasource.main.driver-class-name=com.mysql.cj.jdbc.Driver
mybusiness.datasource.main.jdbc-url=jdbc:mysql://192.168.1.136:3306/mybusiness?characterEncoding=utf8&useSSL=false
mybusiness.datasource.main.username=root
mybusiness.datasource.main.password=root

#订单明细数据库
mybusiness.datasource.orderdetail.driver-class-name=com.mysql.cj.jdbc.Driver
mybusiness.datasource.orderdetail.jdbc-url=jdbc:mysql://192.168.1.136:3306/mybusinessorderdetail?characterEncoding=utf8&useSSL=false
mybusiness.datasource.orderdetail.username=root
mybusiness.datasource.orderdetail.password=root
mybusiness.datasource.orderdetail.type=com.zaxxer.hikari.HikariDataSource

#需要分库、分表的库
sharding.jdbc.datasource.names=mybusinessorderdetail

#分别为两个数据库指定mapper位置
mybusiness.main.mapper-locations=classpath:/mapper/main/*Mapper.xml
mybusiness.orderdetail.mapper-locations=classpath:/mapper/orderdetail/*Mapper.xml


#分片策略
#所有数据节点
sharding.jdbc.config.sharding.tables.sale_comment.actual-data-nodes=mybusinessorderdetail.sale_comment_$->{0..9}
#根据这个列分表
sharding.jdbc.config.sharding.tables.sale_comment.table-strategy.inline.sharding-column=goods_id
#分表规则为:对goods_id取模
sharding.jdbc.config.sharding.tables.sale_comment.table-strategy.inline.algorithm-expression=mybusinessorderdetail.sale_comment_$->{goods_id % 10}

4.编码

首先我们禁用 io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration 的自动配置。

@SpringBootApplication(exclude = io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.class)
@EnableSwagger2
public class RestaurantApplication {

    public static void main(String[] args) {
        SpringApplication.run(RestaurantApplication.class, args);

    }
}

然后创建核心业务的数据库连接池、对应的SqlSessionFactory、DataSourceTransactionManager。

CommonConfig.java（公用配置）如下：

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
public class CommonConfig {

    @Bean
    public PathMatchingResourcePatternResolver resourcePatternResolver(){
        return new PathMatchingResourcePatternResolver();
    }
}

MybatisMainConfig.java(核心业务库)如下:

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
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.core.env.Environment;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.Optional;
import java.util.stream.Stream;

@AutoConfigureAfter({CommonConfig.class})
@Configuration
@MapperScan(basePackages = "com.myBusiness.products.restaurant.dao.main",sqlSessionFactoryRef = "mainSqlSessionFactory")
public class MybatisMainConfig {

    private PathMatchingResourcePatternResolver resolver;

    private Environment environment;

    public MybatisMainConfig(Environment environment,PathMatchingResourcePatternResolver resolver){
        this.environment = environment;
        this.resolver = resolver;
    }

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

    @Bean(name = "mainDatasource")
    @ConfigurationProperties(prefix = "mybusiness.datasource.main")
    public DataSource mainDatasource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mainDatasourceTransactionManager")
    public DataSourceTransactionManager mainDatasourceTransactionManager(){
        return new DataSourceTransactionManager(mainDatasource());
    }

    @Bean(name = "mainSqlSessionFactory")
    public SqlSessionFactory mainSqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(mainDatasource());
        sqlSessionFactory.setMapperLocations(resolveMapperLocations(environment.getProperty("mybusiness.main.mapper-locations").split(",")));
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setDbConfig(new GlobalConfig.DbConfig().setDbType(DbType.MYSQL));
        sqlSessionFactory.setGlobalConfig(globalConfig);
        return sqlSessionFactory.getObject();
    }

    public Resource[] resolveMapperLocations(String[] locations) {
        return Stream.of(Optional.ofNullable(locations).orElse(new String[0]))
                .flatMap(location -> Stream.of(getResources(location)))
                .toArray(Resource[]::new);
    }

    private Resource[] getResources(String location) {
        try {
            return resolver.getResources(location);
        } catch (IOException e) {
            return new Resource[0];
        }
    }
}

SqlSessionFactory这里只是一个简单的基本配置，具体请参照:MybatisPlusAutoConfiguration.sqlSessionFactory(DataSource)

然后是订单明细库的配置：

@AutoConfigureAfter({CommonConfig.class})
@Configuration
@MapperScan(basePackages = "com.myBusiness.products.restaurant.dao.orderDetail",sqlSessionFactoryRef = "orderDetailSqlSessionFactory")
@EnableConfigurationProperties({SpringBootShardingRuleConfigurationProperties.class, SpringBootMasterSlaveRuleConfigurationProperties.class})
public class MybatisOrderDetailConfig {

    @Autowired
    private SpringBootShardingRuleConfigurationProperties shardingProperties;

    @Autowired
    private SpringBootMasterSlaveRuleConfigurationProperties masterSlaveProperties;

    private PathMatchingResourcePatternResolver resolver;

    private Environment environment;

    public MybatisOrderDetailConfig(Environment environment,PathMatchingResourcePatternResolver resolver){
        this.environment = environment;
        this.resolver = resolver;
    }

    /**
     * 订单明细数据源
     * @return
     */
    @Bean(name = "orderdetaildatasource")
    @ConfigurationProperties(prefix = "mybusiness.datasource.orderdetail")
    public DataSource orderDetailDatasource()  {
        return DataSourceBuilder.create().build();
    }

    /**
     * 以mybusinessorderdetail为key，dataSource为value创建map
     * 参照 io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration
     * 这一步目的是将原始DataSource和我们在配置文件中的分片策略对应起来
     * 注意key要和shardingJdbc配置项中的一致
     * @return
     * @throws SQLException
     */
    @Bean(name = "shardingDataSource")
    public DataSource shardingDataSource() throws SQLException {
        Map<String,DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("mybusinessorderdetail", orderDetailDatasource());
        return null == masterSlaveProperties.getMasterDataSourceName()
                ? ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingProperties.getShardingRuleConfiguration(), shardingProperties.getConfigMap(), shardingProperties.getProps())
                : MasterSlaveDataSourceFactory.createDataSource(
                dataSourceMap, masterSlaveProperties.getMasterSlaveRuleConfiguration(), masterSlaveProperties.getConfigMap(), masterSlaveProperties.getProps());
    }

    @Bean(name = "orderDetailDatasourceTransactionManager")
    public DataSourceTransactionManager orderDetailDatasourceTransactionManager(){
        return new DataSourceTransactionManager(orderDetailDatasource());
    }

    /**
     * 将shardingJdbc创建的DataSource传入这里的SqlSessionFactory
     * @return
     * @throws Exception
     */
    @Bean(name = "orderDetailSqlSessionFactory")
    public SqlSessionFactory orderDetailSqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(shardingDataSource());
        sqlSessionFactory.setMapperLocations(resolveMapperLocations(environment.getProperty("mybusiness.orderdetail.mapper-locations").split(",")));
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setDbConfig(new GlobalConfig.DbConfig().setDbType(DbType.MYSQL));
        sqlSessionFactory.setGlobalConfig(globalConfig);
        return sqlSessionFactory.getObject();
    }

    public Resource[] resolveMapperLocations(String[] locations) {
        return Stream.of(Optional.ofNullable(locations).orElse(new String[0]))
                .flatMap(location -> Stream.of(getResources(location)))
                .toArray(Resource[]::new);
    }

    private Resource[] getResources(String location) {
        try {
            return resolver.getResources(location);
        } catch (IOException e) {
            return new Resource[0];
        }
    }
}

5.单元测试

创建10张表

# X 为0~10
create table if not exists `sale_comment_X`(
  `goods_id` bigint,
  `order_id` bigint,
  `user_id` bigint not null ,
  `comment` text,
  `comment_date` date,
  `review` text,
  `review_date` date,
  `score` int not null,
  primary key (`goods_id`,`order_id`)
);

Test类:

import java.time.LocalDate;
import java.util.List;

@SpringBootTest(classes = RestaurantApplication.class)
@RunWith(SpringRunner.class)
public class TestShardingJdbc {

    @Autowired
    SaleCommentService saleCommentService;

    @Test
    public void test(){
        SaleCommentEntity entity1 = new SaleCommentEntity();
        entity1.setGoodsId(1L);
        entity1.setComment("很好啊");
        entity1.setOrderId(11L);
        entity1.setScore(5);
        entity1.setUserId(111L);
        entity1.setCommentDate(LocalDate.now());

        SaleCommentEntity entity2 = new SaleCommentEntity();
        entity2.setGoodsId(2L);
        entity2.setComment("很好啊2");
        entity2.setOrderId(22L);
        entity2.setScore(4);
        entity2.setUserId(222L);
        entity2.setCommentDate(LocalDate.now());

        SaleCommentEntity entity3 = new SaleCommentEntity();
        entity3.setGoodsId(3L);
        entity3.setComment("很好啊3");
        entity3.setOrderId(33L);
        entity3.setScore(3);
        entity3.setUserId(333L);
        entity3.setCommentDate(LocalDate.now());

        saleCommentService.save(entity1);
        saleCommentService.save(entity2);
        saleCommentService.save(entity3);
    }

    @Test
    public void test1(){
        List<SaleCommentEntity> list = saleCommentService.list(
                new QueryWrapper<SaleCommentEntity>()
                        .lambda()
                        .orderByDesc(SaleCommentEntity::getCommentDate));
        for(SaleCommentEntity entity : list){
            System.out.println(entity);
        }
    }
}

测试通过