Springboot整合shardingsphere-jdbc的5.0.0版本实现分库分表

之前项目中一直使用的是shardingsphere的4.1.0版本，该版本有一些SQL是不支持的，如下。

为了使用例如子查询，case when语句等，今日将项目中的shardingsphere升级到5.0.0版本。该版本不支持项明显减少，更方便开发。

下面以两库十表为例，其中dbm为主库，db1、db2用于分库分表。user表分库分表，student表不分库分表。自定义精准分片策略，以user_id的倒数第一位数字对库数取模作为具体库，user_id的倒数第二三位数字对表数取模作为具体表。4.1.0版本和5.0.0版本的配置有一些不同，详细配置如下。

一、引入依赖

添加相关依赖，注意mysql-connector-java不要使用6.0.X版本，会报Table name pattern can not be NULL or empty.的异常信息。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.22</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.13</version>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.0.0</version>
</dependency>

二、自定义分片策略

4.1.0版本实现自定义分片策略实现的是PreciseShardingAlgorithm接口。与4.1.0版本不同的是5.0.0版本继承的是StandardShardingAlgorithm接口，除了支持精准分片策略，还支持范围分片策略。下面只以精准分片策略为例：

1、自定义分库策略

package com.shardingsphere.test.config.datasource.precise;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.Iterator;

/**
 *
 * @description sharding jdbc 精准 `分库` 策略
 *
 * @author luffylv
 * @date 2022-04-15 10:24
 **/
@Slf4j
@Component(value = "preciseShardingDatabaseAlgorithm")
public class PreciseShardingDatabaseAlgorithm implements StandardShardingAlgorithm<String> {

    // 主库别名
    private static final String DBM = "dbm";

    private static int dataBaseSize;

    @Value("${dataBaseSize}")
    public void setDataBaseSize(int size) {
        dataBaseSize = size;
    }

    /**
     * @description: 分库策略，按用户编号最后一位数字对数据库数量取模
     *
     * @param dbNames 所有库名
     * @param preciseShardingValue 精确分片值，包括（columnName，logicTableName，value）
     * @return 表名
     * @author luffylv
     * @date 2022/4/15
     */
    @Override
    public String doSharding(Collection<String> dbNames, PreciseShardingValue<String> preciseShardingValue) {
        log.info("Database PreciseShardingAlgorithm dbNames:{} ,preciseShardingValue: {}.", JSON.toJSONString(dbNames),
                JSON.toJSONString(preciseShardingValue));

        // 若走主库，直接返回主库
        if (dbNames.size() == 1) {
            Iterator<String> iterator = dbNames.iterator();
            String dbName = iterator.next();
            if (DBM.equals(dbName)) {
                return DBM;
            }
        }

        // 按数据库数量取模
        String num = StringUtils.substring(preciseShardingValue.getValue(), -1);
        int mod = Integer.parseInt(num) % dataBaseSize;
        for (String dbName : dbNames) {
            // 分库的规则
            if (dbName.endsWith(String.valueOf(mod))) {
                return dbName;
            }
        }
        throw new UnsupportedOperationException();
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }


    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return null;
    }
}

2、自定义分表策略

package com.shardingsphere.test.config.datasource.precise;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.util.Collection;

/**
 *
 * @description sharding jdbc 精准`分表`策略
 *
 * @author luffylv
 * @date 2022-04-15 10:24
 **/
@Slf4j
@Component(value = "preciseShardingTableAlgorithm")
public class PreciseShardingTableAlgorithm implements StandardShardingAlgorithm<String> {

    // 分表数量
    private static int tableSize;

    @Value("${tableSize}")
    public void setTableSize(int size) {
        tableSize = size;
    }

    /**
     * @description: 分表策略，按用户编号倒数二三位数字对数据库表数量取模
     *
     * @param tableNames 所有表名
     * @param preciseShardingValue 精确分片值，包括（columnName，logicTableName，value）
     * @return 表名
     * @author luffylvlv
     * @date 2022/4/15
     */
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> preciseShardingValue) {
        log.info("Table PreciseShardingAlgorithm tableNames:{} ,preciseShardingValue: {}.",
                JSON.toJSONString(tableNames), JSON.toJSONString(preciseShardingValue));
        // 按表数量取模
        // 截取用户编号倒数二三位数字，（如1234的倒数二三位为23）
        String num = StringUtils.substring(preciseShardingValue.getValue(), -3, -1);
        int mod = Integer.parseInt(num) % tableSize;
        for (String tableName : tableNames) {
            // 分表的规则
            if (tableName.endsWith(String.valueOf(mod))) {
                return tableName;
            }
        }
        throw new UnsupportedOperationException();
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }


    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return null;
    }
}

三、数据库相关配置

配置数据库相关配置，其中库表的分片策略使用上面我们自定义的分片策略。这里配置项和4.1.0版本的还是有一些区别的，但差别不太大。

# 数据源参数配置
initialSize=5
minIdle=5
maxIdle=100
maxActive=20
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
# Sharding Jdbc配置
# 分库的数量（注意：需要排除主库）
dataBaseSize=2
# 分表的数量
tableSize=10
# dbm为主库，db0,db1
spring.shardingsphere.datasource.names=dbm,db0,db1
# 配置主库
spring.shardingsphere.datasource.dbm.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.dbm.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.dbm.url=
spring.shardingsphere.datasource.dbm.username=
spring.shardingsphere.datasource.dbm.password=
spring.shardingsphere.datasource.dbm.initialSize=${initialSize}
spring.shardingsphere.datasource.dbm.minIdle=${minIdle}
spring.shardingsphere.datasource.dbm.maxActive=${maxActive}
spring.shardingsphere.datasource.dbm.maxWait=${maxWait}
spring.shardingsphere.datasource.dbm.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.dbm.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.dbm.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
# 配置db0
spring.shardingsphere.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.url=
spring.shardingsphere.datasource.db0.username=
spring.shardingsphere.datasource.db0.password=
spring.shardingsphere.datasource.db0.initialSize=${initialSize}
spring.shardingsphere.datasource.db0.minIdle=${minIdle}
spring.shardingsphere.datasource.db0.maxActive=${maxActive}
spring.shardingsphere.datasource.db0.maxWait=${maxWait}
spring.shardingsphere.datasource.db0.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.db0.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.db0.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
# 配置db1
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=
spring.shardingsphere.datasource.db1.username=
spring.shardingsphere.datasource.db1.password=
spring.shardingsphere.datasource.db1.initialSize=${initialSize}
spring.shardingsphere.datasource.db1.minIdle=${minIdle}
spring.shardingsphere.datasource.db1.maxActive=${maxActive}
spring.shardingsphere.datasource.db1.maxWait=${maxWait}
spring.shardingsphere.datasource.db1.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.db1.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.db1.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
# 分库配置
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=preciseShardingDatabaseAlgorithm
# 分表配置
# user表配置
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=db$->{0..1}.user_0$->{0..9}
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithm
# 不分库分表配置规则
# student
spring.shardingsphere.rules.sharding.tables.student.actual-data-nodes=dbm.student
spring.shardingsphere.rules.sharding.tables.student.table-strategy.inline.sharding-column=id
spring.shardingsphere.rules.sharding.tables.student.table-strategy.inline.algorithm-expression=student
# 打印分库分表日志
spring.shardingsphere.props.sql-show=true

四、加载数据库相关配置

package com.shardingsphere.test.config.datasource;

import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

/**
 * @Ddescription DataSourceConfig
 **/
@Configuration
@PropertySource(value = {"${config.path}/db.properties"})
public class DataSourceConfig {
}

配置文件目录如下 ，

config.path属性配置在pom文件中。

完成以上三步后即可将sharding-jdbc版本升级到5.0.0版本，亲测可以支持子查询和case when，测试的都是较简单的子查询和case when的sql，这里就不贴具体sql语句了。