【ShardingSphere】ShardingSphere-JDBC 集成SpringBoot - H__D

【ShardingSphere】ShardingSphere-JDBC 集成SpringBoot

一、快速入门

　　【ShardingSphere】ShardingSphere-JDBC 快速入门

二、集成SpringBoot

　　数据库表，使用快速入门的项目表

　　1、新建SpringBoot项目，引入相关依赖

<!-- sharding-jdbc -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

　　完整pom文件如下：

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0"
 3          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5     <modelVersion>4.0.0</modelVersion>
 6 
 7     <groupId>org.example</groupId>
 8     <artifactId>test-springboot-sharding-jdbc</artifactId>
 9     <version>1.0-SNAPSHOT</version>
10 
11     <parent>
12         <groupId>org.springframework.boot</groupId>
13         <artifactId>spring-boot-starter-parent</artifactId>
14         <version>2.2.5.RELEASE</version>
15         <relativePath/> <!-- lookup parent from repository -->
16     </parent>
17 
18     <properties>
19         <maven.compiler.source>8</maven.compiler.source>
20         <maven.compiler.target>8</maven.compiler.target>
21     </properties>
22 
23     <dependencies>
24 
25         <dependency>
26             <groupId>org.springframework.boot</groupId>
27             <artifactId>spring-boot-starter</artifactId>
28         </dependency>
29 
30 
31         <dependency>
32             <groupId>org.springframework.boot</groupId>
33             <artifactId>spring-boot-starter-jdbc</artifactId>
34         </dependency>
35 
36         <!-- mysql -->
37         <dependency>
38             <groupId>mysql</groupId>
39             <artifactId>mysql-connector-java</artifactId>
40             <version>8.0.12</version>
41         </dependency>
42 
43         <dependency>
44             <groupId>org.springframework.boot</groupId>
45             <artifactId>spring-boot-starter-test</artifactId>
46             <scope>test</scope>
47         </dependency>
48 
49         <!-- sharding-jdbc -->
50         <dependency>
51             <groupId>org.apache.shardingsphere</groupId>
52             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
53             <version>4.1.1</version>
54         </dependency>
55 
56     </dependencies>
57 
58 
59     <!-- SpringBoot打包插件，可以将代码打包成一个可执行的jar包 -->
60     <build>
61         <plugins>
62             <plugin>
63                 <groupId>org.springframework.boot</groupId>
64                 <artifactId>spring-boot-maven-plugin</artifactId>
65                 <version>2.2.5.RELEASE</version>
66             </plugin>
67         </plugins>
68     </build>
69 </project>

View Code

　　2、编辑SpringBoot启动文件

1 @SpringBootApplication
2 public class Application{
3     public static void main(String[] args) {
4         SpringApplication.run(Application.class, args);
5     }
6 }

　　3、编辑application.properties文件

数据分片功能配置

# 更多参考：https://shardingsphere.apache.org/document/4.1.1/cn/manual/sharding-jdbc/configuration/config-spring-boot/

# Spring数据源 被shardingsphere数据源替代
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?allowPublicKeyRetrieval=true&useSSL=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=123456


# ==========数据源配置==========
# 配置真实数据源名称
spring.shardingsphere.datasource.names=ds0,ds1
# 指定默认数据源，不指定时,轮询获取各个数据源的值
spring.shardingsphere.sharding.default-data-source-name=ds0
# 显示shardingsphere执行的sql
spring.shardingsphere.props.sql.show=true

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.0.1:3306/test-shardingjdbc1?allowPublicKeyRetrieval=true&useSSL=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.0.1:3306/test-shardingjdbc2?allowPublicKeyRetrieval=true&useSSL=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456


## ==========数据分片默认配置==========

# 默认行表达式 数据库分片策略
# 默认行表达式 数据库分片策略 列
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# 默认数据库分片策略 算法表达式
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds${user_id % 2}

# 默认行表达式 分片策略
# 默认行表达式 分片策略 列
spring.shardingsphere.sharding.default-table-strategy.inline.sharding-column=order_id
# 默认行表达式分片策略 算法表达式
spring.shardingsphere.sharding.default-table-strategy.inline.algorithm-expression=t_order_${order_id % 2}

## 默认自增列名称，缺省表示不使用自增主键生成器
#spring.shardingsphere.sharding.default-key-generate-strategy.xxx=
## 默认自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID
#spring.shardingsphere.sharding.default-sharding-column=

## 用于单分片键的标准分片场景
#spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column= # 分片列名称
#spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name= # 分片算法名称


## ==========数据分片具体配置==========
# 分片表配置
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${0..1}.t_order_${0..1}
# 行表达式 数据库分片策略,可覆盖默认配置
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds${user_id % 2}
# 行表达式 分片策略,可覆盖默认配置
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2}
# 自增列名称，缺省表示不使用自增主键生成器
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# 自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

#用于单分片键的标准分片场景
#分片列名称
#spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
# 精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.test.springboot.sharding.algorithm.CustomShardingAlgorithm
# 范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.range-algorithm-class-name=


spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE

# 绑定表-关联查询时有效
# 指分片规则一致的主表和子表。例如： t_order 表和 t_order_item 表，均按照 order_id 分片,绑定表之间的分区键完全相同，则此两张表互为绑定表关系
spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item
# 广播表
spring.shardingsphere.sharding.broadcast-tables=t_config

　　测试：

 1 package com.test.springboot.sharding;
 2 
 3 import org.junit.Test;
 4 import org.junit.runner.RunWith;
 5 import org.springframework.beans.factory.annotation.Autowired;
 6 import org.springframework.boot.test.context.SpringBootTest;
 7 import org.springframework.jdbc.core.JdbcTemplate;
 8 import org.springframework.test.context.junit4.SpringRunner;
 9 
10 import javax.sql.DataSource;
11 import java.sql.Connection;
12 import java.sql.PreparedStatement;
13 import java.sql.ResultSet;
14 import java.sql.SQLException;
15 import java.util.List;
16 import java.util.Map;
17 
18 @RunWith(SpringRunner.class)
19 @SpringBootTest
20 public class TestApplication {
21 
22     @Autowired
23     private DataSource dataSource;
24 
25     @Autowired
26     private JdbcTemplate jdbcTemplate;
27 
28     @Test
29     public void context() throws SQLException {
30         System.out.println("========");
31         System.out.println("dataSource ====" + dataSource);
32 
33         Connection connection = dataSource.getConnection();
34         System.out.println("connecttion ====" + connection);
35 
36         Connection connection1 = jdbcTemplate.getDataSource().getConnection();
37         System.out.println("connection1 = " + connection1);
38 
39         List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from t_config");
40         System.out.println(maps);
41 //        jdbcTemplate.execute("insert into t_config values(5, 'finish')");
42     }
43 
44     @Test
45     public void test02() throws SQLException {
46 
47         List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from t_order where user_id = 10 and order_id = 1000");
48         System.out.println(maps);
49     }
50 
51     @Test
52     public void test03() throws SQLException {
53         System.out.println("\"668421745522245632\".length() = " + "668421745522245632".length());
54         jdbcTemplate.execute("insert into t_order(`user_id`, `status`) values(10, 'init')");
55 
56     }
57 
58 
59     @Test
60     public void test04() throws SQLException {
61         String sql = "SELECT i.* FROM  t_order o, t_order_item i WHERE o.order_id = i.order_id AND o.order_id = 100 AND o.user_id = 10";
62         List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
63         System.out.println("maps = " + maps);
64 
65     }
66 
67 }

自定义分片策略类

CustomShardingAlgorithm，用法请查看配置

 1 package com.test.springboot.sharding.algorithm;
 2 
 3 import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
 4 import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
 5 
 6 import java.util.Collection;
 7 
 8 
 9 public class CustomShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
10 
11     @Override
12     public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
13         String value = shardingValue.getValue().toString();
14         for (String targetName : availableTargetNames) {
15             Integer i = Integer.parseInt(value) % 2;
16             if (targetName.contains(i.toString())) {
17                 return targetName;
18             }
19         }
20         throw new IllegalArgumentException();
21     }
22 }

数据分片+读写分离功能配置

## ==========数据分片具体配置==========
# 分片表配置
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds${0..1}.t_order_${0..1}
# 行表达式 数据库分片策略,可覆盖默认配置
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds${user_id % 2}
# 行表达式 分片策略,可覆盖默认配置
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2}
# 自增列名称，缺省表示不使用自增主键生成器
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# 自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

#用于单分片键的标准分片场景
#分片列名称
#spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
# 精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.test.springboot.sharding.algorithm.CustomShardingAlgorithm
# 范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.range-algorithm-class-name=


spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE

# 绑定表-关联查询时有效
# 指分片规则一致的主表和子表。例如： t_order 表和 t_order_item 表，均按照 order_id 分片,绑定表之间的分区键完全相同，则此两张表互为绑定表关系
spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item
# 广播表
spring.shardingsphere.sharding.broadcast-tables=t_config

## ==========读写分离配置==========
# 配置真实数据源名称
spring.shardingsphere.datasource.names=master0,slave0,master1,slave1
# 指定默认数据源，不指定时,轮询获取各个数据源的值
spring.shardingsphere.sharding.default-data-source-name=ds0
# 显示shardingsphere执行的sql
spring.shardingsphere.props.sql.show=true

# 配置第 1 个数据源
spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.0.1:3306/test-shardingjdbc1?allowPublicKeyRetrieval=true&useSSL=true
spring.shardingsphere.datasource.master0.username=hd
spring.shardingsphere.datasource.master0.password=hd123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.0.1:3306/test-shardingjdbc2?allowPublicKeyRetrieval=true&useSSL=true
spring.shardingsphere.datasource.master1.username=hd
spring.shardingsphere.datasource.master1.password=hd123456
# 配置第 3 个数据源
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.0.2:3306/test-shardingjdbc1?allowPublicKeyRetrieval=true&useSSL=true
spring.shardingsphere.datasource.slave0.username=hd
spring.shardingsphere.datasource.slave0.password=hd123456

# 配置第 4 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.0.2:3306/test-shardingjdbc2?allowPublicKeyRetrieval=true&useSSL=true
spring.shardingsphere.datasource.slave1.username=hd
spring.shardingsphere.datasource.slave1.password=hd123456

# 主从数据源规则配置
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave0
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=slave1

测试

　　同上

数据脱敏功能配置

sql文件

1 CREATE TABLE `user` (
2   `id` bigint(20) NOT NULL,
3   `username` varchar(255) DEFAULT NULL,
4   `password` varchar(255) DEFAULT NULL,
5   `md5_password` varchar(255) DEFAULT NULL,
6   `aes_password` varchar(255) DEFAULT NULL,
7   PRIMARY KEY (`id`)
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置

## ==========数据脱敏==========
#加解密器类型，可自定义或选择内置类型：MD5/AES
spring.shardingsphere.sharding.encrypt-rule.encryptors.encryptor_aes.type=aes
#属性配置, 注意：使用AES加密器，需要配置AES加密器的KEY属性：aes.key.value
spring.shardingsphere.sharding.encrypt-rule.encryptors.encryptor_aes.props.aes.key.value=123456

#加解密器2
spring.shardingsphere.sharding.encrypt-rule.encryptors.encryptor_md5.type=md5

#加解密器3
spring.shardingsphere.sharding.encrypt-rule.encryptors.encryptor_cust.type=cust

#存储明文的字段
#spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd.plainColumn=password
#存储密文的字段
spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd.cipherColumn=md5_password
#加密器名字
spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd.encryptor=encryptor_aes
#spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd.encryptor=encryptor_cust

#spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd2.plainColumn=password
spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd2.cipherColumn=aes_password
spring.shardingsphere.sharding.encrypt-rule.tables.user.columns.pwd2.encryptor=encryptor_aes

测试

    @Test
    public void test05() throws SQLException {
        jdbcTemplate.update("insert into user(`id`, password, pwd, pwd2) values(?, ?, ?, ?)", new Object[]{5, "abc", "abc", "abc"});

    }

自定义脱敏策略

编辑策略类

 1 public final class CustomShardingEncryptor implements Encryptor {
 2     @Override
 3     public void init() {
 4         System.out.println("CustomShardingEncryptor#init()...");
 5     }
 6 
 7     @Override
 8     public String encrypt(Object plaintext) {
 9         return "abc" + plaintext.toString();
10     }
11 
12     @Override
13     public Object decrypt(String ciphertext) {
14         return ciphertext.substring(2);
15     }
16 
17     @Override
18     public String getType() {
19         return "CUST";
20     }
21 
22     @Override
23     public Properties getProperties() {
24         return null;
25     }
26 
27     @Override
28     public void setProperties(Properties properties) {
29         AESEncryptor aesEncryptor = new AESEncryptor();
30     }
31 }

由于shardingsphere用到了spi技术，在resources/META-INF/services下的文件，新建文件 org.apache.shardingsphere.encrypt.strategy.spi.Encryptor

内容如下：

com.test.springboot.sharding.encrypt.CustomShardingEncryptor

这样既能使自定义的脱敏策略生效

posted on 2021-11-20 15:11 H__D 阅读(827) 评论(0) 编辑收藏举报

刷新页面返回顶部


Copyright © 2024 H__D Powered by .NET 9.0 on Kubernetes 博客园

导航

一、 快速入门