Table Architecture

Two datbaases (db_shard_0 and db_shard_1) each contain four user_info tables: user_info_0 through user_info_3.

Dependencies

<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 Configuration

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

server:
  port: 9002

spring:
  shardingsphere:
    datasource:
      names: ds_0,ds_1
      ds_0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/db_shard_0
        username: root
        password: 123456.ljj
      ds_1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/db_shard_1
        username: root
        password: 123456.ljj
    rules:
      sharding:
        tables:
          user_info:
            actual-data-nodes: ds_${0..1}.user_info_${0..3}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db_sharding
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table_sharding
        default-data-source-name: ds_0
        sharding-algorithms:
          db_sharding:
            type: INLINE
            props:
              algorithm-expression: ds_${user_id % 2}
          table_sharding:
            type: INLINE
            props:
              algorithm-expression: user_info_${user_id % 4}
    props:
      sql-show: true
      sql-comment-parse-enabled: true

Sharding Key Must Be Provided on Insert

When inesrting data, the sharding column (user_id) must have a concrete value. If you rely on database auto‑increment without setting the sharding key, ShardingSphere cannot determine the target node and throws an Unknown exception: Insert statement does not support sharding table routing to multiple data nodes. Always assign a unique identifier before calling the insert method.

Controller Example

@RestController
@RequestMapping("/userinfo")
@RequiredArgsConstructor
public class UserInfoController {

    private final UserInfoMapper userInfoMapper;

    @PostMapping
    public void addUser() {
        long randomId = (long) (Math.random() * 500 + 500);
        UserInfo user = UserInfo.builder()
                .userId(randomId)
                .nickname("demo")
                .username("demo_user")
                .password("secret")
                .email("demo@example.com")
                .phone("123456789")
                .gender("1")
                .status("0")
                .type("1")
                .deleted("0")
                .createdBy(1L)
                .createdAt(new Date())
                .updatedBy(1L)
                .updatedAt(new Date())
                .deptId(1L)
                .tenantId("1")
                .remark("test")
                .build();
        userInfoMapper.insert(user);
    }

    @GetMapping("/page/{current}/{size}")
    public Page<UserInfo> list(@PathVariable long current, @PathVariable long size) {
        return userInfoMapper.selectPage(new Page<>(current, size), null);
    }

    @GetMapping("/{userId}")
    public UserInfo detail(@PathVariable long userId) {
        return userInfoMapper.selectById(userId);
    }
}