分库分表(Springboot3)

springboot3.8.3+版本请使用jeecg-boot-starter-shardingsphere 3.8.3.1 版本

底层shardingsphere版本为5.5.0

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.0</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-test-util</artifactId>
        </exclusion>
    </exclusions>
</dependency>

本文旨在：通过jeecg-system-cloud-start项目集成分库分表例子，进行讲解分库分表用法

准备环境

数据表：sys_log0（日志分表1），sys_log1（日志分表2）拷贝复制系统sys_log表即可
数据库: jeecg-boot2(拷贝jeecg-boot即可,分库分表使用)

示例代码

示例代码在jeecg-cloud-test-shardingsphere中编写，该示例场景用于插入日志时对日志进行分表存放，分表规则是根据日志类型进行取余计算余数为0的存放到sys_log0表中，余数为1的存到sys_log1表中

单库分表

在nacos中新建sharding.yaml分表配置文件，如下所示

databaseName: sharding-db

dataSources:
  db_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    password: root
    username: root

rules:
  - !SHARDING
    tables:
      sys_log:
        actualDataNodes: db_0.sys_log$->{0..1}
        databaseStrategy:
          none:
        tableStrategy:
          standard:
            shardingColumn: log_type
            shardingAlgorithmName: user_inline
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          worker-id: 123
    shardingAlgorithms:
      user_inline:
        type: INLINE
        props:
          algorithm-expression: sys_log$->{log_type % 2}

props:
  sql-show: true

修改nacos中的jeecg-dev.yml项目，将配置sharding.yaml加到动态数据源中

        sharding-db:
         driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
         url: jdbc:shardingsphere:nacos:sharding.yaml?serverAddr=${spring.cloud.nacos.config.server-addr}&namespace=${spring.cloud.nacos.config.namespace}&group=${spring.cloud.nacos.config.group}

提醒：引入的配置文件名字要和nacos创建的文件保持一致。另外注意尽量nacos中的配置尽量不要有中文。

3、引入分库分表测试模块 jeecg-cloud-test-shardingsphere

<!-- 分库分表例子-->
<dependency>
   <groupId>org.jeecgframework.boot3</groupId>
   <artifactId>jeecg-cloud-test-shardingsphere</artifactId>
   <version>${jeecgboot.version}</version>
</dependency>
<!-- shardingsphere对nacos支持 -->
<dependency>
    <groupId>org.jeecgframework.boot3</groupId>
    <artifactId>jeecg-boot-starter-shardingsphere-nacos</artifactId>
    <version>3.8.3.1</version>
</dependency>

4.启动成功后浏览器输入http://localhost:9999 打开接口文档如下图

如下代码批量插入10条数据，根据分配规则logType未奇数的会插入sys_log1表中，logType未偶数的会插入sys_log0表中

测试结果如下

分库分表

在nacos中新建sharding-multi.yaml 分库配置文件

# !!!数据源名称要和动态数据源中配置的名称一致
databaseName: sharding-db

# 具体参看官网文档说明
dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    password: root
    username: root
  ds1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    password: root
    username: root

rules:
  - !SHARDING
    bindingTables:
      - sys_log
    tables:
      sys_log:
        actualDataNodes: ds$->{0..1}.sys_log$->{0..1}
        databaseStrategy:
          standard:
            shardingColumn: operate_type
            shardingAlgorithmName: database-inline
        tableStrategy:
          standard:
            shardingColumn: log_type
            shardingAlgorithmName: table-classbased
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          worker-id: 123
    
    shardingAlgorithms:
      database-inline:
        type: INLINE
        props:
          algorithm-expression: ds$->{operate_type % 2}
      table-classbased:
        type: CLASS_BASED
        props:
          strategy: standard
          algorithmClassName: org.jeecg.modules.test.sharding.algorithm.StandardModTableShardAlgorithm

  - !READWRITE_SPLITTING
    dataSources:
      prds:
        writeDataSourceName: ds0
        readDataSourceNames:
          - ds1
        loadBalancerName: round-robin
    loadBalancers:
      round-robin:
        type: ROUND_ROBIN

props:
  sql-show: true

修改jeecg-dev.yaml配置，引入新增的nacos配置 sharding-multi.yaml

        sharding-db:
         driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
         url: jdbc:shardingsphere:nacos:sharding-multi.yaml?serverAddr=${spring.cloud.nacos.config.server-addr}&namespace=${spring.cloud.nacos.config.namespace}&group=${spring.cloud.nacos.config.group}