先看表结构
两个数据库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);
}
}
标签:springboot3.0,分库,shardingsphere5.2,db,sys,user,sharding,import,com
From: https://www.cnblogs.com/MnysLjj/p/18212833