开发环境
| 名称 | 版本 |
|---|---|
| 操作系统 | Windows 11 X64 |
| SpringBoot | 3.2.4 2.2.2.RELEASE |
| JDK | 17 8 |
| shardingsphere-jdbc | 5.4.1 |
源码下载
新建表
payment_wallet_detail_202402...payment_wallet_detail_202405payment_wallet_transaction_0...payment_wallet_transaction_49
目录结构
pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>5.4.1</version></dependency>
sharding-dev.yml
dataSources:master_0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://a.a.a.a:3306/testDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=falseusername: usernamepassword: password# 这里进行分库分表的表,需要在ShardingConfig中进行热启动,否则第一次查询会很慢rules:- !SHARDINGtables:payment_wallet_detail:actualDataNodes: master_${0}.payment_wallet_detail_${2024}${(2..5).collect{t ->t.toString().padLeft(2,'0')}}tableStrategy:standard:shardingColumn: pay_monthshardingAlgorithmName: pay_month_algorithmkeyGenerateStrategy:column: idkeyGeneratorName: snowflakepayment_wallet_transaction:actualDataNodes: master_${0}.payment_wallet_transaction_${0..49}tableStrategy:complex:shardingColumns: pay_order_numbershardingAlgorithmName: payment_wallet_transaction_algorithmkeyGenerateStrategy:column: idkeyGeneratorName: snowflakeshardingAlgorithms:pay_month_algorithm:type: CLASS_BASE_PAY_MONTHprops:strategy: standardalgorithmClassName: com.sharding.demo.sharingrule.PayMonthAlgorithmpayment_wallet_transaction_algorithm:type: CLASS_WALLET_PAY_ORDER_NUMBERprops:strategy: complexalgorithmClassName: com.sharding.demo.sharingrule.WalletPayOrderNumberShardingRulekeyGenerators:snowflake:type: SNOWFLAKE- !SINGLEtables:- "*.*"props:sql-show: true
application.yml
server:port: 8088#spring:# datasource:# type: com.alibaba.druid.pool.DruidDataSource# driver-class-name: com.mysql.cj.jdbc.Driver# url: jdbc:mysql://a.a.a.a:3306/testDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false# username: username# password: passwordspring:datasource:# 引入shardingdriver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:sharding-dev.ymldynamic:primary: master#mybatismybatis-plus:mapper-locations: classpath:/mapper/*Mapper.xml#实体扫描,多个package用逗号或者分号分隔typeAliasesPackage:typeEnumsPackage:global-config:#主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";id-type: 0#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"field-strategy: 2#驼峰下划线转换db-column-underline: true#刷新mapper 调试神器refresh-mapper: true#数据库大写下划线转换#capital-mode: true#序列接口实现类配置#key-generator:#逻辑删除配置logic-delete-value: 0logic-not-delete-value: 1# #自定义填充策略接口实现# meta-object-handler:configuration:map-underscore-to-camel-case: true #开启驼峰命名cache-enabled: falselog-impl: org.apache.ibatis.logging.stdout.StdOutImpl #日志
分片逻辑配置
- 新建
resources\META-INF\services\org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
com.sharding.demo.sharingrule.PayMonthAlgorithmcom.sharding.demo.sharingrule.WalletPayOrderNumberShardingRule
分片逻辑
按期间分片-PayMonthAlgorithm
package com.sharding.demo.sharingrule;import com.sharding.demo.common.ResponseCode;import com.sharding.demo.utils.AssertUtil;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 java.time.LocalDate;import java.time.format.DateTimeFormatter;import java.util.Collection;import java.util.LinkedHashSet;import java.util.Set;@Slf4jpublic class PayMonthAlgorithm implements StandardShardingAlgorithm<String> {private final static DateTimeFormatter YYYY_MM_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");/*** 等值算法** @param availableTargetNames* @param shardingValue* @return*/@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {log.info("PayMonthAlgorithm accurate availableTargetNames={} shardingValue={}",availableTargetNames,shardingValue);String choseTable = null;String key = shardingValue.getValue();for(String table:availableTargetNames){if(table.endsWith(key)){choseTable = table;break;}}log.info("PayMonthAlgorithm accurate choseTable={},key={}", choseTable, key);AssertUtil.isTrue(StringUtils.isNotBlank(choseTable), ResponseCode.NO_DATA.value(), "not chose table,key=" + key);return choseTable;}/*** 区间算法** @param availableTargetNames* @param shardingValue* @return*/@Overridepublic Collection<String> doSharding(Collection<String> availableTargetNames,RangeShardingValue<String> shardingValue) {log.info("PayMonthAlgorithm accurate range availableTargetNames={} shardingValue={}",availableTargetNames,shardingValue);Set<String> choseTables = new LinkedHashSet<>();String lower = shardingValue.getValueRange().lowerEndpoint();String upper = shardingValue.getValueRange().upperEndpoint();while (lower.compareTo(upper) > 0){String key = lower;for (String table : availableTargetNames) {if (table.endsWith(key)) {choseTables.add(table);}}lower = plusOneMonth(lower);}log.info("PayMonthAlgorithm accurate choseTable={}",choseTables);return choseTables;}/*** 加一个月* @param payDate* @return 加一个月*/private String plusOneMonth(String payDate){LocalDate date = LocalDate.parse(payDate, YYYY_MM_FORMATTER);date = date.plusMonths(1L);return date.format(YYYY_MM_FORMATTER);}@Overridepublic String getType() {return "CLASS_BASE_PAY_MONTH";}}
hash 分片-WalletPayOrderNumberShardingRule
package com.sharding.demo.sharingrule;import lombok.extern.slf4j.Slf4j;import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;import org.springframework.stereotype.Component;import java.util.ArrayList;import java.util.Collection;import java.util.Map;@Component@Slf4jpublic class WalletPayOrderNumberShardingRule implements ComplexKeysShardingAlgorithm<String> {@Overridepublic Collection<String> doSharding(Collection<String> tables,ComplexKeysShardingValue<String> complexKeysShardingValue) {log.info("WalletPayOrderNumberShardingRule {} {}",tables,complexKeysShardingValue);Collection<String> result = new ArrayList<>();Map<String,Collection<String>> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();Collection<String> c = map.get("pay_order_number");for(String tip : c){String hashCode = String.valueOf(tip.hashCode());int hash = Integer.parseInt(hashCode.substring(hashCode.length()-2));result.add("payment_wallet_transaction_" + hash % 50);}log.info("WalletPayOrderNumberShardingRule,select table result: {}",result);return result;}@Overridepublic String getType() {return "CLASS_WALLET_PAY_ORDER_NUMBER";}}
Controller
package com.sharding.demo.controller;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import com.sharding.demo.mapper.PaymentWalletDetailMapper;import com.sharding.demo.model.PaymentWalletDetail;import jakarta.annotation.Resource;import lombok.extern.slf4j.Slf4j;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;import java.util.List;@Slf4j@RestController@RequestMapping("/test")public class TestController {@ResourcePaymentWalletDetailMapper detailService;@GetMapping("/testGetDetail")public List<PaymentWalletDetail> testGetDetail(@RequestParam(name = "payMonth") String payMonth) {LambdaQueryWrapper<PaymentWalletDetail> wrapper = new LambdaQueryWrapper<>();wrapper.eq(PaymentWalletDetail::getPayMonth, payMonth);return detailService.selectList(wrapper);}}
测试
根据分片逻辑,这样会查询表
payment_wallet_detail_202403的数据。
报错-JAXB-API,xml.bind
javax.xml.bind.JAXBException: Implementation of JAXB-API has not been found on module path or classpath.at javax.xml.bind.ContextFinder.newInstance(ContextFinder.java:278) ~[jaxb-api-2.3.0.jar:2.3.0]at javax.xml.bind.ContextFinder.find(ContextFinder.java:421) ~[jaxb-api-2.3.0.jar:2.3.0]at javax.xml.bind.JAXBContext.newInstance(JAXBContext.java:721) ~[jaxb-api-2.3.0.jar:2.3.0]at javax.xml.bind.JAXBContext.newInstance(JAXBContext.java:662) ~[jaxb-api-2.3.0.jar:2.3.0]java.lang.ClassNotFoundException: com.sun.xml.bind.v2.model.annotation.AnnotationReaderat java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641) ~[na:na]at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188) ~[na:na]at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:525) ~[na:na]at java.base/java.lang.ClassLoader.defineClass1(Native Method) ~[na:na]at java.base/java.lang.ClassLoader.defineClass(ClassLoader.java:1017) ~[na:na]
解决
pom.xml 添加引用
<!-- jaxb-api 实现需要的依赖 --><!-- JAXB API --><dependency><groupId>javax.xml.bind</groupId><artifactId>jaxb-api</artifactId><version>2.3.1</version></dependency><!-- JAXB RI --><dependency><groupId>com.sun.xml.bind</groupId><artifactId>jaxb-impl</artifactId><version>2.3.4</version></dependency>
错误-org.yaml.snakeyaml.representer.Representer: method ‘void ()’ not found java.lang.NoSuchMethodError: org.yaml.snakeyaml.representer.Representer: method 'void <init>()' not found at org.apache.shardingsphere.infra.util.yaml.representer.ShardingSphereYamlRepresenter.<init>(ShardingSphereYamlRepresenter.java:42) ~[shardingsphere-infra-util-5.4.0.jar:5.4.0] at org.apache.shardingsphere.infra.util.yaml.YamlEngine.marshal(YamlEngine.java:110) ~[shardingsphere-infra-util-5.4.0.jar:5.4.0] at org.apache.shardingsphere.metadata.persist.service.config.global.GlobalRulePersistService.persist(GlobalRulePersistService.java:45) ~[shardingsphere-metadata-core-5.4.0.jar:5.4.0]
java.lang.NoSuchMethodError: org.yaml.snakeyaml.representer.Representer: method 'void <init>()' not foundat org.apache.shardingsphere.infra.util.yaml.representer.ShardingSphereYamlRepresenter.<init>(ShardingSphereYamlRepresenter.java:42) ~[shardingsphere-infra-util-5.4.0.jar:5.4.0]at org.apache.shardingsphere.infra.util.yaml.YamlEngine.marshal(YamlEngine.java:110) ~[shardingsphere-infra-util-5.4.0.jar:5.4.0]at org.apache.shardingsphere.metadata.persist.service.config.global.GlobalRulePersistService.persist(GlobalRulePersistService.java:45) ~[shardingsphere-metadata-core-5.4.0.jar:5.4.0]
解决【未完成】
参考:
https://github.com/apache/shardingsphere/pull/28805
https://github.com/apache/shardingsphere/issues/30816
https://stackoverflow.com/questions/77713161
根据这些文档的描述,需要等 shardingsphere-jdbc 升级到 5.4.2 才能解决了。
针对 SpringBoot3 和 shardingsphere-jdbc5.4.1 不兼容的问题进行修改
1.修改 SpringBoot 版本为 2.2.2.RELEASE
- pom.xml
<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><!--<version>3.2.4</version>--><version>2.2.2.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent>
2.修改 mybatis-plus 版本
<!--引入 MybatisPlus 依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-spring-boot3-starter</artifactId><version>3.5.5</version></dependency>
修改为
<!--Mybatis-plus的依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.3.1</version></dependency>
3.删除针对 SpringBoot3 相关报错增加的组件
<!-- jaxb-api 实现需要的依赖 --><!-- JAXB API --><dependency><groupId>javax.xml.bind</groupId><artifactId>jaxb-api</artifactId><version>2.3.1</version></dependency><!-- JAXB RI --><dependency><groupId>com.sun.xml.bind</groupId><artifactId>jaxb-impl</artifactId><version>2.3.4</version></dependency>
4.修改 JDK 版本为 8
<properties><!-- <java.version>17</java.version>--><java.version>8</java.version></properties>
5.测试
报错-void org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(int)
java.lang.NoSuchMethodError: 'void org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(int)'at org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor.createLoaderOptions(ShardingSphereYamlConstructor.java:52) ~[shardingsphere-infra-util-5.4.1.jar:5.4.1]at org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor.<init>(ShardingSphereYamlConstructor.java:42) ~[shardingsphere-infra-util-5.4.1.jar:5.4.1]at org.apache.shardingsphere.infra.util.yaml.YamlEngine.unmarshal(YamlEngine.java:69) ~[shardingsphere-infra-util-5.4.1.jar:5.4.1]at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:101) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.createDataSource(DriverDataSourceCache.java:52) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]
报错解决
- pom.xml 增加引用
<!-- SnakeYaml就是用于解析YAML,序列化以及反序列化的第三方框架--><dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>1.33</version></dependency>
测试结果
- 日志可以看到已经根据分片键找到了对应的表
payment_wallet_detail_202403
2024-04-26 17:01:03.810 INFO 25216 --- [nio-8088-exec-1] ShardingSphere-SQL : Actual SQL: master_0 ::: SELECT id,pay_month,create_by,update_by,create_time,update_time,is_delete FROM payment_wallet_detail_202403
- 查询结果
[{"id":973021735680999428,"createBy":"luoma","updateBy":"luoma","createTime":"2024-03-11 23:20:09","updateTime":"2024-04-25 18:47:55","isDelete":0,"payMonth":"202403"},{"id":973021735680999429,"createBy":"luoma","updateBy":"luoma","createTime":"2024-03-11 23:20:09","updateTime":"2024-04-25 18:47:55","isDelete":0,"payMonth":"202403"}]
6.完整 pom.xml
<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><!--<version>3.2.4</version>--><version>2.2.2.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.sharding.demo</groupId><artifactId>sharding-jdbc-demo</artifactId><version>0.0.1-SNAPSHOT</version><name>sharding-jdbc-demo</name><description>sharding-jdbc-demo</description><properties><!-- <java.version>17</java.version>--><java.version>8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!--引入 MybatisPlus 依赖--><!-- <dependency>--><!-- <groupId>com.baomidou</groupId>--><!-- <artifactId>mybatis-plus-spring-boot3-starter</artifactId>--><!-- <version>3.5.5</version>--><!-- </dependency>--><!--Mybatis-plus的依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.3.1</version></dependency><!--mysql--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.30</version></dependency><!--引入 durid 数据源--><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.18</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>5.4.1</version></dependency><!--lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><!-- hutool--><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.25</version></dependency><!-- springboot--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-freemarker</artifactId></dependency><!-- SnakeYaml就是用于解析YAML,序列化以及反序列化的第三方框架--><dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>1.33</version></dependency><!-- <!– jaxb-api 实现需要的依赖 –>--><!-- <!– JAXB API –>--><!-- <dependency>--><!-- <groupId>javax.xml.bind</groupId>--><!-- <artifactId>jaxb-api</artifactId>--><!-- <version>2.3.1</version>--><!-- </dependency>--><!-- <!– JAXB RI –>--><!-- <dependency>--><!-- <groupId>com.sun.xml.bind</groupId>--><!-- <artifactId>jaxb-impl</artifactId>--><!-- <version>2.3.4</version>--><!-- </dependency>--></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins><resources><resource><directory>src/main/resources</directory><includes><include>**/*</include></includes><filtering>false</filtering></resource></resources></build></project>