开发环境
名称 | 版本 |
---|---|
操作系统 | 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_202405
payment_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.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl: jdbc:mysql://a.a.a.a:3306/testDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
username: username
password: password
# 这里进行分库分表的表,需要在ShardingConfig中进行热启动,否则第一次查询会很慢
rules:
- !SHARDING
tables:
payment_wallet_detail:
actualDataNodes: master_${0}.payment_wallet_detail_${2024}${(2..5).collect{t ->t.toString().padLeft(2,'0')}}
tableStrategy:
standard:
shardingColumn: pay_month
shardingAlgorithmName: pay_month_algorithm
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
payment_wallet_transaction:
actualDataNodes: master_${0}.payment_wallet_transaction_${0..49}
tableStrategy:
complex:
shardingColumns: pay_order_number
shardingAlgorithmName: payment_wallet_transaction_algorithm
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
shardingAlgorithms:
pay_month_algorithm:
type: CLASS_BASE_PAY_MONTH
props:
strategy: standard
algorithmClassName: com.sharding.demo.sharingrule.PayMonthAlgorithm
payment_wallet_transaction_algorithm:
type: CLASS_WALLET_PAY_ORDER_NUMBER
props:
strategy: complex
algorithmClassName: com.sharding.demo.sharingrule.WalletPayOrderNumberShardingRule
keyGenerators:
snowflake:
type: SNOWFLAKE
- !SINGLE
tables:
- "*.*"
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: password
spring:
datasource:
# 引入sharding
driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
url: jdbc:shardingsphere:classpath:sharding-dev.yml
dynamic:
primary: master
#mybatis
mybatis-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: 0
logic-not-delete-value: 1
# #自定义填充策略接口实现
# meta-object-handler:
configuration:
map-underscore-to-camel-case: true #开启驼峰命名
cache-enabled: false
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #日志
分片逻辑配置
- 新建
resources\META-INF\services\org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
com.sharding.demo.sharingrule.PayMonthAlgorithm
com.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;
@Slf4j
public class PayMonthAlgorithm implements StandardShardingAlgorithm<String> {
private final static DateTimeFormatter YYYY_MM_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");
/**
* 等值算法
*
* @param availableTargetNames
* @param shardingValue
* @return
*/
@Override
public 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
*/
@Override
public 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);
}
@Override
public 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
@Slf4j
public class WalletPayOrderNumberShardingRule implements ComplexKeysShardingAlgorithm<String> {
@Override
public 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;
}
@Override
public 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 {
@Resource
PaymentWalletDetailMapper 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.AnnotationReader
at 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 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]
解决【未完成】
参考:
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>