Sharding-JDBC分库分表简单示例

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 <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.c3stones</groupId>
 6     <artifactId>sharding-jdbc-demo</artifactId>
 7     <version>0.0.1-SNAPSHOT</version>
 8     <name>sharding-jdbc-demo</name>
 9     <description>Sharding JDBC Demo</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         <relativePath />
16     </parent>
17 
18     <properties>
19         <java.version>1.8</java.version>
20         <maven-jar-plugin.version>3.1.1</maven-jar-plugin.version>
21         <mybatis-plus.version>3.3.1</mybatis-plus.version>
22         <sharding-jdbc.version>3.1.0</sharding-jdbc.version>
23     </properties>
24 
25     <dependencies>
26         <dependency>
27             <groupId>mysql</groupId>
28             <artifactId>mysql-connector-java</artifactId>
29             <scope>runtime</scope>
30         </dependency>
31         <dependency>
32             <groupId>com.baomidou</groupId>
33             <artifactId>mybatis-plus-boot-starter</artifactId>
34             <version>${mybatis-plus.version}</version>
35         </dependency>
36         <dependency>
37             <groupId>io.shardingsphere</groupId>
38             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
39             <version>${sharding-jdbc.version}</version>
40         </dependency>
41         <dependency>
42             <groupId>io.shardingsphere</groupId>
43             <artifactId>sharding-jdbc-spring-namespace</artifactId>
44             <version>${sharding-jdbc.version}</version>
45         </dependency>
46         <dependency>
47             <groupId>org.projectlombok</groupId>
48             <artifactId>lombok</artifactId>
49         </dependency>
50         <dependency>
51             <groupId>org.springframework.boot</groupId>
52             <artifactId>spring-boot-starter-web</artifactId>
53         </dependency>
54         <dependency>
55             <groupId>org.springframework.boot</groupId>
56             <artifactId>spring-boot-starter-test</artifactId>
57             <scope>test</scope>
58         </dependency>
59     </dependencies>
60 
61     <build>
62         <plugins>
63             <plugin>
64                 <groupId>org.springframework.boot</groupId>
65                 <artifactId>spring-boot-maven-plugin</artifactId>
66             </plugin>
67         </plugins>
68     </build>
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 /**
 9  * 用户信息
10  * 
11  * @author CL
12  *
13  */
14 @Data
15 @TableName(value = "t_user")
16 @EqualsAndHashCode(callSuper = false)
17 public class User extends Model<User> {
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<User> {
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<User> 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<UserMapper, User> implements UserService {
18 
19     /**
20      * 查询用户列表
21      * 
22      * @return
23      */
24     @Override
25     public List<User> 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<User> 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

5. 项目地址

　　https://github.com/C3Stones/blog

posted @ 2020-04-30 01:45  C3Stones  阅读(2076)  评论(0)  编辑  收藏  举报