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

单位让切shardingsphere5.2.0的版本，该版本和之前版本在配置上还是有些区别的，特记录一下Springboot整合shardingsphere-jdbc的5.2.0版本。

之前使用的是5.0.0版本，只需配置spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=默认分库算法类全限定名，spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=分表算法类全限定名。而5.2.0版本sharding-algorithm-name属性后配置的是分库分表算法的别名。新增了type属性，如果是自定义类分片算法的话该属性值为CLASS_BASED。并且需要配置两个属性，分别是props.strategy（配置分片策略类型）、props.algorithmClassName（配置分片算法全限定名）。见下图。

 

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

一、引入依赖

添加相关依赖，数据源连接池使用默认的HikariCP。

<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.2.0</version>
</dependency>

二、自定义分片策略

5.2.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 java.util.Collection;
import java.util.Iterator;
import java.util.Properties;

/**
 * @description sharding jdbc 精准 `分库` 策略
 **/
@Slf4j
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 表名
     */
    @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 Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

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 java.util.Collection;
import java.util.Properties;

/**
 * @description sharding jdbc 精准`分表`策略
 **/
@Slf4j
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 表名
     */
    @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 Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

三、数据库相关配置

配置数据库相关配置，其中库表的分片策略使用上面我们自定义的分片策略。这里相较于5.0.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.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.dbm.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.dbm.jdbc-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.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.jdbc-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.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.jdbc-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=database-precise-sharding

# 分表配置

# user表配置
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=db$->{0..1}.user_$->{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=table-precise-sharding

# 分库分表算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.database-precise-sharding.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.database-precise-sharding.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.database-precise-sharding.props.algorithmClassName=com.shardingsphere.test.config.datasource.precise.PreciseShardingDatabaseAlgorithm
spring.shardingsphere.rules.sharding.sharding-algorithms.table-precise-sharding.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.table-precise-sharding.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.table-precise-sharding.props.algorithmClassName=com.shardingsphere.test.config.datasource.precise.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.2.0版本。