一. 升级版本有坑
由于开发环境用的组合是shardJDBC 3.1.0 + Druid 1.2.6 + JPA 2.1.13, mysql DB, 详细版本如下，本来想着升级一下ShardingJDBC到5.X最新版本，却遇到各种不兼容问题，退回到4.X也不能解决版本问题，无奈之下还是保留以下版本组合，另外，升级到5.X之后，保含的JAR可以用巨量来形容，因为项目只用到ShardJDBC的分表分库功能，所以JAR包需要一个个
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web:2.1.3.RELEASE'
providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
testImplementation 'org.springframework.boot:spring-boot-starter-test:2.1.3.RELEASE'// mysql驱动
implementation 'mysql:mysql-connector-java:8.0.21'
implementation 'org.springframework.boot:spring-boot-starter-data-jpa:2.1.13.RELEASE'
implementation 'com.alibaba:druid-spring-boot-starter:1.2.6'
//数据库与读写分离
implementation ('io.shardingsphere:sharding-jdbc-spring-boot-starter:3.1.0')
implementation 'io.shardingsphere:sharding-jdbc-spring-namespace:3.1.0'
}
二. 主写从读+ 不分库，只分表，YAML配置
server:
port: 8080
servlet:
context-path: druid
spring:
application:
name: druid
main:
allow-bean-definition-overriding: true
jpa:
show-sql: true
properties:
hibernate:
temp:
use_jdbc_metadata_defaults: false
format_sql: true
naming:
# implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#data source
sharding:
jdbc:
dataSource:
names: db-master,db-slave1
# 配置主库
db-master:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/world?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: ivy
password: password
driver-class-name: com.mysql.cj.jdbc.Driver
db-slave1: # 配置第一个从库
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/world?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
config:
masterslave: # 配置读写分离
load-balance-algorithm-type: round-robin # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round_robin 轮询
name: db1s2
master-data-source-name: db-master
slave-data-source-names: db-slave1
shardingRule:
tables:
t_order:
actualDataNodes: db-master.t_order${0..1}
#databaseStrategy:
# inline:
# shardingColumn: user_id
# algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
#keyGenerator:
# type: SNOWFLAKE
# column: order_id
bindingTables:
- t_order
broadcastTables:
- t_config
defaultDataSourceName: master
defaultTableStrategy:
none:
defaultKeyGenerator:
type: SNOWFLAKE
#column: order_id
props:
sql:
show: true # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
server:
port: 8080
servlet:
context-path: druid
spring:
application:
name: druid
main:
allow-bean-definition-overriding: true
jpa:
show-sql: true
properties:
hibernate:
temp:
use_jdbc_metadata_defaults: false
format_sql: true
naming:
# implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#data source
sharding:
jdbc:
dataSource:
names: db-master,db-slave1
# 配置主库
db-master:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/world?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: ivy
password: password
driver-class-name: com.mysql.cj.jdbc.Driver
db-slave1: # 配置第一个从库
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/world?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
config:
masterslave: # 配置读写分离
load-balance-algorithm-type: round-robin # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round_robin 轮询
sharing:
master-slave-rules:
mysql1master2slave:
master-data-source-name: db-master
slave-data-source-names: db-slave1
tables:
t_order:
actualDataNodes: mysql1master2slave.t_order${0..1}
#databaseStrategy:
# inline:
# shardingColumn: user_id
# algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${id % 2}
bindingTables:
- t_order
broadcastTables:
- t_config
props:
sql:
show: true # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
三. 当Entity 中的id 是String类型，而YML中配置的分表键为id%2, 会报下面的错误
algorithmExpression: t_order${id % 2}
@Table(name = "t_order")
public class OrderEntity {
@Id
private String id;
private String name;
public OrderEntity() {
}}
解决方案：
1. 将OrderEntity 中的id改为int, 这样Groovy 表达就可以正确执行mod操作
2.
groovy.lang.MissingMethodException: No signature of method: java.lang.String.mod() is applicable for argument types: (java.lang.Integer) values: [2] Possible solutions: drop(int), any(), find(), use([Ljava.lang.Object;), is(java.lang.Object), find(java.util.regex.Pattern) at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:58) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.PojoMetaClassSite.call(PojoMetaClassSite.java:49) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125) ~[groovy-2.4.5-indy.jar:2.4.5] at Script2$_run_closure1.doCall(Script2.groovy:1) ~[na:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_191-1-redhat] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_191-1-redhat] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_191-1-redhat] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_191-1-redhat] at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:93) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:294) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1019) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.Closure.call(Closure.java:426) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.Closure.call(Closure.java:420) ~[groovy-2.4.5-indy.jar:2.4.5] at io.shardingsphere.core.routing.strategy.inline.InlineShardingStrategy.execute(InlineShardingStrategy.java:86) ~[sharding-core-3.1.0.jar:na]
四
sharding-jdbc 分片策略
分片策略
包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。
标准分片策略
对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。
复合分片策略
对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。
行表达式分片策略
对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。
Hint分片策略
对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。
不分片策略
对应NoneShardingStrategy。不分片的策略。
原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/276935.html