1. 简介
Shareding是一个简单的分库分表中间件，它不需要依赖于其他的服务，即可快速应用在实际项目的分库分表策略中。
2. 工程结构
3. 初始化数据库（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;
4. 搭建工程
- 搭建Maven工程
- 修改pom.xml
1 <project xmlns="http://maven.apache.org/POM/4.0.0"
2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4 <modelVersion>4.0.0</modelVersion>
5 <groupId>com.alan</groupId>
6 <artifactId>Sharedind-JDBC-Demo</artifactId>
7 <version>0.0.1-SNAPSHOT</version>
8 <name>Sharedind-JDBC-Demo</name>
9 <description>Sharedind-JDBC简单分库分表示例</description>
10
11 <parent>
12 <groupId>org.springframework.boot</groupId>
13 <artifactId>spring-boot-starter-parent</artifactId>
14 <version>2.1.6.RELEASE</version>
15 </parent>
16
17 <properties>
18 <java.version>1.8</java.version>
19 <maven-jar-plugin.version>3.1.1</maven-jar-plugin.version>
20 <mybatis-plus.version>3.3.1</mybatis-plus.version>
21 <sharding-jdbc.version>3.1.0</sharding-jdbc.version>
22 </properties>
23
24 <dependencies>
25 <dependency>
26 <groupId>mysql</groupId>
27 <artifactId>mysql-connector-java</artifactId>
28 <scope>runtime</scope>
29 </dependency>
30 <dependency>
31 <groupId>com.baomidou</groupId>
32 <artifactId>mybatis-plus-boot-starter</artifactId>
33 <version>${mybatis-plus.version}</version>
34 </dependency>
35 <dependency>
36 <groupId>io.shardingsphere</groupId>
37 <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
38 <version>${sharding-jdbc.version}</version>
39 </dependency>
40 <dependency>
41 <groupId>io.shardingsphere</groupId>
42 <artifactId>sharding-jdbc-spring-namespace</artifactId>
43 <version>${sharding-jdbc.version}</version>
44 </dependency>
45 <dependency>
46 <groupId>org.projectlombok</groupId>
47 <artifactId>lombok</artifactId>
48 </dependency>
49 <dependency>
50 <groupId>org.springframework.boot</groupId>
51 <artifactId>spring-boot-starter-web</artifactId>
52 </dependency>
53 <dependency>
54 <groupId>org.springframework.boot</groupId>
55 <artifactId>spring-boot-starter-test</artifactId>
56 <scope>test</scope>
57 </dependency>
58 </dependencies>
59
60 <build>
61 <plugins>
62 <plugin>
63 <groupId>org.springframework.boot</groupId>
64 <artifactId>spring-boot-maven-plugin</artifactId>
65 </plugin>
66 </plugins>
67 </build>
68
69 </project>
- 编写实体类
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 @Data
9 @TableName(value = "t_user")
10 @EqualsAndHashCode(callSuper = false)
11 public class User extends Model<User> {
12
13 private static final long serialVersionUID = 1L;
14 private int id;
15 private String username;
16 @TableField(value = "org_code")
17 private int orgCode;
18
19 }
- 编写Mapper
1 import com.alan.entity.User;
2 import com.baomidou.mybatisplus.core.mapper.BaseMapper;
3
4 public interface UserMapper extends BaseMapper<User> {
5
6 }
- 编写Service
1 import java.util.List;
2
3 import com.alan.entity.User;
4
5 public interface UserService {
6
7 List<User> findList();
8
9 boolean save(User user);
10
11 }
1 import java.util.List;
2
3 import org.springframework.stereotype.Service;
4
5 import com.alan.entity.User;
6 import com.alan.mapper.UserMapper;
7 import com.alan.service.UserService;
8 import com.baomidou.mybatisplus.core.toolkit.Wrappers;
9 import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
10
11 @Service
12 public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
13
14 @Override
15 public List<User> findList() {
16 return baseMapper.selectList(Wrappers.lambdaQuery());
17 }
18
19 @Override
20 public boolean save(User user) {
21 return super.save(user);
22 }
23
24 }
- 编写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.alan.entity.User;
9 import com.alan.service.UserService;
10
11 @RestController
12 public class UserController {
13
14 @Autowired
15 private UserService userService;
16
17 @PostMapping(value = "save")
18 public boolean save(User user) {
19 return userService.save(user);
20 }
21
22 @GetMapping(value = "list")
23 public List<User> findList() {
24 return userService.findList();
25 }
26 }
- 编写启动类
1 @SpringBootApplication
2 @MapperScan(value = "com.alan.mapper")
3 public class Application {
4
5 public static void main(String[] args) {
6 SpringApplication.run(Application.class, args);
7 }
8
9 }
- 添加配置文件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}
6. 测试
测试时观察控制台打印的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
已为社区贡献3条内容
所有评论(0)