前言

SpringBoot 整合 Sharding-JDBC

提示：以下是本篇文章正文内容，下面案例可供参考

一、Sharding-JDBC简介

是轻量级的 java 框架，是增强版的 JDBC 驱动，就是简化分库分表后对数据相关操作

二、使用步骤

1.初始化环境

项目需求就是银行流水信息入mysql,水平按照流水时间分片分库分表

新建springboot项目引入下面依赖

按照水平分表的方式，创建数据库和数据库表

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.20</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC1</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.0.5</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency>
</dependencies>

CREATE TABLE `bank_flow_2021`.money_flow_202201 (
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
flow_id VARCHAR(50) NOT NULL COMMENT '流水id',
money DECIMAL(10,4) NOT NULL COMMENT '金额',
create_time TIMESTAMP COMMENT '创建时间',
flow_time  TIMESTAMP NOT NULL COMMENT '流水时间',
sharding_time BIGINT(20) NOT NULL COMMENT '分片时间'
)

准备工作已经完成下面我们看下代码

2.实战

首先我们需要修改配置文件，可以参考官网Spring Boot配置 :: ShardingSphere

# shardingjdbc 分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=bank2021,bank2022# 一个实体类对应多张表，覆盖
spring.main.allow-bean-definition-overriding=true#配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.bank2021.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.bank2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.bank2021.jdbc-url=jdbc:mysql://127.0.0.1:3306/bank_flow_2021?autoReconnect=true&allowMultiQueries=true
spring.shardingsphere.datasource.bank2021.username=root
spring.shardingsphere.datasource.bank2021.password=123456#配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.bank2022.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.bank2022.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.bank2022.jdbc-url=jdbc:mysql://127.0.0.1:3306/bank_flow_2022?autoReconnect=true&allowMultiQueries=true
spring.shardingsphere.datasource.bank2022.username=root
spring.shardingsphere.datasource.bank2022.password=123456#指定 money_flow 表分布情况，配置表在哪个数据库里面，表名称都是什么
spring.shardingsphere.sharding.tables.money_flow.actual-data-nodes=bank2021.money_flow_20210${1..9},bank2021.money_flow_20211${0..2},bank2022.money_flow_20220${1..9},bank2022.money_flow_20221${0..2}#指定数据库 表分片策略 数据库和表都用这个字段分片
spring.shardingsphere.sharding.tables.money_flow.database-strategy.standard.sharding-column=sharding_time
spring.shardingsphere.sharding.tables.money_flow.table-strategy.standard.sharding-column=sharding_time#数据库分片策略
spring.shardingsphere.sharding.tables.money_flow.database-strategy.standard.preciseAlgorithmClassName=com.example.demo.config.PreciseDatabaseShardingAlgorithm
spring.shardingsphere.sharding.tables.money_flow.database-strategy.standard.rangeAlgorithmClassName=com.example.demo.config.RangeDatabaseShardingAlgorithm#数据表分片策略
spring.shardingsphere.sharding.tables.money_flow.table-strategy.standard.preciseAlgorithmClassName=com.example.demo.config.PreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.money_flow.table-strategy.standard.rangeAlgorithmClassName=com.example.demo.config.RangeTableShardingAlgorithm# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

分片策略也就是通过分片字段怎么找的所需表，需要代码，数据库和表相关分片策略见下面代码

精确分片算法用于处理使用单一键作为分片键的=与IN进行分片的场景


/*** 库精确分片算法**/
public class PreciseDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {/*** 库精确分片算法** @param availableTargetNames 所有配置的库列表* @param shardingValue        分片值* @return 所匹配库的结果*/@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {// 分片键值Long value = shardingValue.getValue();// 库后缀String yearStr = ShardingAlgorithmUtil.getYearByMillisecond(value);if (value <= 0) {throw new UnsupportedOperationException("preciseShardingValue is null");}for (String availableTargetName : availableTargetNames) {if (availableTargetName.endsWith(yearStr)) {return availableTargetName;}}throw new UnsupportedOperationException();}
}

范围分片算法用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景


/*** 库范围分片算法**/
public class RangeDatabaseShardingAlgorithm implements RangeShardingAlgorithm<Long> {/*** 库范围分片算法** @param availableTargetNames 所有配置的库列表* @param rangeShardingValue   分片值，也就是save_time_com的值，范围分片算法必须提供开始时间和结束时间* @return 所匹配库的结果*/@Overridepublic Collection<String> doSharding(Collection<String> availableTargetNames,RangeShardingValue<Long> rangeShardingValue) {ArrayList<String> result = new ArrayList<>();Range<Long> range = rangeShardingValue.getValueRange();// 起始年和结束年int startYear = Integer.parseInt(ShardingAlgorithmUtil.getYearByMillisecond(range.lowerEndpoint()));int endYear = Integer.parseInt(ShardingAlgorithmUtil.getYearByMillisecond(range.upperEndpoint()));return startYear == endYear ? theSameYear(String.valueOf(startYear), availableTargetNames, result): differentYear(startYear, endYear, availableTargetNames, result);}/*** 同一年，说明只需要一个库*/private Collection<String> theSameYear(String startTime, Collection<String> availableTargetNames,ArrayList<String> result) {for (String availableTargetName : availableTargetNames) {if (availableTargetName.endsWith(startTime))result.add(availableTargetName);}return result;}/*** 跨年*/private Collection<String> differentYear(int startYear, int endYear, Collection<String> availableTargetNames,ArrayList<String> result) {for (String availableTargetName : availableTargetNames) {for (int i = startYear; i <= endYear; i++) {if (availableTargetName.endsWith(String.valueOf(i)))result.add(availableTargetName);}}return result;}
}


/*** 表精确分片算法*/
public class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {/*** 表精确分片算法** @param availableTargetNames 所有配置的表列表，这里代表所匹配到库的所有表* @param shardingValue        分片值* @return 所匹配表的结果*/@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {// 分片键值long value = shardingValue.getValue();if (value <= 0) {throw new UnsupportedOperationException("preciseShardingValue is null");}String yearJoinMonthStr = ShardingAlgorithmUtil.getYearJoinMonthByMillisecond(value);for (String availableTargetName : availableTargetNames) {if (availableTargetName.endsWith(yearJoinMonthStr)) {return availableTargetName;}}throw new UnsupportedOperationException();}
}


/*** 表范围分片算法**/
public class RangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {/*** 表范围分片算法*/@Overridepublic Collection<String> doSharding(Collection<String> availableTargetNames,RangeShardingValue<Long> rangeShardingValue) {Range<Long> range = rangeShardingValue.getValueRange();long startMillisecond = range.lowerEndpoint();long endMillisecond = range.upperEndpoint();return getMonthBetween(startMillisecond, endMillisecond, availableTargetNames);}/*** 计算有效的库表名*/public static Collection<String> getMonthBetween(long minTime, long maxTime,Collection<String> availableTargetNames) {Collection<String> result = new ArrayList<>();Calendar min = Calendar.getInstance();Calendar max = Calendar.getInstance();min.setTime(new Date(minTime));min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);max.setTime(new Date(maxTime));max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");while (min.before(max)) {String yyyyMM = sdf.format(min.getTime());availableTargetNames.forEach(availableTargetName -> {if (availableTargetName.endsWith(yyyyMM)) {result.add(availableTargetName);}});min.add(Calendar.MONTH, 1);}return result;}
}


/*** 分片算法工具类*/
public class ShardingAlgorithmUtil {/*** 获取年份*/public static String getYearByMillisecond(long millisecond) {return new SimpleDateFormat("yyyy").format(new Date(millisecond));}/*** 获取年月*/public static String getYearJoinMonthByMillisecond(long millisecond) {return new SimpleDateFormat("yyyyMM").format(new Date(millisecond));}
}

新建实体类


@Data
@TableName(value = "money_flow")
public class BankFlow {private Long id;private String flowId;private BigDecimal money;private Instant flowTime;private Instant createTime;private Long shardingTime;
}

新建mapper

@Repository
public interface BankFlowMapper extends BaseMapper<BankFlow> {}

代码测试下


@SpringBootTest
class DemoApplicationTests {@Autowiredprivate BankFlowMapper bankFlowMapper;@Testvoid saveFlow() {BankFlow bankFlow = new BankFlow();bankFlow.setId(100L);bankFlow.setCreateTime(Instant.now());bankFlow.setFlowTime(Instant.now());bankFlow.setFlowId("ceshi");bankFlow.setMoney(new BigDecimal("888.88"));bankFlow.setShardingTime(new Date().getTime());bankFlowMapper.insert(bankFlow);}}