sharding-jdbc使用案例-笔记

引入pom

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

一、单个分片键-单库多表配置

#定义数据源
spring.shardingsphere.datasource.names=fincenter00

#数据源连接配置
spring.shardingsphere.datasource.fincenter00.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.fincenter00.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.fincenter00.url=jdbc:mysql://localhost:3306/fincenter00?useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.fincenter00.username=root
spring.shardingsphere.datasource.fincenter00.password=123456
 
#定义表分片规则
spring.shardingsphere.sharding.tables.account_profile.actual-data-nodes=fincenter00.account_profile_0$->{0..9}
#指定user_id键的生成策略：SNOWFLAKE雪花算法
spring.shardingsphere.sharding.tables.account_profile.key-generator.column=user_id
spring.shardingsphere.sharding.tables.account_profile.key-generator.type=SNOWFLAKE
#指定分片键和分片策略
spring.shardingsphere.sharding.tables.account_profile.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.account_profile.table-strategy.inline.algorithm-expression=account_profile_0$->{user_id % 10}
 
#开启sql 输出日志
spring.shardingsphere.props.sql.show=true
# 由于一个实体类对应两张表，所以会产生覆盖操作，加上这个配置解决覆盖问题
spring.main.allow-bean-definition-overriding=true
 
#mybatis
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml
mybatis.type-aliases-package=com.maple.sharding.entity

二、单个分片键-多库多表配置

#定义物理数据源名称
spring.shardingsphere.datasource.names=fincenter00,fincenter01
 
#为每一个物理数据源配置连接信息
#fincenter00
spring.shardingsphere.datasource.fincenter00.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.fincenter00.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.fincenter00.url=jdbc:mysql://localhost:3306/fincenter00?useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.fincenter00.username=root
spring.shardingsphere.datasource.fincenter00.password=123456
 
#fincenter01
spring.shardingsphere.datasource.fincenter01.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.fincenter01.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.fincenter01.url=jdbc:mysql://localhost:3306/fincenter01?useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.fincenter01.username=root
spring.shardingsphere.datasource.fincenter01.password=123456

#定义库、表分片规则
spring.shardingsphere.sharding.tables.account_profile.actual-data-nodes=fincenter0$->{0..1}.account_profile_0$->{0..9}

#指定user_id键的生成策略：SNOWFLAKE雪花算法
spring.shardingsphere.sharding.tables.account_profile.key-generator.column=user_id
spring.shardingsphere.sharding.tables.account_profile.key-generator.type=SNOWFLAKE

#指定分片键和分库算法
spring.shardingsphere.sharding.tables.account_profile.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.account_profile.table-strategy.inline.algorithm-expression=account_profile_0$->{user_id % 10}
#指定分片键和分表算法
spring.shardingsphere.sharding.tables.account_profile.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.account_profile.database-strategy.inline.algorithm-expression=fincenter0$->{user_id % 2}
 

#开启sql 输出日志
spring.shardingsphere.props.sql.show=true
# 由于一个实体类对应两张表，所以会产生覆盖操作，加上这个配置解决覆盖问题
spring.main.allow-bean-definition-overriding=true
 
#mybatis
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml
mybatis.type-aliases-package=com.maple.sharding.entity

三、复合分片-多库多表的配置

复合分片策略有2个维度：数据源分片策略（DatabaseShardingStrategy）、表分片策略（TableShardingStrategy）

开发自定义复合分库策略
现在我们定义2个分片键，user_id、order_id，user_id作为分库建、order_id作为分表建。

#订单表多分片键策略配置
sharding.jdbc.config.sharding.tables.t_new_order.actualDataNodes=ds$->{0..3}.t_new_order_000$->{0..1}
#指定分库分片键
sharding.jdbc.config.sharding.tables.t_new_order.databaseStrategy.complex.shardingColumns=user_id,order_id
#指定自定义分库策略类
sharding.jdbc.config.sharding.tables.t_new_order.databaseStrategy.complex.algorithmClassName=
com.snowalker.shardingjdbc.snowalker.demo.complex.sharding.strategy.SnoWalkerComplexShardingDB
#指定分表分片键
sharding.jdbc.config.sharding.tables.t_new_order.tableStrategy.complex.shardingColumns=user_id,order_id
#指定自定义分表策略类
sharding.jdbc.config.sharding.tables.t_new_order.tableStrategy.complex.algorithmClassName=
com.snowalker.shardingjdbc.snowalker.demo.complex.sharding.strategy.SnoWalkerComplexShardingTB

定义自定义复合分库策略实现类：ComplexKeysShardingAlgorithm.java，重写doSharding该方法最终返回具体库名
开发自定义复合分表策略实现类：ComplexKeysShardingAlgorithm.java 重写doSharding该方法最终返回具体表名

注意：获取具体要路由的库名和具体要路由的表名，是通过配置文件中配置的分库策略和分表策略指定的类来实现的

本文转载：https://blog.csdn.net/qq_33101675/article/details/112117938
本文转载：https://hongchenkezhan.blog.csdn.net/article/details/112393833
本文转载：https://zhuanlan.zhihu.com/p/61137168?from=singlemessage