Sharding-JDBC分库分表简单示例

阅读原文时间：2022年04月04日阅读：1

1. 简介

　　Sharding是一个简单的分库分表中间件，它不需要依赖于其他的服务，即可快速应用在实际项目的分库分表策略中。

2. 初始化数据库（db0、db1、db2）

1 #创建数据库db0
2 CREATE DATABASE IF NOT EXISTS `db0` DEFAULT CHARACTER SET utf8;
3
4 USE `db0`;
5
6 DROP TABLE IF EXISTS `t_user_0`;
7 CREATE TABLE `t_user_0` (
8 `id` int(11) NOT NULL,
9 `username` varchar(255) DEFAULT NULL,
10 `org_code` int(11) DEFAULT NULL,
11 PRIMARY KEY (`id`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
13
14 DROP TABLE IF EXISTS `t_user_1`;
15 CREATE TABLE `t_user_1` (
16 `id` int(11) NOT NULL,
17 `username` varchar(255) DEFAULT NULL,
18 `org_code` int(11) DEFAULT NULL,
19 PRIMARY KEY (`id`)
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
21
22 DROP TABLE IF EXISTS `t_user_2`;
23 CREATE TABLE `t_user_2` (
24 `id` int(11) NOT NULL,
25 `username` varchar(255) DEFAULT NULL,
26 `org_code` int(11) DEFAULT NULL,
27 PRIMARY KEY (`id`)
28 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
29
30 #创建数据库db1
31 CREATE DATABASE IF NOT EXISTS `db1` DEFAULT CHARACTER SET utf8 ;
32
33 USE `db1`;
34
35 DROP TABLE IF EXISTS `t_user_0`;
36 CREATE TABLE `t_user_0` (
37 `id` int(11) NOT NULL,
38 `username` varchar(255) DEFAULT NULL,
39 `org_code` int(11) DEFAULT NULL,
40 PRIMARY KEY (`id`)
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
42
43 DROP TABLE IF EXISTS `t_user_1`;
44 CREATE TABLE `t_user_1` (
45 `id` int(11) NOT NULL,
46 `username` varchar(255) DEFAULT NULL,
47 `org_code` int(11) DEFAULT NULL,
48 PRIMARY KEY (`id`)
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
50
51 DROP TABLE IF EXISTS `t_user_2`;
52 CREATE TABLE `t_user_2` (
53 `id` int(11) NOT NULL,
54 `username` varchar(255) DEFAULT NULL,
55 `org_code` int(11) DEFAULT NULL,
56 PRIMARY KEY (`id`)
57 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
58
59 #创建数据库db2
60 CREATE DATABASE IF NOT EXISTS `db2` DEFAULT CHARACTER SET utf8;
61
62 USE `db2`;
63
64 DROP TABLE IF EXISTS `t_user_0`;
65 CREATE TABLE `t_user_0` (
66 `id` int(11) NOT NULL,
67 `username` varchar(255) DEFAULT NULL,
68 `org_code` int(11) DEFAULT NULL,
69 PRIMARY KEY (`id`)
70 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
71
72 DROP TABLE IF EXISTS `t_user_1`;
73 CREATE TABLE `t_user_1` (
74 `id` int(11) NOT NULL,
75 `username` varchar(255) DEFAULT NULL,
76 `org_code` int(11) DEFAULT NULL,
77 PRIMARY KEY (`id`)
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
79
80 DROP TABLE IF EXISTS `t_user_2`;
81 CREATE TABLE `t_user_2` (
82 `id` int(11) NOT NULL,
83 `username` varchar(255) DEFAULT NULL,
84 `org_code` int(11) DEFAULT NULL,
85 PRIMARY KEY (`id`)
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 搭建工程

搭建Maven工程

修改pom.xml

1 http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 4 4.0.0 5 com.c3stones 6 sharding-jdbc-demo 7 0.0.1-SNAPSHOT 8 sharding-jdbc-demo 9 Sharding JDBC Demo 10 11 12 org.springframework.boot 13 spring-boot-starter-parent 14 2.1.6.RELEASE 15 16 17 18 19 1.8 20 3.1.1 21 3.3.1 22 3.1.0 23 24 25 26 27 mysql 28 mysql-connector-java 29 runtime 30 31 32 com.baomidou 33 mybatis-plus-boot-starter 34 ${mybatis-plus.version} 35 36 37 io.shardingsphere 38 sharding-jdbc-spring-boot-starter 39 ${sharding-jdbc.version} 40 41 42 io.shardingsphere 43 sharding-jdbc-spring-namespace 44 ${sharding-jdbc.version} 45 46 47 org.projectlombok 48 lombok 49 50 51 org.springframework.boot 52 spring-boot-starter-web 53 54 55 org.springframework.boot 56 spring-boot-starter-test 57 test 58 59 60 61 62 63 64 org.springframework.boot 65 spring-boot-maven-plugin 66 67 68 69
编写实体类

1 import com.baomidou.mybatisplus.annotation.TableField;
2 import com.baomidou.mybatisplus.annotation.TableName;
3 import com.baomidou.mybatisplus.extension.activerecord.Model;
4
5 import lombok.Data;
6 import lombok.EqualsAndHashCode;
7
8 /**
9 * 用户信息
10 *
11 * @author CL
12 *
13 */
14 @Data
15 @TableName(value = "t_user")
16 @EqualsAndHashCode(callSuper = false)
17 public class User extends Model {
18
19 private static final long serialVersionUID = 1L;
20 private int id;
21 private String username;
22 @TableField(value = "org_code")
23 private int orgCode;
24
25 }
编写Mapper

1 import com.baomidou.mybatisplus.core.mapper.BaseMapper;
2 import com.c3stones.entity.User;
3
4 /**
5 * 用户Mapper
6 *
7 * @author CL
8 *
9 */
10 public interface UserMapper extends BaseMapper {
11
12 }
编写Service

1 import java.util.List;
2
3 import com.c3stones.entity.User;
4
5 /**
6 * 用户Service
7 *
8 * @author CL
9 *
10 */
11 public interface UserService {
12
13 /**
14 * 查询用户列表
15 *
16 * @return
17 */
18 List findList();
19
20 /**
21 * 保存用户信息
22 *
23 * @param user
24 * @return
25 */
26 boolean save(User user);
27
28 }

1 import java.util.List;
2
3 import org.springframework.stereotype.Service;
4
5 import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
6 import com.c3stones.entity.User;
7 import com.c3stones.mapper.UserMapper;
8 import com.c3stones.service.UserService;
9
10 /**
11 * 用户Service实现类
12 *
13 * @author CL
14 *
15 */
16 @Service
17 public class UserServiceImpl extends ServiceImpl implements UserService {
18
19 /**
20 * 查询用户列表
21 *
22 * @return
23 */
24 @Override
25 public List findList() {
26 return new User().selectAll();
27 }
28
29 /**
30 * 保存用户信息
31 *
32 * @param user
33 * @return
34 */
35 @Override
36 public boolean save(User user) {
37 return super.save(user);
38 }
39
40 }
编写Controller

1 import java.util.List;
2
3 import org.springframework.beans.factory.annotation.Autowired;
4 import org.springframework.web.bind.annotation.GetMapping;
5 import org.springframework.web.bind.annotation.PostMapping;
6 import org.springframework.web.bind.annotation.RestController;
7
8 import com.c3stones.entity.User;
9 import com.c3stones.service.UserService;
10
11 /**
12 * 用户Controller
13 *
14 * @author CL
15 *
16 */
17 @RestController
18 public class UserController {
19
20 @Autowired
21 private UserService userService;
22
23 @PostMapping(value = "save")
24 public boolean save(User user) {
25 return userService.save(user);
26 }
27
28 @GetMapping(value = "list")
29 public List findList() {
30 return userService.findList();
31 }
32 }
编写启动类

1 import org.mybatis.spring.annotation.MapperScan;
2 import org.springframework.boot.SpringApplication;
3 import org.springframework.boot.autoconfigure.SpringBootApplication;
4
5 /**
6 * 启动类
7 *
8 * @author CL
9 *
10 */
11 @SpringBootApplication
12 @MapperScan(value = "com.c3stones.mapper")
13 public class Application {
14
15 public static void main(String[] args) {
16 SpringApplication.run(Application.class, args);
17 }
18
19 }
添加配置文件application.yml

1 spring:
2 main:
3 allow-bean-definition-overriding: true #允许Bean重复注入，后者覆盖前者
4 sharding:
5 jdbc:
6 datasource:
7 names: db0,db1,db2
8 db0:
9 type: com.zaxxer.hikari.HikariDataSource
10 driver-class-name: com.mysql.cj.jdbc.Driver
11 jdbc-url: jdbc:mysql://localhost:3306/db0?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
12 username: root
13 password: root
14 db1:
15 type: com.zaxxer.hikari.HikariDataSource
16 driver-class-name: com.mysql.cj.jdbc.Driver
17 jdbc-url: jdbc:mysql://localhost:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
18 username: root
19 password: root
20 db2:
21 type: com.zaxxer.hikari.HikariDataSource
22 driver-class-name: com.mysql.cj.jdbc.Driver
23 jdbc-url: jdbc:mysql://localhost:3306/db2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
24 username: root
25 password: root
26 config:
27 props:
28 sql.show: true #打印sql
29 sharding:
30 default-database-strategy: #默认分库策略
31 inline:
32 sharding-column: id
33 algorithm-expression: db$->{id % 3}
34 tables:
35 t_user:
36 actual-data-nodes: db$->{0..2}.t_user_$->{0..2} #实际节点
37 table-strategy: #分表策略
38 inline:
39 sharding-column: org_code
40 algorithm-expression: t_user_$->{org_code % 3}

4. 测试

　　测试时观察控制台打印的SQL。

保存用户信息，id=1

　　控制台：

2020-04-29 12:41:36.849 INFO 4384 --- [nio-8080-exec-6] ShardingSphere-SQL : Rule Type: sharding
2020-04-29 12:41:36.850 INFO 4384 --- [nio-8080-exec-6] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( id,
username,
org_code ) VALUES ( ?,
?,
? )
2020-04-29 12:41:36.850 INFO 4384 --- [nio-8080-exec-6] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@37b6baa2), columns=[Column(name=id, tableName=t_user), Column(name=username, tableName=t_user), Column(name=org_code, tableName=t_user)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=( ?,
?,
? ), parametersCount=3)]), columnsListLastPosition=45, generateKeyColumnIndex=-1, insertValuesListLastPosition=67)
2020-04-29 12:41:36.850 INFO 4384 --- [nio-8080-exec-6] ShardingSphere-SQL : Actual SQL: db1 ::: INSERT INTO t_user_2 ( id,
username,
org_code ) VALUES ( ?,
?,
? ) ::: [[1, 张三, 1001]]

保存用户信息，id=2

　　控制台：

1 2020-04-29 12:40:34.611 INFO 4384 --- [nio-8080-exec-3] ShardingSphere-SQL : Rule Type: sharding
2 2020-04-29 12:40:34.611 INFO 4384 --- [nio-8080-exec-3] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( id,
3 username,
4 org_code ) VALUES ( ?,
5 ?,
6 ? )
7 2020-04-29 12:40:34.611 INFO 4384 --- [nio-8080-exec-3] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@37b6baa2), columns=[Column(name=id, tableName=t_user), Column(name=username, tableName=t_user), Column(name=org_code, tableName=t_user)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=( ?,
8 ?,
9 ? ), parametersCount=3)]), columnsListLastPosition=45, generateKeyColumnIndex=-1, insertValuesListLastPosition=67)
10 2020-04-29 12:40:34.611 INFO 4384 --- [nio-8080-exec-3] ShardingSphere-SQL : Actual SQL: db2 ::: INSERT INTO t_user_0 ( id,
11 username,
12 org_code ) VALUES ( ?,
13 ?,
14 ? ) ::: [[2, 李四, 1002]]

保存用户信息，id=3

　　控制台：

1 2020-04-29 12:42:02.260 INFO 4384 --- [nio-8080-exec-1] ShardingSphere-SQL : Rule Type: sharding
2 2020-04-29 12:42:02.263 INFO 4384 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( id,
3 username,
4 org_code ) VALUES ( ?,
5 ?,
6 ? )
7 2020-04-29 12:42:02.263 INFO 4384 --- [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@37b6baa2), columns=[Column(name=id, tableName=t_user), Column(name=username, tableName=t_user), Column(name=org_code, tableName=t_user)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=( ?,
8 ?,
9 ? ), parametersCount=3)]), columnsListLastPosition=45, generateKeyColumnIndex=-1, insertValuesListLastPosition=67)
10 2020-04-29 12:42:02.263 INFO 4384 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: db0 ::: INSERT INTO t_user_1 ( id,
11 username,
12 org_code ) VALUES ( ?,
13 ?,
14 ? ) ::: [[3, 赵六, 1003]]

查询用户信息

　　控制台：

1 2020-04-29 12:42:15.962 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Rule Type: sharding
2 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Logic SQL: SELECT id,username,org_code FROM t_user
3 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@53468979), containStar=false, firstSelectItemStartPosition=8, selectListLastPosition=29, groupByLastPosition=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=username, alias=Optional.absent()), CommonSelectItem(expression=org_code, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
4 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db0 ::: SELECT id,username,org_code FROM t_user_0
5 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db0 ::: SELECT id,username,org_code FROM t_user_1
6 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db0 ::: SELECT id,username,org_code FROM t_user_2
7 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db1 ::: SELECT id,username,org_code FROM t_user_0
8 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db1 ::: SELECT id,username,org_code FROM t_user_1
9 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db1 ::: SELECT id,username,org_code FROM t_user_2
10 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db2 ::: SELECT id,username,org_code FROM t_user_0
11 2020-04-29 12:42:15.963 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db2 ::: SELECT id,username,org_code FROM t_user_1
12 2020-04-29 12:42:15.964 INFO 4384 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: db2 ::: SELECT id,username,org_code FROM t_user_2