SpringBoot3- 整合 shardingsphere-jdbc【未完成】

2024年04月24日 18:03 · 阅读(930) ·

开发环境

基于项目 SpringBoot3- 整合 Mybatis-plus

名称 版本
操作系统 Windows 11 X64
SpringBoot 3.2.4
2.2.2.RELEASE
JDK 17
8
shardingsphere-jdbc 5.4.1

源码下载

sharding-jdbc-demo-springboot2.zip

新建表

  1. payment_wallet_detail_202402
  2. ...
  3. payment_wallet_detail_202405
  4. payment_wallet_transaction_0
  5. ...
  6. payment_wallet_transaction_49

目录结构

目录结构

pom.xml

  1. <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core -->
  2. <dependency>
  3. <groupId>org.apache.shardingsphere</groupId>
  4. <artifactId>shardingsphere-jdbc-core</artifactId>
  5. <version>5.4.1</version>
  6. </dependency>

sharding-dev.yml

  1. dataSources:
  2. master_0:
  3. dataSourceClassName: com.zaxxer.hikari.HikariDataSource
  4. driverClassName: com.mysql.jdbc.Driver
  5. jdbcUrl: jdbc:mysql://a.a.a.a:3306/testDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
  6. username: username
  7. password: password
  8. # 这里进行分库分表的表,需要在ShardingConfig中进行热启动,否则第一次查询会很慢
  9. rules:
  10. - !SHARDING
  11. tables:
  12. payment_wallet_detail:
  13. actualDataNodes: master_${0}.payment_wallet_detail_${2024}${(2..5).collect{t ->t.toString().padLeft(2,'0')}}
  14. tableStrategy:
  15. standard:
  16. shardingColumn: pay_month
  17. shardingAlgorithmName: pay_month_algorithm
  18. keyGenerateStrategy:
  19. column: id
  20. keyGeneratorName: snowflake
  21. payment_wallet_transaction:
  22. actualDataNodes: master_${0}.payment_wallet_transaction_${0..49}
  23. tableStrategy:
  24. complex:
  25. shardingColumns: pay_order_number
  26. shardingAlgorithmName: payment_wallet_transaction_algorithm
  27. keyGenerateStrategy:
  28. column: id
  29. keyGeneratorName: snowflake
  30. shardingAlgorithms:
  31. pay_month_algorithm:
  32. type: CLASS_BASE_PAY_MONTH
  33. props:
  34. strategy: standard
  35. algorithmClassName: com.sharding.demo.sharingrule.PayMonthAlgorithm
  36. payment_wallet_transaction_algorithm:
  37. type: CLASS_WALLET_PAY_ORDER_NUMBER
  38. props:
  39. strategy: complex
  40. algorithmClassName: com.sharding.demo.sharingrule.WalletPayOrderNumberShardingRule
  41. keyGenerators:
  42. snowflake:
  43. type: SNOWFLAKE
  44. - !SINGLE
  45. tables:
  46. - "*.*"
  47. props:
  48. sql-show: true

application.yml

  1. server:
  2. port: 8088
  3. #spring:
  4. # datasource:
  5. # type: com.alibaba.druid.pool.DruidDataSource
  6. # driver-class-name: com.mysql.cj.jdbc.Driver
  7. # url: jdbc:mysql://a.a.a.a:3306/testDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
  8. # username: username
  9. # password: password
  10. spring:
  11. datasource:
  12. # 引入sharding
  13. driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
  14. url: jdbc:shardingsphere:classpath:sharding-dev.yml
  15. dynamic:
  16. primary: master
  17. #mybatis
  18. mybatis-plus:
  19. mapper-locations: classpath:/mapper/*Mapper.xml
  20. #实体扫描,多个package用逗号或者分号分隔
  21. typeAliasesPackage:
  22. typeEnumsPackage:
  23. global-config:
  24. #主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
  25. id-type: 0
  26. #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
  27. field-strategy: 2
  28. #驼峰下划线转换
  29. db-column-underline: true
  30. #刷新mapper 调试神器
  31. refresh-mapper: true
  32. #数据库大写下划线转换
  33. #capital-mode: true
  34. #序列接口实现类配置
  35. #key-generator:
  36. #逻辑删除配置
  37. logic-delete-value: 0
  38. logic-not-delete-value: 1
  39. # #自定义填充策略接口实现
  40. # meta-object-handler:
  41. configuration:
  42. map-underscore-to-camel-case: true #开启驼峰命名
  43. cache-enabled: false
  44. log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #日志

分片逻辑配置

  • 新建
  1. resources\META-INF\services\org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
  1. com.sharding.demo.sharingrule.PayMonthAlgorithm
  2. com.sharding.demo.sharingrule.WalletPayOrderNumberShardingRule

分片逻辑

按期间分片-PayMonthAlgorithm

  1. package com.sharding.demo.sharingrule;
  2. import com.sharding.demo.common.ResponseCode;
  3. import com.sharding.demo.utils.AssertUtil;
  4. import lombok.extern.slf4j.Slf4j;
  5. import org.apache.commons.lang3.StringUtils;
  6. import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
  7. import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
  8. import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
  9. import java.time.LocalDate;
  10. import java.time.format.DateTimeFormatter;
  11. import java.util.Collection;
  12. import java.util.LinkedHashSet;
  13. import java.util.Set;
  14. @Slf4j
  15. public class PayMonthAlgorithm implements StandardShardingAlgorithm<String> {
  16. private final static DateTimeFormatter YYYY_MM_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");
  17. /**
  18. * 等值算法
  19. *
  20. * @param availableTargetNames
  21. * @param shardingValue
  22. * @return
  23. */
  24. @Override
  25. public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
  26. log.info("PayMonthAlgorithm accurate availableTargetNames={} shardingValue={}",availableTargetNames,shardingValue);
  27. String choseTable = null;
  28. String key = shardingValue.getValue();
  29. for(String table:availableTargetNames){
  30. if(table.endsWith(key)){
  31. choseTable = table;
  32. break;
  33. }
  34. }
  35. log.info("PayMonthAlgorithm accurate choseTable={},key={}", choseTable, key);
  36. AssertUtil.isTrue(StringUtils.isNotBlank(choseTable), ResponseCode.NO_DATA.value(), "not chose table,key=" + key);
  37. return choseTable;
  38. }
  39. /**
  40. * 区间算法
  41. *
  42. * @param availableTargetNames
  43. * @param shardingValue
  44. * @return
  45. */
  46. @Override
  47. public Collection<String> doSharding(Collection<String> availableTargetNames,
  48. RangeShardingValue<String> shardingValue) {
  49. log.info("PayMonthAlgorithm accurate range availableTargetNames={} shardingValue={}",availableTargetNames,shardingValue);
  50. Set<String> choseTables = new LinkedHashSet<>();
  51. String lower = shardingValue.getValueRange().lowerEndpoint();
  52. String upper = shardingValue.getValueRange().upperEndpoint();
  53. while (lower.compareTo(upper) > 0){
  54. String key = lower;
  55. for (String table : availableTargetNames) {
  56. if (table.endsWith(key)) {
  57. choseTables.add(table);
  58. }
  59. }
  60. lower = plusOneMonth(lower);
  61. }
  62. log.info("PayMonthAlgorithm accurate choseTable={}",choseTables);
  63. return choseTables;
  64. }
  65. /**
  66. * 加一个月
  67. * @param payDate
  68. * @return 加一个月
  69. */
  70. private String plusOneMonth(String payDate){
  71. LocalDate date = LocalDate.parse(payDate, YYYY_MM_FORMATTER);
  72. date = date.plusMonths(1L);
  73. return date.format(YYYY_MM_FORMATTER);
  74. }
  75. @Override
  76. public String getType() {
  77. return "CLASS_BASE_PAY_MONTH";
  78. }
  79. }

hash 分片-WalletPayOrderNumberShardingRule

  1. package com.sharding.demo.sharingrule;
  2. import lombok.extern.slf4j.Slf4j;
  3. import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
  4. import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;
  5. import org.springframework.stereotype.Component;
  6. import java.util.ArrayList;
  7. import java.util.Collection;
  8. import java.util.Map;
  9. @Component
  10. @Slf4j
  11. public class WalletPayOrderNumberShardingRule implements ComplexKeysShardingAlgorithm<String> {
  12. @Override
  13. public Collection<String> doSharding(Collection<String> tables,
  14. ComplexKeysShardingValue<String> complexKeysShardingValue) {
  15. log.info("WalletPayOrderNumberShardingRule {} {}",tables,complexKeysShardingValue);
  16. Collection<String> result = new ArrayList<>();
  17. Map<String,Collection<String>> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
  18. Collection<String> c = map.get("pay_order_number");
  19. for(String tip : c){
  20. String hashCode = String.valueOf(tip.hashCode());
  21. int hash = Integer.parseInt(hashCode.substring(hashCode.length()-2));
  22. result.add("payment_wallet_transaction_" + hash % 50);
  23. }
  24. log.info("WalletPayOrderNumberShardingRule,select table result: {}",result);
  25. return result;
  26. }
  27. @Override
  28. public String getType() {
  29. return "CLASS_WALLET_PAY_ORDER_NUMBER";
  30. }
  31. }

Controller

  1. package com.sharding.demo.controller;
  2. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
  3. import com.sharding.demo.mapper.PaymentWalletDetailMapper;
  4. import com.sharding.demo.model.PaymentWalletDetail;
  5. import jakarta.annotation.Resource;
  6. import lombok.extern.slf4j.Slf4j;
  7. import org.springframework.web.bind.annotation.GetMapping;
  8. import org.springframework.web.bind.annotation.RequestMapping;
  9. import org.springframework.web.bind.annotation.RequestParam;
  10. import org.springframework.web.bind.annotation.RestController;
  11. import java.util.List;
  12. @Slf4j
  13. @RestController
  14. @RequestMapping("/test")
  15. public class TestController {
  16. @Resource
  17. PaymentWalletDetailMapper detailService;
  18. @GetMapping("/testGetDetail")
  19. public List<PaymentWalletDetail> testGetDetail(@RequestParam(name = "payMonth") String payMonth) {
  20. LambdaQueryWrapper<PaymentWalletDetail> wrapper = new LambdaQueryWrapper<>();
  21. wrapper.eq(PaymentWalletDetail::getPayMonth, payMonth);
  22. return detailService.selectList(wrapper);
  23. }
  24. }

测试

报错-JAXB-API,xml.bind

  1. javax.xml.bind.JAXBException: Implementation of JAXB-API has not been found on module path or classpath.
  2. at javax.xml.bind.ContextFinder.newInstance(ContextFinder.java:278) ~[jaxb-api-2.3.0.jar:2.3.0]
  3. at javax.xml.bind.ContextFinder.find(ContextFinder.java:421) ~[jaxb-api-2.3.0.jar:2.3.0]
  4. at javax.xml.bind.JAXBContext.newInstance(JAXBContext.java:721) ~[jaxb-api-2.3.0.jar:2.3.0]
  5. at javax.xml.bind.JAXBContext.newInstance(JAXBContext.java:662) ~[jaxb-api-2.3.0.jar:2.3.0]
  6. java.lang.ClassNotFoundException: com.sun.xml.bind.v2.model.annotation.AnnotationReader
  7. at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641) ~[na:na]
  8. at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188) ~[na:na]
  9. at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:525) ~[na:na]
  10. at java.base/java.lang.ClassLoader.defineClass1(Native Method) ~[na:na]
  11. at java.base/java.lang.ClassLoader.defineClass(ClassLoader.java:1017) ~[na:na]

解决

参考:javax.xml.bind.JAXBException Implementation of JAXB-API has not been found on module path or classpath

pom.xml 添加引用

  1. <!-- jaxb-api 实现需要的依赖 -->
  2. <!-- JAXB API -->
  3. <dependency>
  4. <groupId>javax.xml.bind</groupId>
  5. <artifactId>jaxb-api</artifactId>
  6. <version>2.3.1</version>
  7. </dependency>
  8. <!-- JAXB RI -->
  9. <dependency>
  10. <groupId>com.sun.xml.bind</groupId>
  11. <artifactId>jaxb-impl</artifactId>
  12. <version>2.3.4</version>
  13. </dependency>

错误-org.yaml.snakeyaml.representer.Representer: method ‘void ()’ not found
  1. java.lang.NoSuchMethodError: org.yaml.snakeyaml.representer.Representer: method 'void <init>()' not found
  2. at org.apache.shardingsphere.infra.util.yaml.representer.ShardingSphereYamlRepresenter.<init>(ShardingSphereYamlRepresenter.java:42) ~[shardingsphere-infra-util-5.4.0.jar:5.4.0]
  3. at org.apache.shardingsphere.infra.util.yaml.YamlEngine.marshal(YamlEngine.java:110) ~[shardingsphere-infra-util-5.4.0.jar:5.4.0]
  4. 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
  1. <parent>
  2. <groupId>org.springframework.boot</groupId>
  3. <artifactId>spring-boot-starter-parent</artifactId>
  4. <!--<version>3.2.4</version>-->
  5. <version>2.2.2.RELEASE</version>
  6. <relativePath/> <!-- lookup parent from repository -->
  7. </parent>

2.修改 mybatis-plus 版本

  1. <!--引入 MybatisPlus 依赖-->
  2. <dependency>
  3. <groupId>com.baomidou</groupId>
  4. <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
  5. <version>3.5.5</version>
  6. </dependency>

修改为

  1. <!--Mybatis-plus的依赖-->
  2. <dependency>
  3. <groupId>com.baomidou</groupId>
  4. <artifactId>mybatis-plus-boot-starter</artifactId>
  5. <version>3.3.1</version>
  6. </dependency>

3.删除针对 SpringBoot3 相关报错增加的组件

  1. <!-- jaxb-api 实现需要的依赖 -->
  2. <!-- JAXB API -->
  3. <dependency>
  4. <groupId>javax.xml.bind</groupId>
  5. <artifactId>jaxb-api</artifactId>
  6. <version>2.3.1</version>
  7. </dependency>
  8. <!-- JAXB RI -->
  9. <dependency>
  10. <groupId>com.sun.xml.bind</groupId>
  11. <artifactId>jaxb-impl</artifactId>
  12. <version>2.3.4</version>
  13. </dependency>

4.修改 JDK 版本为 8

  1. <properties>
  2. <!-- <java.version>17</java.version>-->
  3. <java.version>8</java.version>
  4. </properties>

5.测试

报错-void org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(int)

  1. java.lang.NoSuchMethodError: 'void org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(int)'
  2. at org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor.createLoaderOptions(ShardingSphereYamlConstructor.java:52) ~[shardingsphere-infra-util-5.4.1.jar:5.4.1]
  3. at org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor.<init>(ShardingSphereYamlConstructor.java:42) ~[shardingsphere-infra-util-5.4.1.jar:5.4.1]
  4. at org.apache.shardingsphere.infra.util.yaml.YamlEngine.unmarshal(YamlEngine.java:69) ~[shardingsphere-infra-util-5.4.1.jar:5.4.1]
  5. at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:101) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]
  6. at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.createDataSource(DriverDataSourceCache.java:52) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]

报错解决

参考:关于java.lang.NoSuchMethodError: org.yaml.snakeyaml.LoaderOptions.setMaxAliasesForCollections(I)V报错的问题

  • pom.xml 增加引用
  1. <!-- SnakeYaml就是用于解析YAML,序列化以及反序列化的第三方框架-->
  2. <dependency>
  3. <groupId>org.yaml</groupId>
  4. <artifactId>snakeyaml</artifactId>
  5. <version>1.33</version>
  6. </dependency>

测试结果

  • 日志可以看到已经根据分片键找到了对应的表 payment_wallet_detail_202403
  1. 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
  • 查询结果
  1. [
  2. {"id":973021735680999428,"createBy":"luoma","updateBy":"luoma","createTime":"2024-03-11 23:20:09","updateTime":"2024-04-25 18:47:55","isDelete":0,"payMonth":"202403"},
  3. {"id":973021735680999429,"createBy":"luoma","updateBy":"luoma","createTime":"2024-03-11 23:20:09","updateTime":"2024-04-25 18:47:55","isDelete":0,"payMonth":"202403"}
  4. ]

6.完整 pom.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. <modelVersion>4.0.0</modelVersion>
  5. <parent>
  6. <groupId>org.springframework.boot</groupId>
  7. <artifactId>spring-boot-starter-parent</artifactId>
  8. <!--<version>3.2.4</version>-->
  9. <version>2.2.2.RELEASE</version>
  10. <relativePath/> <!-- lookup parent from repository -->
  11. </parent>
  12. <groupId>com.sharding.demo</groupId>
  13. <artifactId>sharding-jdbc-demo</artifactId>
  14. <version>0.0.1-SNAPSHOT</version>
  15. <name>sharding-jdbc-demo</name>
  16. <description>sharding-jdbc-demo</description>
  17. <properties>
  18. <!-- <java.version>17</java.version>-->
  19. <java.version>8</java.version>
  20. </properties>
  21. <dependencies>
  22. <dependency>
  23. <groupId>org.springframework.boot</groupId>
  24. <artifactId>spring-boot-starter-web</artifactId>
  25. </dependency>
  26. <!--引入 MybatisPlus 依赖-->
  27. <!-- <dependency>-->
  28. <!-- <groupId>com.baomidou</groupId>-->
  29. <!-- <artifactId>mybatis-plus-spring-boot3-starter</artifactId>-->
  30. <!-- <version>3.5.5</version>-->
  31. <!-- </dependency>-->
  32. <!--Mybatis-plus的依赖-->
  33. <dependency>
  34. <groupId>com.baomidou</groupId>
  35. <artifactId>mybatis-plus-boot-starter</artifactId>
  36. <version>3.3.1</version>
  37. </dependency>
  38. <!--mysql-->
  39. <dependency>
  40. <groupId>mysql</groupId>
  41. <artifactId>mysql-connector-java</artifactId>
  42. <version>8.0.30</version>
  43. </dependency>
  44. <!--引入 durid 数据源-->
  45. <dependency>
  46. <groupId>com.alibaba</groupId>
  47. <artifactId>druid-spring-boot-starter</artifactId>
  48. <version>1.1.18</version>
  49. </dependency>
  50. <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core -->
  51. <dependency>
  52. <groupId>org.apache.shardingsphere</groupId>
  53. <artifactId>shardingsphere-jdbc-core</artifactId>
  54. <version>5.4.1</version>
  55. </dependency>
  56. <!--lombok-->
  57. <dependency>
  58. <groupId>org.projectlombok</groupId>
  59. <artifactId>lombok</artifactId>
  60. </dependency>
  61. <!-- hutool-->
  62. <dependency>
  63. <groupId>cn.hutool</groupId>
  64. <artifactId>hutool-all</artifactId>
  65. <version>5.8.25</version>
  66. </dependency>
  67. <!-- springboot-->
  68. <dependency>
  69. <groupId>org.springframework.boot</groupId>
  70. <artifactId>spring-boot-starter</artifactId>
  71. </dependency>
  72. <dependency>
  73. <groupId>org.springframework.boot</groupId>
  74. <artifactId>spring-boot-starter-freemarker</artifactId>
  75. </dependency>
  76. <!-- SnakeYaml就是用于解析YAML,序列化以及反序列化的第三方框架-->
  77. <dependency>
  78. <groupId>org.yaml</groupId>
  79. <artifactId>snakeyaml</artifactId>
  80. <version>1.33</version>
  81. </dependency>
  82. <!-- &lt;!&ndash; jaxb-api 实现需要的依赖 &ndash;&gt;-->
  83. <!-- &lt;!&ndash; JAXB API &ndash;&gt;-->
  84. <!-- <dependency>-->
  85. <!-- <groupId>javax.xml.bind</groupId>-->
  86. <!-- <artifactId>jaxb-api</artifactId>-->
  87. <!-- <version>2.3.1</version>-->
  88. <!-- </dependency>-->
  89. <!-- &lt;!&ndash; JAXB RI &ndash;&gt;-->
  90. <!-- <dependency>-->
  91. <!-- <groupId>com.sun.xml.bind</groupId>-->
  92. <!-- <artifactId>jaxb-impl</artifactId>-->
  93. <!-- <version>2.3.4</version>-->
  94. <!-- </dependency>-->
  95. </dependencies>
  96. <build>
  97. <plugins>
  98. <plugin>
  99. <groupId>org.springframework.boot</groupId>
  100. <artifactId>spring-boot-maven-plugin</artifactId>
  101. <configuration>
  102. <excludes>
  103. <exclude>
  104. <groupId>org.projectlombok</groupId>
  105. <artifactId>lombok</artifactId>
  106. </exclude>
  107. </excludes>
  108. </configuration>
  109. </plugin>
  110. </plugins>
  111. <resources>
  112. <resource>
  113. <directory>src/main/resources</directory>
  114. <includes>
  115. <include>**/*</include>
  116. </includes>
  117. <filtering>false</filtering>
  118. </resource>
  119. </resources>
  120. </build>
  121. </project>