springboot3.0+shardingsphere5.2 最简单的分库分表

#先看表结构
两个数据库test1,test2 每个库有4张sys_user表分别命名sys_user_0-4

#maven依赖

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.1</version>
        </dependency>
        
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.5</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
            <scope>runtime</scope>
        </dependency>
        
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>29.0-jre</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

yaml配置

mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  type-aliases-package: com.example.domain.SysUser
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

server:
  port: 9002
spring:
  # 分库分表配置
  shardingsphere:
    datasource:
      # 默认数据源
      sharding:
        default-data-source-name: db_0
      names: db_1,db_0
      db_0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/test1
        username: root
        password: 123456.ljj
      db_1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/test2
        username: root
        password: 123456.ljj
    rules:
      sharding:
        tables:
          # sys_user
          sys_user:
            # 这里的db_xxx是 数据源名称，而不是实际数据库名称哦
            actual-data-nodes: db_${0..1}.sys_user_${0..3}
            # sys_user 库路由
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database_inline
            # sys_user 表路由
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table_inline
        sharding-algorithms:
          # sys_user库路由算法
          database_inline:
            type: INLINE
            props:
              # 有几个数据库就%多少              
              algorithm-expression: db_${user_id % 2}
          # sys_user表路由算法
          table_inline:
            type: INLINE
            props:
              # 有几张表就%多少
              algorithm-expression: sys_user_${user_id % 3}
    props:
      sql-show: true
      sql-comment-parse-enabled: true

提示

使用mybatis-plus生成以下crud各种类就行
值得注意的是，插入前设置好主键id 否则会报：java.sql.SQLException: Unknown exception: Insert statement does not support sharding table routing to multiple data nodes.
原因是可能是需要根据id分配路由到分片表吧

最后测试

POST http://localhost:9002/user
Content-Type: application/x-www-form-urlencoded

###
GET http://localhost:9002/user/2/10

###
GET http://localhost:9002/user/find/500

package com.example.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.domain.SysUser;
import com.example.mapper.SysUserMapper;
import com.example.service.SysUserService;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * description:
 * </p>
 * @date 25/05/2024
 */
@RestController
@RequestMapping("/user")
public class SysUserController {

    @Autowired
    SysUserMapper sysUserMapper;


    @PostMapping
    public void insert(SysUser sysUser) {
        SysUser build = SysUser.builder()
                .nickName("xxxx")
                .userName("xxxx")
                .password("123456")
                .email("")
                .phonenumber("123456")
                .sex("1")
                .status("0")
                .userType("1")
                .delFlag("0")
                .createBy(1L)
                .createTime(new Date())
                .updateBy(1L)
                .updateTime(new Date())
                .deptId(1L)
                .tenantId("1")
                .remark("remark")
                // 500-1000 之间的随机数
                .userId((long) (Math.random() * 500 + 500))
                .build();
        sysUserMapper.insert(build);
    }

    @GetMapping("/{size}/{page}")
    public Page<SysUser> select(@PathVariable Long page, @PathVariable Long size) {
        return sysUserMapper.selectPage(new Page<>(page, size), null);
    }

    @GetMapping("/find/{userId}")
    public SysUser find(@PathVariable Long userId) {
        return sysUserMapper.selectById(userId);
    }
}