ShardingSphere-jdbc 5.5.0 + spring boot 基础配置 - 实战篇
ShardingSphere-jdbc 5.5.0 + spring boot 基础配置
- 环境准备
- 版本
- 数据库说明
- 集群
- 配置
- 配置文件
- Maven依赖
- spring boot配置
- shardingsphere-jdbc配置
- 自定义配置1：SM4加解密存储数据
- 完整的基础配置
- 其他
- 雪花算法自定义worker.id
环境准备
版本
spring boot 2.7.17
shardingsphere-jdbc 5.5.0
druid 1.2.23
数据库说明
本示例数据库为单机多库schema的架构，以一主一从作为集群演示，转为一主多从数据库集群时，可自定义修改配置。
集群
一主一从：
逻辑主库：ds_basic、ds0000、ds0001（ds_basic为数据简单、量少的元数据库）
逻辑从库：ds0000_slave、ds0001_slave
配置
配置文件
Maven依赖
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc</artifactId> <version>5.5.0</version> <exclusions> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-test-util</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.yaml</groupId> <artifactId>snakeyaml</artifactId> <version>2.2</version> </dependency>spring boot配置
application.yml
spring: application: name: demo main: allow-bean-definition-overriding: true datasource: driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver url: jdbc:shardingsphere:classpath:sharding.yamlshardingsphere-jdbc配置
自定义配置1：SM4加解密存储数据
shardingsphere 5.5.0移除了sm4算法，两种方式解决：
1、使用官方shardingpshere plugin，找到相关组件依赖引用即可，
2、自己添加sm4算法代码SPI实现。
本文使用方式2（注意：ShardingSphere-jdbc 5.5.1版本加密算法接口升级了，以下sm4的SPI代码不适用，后续推出适配版本）。
增加spi扩展
org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm增加以下代码：com.demo.core.encrypt.SM4EncryptAlgorithmSM4算法代码
package com.demo.core.encrypt;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.SneakyThrows;
import org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm;
import org.apache.shardingsphere.encrypt.spi.EncryptAlgorithmMetaData;
import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
import org.apache.shardingsphere.infra.algorithm.core.exception.AlgorithmInitializationException;
import org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.bouncycastle.pqc.math.linearalgebra.ByteUtils;import javax.crypto.Cipher;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;
import java.nio.charset.StandardCharsets;
import java.security.GeneralSecurityException;
import java.security.Security;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Optional;
import java.util.Properties;
import java.util.Set;/**
shardingsphere SM4 encrypt algorithm.
@author Robin Wang
*/
@EqualsAndHashCode
public final class SM4EncryptAlgorithm implements EncryptAlgorithm {static {
Security.addProvider(new BouncyCastleProvider());
}@Getter
private final EncryptAlgorithmMetaData metaData = new EncryptAlgorithmMetaData(true, true, false);private static final String SM4_KEY = “sm4-key”;
private static final String SM4_IV = “sm4-iv”;
private static final String SM4_MODE = “sm4-mode”;
private static final String SM4_PADDING = “sm4-padding”;
private static final int KEY_LENGTH = 16;
private static final int IV_LENGTH = 16;
private static final Set MODES = new HashSet<>(Arrays.asList(“ECB”, “CBC”));
private static final Set PADDINGS = new HashSet<>(Arrays.asList(“PKCS5Padding”, “PKCS7Padding”));
private byte[] sm4Key;
private byte[] sm4Iv;
private String sm4ModePadding;
@Override
public void init(final Properties props) {
String sm4Mode = createSm4Mode(props);
String sm4Padding = createSm4Padding(props);
sm4ModePadding = “SM4/” + sm4Mode + “/” + sm4Padding;
sm4Key = createSm4Key(props);
sm4Iv = createSm4Iv(props, sm4Mode);
}private String createSm4Mode(final Properties props) {
ShardingSpherePreconditions.checkState(props.containsKey(SM4_MODE), () -> new AlgorithmInitializationException(this, “%s can not be null or empty”, SM4_MODE));
String result = String.valueOf(props.getProperty(SM4_MODE)).toUpperCase();
ShardingSpherePreconditions.checkState(MODES.contains(result), () -> new AlgorithmInitializationException(this, “Mode must be either CBC or ECB”));
return result;
}private byte[] createSm4Key(final Properties props) {
ShardingSpherePreconditions.checkState(props.containsKey(SM4_KEY), () -> new AlgorithmInitializationException(this, “%s can not be null”, SM4_KEY));
byte[] result = ByteUtils.fromHexString(String.valueOf(props.getProperty(SM4_KEY)));
ShardingSpherePreconditions.checkState(KEY_LENGTH == result.length,
() -> new AlgorithmInitializationException(this, “Key length must be " + KEY_LENGTH + " bytes long”));
return result;
}private byte[] createSm4Iv(final Properties props, final String sm4Mode) {
if (!“CBC”.equalsIgnoreCase(sm4Mode)) {
return null;
}
ShardingSpherePreconditions.checkState(props.containsKey(SM4_IV), () -> new AlgorithmInitializationException(this, “%s can not be null”, SM4_IV));
String sm4IvValue = String.valueOf(props.getProperty(SM4_IV));
byte[] result = ByteUtils.fromHexString(sm4IvValue);
ShardingSpherePreconditions.checkState(IV_LENGTH == result.length, () -> new AlgorithmInitializationException(this, “Iv length must be " + IV_LENGTH + " bytes long”));
return result;
}private String createSm4Padding(final Properties props) {
ShardingSpherePreconditions.checkState(props.containsKey(SM4_PADDING), () -> new AlgorithmInitializationException(this, “%s can not be null”, SM4_PADDING));
String result = String.valueOf(props.getProperty(SM4_PADDING)).toUpperCase().replace(“PADDING”, “Padding”);
ShardingSpherePreconditions.checkState(PADDINGS.contains(result), () -> new AlgorithmInitializationException(this, “Padding must be either PKCS5Padding or PKCS7Padding”));
return result;
}@Override
public String encrypt(Object plainValue, AlgorithmSQLContext algorithmSQLContext) {
return null == plainValue ? null : ByteUtils.toHexString(encrypt(String.valueOf(plainValue).getBytes(StandardCharsets.UTF_8)));
}private byte[] encrypt(final byte[] plainValue) {
return handle(plainValue, Cipher.ENCRYPT_MODE);
}@Override
public Object decrypt(Object cipherValue, AlgorithmSQLContext algorithmSQLContext) {
return null == cipherValue ? null : new String(decrypt(ByteUtils.fromHexString((String) cipherValue)), StandardCharsets.UTF_8);
}private byte[] decrypt(final byte[] cipherValue) {
return handle(cipherValue, Cipher.DECRYPT_MODE);
}@SneakyThrows(GeneralSecurityException.class)
private byte[] handle(final byte[] input, final int mode) {
Cipher cipher = Cipher.getInstance(sm4ModePadding, BouncyCastleProvider.PROVIDER_NAME);
SecretKeySpec secretKeySpec = new SecretKeySpec(sm4Key, “SM4”);
Optional<byte[]> sm4Iv = Optional.ofNullable(this.sm4Iv);
if (sm4Iv.isPresent()) {
cipher.init(mode, secretKeySpec, new IvParameterSpec(sm4Iv.get()));
} else {
cipher.init(mode, secretKeySpec);
}
return cipher.doFinal(input);
}@Override
public String getType() {
return “SM4”;
}
}
sm4算法yaml配置
encryptors:
sm4_encryptor:
type: SM4
props:
sm4-key: 86C63180C2806ED1F43A859DE501215C
sm4-mode: ECB
sm4-padding: PKCS5Padding
完整的基础配置
sharding.yaml
配置包括：单机模式服务、数据源（加解密）、规则配置：【数据分片、读写分离、数据加密、单表】
待新增补充：混合规则等
mode: type: Standalone repository: type: JDBC databaseName: demo_db dataSources: ds_basic: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/demo_basic?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false username: root password: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ== initialSize: 1 minIdle: 1 maxActive: 64 maxWait: 20000 validationQuery: SELECT 1 FROM DUAL validationQueryTimeout: 30000 minEvictableIdleTimeMillis: 300000 maxEvictableIdleTimeMillis: 600000 timeBetweenEvictionRunsMillis: 300000 testOnBorrow: true testWhileIdle: true filters: config, stat, wall connectProperties: connectTimeout: 5000 socketTimeout: '20000' config.decrypt: 'true' config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ== ds0000: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/demo_0000?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false username: root password: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ== initialSize: 1 minIdle: 1 maxActive: 64 maxWait: 20000 validationQuery: SELECT 1 FROM DUAL validationQueryTimeout: 30000 minEvictableIdleTimeMillis: 300000 maxEvictableIdleTimeMillis: 600000 timeBetweenEvictionRunsMillis: 300000 testOnBorrow: true testWhileIdle: true filters: config, stat, wall connectProperties: connectTimeout: 5000 socketTimeout: '20000' config.decrypt: 'true' config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ== ds0001: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/demo_0001?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false username: root password: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ== initialSize: 1 minIdle: 1 maxActive: 64 maxWait: 20000 validationQuery: SELECT 1 FROM DUAL validationQueryTimeout: 30000 minEvictableIdleTimeMillis: 300000 maxEvictableIdleTimeMillis: 600000 timeBetweenEvictionRunsMillis: 300000 testOnBorrow: true testWhileIdle: true filters: config, stat, wall connectProperties: connectTimeout: 5000 socketTimeout: '20000' config.decrypt: 'true' config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ== ds0000_slave: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.1.88:3306/demo_0000?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false username: root password: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ== initialSize: 1 minIdle: 1 maxActive: 64 maxWait: 20000 validationQuery: SELECT 1 FROM DUAL validationQueryTimeout: 30000 minEvictableIdleTimeMillis: 300000 maxEvictableIdleTimeMillis: 600000 timeBetweenEvictionRunsMillis: 300000 testOnBorrow: true testWhileIdle: true filters: config, stat, wall connectProperties: connectTimeout: 5000 socketTimeout: '20000' config.decrypt: 'true' config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ== ds0001_slave: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.1.88:3306/demo_0001?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false username: root password: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ== initialSize: 1 minIdle: 1 maxActive: 64 maxWait: 20000 validationQuery: SELECT 1 FROM DUAL validationQueryTimeout: 30000 minEvictableIdleTimeMillis: 300000 maxEvictableIdleTimeMillis: 600000 timeBetweenEvictionRunsMillis: 300000 testOnBorrow: true testWhileIdle: true filters: config, stat, wall connectProperties: connectTimeout: 5000 socketTimeout: '20000' config.decrypt: 'true' config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ== rules: # 数据分片 - !SHARDING tables: t_claim_case_mdtrt: actualDataNodes: ds$->{['0000','0001']}.t_claim_case_mdtrt_000$->{0..9} tableStrategy: standard: shardingColumn: transaction_no shardingAlgorithmName: t_claim_case_mdtrt_inline keyGenerateStrategy: column: id keyGeneratorName: snowflake t_claim_case_info: actualDataNodes: ds$->{['0000','0001']}.t_claim_case_info_000$->{0..9} tableStrategy: standard: shardingColumn: transaction_no shardingAlgorithmName: t_claim_case_info_inline keyGenerateStrategy: column: id keyGeneratorName: snowflake defaultShardingColumn: transaction_no bindingTables: - t_claim_case_mdtrt, t_claim_case_info defaultDatabaseStrategy: standard: shardingColumn: transaction_no shardingAlgorithmName: database_inline defaultTableStrategy: none: shardingAlgorithms: database_inline: type: INLINE props: algorithm-expression: ds$->{transaction_no[-8..-5]} t_claim_case_mdtrt_inline: type: INLINE props: algorithm-expression: t_claim_case_mdtrt_$->{transaction_no[-4..-1]} t_claim_case_info_inline: type: INLINE props: algorithm-expression: t_claim_case_info_$->{transaction_no[-4..-1]} keyGenerators: snowflake: type: SNOWFLAKE #数据加密 - !ENCRYPT tables: t_claim_case_info: columns: appl_mobile: cipher: name: appl_mobile encryptorName: sm4_encryptor opsnId_no: cipher: name: opsnId_no encryptorName: sm4_encryptor rpter_id_no: cipher: name: rpter_id_no encryptorName: sm4_encryptor rpter_mobile: cipher: name: rpter_mobile encryptorName: sm4_encryptor encryptors: sm4_encryptor: type: SM4 props: sm4-key: 86C63180C2806ED1F43A859DE501215C sm4-mode: ECB sm4-padding: PKCS5Padding # 单表 - !SINGLE tables: - ds_basic.* # 读写分离 - !READWRITE_SPLITTING dataSources: ds0000: writeDataSourceName: ds0000 readDataSourceNames: - ds0000_slave transactionalReadQueryStrategy: PRIMARY loadBalancerName: random ds0001: writeDataSourceName: ds0001 readDataSourceNames: - ds0001_slave transactionalReadQueryStrategy: PRIMARY loadBalancerName: random loadBalancers: random: type: RANDOM props: sql-show: true max-connections-size-per-query: 5其他
雪花算法自定义worker.id
集群模式下，不同机器需要配置不同的workerId，适合使用ShardingSphere Proxy集群模式，需要使用第三方配置中心（zookeeper)。
但这会变更架构，且本项目CICD时只有一个war部署weblogic server集群的方式。
因此特沿用ShardingSphere JDBC单机模式，并且启动服务时添加处理自定义随机数的workerId以适应集群机器部署。
1.改造雪花算法代码
package com.demo.core.config; import cn.hutool.core.util.RandomUtil; import lombok.Generated; import lombok.Setter; import lombok.SneakyThrows; import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext; import org.apache.shardingsphere.infra.algorithm.core.exception.AlgorithmExecuteException; import org.apache.shardingsphere.infra.algorithm.core.exception.AlgorithmInitializationException; import org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm; import org.apache.shardingsphere.infra.algorithm.keygen.snowflake.SnowflakeKeyGenerateAlgorithm; import org.apache.shardingsphere.infra.algorithm.keygen.snowflake.TimeService; import org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions; import org.apache.shardingsphere.infra.instance.InstanceContext; import org.apache.shardingsphere.infra.instance.InstanceContextAware; import org.apache.shardingsphere.infra.instance.workerid.WorkerIdGenerator; import java.time.Instant; import java.time.LocalDateTime; import java.time.ZoneId; import java.util.Collection; import java.util.LinkedList; import java.util.Properties; import java.util.concurrent.atomic.AtomicInteger; import java.util.concurrent.atomic.AtomicLong; import java.util.concurrent.atomic.AtomicReference; import static org.apache.shardingsphere.infra.instance.workerid.WorkerIdGenerator.WORKER_ID_KEY; /** * ShardingSphere JDBC 随机workerId雪花算法 * * @date 2024/10/06 13:00 **/ public class RandomWorkerIdSnowflakeKeyGenerateAlgorithm implements KeyGenerateAlgorithm, InstanceContextAware { public static final long EPOCH; private static final String MAX_VIBRATION_OFFSET_KEY = "max-vibration-offset"; private static final String MAX_TOLERATE_TIME_DIFFERENCE_MILLIS_KEY = "max-tolerate-time-difference-milliseconds"; private static final long SEQUENCE_BITS = 12L; private static final long WORKER_ID_BITS = 10L; private static final long SEQUENCE_MASK = (1 << SEQUENCE_BITS) - 1L; private static final long WORKER_ID_LEFT_SHIFT_BITS = SEQUENCE_BITS; private static final long TIMESTAMP_LEFT_SHIFT_BITS = WORKER_ID_LEFT_SHIFT_BITS + WORKER_ID_BITS; private static final int DEFAULT_VIBRATION_VALUE = 1; private static final int MAX_TOLERATE_TIME_DIFFERENCE_MILLIS = 10; private static final int DEFAULT_WORKER_ID = 0; @Setter private static TimeService timeService = new TimeService(); private final AtomicReference<InstanceContext> instanceContext = new AtomicReference<>(); private final AtomicInteger sequenceOffset = new AtomicInteger(-1); private final AtomicLong sequence = new AtomicLong(); private final AtomicLong lastMillis = new AtomicLong(); private Properties props; private int maxVibrationOffset; private int maxTolerateTimeDifferenceMillis; private static final String randomWorkerId = RandomUtil.randomNumbers(3); static { EPOCH = LocalDateTime.of(2016, 11, 1, 0, 0, 0).toInstant(ZoneId.systemDefault().getRules().getOffset(Instant.now())).toEpochMilli(); } @Override public void init(final Properties props) { props.setProperty(WorkerIdGenerator.WORKER_ID_KEY, randomWorkerId); this.props = props; maxVibrationOffset = getMaxVibrationOffset(props); maxTolerateTimeDifferenceMillis = getMaxTolerateTimeDifferenceMillis(props); } private int getMaxVibrationOffset(final Properties props) { int result = Integer.parseInt(props.getOrDefault(MAX_VIBRATION_OFFSET_KEY, DEFAULT_VIBRATION_VALUE).toString()); ShardingSpherePreconditions.checkState(result >= 0 && result <= SEQUENCE_MASK, () -> new AlgorithmInitializationException(this, "Illegal max vibration offset.")); return result; } private int getMaxTolerateTimeDifferenceMillis(final Properties props) { int result = Integer.parseInt(props.getOrDefault(MAX_TOLERATE_TIME_DIFFERENCE_MILLIS_KEY, MAX_TOLERATE_TIME_DIFFERENCE_MILLIS).toString()); ShardingSpherePreconditions.checkState(result >= 0, () -> new AlgorithmInitializationException(this, "Illegal max tolerate time difference milliseconds.")); return result; } @Override public void setInstanceContext(final InstanceContext instanceContext) { this.instanceContext.set(instanceContext); if (null != instanceContext) { instanceContext.generateWorkerId(props); } } @Override public Collection<Long> generateKeys(final AlgorithmSQLContext context, final int keyGenerateCount) { Collection<Long> result = new LinkedList<>(); for (int index = 0; index < keyGenerateCount; index++) { result.add(generateKey()); } return result; } private synchronized Long generateKey() { long currentMillis = timeService.getCurrentMillis(); if (waitTolerateTimeDifferenceIfNeed(currentMillis)) { currentMillis = timeService.getCurrentMillis(); } if (lastMillis.get() == currentMillis) { sequence.set(sequence.incrementAndGet() & SEQUENCE_MASK); if (0L == sequence.get()) { currentMillis = waitUntilNextTime(currentMillis); } } else { vibrateSequenceOffset(); sequence.set(sequenceOffset.get()); } lastMillis.set(currentMillis); return ((currentMillis - EPOCH) << TIMESTAMP_LEFT_SHIFT_BITS) | ((long) getWorkerId() << WORKER_ID_LEFT_SHIFT_BITS) | sequence.get(); } @SneakyThrows(InterruptedException.class) private boolean waitTolerateTimeDifferenceIfNeed(final long currentMillis) { if (lastMillis.get() <= currentMillis) { return false; } long timeDifferenceMillis = lastMillis.get() - currentMillis; ShardingSpherePreconditions.checkState(timeDifferenceMillis < maxTolerateTimeDifferenceMillis, () -> new AlgorithmExecuteException(this, "Clock is moving backwards, last time is %d milliseconds, current time is %d milliseconds.", lastMillis.get(), currentMillis)); Thread.sleep(timeDifferenceMillis); return true; } private long waitUntilNextTime(final long lastTime) { long result = timeService.getCurrentMillis(); while (result <= lastTime) { result = timeService.getCurrentMillis(); } return result; } private void vibrateSequenceOffset() { if (!sequenceOffset.compareAndSet(maxVibrationOffset, 0)) { sequenceOffset.incrementAndGet(); } } private int getWorkerId() { return null == instanceContext.get() ? DEFAULT_WORKER_ID : instanceContext.get().getWorkerId(); } @Override public String getType() { return "RANDOM_WORKER_ID_SNOWFLAKE"; } @Override public boolean isDefault() { return true; } }2.添加SPI
添加以下代码路径：
com.demo.core.config.RandomWorkerIdSnowflakeKeyGenerateAlgorithm3.修改雪花算法配置