Sharding JDBC 遇到的问题


一. 升级版本有坑

由于开发环境用的组合是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/276935.html

(0)
上一篇 2022年7月25日
下一篇 2022年7月25日

相关推荐

发表回复

登录后才能评论