springboot通过sharding-dbc按年、月分片

目录

 springboot通过sharding-dbc按年、月分片

1、引入pom依赖

2、application.yml配置

3、分片算法

4、注意事项

---

1、引入pom依赖

 <!--shardingjdbc分片，和Druid不兼容，如果不使用sharding则需要注释--><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.1</version></dependency>

2、application.yml配置

spring:autoconfigure:exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfiguremain:allow-bean-definition-overriding: trueshardingsphere:#配置数据源datasource:names: ds-masterds-master:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://147.1.5.229:3306/aihosp?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=trueusername: aihosppassword: DF3f3#KF#83Fesharding:tables:year_table:actual-data-nodes: ds-master.year_table$->{2021..2025}  #按年分表tableStrategy:standard: #用于单分片键的标准分片场景sharding-column: create_dateprecise-algorithm-class-name: com.gxfy.common.algorithm.PreciseRangeShardingAlgorithm # 精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器range-algorithm-class-name: com.gxfy.common.algorithm.PreciseRangeShardingAlgorithm #范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器key-generator:column: idtype: SNOWFLAKE #分布式全局ID（雪花算法）retry-interval-milliseconds: 500month_table:actual-data-nodes: ds-master.month_table$->{2022..2025}0$->{1..9},ds-master.month_table$->{2022..2025}1$->{0..2}  #按月分表tableStrategy:standard: #用于单分片键的标准分片场景sharding-column: create_dateprecise-algorithm-class-name: com.gxfy.common.algorithm.PreciseRangeShardingAlgorithmrange-algorithm-class-name: com.gxfy.common.algorithm.PreciseRangeShardingAlgorithmkey-generator:column: idtype: SNOWFLAKE #分布式全局ID（雪花算法）retry-interval-milliseconds: 500）retry-interval-milliseconds: 500#其他运行属性props:sql:show: false

3、分片算法

import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.Set;/**** 按年分片* 精准分库PreciseShardingDBAlgorithm** 范围分库RangeShardingDBAlgorithm** 精准分表PreciseShardingTableAlgorithm** 范围分表RangeShardingTableAlgorithm：*/
@Slf4j
public class PreciseRangeShardingAlgorithm implements PreciseShardingAlgorithm<String>,RangeShardingAlgorithm<String> {/***  RangeShardingAlgorithm的重写  根据传入的分片健的值，对所有待选择的表中 根据自己的业务逻辑进行判断，选择符合条件的表返回* @param tableNameList 返回需要查询的表* @param shardingValue 传入的分片健的值* @return 返回符合条件的表名称*/@Overridepublic Collection<String> doSharding(Collection<String> tableNameList, RangeShardingValue<String> shardingValue) {System.out.println("[MyTableRangeShardingAlgorithm] shardingValue: [{}]\n"+ shardingValue);Set<String> tableNameResultList = new LinkedHashSet<>();Range<String> rangeValue = shardingValue.getValueRange();String flag = "year";for (String tableName : tableNameList) {if (tableName.startsWith("month_table")) {flag = "month";break;}}if ("year".equals(flag)) {int lowInt = Integer.parseInt(rangeValue.lowerEndpoint().substring(0,5).replaceAll("-",""));int upperInt = Integer.parseInt(rangeValue.upperEndpoint().substring(0,5).replaceAll("-",""));for (String tableNameItem : tableNameList) {String substring = tableNameItem.substring(tableNameItem.length() - 4);int tableItem = Integer.valueOf(substring);if(tableItem >=  lowInt && tableItem <= upperInt ){tableNameResultList.add(tableNameItem);}}} else if ("month".equals(flag)) {int lowInt = Integer.parseInt(rangeValue.lowerEndpoint().substring(0,7).replaceAll("-",""));int upperInt = Integer.parseInt(rangeValue.upperEndpoint().substring(0,7).replaceAll("-",""));for (String tableNameItem : tableNameList) {String substring = tableNameItem.substring(tableNameItem.length() - 6,tableNameItem.length());int tableItem = Integer.valueOf(substring);if(tableItem >=  lowInt && tableItem <= upperInt ){tableNameResultList.add(tableNameItem);}}}return tableNameResultList;}/** PreciseShardingAlgorithm的重写 */@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {String s = buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue());return s;}/*** 构建分片后的表名* @param logicTableName* @param date* @return*/private String buildShardingTable(String logicTableName, String date) {StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 4);if (logicTableName.startsWith("month_table") ) {// 月分表stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 4).append(date, 5, 7);}return stringBuffer.toString();}}

4、注意事项

（1）分片主键不能修改。

（2）分表后如果需指定表，入参需使用例如 ${tableSuf}

          ${}和#{}的区别：${}参数不会携带‘’，但#{}会携带。

实施sharding-jdbc，一些非常痛的注意点 - 掘金 (juejin.cn)