1. 创建Mysql分库表
目标：两个数据库splitting0、splitting1两个数据库中分别有t_content_0和t_content_1两张表，实现按照指定规则分库分表
1.1 创建数据库
create database splitting0;
create database splitting1;
1.2 创建数据表
分别在两个数据库中创建t_content_0和t_content_1表
CREATE TABLE `t_content_0` (
`id` bigint NOT NULL,
`user_id` int DEFAULT NULL,
`content_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_content_1` (
`id` bigint NOT NULL,
`user_id` int DEFAULT NULL,
`content_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2. 创建SpringBoot项目并引入依赖
通过IDEA或官网创建SpringBoot项目，并在pom中引入shardingsphere-jdbc-core-spring-boot-starter依赖，这里引入的是5.1.1版本。
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
完整的pom文件如下
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.mrssz</groupId>
<artifactId>db-table-sharding</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>db-table-sharding</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
配置数据源以及读写库
在application.yml配置文件中配置数据源以及分库分表策略，具体配置详见官方文档ShardingSphere-JDBC官方文档
本文配置如下:
server:
port: 8080
spring:
application:
name: db-table-sharding
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
pool-name: MrsszHikariCP
connection-timeout: 30000
idle-timeout: 600000
minimum-idle: 10
maximum-pool-size: 10
auto-commit: true
shardingsphere:
mode:
overwrite: true
repository:
type: File
type: Standalone
datasource:
names: splitting0,splitting1
splitting0:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: 'jdbc:mysql://127.0.0.1:3306/splitting0?characterEncoding=utf-8&useSSL=false'
type: com.zaxxer.hikari.HikariDataSource
username: root
password: ***
splitting1:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: 'jdbc:mysql://127.0.0.1:3306/splitting1?characterEncoding=utf-8&useSSL=false'
type: com.zaxxer.hikari.HikariDataSource
username: root
password: ***
props:
sql-show: true
rules:
sharding:
tables:
t_content:
actualDataNodes: splitting$->{0..1}.t_content_$->{0..1}
database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: dts-db
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: dts-table
keyGenerateStrategy:
column: id
keyGenerateName: dts-key
shardingAlgorithms:
dts-db:
type: INLINE
props:
algorithm-expression: splitting$->{id % 2}
dts-table:
type: INLINE
props:
algorithm-expression: t_content_$->{user_id % 2}
keyGenerates:
dts-key:
type: SNOWFLAKE
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
use-generated-keys: true
type-aliases-package:
4. 实现具体业务代码
4.1 controller层
@RestController
@RequestMapping("/db")
public class DbController {
@Autowired DbService dbService;
@GetMapping("/get")
public List<ContentEntity> getContentList(@RequestParam("id") int id) {
return dbService.getContentList(id);
}
@PostMapping("/save")
public int saveContent(@RequestBody ContentEntity entity) {
return dbService.saveContent(entity);
}
}
4.2 service层
@Service
public class DbService {
@Autowired DbDao dbDao;
public List<ContentEntity> getContentList(int id) {
return dbDao.getContentList(id);
}
public int saveContent(ContentEntity entity) {
return dbDao.saveContent(entity);
}
}
4.3 dao层
@Mapper
public interface DbDao {
List<ContentEntity> getContentList(@Param("id") int id);
int saveContent(@Param("entity") ContentEntity entity);
}
4.4 mapper层
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.mrssz.dbtablesharding.dao.DbDao">
<select id="getContentList" resultType="cn.mrssz.dbtablesharding.entity.ContentEntity">
SELECT * FROM t_content;
</select>
<insert id="saveContent">
INSERT INTO t_content(id, user_id, content_id) VALUES(#{entity.id}, #{entity.userId}, #{entity.contentId});
</insert>
</mapper>
4.5 各文件位置
上诉文件在项目中位置如下图
5. 功能测试
5.1 验证新增记录
调用save接口新增记录，发现会根据配置的规则id为奇数时新增到splitting1库、id为偶数时新增到splitting0库，user_id为奇数时新增到t_content_1表、user_id为偶数时新增到t_content_0表。
5.2 验证读数据
从实际执行SQL可以看出、读数据时是汇聚了四张表的符合条件的记录。
