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

巴特西

Sharding-JDBC分库分表简单示例

1. 简介

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

3. 搭建工程

4. 测试

5. 项目地址

最新文章

热门文章