SpringBoot中使用Sharding-JDBC实战（实战+版本兼容+Bug解决）

最新推荐文章于 2025-04-15 11:24:15 发布

m0_74823490

最新推荐文章于 2025-04-15 11:24:15 发布

阅读量858

点赞数 10

分类专栏：面试学习路线阿里巴巴文章标签： spring boot bug 后端

本文链接：https://blog.youkuaiyun.com/m0_74823490/article/details/145600031

版权

一、实战

1、引入 ShardingSphere-JDBC 的依赖

https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc/5.5.0[这里是图片001]https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc/5.5.0

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc -->
<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>

下面是我项目的一些版本信息：

SpringBoot 3.2.4

shardingsphere 5.5.0

2、修改yaml配置文件

application.yaml：

spring:
  profiles:
    active: dev
  main:
    allow-circular-references: true

  datasource:
    # ShardingSphere 对 Driver 自定义，实现分库分表等隐藏逻辑
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    # ShardingSphere 配置文件路径
    url: jdbc:shardingsphere:classpath:shardingsphere-config.yaml
#    driver-class-name: ${quick.datasource.driver-class-name}
#    url: jdbc:mysql://${quick.datasource.host}:${quick.datasource.port}/${quick.datasource.database}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
#    username: ${quick.datasource.username}
#    password: ${quick.datasource.password}
#    hikari:
#      maximum-pool-size: 50

shardingsphere-config.yaml

名字必须相同，前面的 jdbc:shardingsphere:classpath: 是固定写法。

大致的配置结构如上。

（1）使用自带分片算法

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/quick_pickup?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
    username: root
    password: 123456

# 规则配置
rules:
  - !SINGLE
    tables:
      # 加载全部单表
      - "ds_0.*"
  - !SHARDING
    tables:
      integral_package_order:
        actualDataNodes: ds_0.integral_package_order_${0..15}  # 配置数据表分片规则
        tableStrategy:
          standard:
            shardingColumn: user_id  # 使用 user_id 作为分片键
            shardingAlgorithmName: integral_package_order_table_hash_mod  # 使用自定义的分片算法
    shardingAlgorithms:
      integral_package_order_table_hash_mod:
#        type: HASH_MOD  # 使用 HASH_MOD 算法
#        props:
#          sharding-count: 16  # 设置分片数量为 16
        type: INLINE
        props:
            algorithm-expression: integral_package_order_${user_id % 16}

props:
  sql-show: true  # 控制台打印 SQL 日志，便于调试

# 单机模式 也有集群模式 此处本机选择单机即可 这个必须添加! 不然要报错
mode:
  type: Standalone

其中注意必须加上：

就是两点可能错误：

1.xxx表无法加载

Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view ‘store’ does not exist. at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:99) ~[mybatis-3.5.14.jar:3.5.14] at org.apache.ibatis.executor.statement.Ro

最低0.47元/天解锁文章