ShardingSphere-JDBC 介绍

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

JDBC 数据分片的简单使用

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

 <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 如下

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+08:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- 导出 ds_0 的数据库结构
DROP DATABASE IF EXISTS `ds_0`;
CREATE DATABASE IF NOT EXISTS `ds_0` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_0`;

-- 导出  表 ds_0.t_order 结构
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='订单表';

-- 导出 ds_1 的数据库结构
DROP DATABASE IF EXISTS `ds_1`;
CREATE DATABASE IF NOT EXISTS `ds_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_1`;

-- 导出  表 ds_1.t_order 结构
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='订单表';

-- 导出 ds_2 的数据库结构
DROP DATABASE IF EXISTS `ds_2`;
CREATE DATABASE IF NOT EXISTS `ds_2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_2`;

-- 导出  表 ds_2.t_order 结构
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='订单表';

-- 导出 ds_3 的数据库结构
DROP DATABASE IF EXISTS `ds_3`;
CREATE DATABASE IF NOT EXISTS `ds_3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_3`;

-- 导出  表 ds_3.t_order 结构
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='订单表';

-- 导出 ds_4 的数据库结构
DROP DATABASE IF EXISTS `ds_4`;
CREATE DATABASE IF NOT EXISTS `ds_4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_4`;

-- 导出  表 ds_4.t_order 结构
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='订单表';

-- 导出 ds_5 的数据库结构
DROP DATABASE IF EXISTS `ds_5`;
CREATE DATABASE IF NOT EXISTS `ds_5` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_5`;

-- 导出  表 ds_5.t_order 结构
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='订单表';

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

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

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

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

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字段实现数据插入了。

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 框架。

<?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 文件的路径信息。

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，这里的配置和上面简单使用的配置差不多，不再赘述了

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，订单包含了一些属性信息

public class Order {
    private String        id;
    private String        orderId;
    private Long          userId;
    private BigDecimal    totalPrice;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

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

@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 文件中进行实现

<?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 的配置之后，我们可以创建相关的数据库和表

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+08:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- 导出 ds_0 的数据库结构
DROP DATABASE IF EXISTS `ds_0`;
CREATE DATABASE IF NOT EXISTS `ds_0` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_0`;

-- 导出  表 ds_0.t_order_1 结构
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='订单表';

-- 导出  表 ds_0.t_order_2 结构
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='订单表';


-- 导出 ds_1 的数据库结构
DROP DATABASE IF EXISTS `ds_1`;
CREATE DATABASE IF NOT EXISTS `ds_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_1`;

-- 导出  表 ds_1.t_order_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='订单表';

-- 导出  表 ds_1.t_order_2 结构
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='订单表';


-- 导出 ds_2 的数据库结构
DROP DATABASE IF EXISTS `ds_2`;
CREATE DATABASE IF NOT EXISTS `ds_2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_2`;

-- 导出  表 ds_2.t_order_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='订单表';

-- 导出  表 ds_2.t_order_2 结构
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='订单表';


-- 导出 ds_3 的数据库结构
DROP DATABASE IF EXISTS `ds_3`;
CREATE DATABASE IF NOT EXISTS `ds_3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_3`;

-- 导出  表 ds_3.t_order_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='订单表';

-- 导出  表 ds_3.t_order_2 结构
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='订单表';


-- 导出 ds_4 的数据库结构
DROP DATABASE IF EXISTS `ds_4`;
CREATE DATABASE IF NOT EXISTS `ds_4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_4`;

-- 导出  表 ds_4.t_order_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='订单表';

-- 导出  表 ds_4.t_order_2 结构
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='订单表';


-- 导出 ds_5 的数据库结构
DROP DATABASE IF EXISTS `ds_5`;
CREATE DATABASE IF NOT EXISTS `ds_5` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_5`;

-- 导出  表 ds_5.t_order_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='订单表';

-- 导出  表 ds_5.t_order_2 结构
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='订单表';

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

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

@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

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属性下添加如下配置

- !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

public class User {
    private String id;
    private String name;
    private String phone;
    private String address;
}

以及 mapper

@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();
}

之后我们测试上面的操作

@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

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 的数据加密配置

- !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

public class Member {
    private String name;
    private String password;
}

和 mapper

@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();
}

并测试写入和读取

@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

参考

官方文档