ShardingSphere-JDBC是一款可以将JDBC操作进行封装，然后实现数据分片、分布式事务、读写分离、高可用、数据加密和数据脱敏等功能的模块。它的原理是实现JDBC的接口，随后将收到的JDBC操作进行改写和处理，再将操作命中到真正的数据库之上。因为它实现了JDBC接口，因此现有的Java项目都可以100%兼容使用，只需要依赖ShardingSphere-JDBC并提供相关的配置即可。

JDBC数据分片的简单使用

我们看一个简单的JDBC数据分片的例子，首先我们需要添加相关的maven依赖

1
2
3
4
5
6
7
8
9
10
 <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.3.2</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

如上添加了shardingsphere-jdbc和mysql的依赖，shardingsphere-jdbc是项目的核心依赖，而mysql则是jdbc操作需要用到的依赖。添加了maven依赖之后我们可以先创建相关的数据库和表，创建数据库和表的sql如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
;
;
;
;
;
;
;
;


DROP DATABASE IF EXISTS `ds_0`;
CREATE DATABASE IF NOT EXISTS `ds_0`  ;
USE `ds_0`;


DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order` (
  `id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
  `order_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP DATABASE IF EXISTS `ds_1`;
CREATE DATABASE IF NOT EXISTS `ds_1`  ;
USE `ds_1`;


DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP DATABASE IF EXISTS `ds_2`;
CREATE DATABASE IF NOT EXISTS `ds_2`  ;
USE `ds_2`;


DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP DATABASE IF EXISTS `ds_3`;
CREATE DATABASE IF NOT EXISTS `ds_3`  ;
USE `ds_3`;


DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP DATABASE IF EXISTS `ds_4`;
CREATE DATABASE IF NOT EXISTS `ds_4`  ;
USE `ds_4`;


DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP DATABASE IF EXISTS `ds_5`;
CREATE DATABASE IF NOT EXISTS `ds_5`  ;
USE `ds_5`;


DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';

;
;
;
;
;

我们会创建6个数据库，分别为ds_0到ds_5，并且会在每个数据库里面创建一个名叫t_order的表。

为了使用shardingsphere-jdbc，我们需要创建相应的jdbc连接和配置，因为shardingsphere-jdbc实现了jdbc的接口，所以我们可以像使用普通的jdbc一样使用shardingsphere-jdbc。创建shardingsphere-jdbc连接的代码如下

1
2
Class.forName("org.apache.shardingsphere.driver.ShardingSphereDriver");
Connection conn = DriverManager.getConnection("jdbc:shardingsphere:classpath:shardingsphere-config.yaml");

如上我们创建了一个shardingsphere-jdbc的连接，可以看到就是一个创建JDBC的过程。其中使用的SPI类是org.apache.shardingsphere.driver.ShardingSphereDriver，而具体的jdbcUrl则是一个文件地址shardingsphere-config.yaml，shardingsphere-jdbc的配置就保存在这个文件中。根据shardingsphere-jdbc的官方文档，其配置包含五大类：

  1. JDBC逻辑数据库名称
  2. 运行模式配置
  3. 数据源集合配置
  4. 规则集合配置
  5. 属性配置

shardingsphere-jdbc的配置支持Java代码和yaml文件，这里我们只介绍yaml文件，下面是一个简单的例子shardingsphere-config.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ds_0
    username: root
    password: 1234
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/ds_1
    username: root
    password: 1234
  ds_2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ds_2
    username: root
    password: 1234
  ds_3:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/ds_3
    username: root
    password: 1234
  ds_4:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ds_4
    username: root
    password: 1234
  ds_5:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/ds_5
    username: root
    password: 1234
rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_$->{0..5}.t_order
        databaseStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: testInline
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    shardingAlgorithms:
      testInline:
        type: INLINE
        props:
          algorithm-expression: ds_$->{id % 6}
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
props:
  sql-show: true

如上配置了6个数据源分别是数据库ds_0到ds_5，props设置了打印sql语句，rules包含了表、分片算法和主键生成算法的配置。表设置中创建了一个逻辑表t_order，对应的真正数据库表是ds_0.t_order到ds_5.t_order，数据库的使用策略是通过id进行分片，分片算法是testInline，表的id字段的生成算法为snowflake。分片算法中定义了算法testInline，它使用INLINE内置方式来对id取模并和ds_进行拼接，构成数据库名。字段生成算法中定义了类型为SNOWFLAKE的字段生成算法。

有了如上配置之后，我们就可以使用shardingsphere-jdbc了。以一个数据插入操作为例，在引入了maven依赖、创建了相关的数据库和表、定义了相关的shardingsphere-jdbc配置之后，我们就可以使用上面创建的conn字段实现数据插入了。

1
2
3
4
5
6
7
String sql = "INSERT INTO t_order (`user_id`, `order_id`) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(sql))
for (int i = 0; i < 20; i++) {
    ps.setString(1, "userId");
    ps.setString(2, "orderId");
    ps.executeUpdate();
}

如上代码会创建一条数据并且随机根据snowflake算法生成一个id字段，并根据id字段的取模结果将数据保存到真正的数据库中去。更多的增删改查操作可在如下代码中看到：https://github.com/RitterHou/test-shardingsphere/tree/basic/src/main/java/com/nosuchfield/shardingsphere/data

SpringBoot集成MyBatis使用shardingsphere-jdbc

根据官方issue，目前shardingsphere-jdbc已经不再使用spring-boot-starter，而是直接使用jdbc实现相关功能。这种方式可以完美兼容JDBC的相关接口，因此可以简化很多已有项目的使用。

在SpringBoot中使用ShardingSphere需要设置如下的pom配置，在这里我们使用MyBatis作为ORM框架。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
<?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 http://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>3.0.0</version>
    </parent>

    <groupId>com.nosuchfield</groupId>
    <artifactId>test-shardingsphere</artifactId>
    <version>1.0.0-SNAPSHOT</version>

    <properties>
        <java.version>17</java.version>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.28</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.33</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>3.1.2</version>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

SpringBoot的application.yml配置如下，这里配置的数据源驱动为ShardingSphereDriver，而url就是我们配置ShardingSphere属性的地方。除此之外，我们还配置了mybatis的SQL语句所对应xml文件的路径信息。

1
2
3
4
5
6
7
8
9
10
11
spring:
  datasource:
    url: jdbc:shardingsphere:classpath:shardingsphere/config.yaml
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
  application:
    name: ShardingSphere

mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true

接着我们配置ShardingSphere的配置信息config.yaml，这里的配置和上面简单使用的配置差不多，不再赘述了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ds_0
    username: root
    password: 1234
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/ds_1
    username: root
    password: 1234
  ds_2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ds_2
    username: root
    password: 1234
  ds_3:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/ds_3
    username: root
    password: 1234
  ds_4:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ds_4
    username: root
    password: 1234
  ds_5:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/ds_5
    username: root
    password: 1234
rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_$->{0..5}.t_order_$->{1..2}
        databaseStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: databaseInline
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: tableInline
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    shardingAlgorithms:
      databaseInline:
        type: INLINE
        props:
          algorithm-expression: ds_$->{id % 6}
      tableInline:
        type: INLINE
        props:
          algorithm-expression: t_order_$->{id % 2 + 1}
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
props:
  sql-show: true

接着我们定义一个订单模型Order，订单包含了一些属性信息

1
2
3
4
5
6
7
8
public class Order {
    private String        id;
    private String        orderId;
    private Long          userId;
    private BigDecimal    totalPrice;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

我们根据这个模型可以定义个MyBatis的Mapper，它包含了插入、查询的操作

1
2
3
4
5
6
7
@Mapper
public interface OrderMapper {
    void insert(Order order);
    List<Order> selectListByIds(@Param("idList") List<Long> idList);
    @Select("SELECT * FROM t_order")
    List<Order> getAllOrders();
}

其中getAllOrders方法通过注解实现了SQL的定义，而另外两个方法的SQL则在xml文件中进行实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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="com.nosuchfield.shardingsphere.mapper.OrderMapper">
    <insert id="insert" parameterType="com.nosuchfield.shardingsphere.model.Order">
        INSERT INTO t_order(user_id, order_id, total_price, create_time, update_time)
        VALUES(#{userId}, #{orderId}, #{totalPrice}, #{createTime}, #{updateTime})
    </insert>
    <select id="selectListByIds" resultType="com.nosuchfield.shardingsphere.model.Order">
        SELECT order_id, user_id, total_price, state FROM t_order WHERE order_id IN
        <foreach collection="idList" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>

构建了如上的ShardingSphere和MyBatis的配置之后，我们可以创建相关的数据库和表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
;
;
;
;
;
;
;
;



DROP DATABASE IF EXISTS `ds_0`;
CREATE DATABASE IF NOT EXISTS `ds_0`  ;
USE `ds_0`;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE IF NOT EXISTS `t_order_2` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';



DROP DATABASE IF EXISTS `ds_1`;
CREATE DATABASE IF NOT EXISTS `ds_1`  ;
USE `ds_1`;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE IF NOT EXISTS `t_order_2` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';



DROP DATABASE IF EXISTS `ds_2`;
CREATE DATABASE IF NOT EXISTS `ds_2`  ;
USE `ds_2`;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE IF NOT EXISTS `t_order_2` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';



DROP DATABASE IF EXISTS `ds_3`;
CREATE DATABASE IF NOT EXISTS `ds_3`  ;
USE `ds_3`;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE IF NOT EXISTS `t_order_2` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';



DROP DATABASE IF EXISTS `ds_4`;
CREATE DATABASE IF NOT EXISTS `ds_4`  ;
USE `ds_4`;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE IF NOT EXISTS `t_order_2` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';



DROP DATABASE IF EXISTS `ds_5`;
CREATE DATABASE IF NOT EXISTS `ds_5`  ;
USE `ds_5`;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';


DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE IF NOT EXISTS `t_order_2` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` decimal(20,6) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';

;
;
;
;
;

有了上面的数据库和表之后，我们就可以测试ShardingSphere的数据插入和查询了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class OrderMapperTest {
    @Autowired
    private OrderMapper orderMapper;
    @Test
    public void testInsert() {
        for (int i = 0; i < 30; i++) {
            Order order = new Order();
            order.setOrderId("66666666666");
            order.setUserId(1L);
            order.setTotalPrice(new BigDecimal((i + 1) * 5));
            order.setCreateTime(LocalDateTime.now());
            order.setUpdateTime(order.getCreateTime());
            this.orderMapper.insert(order);
        }
    }
    @Test
    public void testQueryAll() {
        List<Order> orders = orderMapper.getAllOrders();
        orders.forEach(order -> log.info(order.toString()));
    }
}

读写分离和数据脱敏

上面我们测试了ShardingSphere的数据分片功能，下面我们了解一下它的读写分离和数据脱敏。我们先在ds_0、ds_1和ds_2数据库中创建表t_user

1
2
3
4
5
6
CREATE TABLE `t_user` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

之后我们在ShardingSphere的rules属性下添加如下配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      staticStrategy:
        writeDataSourceName: ds_0
        readDataSourceNames:
          - ds_1
          - ds_2
      loadBalancerName: random
  loadBalancers:
    random:
      type: RANDOM
- !MASK
  tables:
    t_user:
      columns:
        id:
          maskAlgorithm: md5_mask
        phone:
          maskAlgorithm: keep_first_n_last_m_mask
  maskAlgorithms:
    md5_mask:
      type: MD5
    keep_first_n_last_m_mask:
      type: KEEP_FIRST_N_LAST_M
      props:
        first-n: 3
        last-m: 4
        replace-char: '*'

配置包含了写库ds_0和读库ds_1、ds_2的配置，读库的负载均衡策略为随机（这里需要先设置ds_1和ds_2自动同步ds_0的数据，详细过程可查看文章MySQL实现双服务器主从同步）。数据脱敏策略为对t_user的id字段进行md5脱敏，对phone字段保留前3位和后4位，剩下的部分用*替换。创建好了表和配置之后，我们设置User的model

1
2
3
4
5
6
public class User {
    private String id;
    private String name;
    private String phone;
    private String address;
}

以及mapper

1
2
3
4
5
6
7
@Mapper
public interface UserMapper {
    @Insert("INSERT INTO t_user(id, name, phone, address) VALUES (#{id}, #{name}, #{phone}, #{address})")
    void save(User user);
    @Select("SELECT * FROM t_user")
    List<User> query();
}

之后我们测试上面的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Slf4j
public class UserMapperTest extends BaseTest {
    @Autowired
    private UserMapper userMapper;
    @Test
    public void testInsert() {
        userMapper.save(User.builder()
                .id("888")
                .name("小明")
                .phone("13866688888")
                .address("江苏省南京市").build());
    }
    @Test
    public void testQuery() {
        List<User> users = userMapper.query();
        log.info(users.toString());
    }
}

我们先插入数据，随后到从库中查询数据，得到结果如下

ShardingSphere-SQL:73 Logic SQL: SELECT * FROM t_user
ShardingSphere-SQL:73 Actual SQL: ds_1 ::: SELECT * FROM t_user
com.nosuchfield.shardingsphere.UserMapperTest:30 [User(id=0a113ef6b61820daa5611c870ed8d5ee, name=小明, phone=138****8888, address=江苏省南京市)]

可以看到数据插入到了主库中，随后从从库ds_1中查询出了相关的数据，并且对id和phone字段的数据进行了脱敏操作，id字段被转化为了MD5的结果，而phone的中间4位被星号替代了。

数据加密

数据加密可以保证我们存到数据库中的数据都是经过加密的，和数据脱敏刚好反过来。首先我们创建表t_member

1
2
3
4
CREATE TABLE `t_member` (
  `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

随后我们配置ShardingSphere的数据加密配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
- !ENCRYPT
  tables:
    t_member:
      columns:
        name:
          cipherColumn: name
          encryptorName: name_encryptor
        password:
          cipherColumn: password
          encryptorName: pwd_encryptor
      queryWithCipherColumn: true
  encryptors:
    name_encryptor:
      type: AES
      props:
        aes-key-value: 123abc
    pwd_encryptor:
      type: MD5
      props:
        salt: nosuchfield

我们将表t_member的name字段使用name_encryptor的加密方式进行加密，加密之后的字段名仍然叫做name，name_encryptor的配置在encryptors中可以看到，使用了AES加密算法并设置key为123abc。类似的，password的加密方式为MD5，在计算MD5的时候加盐nosuchfield。

随后我们创建model

1
2
3
4
public class Member {
    private String name;
    private String password;
}

和mapper

1
2
3
4
5
6
7
@Mapper
public interface MemberMapper {
    @Insert("INSERT INTO t_member(name, password) VALUES (#{name}, #{password})")
    void save(Member member);
    @Select("SELECT * FROM t_member")
    List<Member> query();
}

并测试写入和读取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Slf4j
public class MemberMapperTest extends BaseTest {
    @Autowired
    private MemberMapper memberMapper;
    @Test
    public void testSave() {
        memberMapper.save(Member.builder()
                .name("张三")
                .password("123456").build());
    }
    @Test
    public void testQuery() {
        List<Member> members = memberMapper.query();
        log.info(members.toString());
    }
}

在插入了数据{"name": "张三", "password": "123456"}之后，可以到数据库中查看插入的数据如下

PS C:\Program Files\MySQL\MySQL Server 8.0\bin> ./mysql -u root -p
mysql> select * from t_member;
+--------------------------+----------------------------------+
| name                     | password                         |
+--------------------------+----------------------------------+
| Fod6ouOanqNvHlTdBsx1Lw== | 47514eed77109a04ce4c9f9931d0c5ec |
+--------------------------+----------------------------------+
1 row in set (0.00 sec)

可以看到name和password在存储到数据库的时候都加密了。随后我们执行测试代码中的查询逻辑，可以看到结果如下，name又通过AES算法解密成功，而password因为使用的是MD5算法就无法解密了

com.nosuchfield.shardingsphere.MemberMapperTest:27 [Member(name=张三, password=47514eed77109a04ce4c9f9931d0c5ec)]

本节使用到的代码：https://github.com/RitterHou/test-shardingsphere

参考

官方文档