ShardingSphere-jdbc 5.5.0 spring boot 基础配置环境准备版本数据库说明集群配置配置文件Maven依赖spring boot配置shardingsphere-jdbc配置自定义配置1SM4加解密存储数据完整的基础配置其他雪花算法自定义worker.id环境准备版本spring boot 2.7.17shardingsphere-jdbc 5.5.0druid 1.2.23数据库说明本示例数据库为单机多库schema的架构以一主一从作为集群演示转为一主多从数据库集群时可自定义修改配置。集群一主一从逻辑主库ds_basic、ds0000、ds0001ds_basic为数据简单、量少的元数据库逻辑从库ds0000_slave、ds0001_slave配置配置文件Maven依赖dependency groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-jdbc/artifactId version5.5.0/version exclusions exclusion groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-test-util/artifactId /exclusion /exclusions /dependency dependency groupIdorg.yaml/groupId artifactIdsnakeyaml/artifactId version2.2/version /dependencyspring boot配置application.ymlspring: 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配置自定义配置1SM4加解密存储数据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*/EqualsAndHashCodepublic final class SM4EncryptAlgorithm implements EncryptAlgorithm {static {Security.addProvider(new BouncyCastleProvider());}Getterprivate 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;Overridepublic 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;}Overridepublic 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);}Overridepublic 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”);Optionalbyte[] 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);}Overridepublic String getType() {return “SM4”;}}sm4算法yaml配置encryptors:sm4_encryptor:type: SM4props:sm4-key: 86C63180C2806ED1F43A859DE501215Csm4-mode: ECBsm4-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?characterEncodingutf-8allowPublicKeyRetrievaltrueuseSSLfalse username: root password: Ph9ep971fm14nYaZsLl9LYMCqX9uJSozYRNgP2VVSj/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: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7Vtqv70cG3y6T3bmDcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ ds0000: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/demo_0000?characterEncodingutf-8allowPublicKeyRetrievaltrueuseSSLfalse username: root password: Ph9ep971fm14nYaZsLl9LYMCqX9uJSozYRNgP2VVSj/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: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7Vtqv70cG3y6T3bmDcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ ds0001: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/demo_0001?characterEncodingutf-8allowPublicKeyRetrievaltrueuseSSLfalse username: root password: Ph9ep971fm14nYaZsLl9LYMCqX9uJSozYRNgP2VVSj/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: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7Vtqv70cG3y6T3bmDcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ ds0000_slave: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.1.88:3306/demo_0000?characterEncodingutf-8allowPublicKeyRetrievaltrueuseSSLfalse username: root password: Ph9ep971fm14nYaZsLl9LYMCqX9uJSozYRNgP2VVSj/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: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7Vtqv70cG3y6T3bmDcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ ds0001_slave: dataSourceClassName: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.1.88:3306/demo_0001?characterEncodingutf-8allowPublicKeyRetrievaltrueuseSSLfalse username: root password: Ph9ep971fm14nYaZsLl9LYMCqX9uJSozYRNgP2VVSj/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: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7Vtqv70cG3y6T3bmDcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ 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 AtomicReferenceInstanceContext 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 CollectionLong generateKeys(final AlgorithmSQLContext context, final int keyGenerateCount) { CollectionLong 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.修改雪花算法配置