Spring Boot + Mybatis + Sharding-JDBC 在复杂库表场景下的尝试

最新推荐文章于 2023-04-26 09:53:52 发布

置顶乌苏100

最新推荐文章于 2023-04-26 09:53:52 发布

阅读量650

点赞数 1

分类专栏： Spring mybatis sharding-jdbc 文章标签： java mybatis mysql 分布式数据库

本文链接：https://blog.csdn.net/qiyanli123/article/details/103843917

版权

Spring 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

mybatis

1 篇文章 0 订阅

订阅专栏

sharding-jdbc

1 篇文章 0 订阅

订阅专栏

Sharding-JDBC是开源分布式数据库中间件解决方案ShardingSphere的一部分，定位为轻量级Java框架，在Java的JDBC层提供服务，实现标准化的数据分片、分布式事务和数据库治理功能。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
本文记录了Sharding-JDBC在相对复杂的库表场景中的尝试，技术栈采用Spring Boot + Mybatis + Sharding-JDBC + Druid。

一、需求简介：按下述规则分库，并实现读写分离

core库11个：每个库中都有4张表book、common、stock、description，存储时按照shop_id分片，shop_id=0的相关数据放入core库，shop_id！=0时按照尾号存入core_00到core_09这10个库中。不同之处是book、common 2张表字段中包含分片列shop_id, 而stock、description 2张表字段中不包含分片列shop_id。

— core库:

| – book表

| – common表

| – stock 表

| – description 表

— core_00库:

| – book表

| – common表

| – stock 表

| – description 表

…

— core_09库:

| – book表

| – common表

| – stock 表

| – description 表

log库3个：每个库中都有2张表a_log和b_log，存储时根据shop_id分片，shop_id = 0时存入log_04库，其余shop_id奇数存入log_01库，偶数存入log_02库。

— log_01库:

| – a_log表

| – b_log表

— log_02库:

| – a_log表

| – b_log表

— log_04库:

| – a_log表

| – b_log表

global库1个：包含一张category表，所有数据都存入同一个库。

— global库:

| – category表

core_adm库1个：包含与上述11个core库相同的2张表book和common。不同之处是该库中不分片，所有shop_id对应的数据都可以存入。

— core_adm库:

| – book表

|-- common表

二、具体实现：

创建maven项目，引入相关依赖：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.0.0-RC3</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
	<version>2.1.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
	<version>5.1.42</version></dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
	<version>0.2.9</version>
</dependency>

core, core_00 … core_09 及 log_01,log_02,log_04中book、common、a_log、b_log表分库的配置：

spring.shardingsphere.datasource.names=ds-master-0,ds-master-1,ds-master-2,ds-master-3,ds-master-4,ds-master-5,ds-master-6,ds-master-7,ds-master-8,ds-master-9,ds-master-10,ds-master-12,ds-master-13,ds-master-14，ds-master-0-slave-0,ds-master-1-slave-0,ds-master-2-slave-0,ds-master-3-slave-0,ds-master-4-slave-0,ds-master-5-slave-0,ds-master-6-slave-0,ds-master-7-slave-0,ds-master-8-slave-0,ds-master-9-slave-0,ds-master-10-slave-0ds-master-12-slave-0,ds-master-13-slave-0,ds-master-14-slave-0

spring.shardingsphere.datasource.ds-master-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-0.url=jdbc:mysql://xxx:3306/core
spring.shardingsphere.datasource.ds-master-0.username=xxx
spring.shardingsphere.datasource.ds-master-0.password=xxx
spring.shardingsphere.datasource.ds-master-0.maxActive=20
spring.shardingsphere.datasource.ds-master-0.minIdle=5
spring.shardingsphere.datasource.ds-master-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-0.initialSize=5
spring.shardingsphere.datasource.ds-master-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-0.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-0-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-0-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-0-slave-0.url=jdbc:mysql://xxx:3306/core
spring.shardingsphere.datasource.ds-master-0-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-0-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-0-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-0-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-0-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-0-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-0-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-0-slave-0.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-1.url=jdbc:mysql://xxx:3306/core_00
spring.shardingsphere.datasource.ds-master-1.username=xxx
spring.shardingsphere.datasource.ds-master-1.password=xxx
spring.shardingsphere.datasource.ds-master-1.maxActive=20
spring.shardingsphere.datasource.ds-master-1.minIdle=5
spring.shardingsphere.datasource.ds-master-1.maxWait=120000
spring.shardingsphere.datasource.ds-master-1.initialSize=5
spring.shardingsphere.datasource.ds-master-1.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-1.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-1-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-1-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-1-slave-0.url=jdbc:mysql://xxx:3306/core_00
spring.shardingsphere.datasource.ds-master-1-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-1-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-1-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-1-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-1-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-1-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-1-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-1-slave-0.validationQuery = SELECT 1
	...中间都类似，此处省略...
spring.shardingsphere.datasource.ds-master-10.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-10.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-10.url=jdbc:mysql://xxx:3306/core_09
spring.shardingsphere.datasource.ds-master-10.username=xxx
spring.shardingsphere.datasource.ds-master-10.password=xxx
spring.shardingsphere.datasource.ds-master-10.maxActive=20
spring.shardingsphere.datasource.ds-master-10.minIdle=5
spring.shardingsphere.datasource.ds-master-10.maxWait=120000
spring.shardingsphere.datasource.ds-master-10.initialSize=5
spring.shardingsphere.datasource.ds-master-10.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-10.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-10-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-10-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-10-slave-0.url=jdbc:mysql://xxx:3306/core_09
spring.shardingsphere.datasource.ds-master-10-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-10-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-10-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-10-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-10-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-10-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-10-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-10-slave-0.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-12.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-12.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-12.url=jdbc:mysql://xxx:3306/log_04
spring.shardingsphere.datasource.ds-master-12.username=xxx
spring.shardingsphere.datasource.ds-master-12.password=xxx
spring.shardingsphere.datasource.ds-master-12.maxActive=20
spring.shardingsphere.datasource.ds-master-12.minIdle=5
spring.shardingsphere.datasource.ds-master-12.maxWait=120000
spring.shardingsphere.datasource.ds-master-12.initialSize=5
spring.shardingsphere.datasource.ds-master-12.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-12.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-12-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-12-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-12-slave-0.url=jdbc:mysql://xxx:3306/log_04
spring.shardingsphere.datasource.ds-master-12-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-12-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-12-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-12-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-12-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-12-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-12-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-12-slave-0.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-13.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-13.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-13.url=jdbc:mysql://xxx:3306/log_01
spring.shardingsphere.datasource.ds-master-13.username=xxx
spring.shardingsphere.datasource.ds-master-13.password=xxx
spring.shardingsphere.datasource.ds-master-13.maxActive=20
spring.shardingsphere.datasource.ds-master-13.minIdle=5
spring.shardingsphere.datasource.ds-master-13.maxWait=120000
spring.shardingsphere.datasource.ds-master-13.initialSize=5
spring.shardingsphere.datasource.ds-master-13.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-13.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-13-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-13-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-13-slave-0.url=jdbc:mysql://xxx:3306/log_01
spring.shardingsphere.datasource.ds-master-13-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-13-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-13-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-13-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-13-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-13-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-13-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-13-slave-0.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-14.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-14.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-14.url=jdbc:mysql://xxx:3306/log_02
spring.shardingsphere.datasource.ds-master-14.username=xxx
spring.shardingsphere.datasource.ds-master-14.password=xxx
spring.shardingsphere.datasource.ds-master-14.maxActive=20
spring.shardingsphere.datasource.ds-master-14.minIdle=5
spring.shardingsphere.datasource.ds-master-14.maxWait=120000
spring.shardingsphere.datasource.ds-master-14.initialSize=5
spring.shardingsphere.datasource.ds-master-14.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-14.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-14-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-14-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-14-slave-0.url=jdbc:mysql://xxx:3306/log_02
spring.shardingsphere.datasource.ds-master-14-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-14-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-14-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-14-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-14-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-14-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-14-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-14-slave-0.validationQuery = SELECT 1

#配置分片列为shop_id，分片策略为标准分片策略，并指定精确分片算法和范围分片算法的实现类分别为#MyPreciseShardingDatabaseAlgorithm和MyRangeShardingDatabaseAlgorithm
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=shop_id
spring.shardingsphere.sharding.default-database-strategy.standard.range-algorithm-class-name = com.xxx.core.shardingAlgorithm.MyRangeShardingDatabaseAlgorithm
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name = com.xxx.core.shardingAlgorithm.MyPreciseShardingDatabaseAlgorithm

#配置每个逻辑表对应的数据节点，如：逻辑表book对应ds-0.book到ds-10.book这11个数据节点
spring.shardingsphere.sharding.tables.book.actual-data-nodes=ds-$->{0..10}.book
spring.shardingsphere.sharding.tables.common.actual-data-nodes=ds-$->{0..10}.common
spring.shardingsphere.sharding.tables.a_log.actual-data-nodes=ds-$->{12..14}.a_log
spring.shardingsphere.sharding.tables.b_log.actual-data-nodes=ds-$->{12..14}.b_log

#数据源名称及主从配置，如：数据源ds-0的主库为ds-master-0，从库为ds-master-0-slave-0
spring.shardingsphere.sharding.master-slave-rules.ds-0.master-data-source-name=ds-master-0
spring.shardingsphere.sharding.master-slave-rules.ds-0.slave-data-source-names=ds-master-0-slave-0
spring.shardingsphere.sharding.master-slave-rules.ds-1.master-data-source-name=ds-master-1
spring.shardingsphere.sharding.master-slave-rules.ds-1.slave-data-source-names=ds-master-1-slave-0
	...中间都类似，此处省略...
spring.shardingsphere.sharding.master-slave-rules.ds-13.master-data-source-name=ds-master-13
spring.shardingsphere.sharding.master-slave-rules.ds-13.slave-data-source-names=ds-master-13-slave-0
spring.shardingsphere.sharding.master-slave-rules.ds-14.master-data-source-name=ds-master-14
spring.shardingsphere.sharding.master-slave-rules.ds-14.slave-data-source-names=ds-master-14-slave-0

spring.shardingsphere.props.sql.show=true

几点说明：

由于除了core_00 到core_09之外还存在一个单独不带后缀的core库，且除此之外还有3个log库及global，core_adm库，因此采用行表达式分片策略配置分片规则比较困难。因此选择标准分片策略，实现PreciseShardingAlgorithm和RangeShardingAlgorithm 2个接口，使用Java代码实现我们的需求。
对应官方文档中描述如下：
精确分片算法实现类MyPreciseShardingDatabaseAlgorithm代码如下：

/**
 * 精确分片算法
 * （用于处理使用单一键作为分片键的 = 与 IN 进行分片的场景，需要配合StandardShardingStrategy使用）
 */
@Component
public class MyPreciseShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Integer> {
    @Override
    public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Integer> shardingValue) {
        //单库不需要分片
        if (databaseNames.size() == 1) {
            for (String each : databaseNames) {
                return each;
            }
        }
        //shop_id = 0取core库， shop_id != 0 时按照shop_id % 10 对应core_00 ~ core_09
        //shop_id = 0取log_04库，shop_id != 0 时按照shop_id 奇偶 对应log_01 log_02
        if (shardingValue.getValue().equals(0)) {
            for (String each : databaseNames) {
                if (each.equals("ds-0") || each.equals("ds-12")) {
                    return each;
                }
            }
        } else {
            int mod10 = shardingValue.getValue() % 10;
            int mod2 = shardingValue.getValue() % 2;
            for (String each : databaseNames) {
                Integer dsNo = Integer.valueOf(each.substring(3));
                if (dsNo >= 1 && dsNo <= 10 && (mod10 + 1) == dsNo) {
                    return each;
                }
                if (dsNo >= 13 && dsNo <= 14 && dsNo % 2 == mod2) {
                    return each;
                }
            }
        }
        throw new UnsupportedOperationException();
    }
}

大家可能存在疑问的一点就是如果shop_id=10,是怎么知道路由到core_00库还是log_02库呢？
解答：假设我们操作book表，因为book表配置的数据节点就是ds-0到ds-10。如下：

spring.shardingsphere.sharding.tables.book.actual-data-nodes=ds-$->{0..10}.book

MyPreciseShardingDatabaseAlgorithm 中运行时获取到的databaseNames即ds-0到ds-10的数组，对应core以及core_00到core_09这11个库，不会包含log相关的3个库，因此通过相关规则的计算自然会路由到core_00库而不是log_02库。同理，当你操作a_log或b_log表时，会路由到log_02库。

类似的，小伙伴们可以根据自身项目需求完成范围分片算法的实现类MyRangeShardingDatabaseAlgorithm。由于我的需求暂时还没涉及，因此空置了。

/**
 * 范围分片算法
 * （用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景，需要配合StandardShardingStrategy使用）
 */
@Component
public class MyRangeShardingDatabaseAlgorithm implements RangeShardingAlgorithm<Integer> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Integer> shardingValue) {
        //todo
        return null;
    }
}

至此，分片的配置基本完成，加上一行指定各个Mapper.xml文件位置的配置就可以了，后面就是生成entity、Mapper接口、XxxMapper.xml文件，再写个service就能跑起来了。

mybatis.mapper-locations = classpath:META-INF/mappers/*/*.xml

我是把xml文件统一放在了classpath:META-INF/mappers/ 下，按照逻辑库名放置，如book表和common表的xml文件放置在classpath:META-INF/mappers/core文件夹下，a_log表和b_log表的xml文件放置在classpath:META-INF/mappers/log文件夹下。
如果想打印sql语句，再加上一行配置：

spring.shardingsphere.props.sql.show=true

使用时不需任何额外的注解，如指定数据源什么的，都不需要，service里直接调Mapper接口就OK了，和不用Sharding-JDBC没有差别，此处不赘述。

global库分片的配置：

配置数据库连接：

spring.shardingsphere.datasource.ds-master-11.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-11.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-11.url=jdbc:mysql://xxx:3306/global
spring.shardingsphere.datasource.ds-master-11.username=xxx
spring.shardingsphere.datasource.ds-master-11.password=xxx
spring.shardingsphere.datasource.ds-master-11.maxActive=20
spring.shardingsphere.datasource.ds-master-11.minIdle=5
spring.shardingsphere.datasource.ds-master-11.maxWait=120000
spring.shardingsphere.datasource.ds-master-11.initialSize=5
spring.shardingsphere.datasource.ds-master-11.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-11.validationQuery = SELECT 1

spring.shardingsphere.datasource.ds-master-11-slave-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-11-slave-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-11-slave-0.url=jdbc:mysql://xxx:3306/global
spring.shardingsphere.datasource.ds-master-11-slave-0.username=xxx
spring.shardingsphere.datasource.ds-master-11-slave-0.password=xxx
spring.shardingsphere.datasource.ds-master-11-slave-0.maxActive=20
spring.shardingsphere.datasource.ds-master-11-slave-0.minIdle=5
spring.shardingsphere.datasource.ds-master-11-slave-0.maxWait=120000
spring.shardingsphere.datasource.ds-master-11-slave-0.initialSize=5
spring.shardingsphere.datasource.ds-master-11-slave-0.testOnBorrow = true
spring.shardingsphere.datasource.ds-master-11-slave-0.validationQuery = SELECT 1

spring.shardingsphere.datasource.names增加：

ds-master-11,ds-master-11-slave-0

配置数据源名称及主从：

spring.shardingsphere.sharding.master-slave-rules.ds-11.master-data-source-name=ds-master-11
spring.shardingsphere.sharding.master-slave-rules.ds-11.slave-data-source-names=ds-master-11-slave-0

配置逻辑表和数据节点对应关系：

spring.shardingsphere.sharding.tables.category.actual-data-nodes=ds-11.category

至此，global库catrgory表的分片配置完成。由于步骤2）中已经配置了默认的数据库分片策略：

spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=shop_id
spring.shardingsphere.sharding.default-database-strategy.standard.range-algorithm-class-name = com.xxx.core.shardingAlgorithm.MyRangeShardingDatabaseAlgorithm
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name = com.xxx.core.shardingAlgorithm.MyPreciseShardingDatabaseAlgorithm

因此，该库global采用的仍然是这个策略，虽然事实上并没有分库，但MyPreciseShardingDatabaseAlgorithm中已兼容了只有一个数据源的情况，即：

//单库不需要分片
if (databaseNames.size() == 1) {
    for (String each : databaseNames) {
        return each;
    }
}

core_adm库分片的配置：

前面需求介绍时已经说过，该库的表与core、core_00 … core_09中的相同，差异在于该库不分片，所有shop_id对应的数据都可以存入，相当于那11个库的汇总。
使用Sharding-JDBC实现会比较麻烦，无法兼顾那11张表的路由规则，因此我们采用单独配置数据源的方式解决，增加数据源config类如下：

/**
 * core_adm库数据源
 * (由于和core core_00 ... core_09 库中表名相同，使用sharding-jdbc的分片策略比麻烦，因此单独配置数据源)
 */
@Configuration
@MapperScan(value = "com.xxx.core.mapper.coreadm", sqlSessionFactoryRef = "coreadmSqlSessionFactory")
public class CoreadmConfig {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.unique.coreadm")
    public DataSource coreadmDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean
    @Primary
    public SqlSessionFactory coreadmSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(coreadmDataSource());

        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:META-INF/mappers/coreadm/*.xml");
        sqlSessionFactory.setMapperLocations(resources);
        return sqlSessionFactory.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager coreadmTransactionManager(){
        return new DataSourceTransactionManager(coreadmDataSource());
    }
}

需要注意的是，单独配置的数据源仅用于core_adm库，因此配置类上的MapperScan注解不能配置的扫描范围过大，只让它扫core_adm库的mapper接口所在的文件夹。
其余用法都一样，生成entity、Mapper接口、XxxMapper.xml文件，再写个service就能使用了。
但是问题来了，单独配置的数据源导致原来使用Sharding-jdbc的那些库的操作失败。我们的解决办法是将所有使用Sharding-JDBC的库作为一个数据源，也使用Java代码的方式配置进来。配置类ShardingConfig如下：

/**
 * sharding-jdbc相关配置
 */
@Configuration
@MapperScan(value = {"com.xxx.core.mapper.core",      "com.xxx.core.mapper.global","com.xxx.core.mapper.log"},sqlSessionFactoryRef = "shardingSqlSessionFactory")
public class ShardingConfig {
    @Bean(name = "shardingSqlSessionFactory")
    public SqlSessionFactory shardingSqlSessionFactory(@Qualifier("shardingDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);

        Resource[] resources = new PathMatchingResourcePatternResolver().
                getResources("classpath:META-INF/mappers/*/*.xml");
        sqlSessionFactory.setMapperLocations(resources);
        return sqlSessionFactory.getObject();
    }

    @Bean
    public DataSourceTransactionManager shardingTransactionManager(@Qualifier("shardingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

其中数据源直接将名字为shardingDataSource的数据源注入进来，MapperScan注解的扫描范围即所有使用Sharding-JDBC的表的mapper接口所在路径。
至此，单独配置的数据库core_adm和使用Sharding-JDBC的数据库core,log,global都可以正常操作了。

core, core_00 … core_09 中stock、description表分库的配置：

由于这2个表结构中不包含分片字段shop_id, 运行时就执行不到分片策略MyPreciseShardingDatabaseAlgorithm类中，宜采用强制路由，直接指定要操作的数据源。如官方文档中描述：
在这里插入图片描述

增加如下配置：

#以下表中不包含shop_id字段，采用强制路由hint
spring.shardingsphere.sharding.tables.stock.database-strategy.hint.algorithm-class-name=com.xxx.core.shardingAlgorithm.MyHintShardingDatabaseAlgorithm
spring.shardingsphere.sharding.tables.description.database-strategy.hint.algorithm-class-name=com.
xxx.core.shardingAlgorithm.MyHintShardingDatabaseAlgorithm

MyHintShardingDatabaseAlgorithm如下：

**
 * 基于hint的分片算法（强制路由）
 */
@Service
public class MyHintShardingDatabaseAlgorithm implements HintShardingAlgorithm {
    @Override
    public Collection<String> doSharding(Collection availableTargetNames, HintShardingValue shardingValue) {
        Collection<String> result = new ArrayList<>();
        // 获取到强制路由的值
        Collection<Integer> values = shardingValue.getValues();
        for (Integer value : values) {
            //获取对应的数据源
            result.add(EnumHint.getDataSourceNameById(value));
        }
        return result;
    }
}

/**
 * 强制路由枚举
 */
public enum EnumHint {
    CORE(0, "ds-0"),
    CORE_00(10, "ds-1"),
    CORE_01(1, "ds-2"),
    CORE_02(2, "ds-3"),
    CORE_03(3, "ds-4"),
    CORE_04(4, "ds-5"),
    CORE_05(5, "ds-6"),
    CORE_06(6, "ds-7"),
    CORE_07(7, "ds-8"),
    CORE_08(8, "ds-9"),
    CORE_09(9, "ds-10");

    private Integer code;
    private String dataSourceName;
    EnumHint(Integer code, String dataSourceName) {
        this.code = code;
        this.dataSourceName = dataSourceName;
    }
    ...get set 方法此处省略...
    public static String getDataSourceNameById(Integer code) {
        EnumHint[] temp = EnumHint.values();
        for (EnumHint enumHint : temp) {
            if (enumHint.getCode().equals(code)) {
                return enumHint.getDataSourceName();
            }
        }
        return null;
    }
}

在service方法中调mapper接口前添加数据源分片键值，例如：

@Slf4j
@Service("stockService")
public class StockServiceImpl implements StockService {
    @Autowired
    private StockMapper stockMapper;
    @Override
    public void deleteByProductId(Integer shopId, Integer productId) {
       //数据源code，与EnumHint中的对应关系一致 0:ds-0 10:ds-1 1:ds-2 2:ds-3 3:ds-4 ...  9:ds-10
       Integer dsCode = shopId.equals(0) ? 0 : (shopId % 10 == 0 ? 10 : shopId % 10);

//分片键值保存在ThreadLocal中，所以需要在操作结束时调用hintManager.close()来清除ThreadLocal中的内容。hintManager实现了AutoCloseable接口，可推荐使用try with resource自动关闭
try (HintManager hintManager = HintManager.getInstance();) {
hintManager.addDatabaseShardingValue("stock", dsCode);
        	StockExample stockExample = new StockExample();
        	stockExample.createCriteria().andProductIdEqualTo(productId);
        	stockMapper.deleteByExample(stockExample);
        }
    }
}

所以在执行StockService 的deleteByProductId方法时，若shopId=1,则dsCode为1，即hintManager.addDatabaseShardingValue(“stock”, 1)。从EnumHint 中的对应关系看出1对应ds-2,在执行MyHintShardingDatabaseAlgorithm 时就会路由到ds-2也就是core_01库的stock表。

本文记录了如何使用Sharding-JDBC解决在同一个工程中同时遇到这4种分库场景的需求，由于我的需求中不涉及分表，文中没有提及，需要的小伙伴再结合官方文档尝试，希望我的文章能在你学习Sharding-JDBC的路上起到抛砖引玉的作用。

乌苏100

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
Spring Boot + Mybatis + Sharding-JDBC 在复杂库表场景下的尝试

Sharding-JDBC是开源分布式数据库中间件解决方案ShardingSphere的一部分，定位为轻量级Java框架，在Java的JDBC层提供服务，实现标准化的数据分片、分布式事务和数据库治理功能。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。本文记录了Sharding-JDBC在相对复杂的库表场景中的尝试，技术栈采用Spring Boot+Mybatis+Sharding-JDBC+Druid。
复制链接

扫一扫