526互联

shardingsphere-jdbc

发布时间 2023-04-03 10:57:51作者: edclol

shardingsphere-jdbc

5.1.1版本

<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>


#shardingsphere
#spring.shardingsphere.mode.type=memory
#spring.shardingsphere.props.sql-show=true
#spring.shardingsphere.enabled=true
#spring.shardingsphere.datasource.names=ds0
#spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.shardingsphere.datasource.ds0.url=jdbc:mysql://${mysql.host}/atp_dhs?useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
#spring.shardingsphere.datasource.ds0.username=${mysql.username}
#spring.shardingsphere.datasource.ds0.password=${mysql.password}
#spring.shardingsphere.datasource.ds0.initial-size=10
#spring.shardingsphere.datasource.ds0.min-idle=10
#spring.shardingsphere.datasource.ds0.maxActive=1000
#spring.shardingsphere.datasource.ds0.maxWait=60000
#spring.shardingsphere.datasource.ds0.timeBetweenEvictionRunsMillis=60000
#spring.shardingsphere.datasource.ds0.minEvictableIdleTimeMillis=300000
#spring.shardingsphere.datasource.ds0.validationQuery=SELECT 1
#spring.shardingsphere.datasource.ds0.testWhileIdle=true
#spring.shardingsphere.datasource.ds0.testOnBorrow=false
#spring.shardingsphere.datasource.ds0.testOnReturn=false
#spring.shardingsphere.datasource.ds0.poolPreparedStatements=true
#spring.shardingsphere.datasource.ds0.maxPoolPreparedStatementPerConnectionSize=20
#spring.shardingsphere.datasource.ds0.filters=stat,wall,log4j2
##spring.shardingsphere.datasource.ds0.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
#
#
#spring.shardingsphere.rules.sharding.default-data-source-name=ds0
#spring.shardingsphere.rules.sharding.props.sql-show=true
#spring.shardingsphere.rules.sharding.binding-tables[0]=dhs_annotation_gt,device_alarm_record
#spring.shardingsphere.rules.sharding.tables.dhs_annotation_gt.actual-data-nodes=ds0.dhs_annotation_gt_$->{0..1}
#spring.shardingsphere.rules.sharding.tables.dhs_annotation_gt.table-strategy.standard.sharding-column=image_id
#spring.shardingsphere.rules.sharding.tables.dhs_annotation_gt.table-strategy.standard.sharding-algorithm-name=sharding-by-image_id
#spring.shardingsphere.rules.sharding.sharding-algorithms.sharding-by-image_id.type=INLINE
#spring.shardingsphere.rules.sharding.sharding-algorithms.sharding-by-image_id.props.algorithm-expression=dhs_annotation_gt_$->{image_id % 2}
#
#spring.shardingsphere.rules.sharding.tables.device_alarm_record.actual-data-nodes=ds0.device_alarm_record20$->{21..99}$->{1..12}
#spring.shardingsphere.rules.sharding.tables.device_alarm_record.table-strategy.standard.sharding-column=pass_month
#spring.shardingsphere.rules.sharding.tables.device_alarm_record.table-strategy.standard.sharding-algorithm-name=sharding-alarm-by-month

5.3.1版本

         <shardingsphere.version>5.3.1</shardingsphere.version>
        <snakeyaml.version>1.33</snakeyaml.version>
       
       <!--ShardingSphere-jdbc start-->
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>${snakeyaml.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
        <!--ShardingSphere-jdbc end-->

# 配置 DataSource Driver
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
spring.datasource.url=jdbc:shardingsphere:classpath:atp-dhs-shardingsphere.yaml

mode:
  type: Standalone

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://172.16.XX.XX:3306/atp_dhs?useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
    username: root
    password: RFErCVzNafwMySOb
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://172.16.XX.XX:3306/atp_dhs?useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
    username: root
    password: RFErCVzNafwMySOb

rules:
  - !SHARDING
    tables:
      dhs_annotation_gt:
        actualDataNodes: ds_${0..1}.dhs_annotation_gt_${0..1}
        tableStrategy:
          standard:
            shardingColumn: image_id
            shardingAlgorithmName: dhs_annotation_gt_inline
        keyGenerateStrategy:
          column: image_id
          keyGeneratorName: snowflake
      dhs_annotation_pred:
        actualDataNodes: ds_${0..1}.dhs_annotation_pred_${0..1}
        tableStrategy:
          standard:
            shardingColumn: image_id
            shardingAlgorithmName: dhs_annotation_pred_inline
        keyGenerateStrategy:
          column: image_id
          keyGeneratorName: snowflake
      dhs_origin_annotation_pred:
        actualDataNodes: ds_${0..1}.dhs_origin_annotation_pred_${0..1}
        tableStrategy:
          standard:
            shardingColumn: image_id
            shardingAlgorithmName: dhs_origin_annotation_pred_inline
        keyGenerateStrategy:
          column: image_id
          keyGeneratorName: snowflake

    defaultShardingColumn: image_id
    bindingTables:
      - dhs_annotation_gt,dhs_annotation_pred,dhs_origin_annotation_pred
    defaultDatabaseStrategy:
      standard:
        shardingColumn: image_id
        shardingAlgorithmName: database_inline
    defaultTableStrategy:
      none:

    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_0
      dhs_annotation_gt_inline:
        type: HASH_MOD
        props:
          sharding-count: 2
      dhs_annotation_pred_inline:
        type: HASH_MOD
        props:
          sharding-count: 2
      dhs_origin_annotation_pred_inline:
        type: HASH_MOD
        props:
          sharding-count: 2

    keyGenerators:
      snowflake:
        type: SNOWFLAKE


props:
  sql-show: true