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的官方文档 ，其配置包含五大类：
JDBC逻辑数据库名称
运行模式配置
数据源集合配置
规则集合配置
属性配置
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
参考 官方文档
本文链接：
https://www.nosuchfield.com/2023/06/23/Introduction-to-ShardingSphere-JDBC/