当前位置： 首页 > java >正文

【Sharding-JDBC】​Spring/Spring Boot 集成 Sharding-JDBC，分表策略与 API、YAML 配置实践​

java 2025/9/5 11:03:27

在这里插入图片描述

文章目录

  • 环境准备
  • Spring框架
    • Sharding-JDBC 4.x版本api实现
    • Sharding-JDBC 5.4.x版本yaml实现
  • Springboot框架
    • Sharding-JDBC 5.4.x版本yaml实现
  • 分库、加密、读写分离基于yaml的配置示例

更多相关内容可查看

需求：按月分区，按年分表，找不到对应年份表走备份表,动态从linux获取账户密码用于创建数据库链接

备份表：table
已经提前建表语句创建好未来年份的表:table_2026,table_2027

环境准备

maven
此依赖会引入很多有漏洞的包，如果有漏洞扫描的情况下，请替换相关包的其他版本或加白名单

 <dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>${sharding-jdbc.version}</version></dependency>

Spring框架

Sharding-JDBC 4.x版本api实现

注意：4.x版本，在用api实现，如果想要非分片表走相同的数据源的情况下
需要用setDefaultdatasource赋默认数据源，在5.x版本已经移除了该方法，所以仅支持低版本实现，如果不加的话shading会无法路由其他表，会报表不存在的情况

datasource

    @Bean(name = "dataSource")public DataSource shardingDataSource() throws SQLException, HKEException {DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource();// 1. 配置数据源Map<String, DataSource> dataSourceMap = new HashMap<>();dataSourceMap.put("ds", dataSource());// 2. 配置分片规则ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();//        shardingRuleConfig.setDefaultDatabaseShardingStrategy(new NoneShardingStrategyConfiguration());// 配置table表的分片规则ShardingTableRuleConfiguration signInfoRule = new ShardingTableRuleConfiguration("table","ds.table_2025"  // 包含备份表和未来几年的表);signInfoRule.setTableShardingStrategy(new StandardShardingStrategyConfiguration("operate_time",  // 分片字段"year-sharding-algorithm"  // 自定义分片算法));
//        signInfoRule.setKeyGenerateStrategy(
//                new KeyGenerateStrategyConfiguration("row_id", "snowflake")
//        );// 添加其他表的规则（tablexxx）// 添加表规则到分片配置shardingRuleConfig.getTables().add(signInfoRule);// 3. 配置分片算法shardingRuleConfig.getShardingAlgorithms().put("year-sharding-algorithm",new AlgorithmConfiguration("CLASS_BASED", new Properties() {{setProperty("strategy", "STANDARD");setProperty("algorithmClassName", "com.package.YearShardingAlgorithm");}}));// 4. 配置主键生成器shardingRuleConfig.getKeyGenerators().put("snowflake",new AlgorithmConfiguration("SNOWFLAKE", new Properties()));// 5. 创建ShardingSphere数据源Properties props = new Properties();props.setProperty("sql-show", "true");  // 显示SQL日志return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap,Collections.singleton(shardingRuleConfig),props);}

分表算法实现

注意：implements 哪个类取决于你需要用那个对应的算法，有stand，complex等

package cfca.hke.privatization.sharding;import cfca.hke.privatization.common.logging.LoggerManager;
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.LocalDateTime;
import java.util.Collection;
import java.util.Collections;public class YearShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {// 获取逻辑表名String logicTableName = shardingValue.getLogicTableName();Object value = shardingValue.getValue();String year = value.toString();//年份格式会有多种 所以直接截取这里if (year.length() > 4) {year = year.substring(0, 4);}//25年的存到备份表logicTableNameif (!"2025".equals(year)){logicTableName = logicTableName + "_" + year;}// 检查表是否存在if (availableTargetNames.contains(logicTableName)) {return logicTableName;} else {LoggerManager.exceptionLogger.error("The table does not exist,please check HKEPrivateConfig/sharding.yaml, tableName: {}", logicTableName);return logicTableName;}}@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<LocalDateTime> rangeShardingValue) {return Collections.emptyList();}
}

Sharding-JDBC 5.4.x版本yaml实现

此版本有漏洞，当你配置不分表的表时候，配置的表明会区分大小写，此漏洞在5.4.1版本已经兼容了，所以可以尽量用最新版

注意：ds.*不会影响你加分片规则的表，优先级是如果有分片规则则认为是需分片的表，这里配单表不配ds.*的时候，必须升级到5.4.1版本，或未来出现新版之后，尽量使用高版本

mode:
#设置为​​单机模式​​。此模式下，配置信息存储在本地，适用于开发测试或小型应用，无需额外的协调服务（如 ZooKeeper）type: Standalonerepository:#指定使用内嵌的 JDBC 数据库来存储 ShardingSphere 的元数据（如逻辑库、表结构等）type: JDBC
# 数据库标识 随便写
databaseName: zhongxin
# 数据库配置，连接池配置
dataSources:ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.xx.xx:3306/zhongxin_new?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000&connectTimeout=60000username: rootpassword:connectionTimeout: 50000minimumIdle: 5maximumPoolSize: 10idleTimeout: 600000maxLifetime: 1800000connectionTestQuery: SELECT 1
# 5.x版本的配法
rules:
#分片规则标签
- !SHARDING
#需要进行分片的​​逻辑表​​及其规则tables:table:#分片的表名actualDataNodes: ds.table,ds.table_$->{2026..2028}tableStrategy:standard:#分片字段shardingColumn: operate_time#分片算法shardingAlgorithmName: infoAlgorithmtable1:actualDataNodes: ds.table1,ds.table1_$->{2026..2028}tableStrategy:standard:shardingColumn: operate_timeshardingAlgorithmName: infoAlgorithmtable2:actualDataNodes: ds.table2,ds.table2_$->{2026..2028}tableStrategy:standard:shardingColumn: operate_timeshardingAlgorithmName: infoAlgorithmshardingAlgorithms:infoAlgorithm:#分片算法类型为​​基于自定义类type: CLASS_BASEDprops:#指定自定义算法实现的​​策略类型​​为 STANDARD（标准）strategy: STANDARD#自定义分片算法的完整类名algorithmClassName: com.package.YearShardingAlgorithmkeyGenerators:snowflake:type: SNOWFLAKE
# 不分区的表
- !SINGLEtables:- ds.*props:
#​​是否在日志中打印 SQL​​sql-show: true

动态修改账号密码
注意：这里要用sharding提供的解析yaml的方法去解析，如果你是低版本可以自己去写流读取，高版本在读取sharding特殊的配置会报错，在解析完后也要用sharding提供的方法转回string或者bytes

    @Bean(name = "dataSource")public DataSource shardingDataSource() throws Exception {//获取用户名密码String username = credentials[0];String password = credentials[1];File yamlFile = new File("./xxx/sharding.yaml");YamlJDBCConfiguration rootConfig = YamlEngine.unmarshal(yamlFile, YamlJDBCConfiguration.class);Map<String, Object> dsMap = rootConfig.getDataSources().get("ds");dsMap.put("username",username);dsMap.put("password",password);String marshal = YamlEngine.marshal(rootConfig);DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(marshal.getBytes());printDBInfo(username,password);return dataSource;}

分表算法实现

注意：implements 哪个类取决于你需要用那个对应的算法，有stand，complex等

package cfca.hke.privatization.sharding;import cfca.hke.privatization.common.logging.LoggerManager;
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.LocalDateTime;
import java.util.Collection;
import java.util.Collections;public class YearShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {// 获取逻辑表名String logicTableName = shardingValue.getLogicTableName();Object value = shardingValue.getValue();String year = value.toString();//年份格式会有多种 所以直接截取这里if (year.length() > 4) {year = year.substring(0, 4);}//25年的存到备份表logicTableNameif (!"2025".equals(year)){logicTableName = logicTableName + "_" + year;}// 检查表是否存在if (availableTargetNames.contains(logicTableName)) {return logicTableName;} else {LoggerManager.exceptionLogger.error("The table does not exist,please check HKEPrivateConfig/sharding.yaml, tableName: {}", logicTableName);return logicTableName;}}@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<LocalDateTime> rangeShardingValue) {return Collections.emptyList();}
}

Springboot框架

Sharding-JDBC 5.4.x版本yaml实现

在springboot的application.yml文件里面，直接加入分片的配置即可,springboot会自动解析该yaml文件不需要手动读取

mode:type: Standalonerepository:type: JDBC
# 数据库标识 随便写
databaseName: zhongxin
# 数据库配置，连接池配置
dataSources:ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.xx.xx:3306/zhongxin_new?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000&connectTimeout=60000username: rootpassword:connectionTimeout: 50000minimumIdle: 5maximumPoolSize: 10idleTimeout: 600000maxLifetime: 1800000connectionTestQuery: SELECT 1
# 5.x版本的配法
rules:
- !SHARDINGtables:table:actualDataNodes: ds.table,ds.table_$->{2026..2028}tableStrategy:standard:shardingColumn: operate_timeshardingAlgorithmName: infoAlgorithmtable1:actualDataNodes: ds.table1,ds.table1_$->{2026..2028}tableStrategy:standard:shardingColumn: operate_timeshardingAlgorithmName: infoAlgorithmtable2:actualDataNodes: ds.table2,ds.table2_$->{2026..2028}tableStrategy:standard:shardingColumn: operate_timeshardingAlgorithmName: infoAlgorithmshardingAlgorithms:infoAlgorithm:type: CLASS_BASEDprops:strategy: STANDARDalgorithmClassName: com.package.YearShardingAlgorithmkeyGenerators:snowflake:type: SNOWFLAKE
# 不分区的表
- !SINGLEtables:- ds.*props:sql-show: true

分库、加密、读写分离基于yaml的配置示例

mode:type: Standalonerepository:type: JDBC
databaseName: demo_db
dataSources:ds_basic:dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/demo_basic?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ==initialSize: 1minIdle: 1maxActive: 64maxWait: 20000validationQuery: SELECT 1 FROM DUALvalidationQueryTimeout: 30000minEvictableIdleTimeMillis: 300000maxEvictableIdleTimeMillis: 600000timeBetweenEvictionRunsMillis: 300000testOnBorrow: truetestWhileIdle: truefilters: config, stat, wallconnectProperties:connectTimeout: 5000socketTimeout: '20000'config.decrypt: 'true'config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ==ds0000:dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/demo_0000?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ==initialSize: 1minIdle: 1maxActive: 64maxWait: 20000validationQuery: SELECT 1 FROM DUALvalidationQueryTimeout: 30000minEvictableIdleTimeMillis: 300000maxEvictableIdleTimeMillis: 600000timeBetweenEvictionRunsMillis: 300000testOnBorrow: truetestWhileIdle: truefilters: config, stat, wallconnectProperties:connectTimeout: 5000socketTimeout: '20000'config.decrypt: 'true'config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ==ds0001:dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/demo_0001?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ==initialSize: 1minIdle: 1maxActive: 64maxWait: 20000validationQuery: SELECT 1 FROM DUALvalidationQueryTimeout: 30000minEvictableIdleTimeMillis: 300000maxEvictableIdleTimeMillis: 600000timeBetweenEvictionRunsMillis: 300000testOnBorrow: truetestWhileIdle: truefilters: config, stat, wallconnectProperties:connectTimeout: 5000socketTimeout: '20000'config.decrypt: 'true'config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ==ds0000_slave:dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://192.168.1.88:3306/demo_0000?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ==initialSize: 1minIdle: 1maxActive: 64maxWait: 20000validationQuery: SELECT 1 FROM DUALvalidationQueryTimeout: 30000minEvictableIdleTimeMillis: 300000maxEvictableIdleTimeMillis: 600000timeBetweenEvictionRunsMillis: 300000testOnBorrow: truetestWhileIdle: truefilters: config, stat, wallconnectProperties:connectTimeout: 5000socketTimeout: '20000'config.decrypt: 'true'config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ==ds0001_slave:dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://192.168.1.88:3306/demo_0001?characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=falseusername: rootpassword: Ph9ep971fm14nYaZsLl9LY+MCqX9uJSozYRNgP2VVSj/hbmokn5OC6kpiAA1I0okA9GiDHEo7qHUvRQYYUNZvQ==initialSize: 1minIdle: 1maxActive: 64maxWait: 20000validationQuery: SELECT 1 FROM DUALvalidationQueryTimeout: 30000minEvictableIdleTimeMillis: 300000maxEvictableIdleTimeMillis: 600000timeBetweenEvictionRunsMillis: 300000testOnBorrow: truetestWhileIdle: truefilters: config, stat, wallconnectProperties:connectTimeout: 5000socketTimeout: '20000'config.decrypt: 'true'config.decrypt.key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALZRYgsnvVKPqZTfMOWmmj6OuupFRSk7+Vtqv70cG3y6T3bm+DcQU3zOC993ozbHpmqeODtuLzURhIuXDMyTKW8CAwEAAQ==rules:
# 数据分片
- !SHARDINGtables:t_claim_case_mdtrt:actualDataNodes: ds$->{['0000','0001']}.t_claim_case_mdtrt_000$->{0..9}tableStrategy:standard:shardingColumn: transaction_noshardingAlgorithmName: t_claim_case_mdtrt_inlinekeyGenerateStrategy:column: idkeyGeneratorName: snowflaket_claim_case_info:actualDataNodes: ds$->{['0000','0001']}.t_claim_case_info_000$->{0..9}tableStrategy:standard:shardingColumn: transaction_noshardingAlgorithmName: t_claim_case_info_inlinekeyGenerateStrategy:column: idkeyGeneratorName: snowflakedefaultShardingColumn: transaction_nobindingTables:- t_claim_case_mdtrt, t_claim_case_infodefaultDatabaseStrategy:standard:shardingColumn: transaction_noshardingAlgorithmName: database_inlinedefaultTableStrategy:none:shardingAlgorithms:database_inline:type: INLINEprops:algorithm-expression: ds$->{transaction_no[-8..-5]}t_claim_case_mdtrt_inline:type: INLINEprops:algorithm-expression: t_claim_case_mdtrt_$->{transaction_no[-4..-1]}t_claim_case_info_inline:type: INLINEprops:algorithm-expression: t_claim_case_info_$->{transaction_no[-4..-1]}keyGenerators:snowflake:type: SNOWFLAKE#数据加密
- !ENCRYPTtables:t_claim_case_info:columns:appl_mobile:cipher:name: appl_mobileencryptorName: sm4_encryptoropsnId_no:cipher:name: opsnId_noencryptorName: sm4_encryptorrpter_id_no:cipher:name: rpter_id_noencryptorName: sm4_encryptorrpter_mobile:cipher:name: rpter_mobileencryptorName: sm4_encryptorencryptors:sm4_encryptor:type: SM4props:sm4-key: 86C63180C2806ED1F43A859DE501215Csm4-mode: ECBsm4-padding: PKCS5Padding
# 单表
- !SINGLEtables:- ds_basic.*# 读写分离
- !READWRITE_SPLITTINGdataSources:ds0000:writeDataSourceName: ds0000readDataSourceNames:- ds0000_slavetransactionalReadQueryStrategy: PRIMARYloadBalancerName: randomds0001:writeDataSourceName: ds0001readDataSourceNames:- ds0001_slavetransactionalReadQueryStrategy: PRIMARYloadBalancerName: randomloadBalancers:random:type: RANDOMprops:sql-show: truemax-connections-size-per-query: 5
http://www.xdnf.cn/news/20073.html

相关文章：

  • 达梦数据库-共享内存池
  • 3.3.3 钢结构工程施工
  • Kubernetes知识点（三）
  • 探究Linux系统的SSL/TLS证书机制
  • 河南萌新联赛2025第（七）场：郑州轻工业大学
  • 直接让前端请求代理到自己的本地服务器，告别CV报文到自己的API工具，解放双手
  • android View详解—自定义ViewGroup,流式布局
  • 亚洲数字能源独角兽的 “安全密码”：Parasoft为星星充电筑牢软件防线
  • MongoDB 高可用部署：Replica Set 搭建与故障转移测试
  • SpringCloud微服务基于nacos注册中心的服务发现模式及OpenFeign的使用
  • Redis在商城开发中起到什么作用？
  • 漏洞修复 Nginx TLSSSL 弱密码套件
  • 2025国赛C题保姆级教程思路分析 NIPT 的时点选择与胎儿的异常判定
  • 【完整源码+数据集+部署教程】陶瓷物品实例分割系统源码和数据集：改进yolo11-LVMB
  • 第22节：性能监控与内存管理——构建高性能3D应用
  • 3ds Max流体模拟终极指南：打造逼真液体效果，从瀑布到杯中溢出的饮料！
  • 240. 搜索二维矩阵 II
  • 2025年含金量高的经济学专业证书工科！【纯干货分享】
  • 文件系统-哈希结构文件
  • 食物分类案例优化 调整学习率和迁移学习
  • Paraverse平行云实时云渲染助力第82届威尼斯电影节XR沉浸式体验
  • 火山引擎数据智能体DataAgent总结分享
  • 小型企业MES软件开发的核心要点
  • 遥感语义分割辅导
  • PWM正相输出和PWM反相输出的各是怎样的工作原理
  • 别再和正则表达式死磕了！这套AI工具集让你的开发效率翻倍⚙️[特殊字符]
  • OPENCV复习第二期
  • 【ffmepg+ AI 】从mp3歌曲提取伴奏（纯音乐）
  • SQL常见索引失效导致慢查询情况
  • mysql集群部署（Mysql Group Replication）