Implement Field Encryption with ShardingSphere in Spring Boot 2.6

Prepare Environment

Environment: Springboot 2.6.14 + ShardingSphere 5.3.0

Add Dependencies

<code>&lt;dependency&gt;
  &lt;groupId&gt;org.apache.shardingsphere&lt;/groupId&gt;
  &lt;artifactId&gt;shardingsphere-jdbc-core&lt;/artifactId&gt;
  &lt;version&gt;${shardingsphere.version}&lt;/version&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
  &lt;groupId&gt;com.baomidou&lt;/groupId&gt;
  &lt;artifactId&gt;mybatis-plus-boot-starter&lt;/artifactId&gt;
  &lt;version&gt;${mybatis-plus.version}&lt;/version&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
  &lt;groupId&gt;com.baomidou&lt;/groupId&gt;
  &lt;artifactId&gt;mybatis-plus-generator&lt;/artifactId&gt;
  &lt;version&gt;${mybatis-plus.version}&lt;/version&gt;
&lt;/dependency&gt;</code>

users Table Definition

Columns: pwd (plain text), pwd_cipher (encrypted), assisted_query_pwd (assist column).

Configuration Files

application.yml (Spring Boot)

<code>spring:
  datasource:
    driverClassName: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:config.yaml
    name: EncryptHikariCP
---
mybatis-plus:
  configuration:
    mapUnderscoreToCamelCase: true
  mapperLocations: classpath*:/mapper/**/*.xml
  typeAliasesPackage: com.pack</code>

config.yaml (ShardingSphere)

<code># Data source configuration
dataSources:
  ds1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/testjpa?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 123123
    minimumIdle: 10
    maximumPoolSize: 200
    autoCommit: true
    idleTimeout: 30000
    poolName: MasterHikariCP
    maxLifetime: 1800000
    connectionTimeout: 30000
    connectionTestQuery: SELECT 1
# Rule configuration
rules:
- !ENCRYPT # encryption settings
  tables:
    users:
      columns:
        pwd:
          plainColumn: pwd
          cipherColumn: pwd_cipher
          encryptorName: pwd_encryptor
      queryWithCipherColumn: true
  encryptors:
    pwd_encryptor:
      type: SM4
      props:
        sm4-key: aaaabbbbccccdddd1111222233334444
        sm4-mode: ECB
        sm4-iv: aabbccddeeffgghh
        sm4-padding: PKCS7Padding
    assisted_encryptor:
      type: SM3
      props:
        sm3-salt: aaaabbbb
props:
  sql-show: true</code>

After these configurations, you can perform the corresponding CRUD operations.

CRUD Operations

Entity Class

<code>@TableName("users")
public class Users {
  @TableId(type = IdType.ASSIGN_ID)
  private Long id;
  private String name;
  @TableField("id_no")
  private String idNo;
  private Integer age;
  private String email;
  private String pwd;
}</code>

Mapper Interface

<code>public interface UsersMapper extends BaseMapper<Users> {
}</code>

Test Class

<code>@SpringBootTest
public class UserMapperTest {
  @Resource
  private UsersMapper usersMapper;
  @Resource
  private IUsersService us;
  @Resource
  private List<DataSource> dataSources;

  @Test
  public void testUserList() {
    QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("pwd", "999999");
    System.out.println(this.usersMapper.selectList(queryWrapper));
  }

  @Test
  public void testSave() {
    Users user = new Users();
    user.setAge(99);
    user.setEmail("[email protected]");
    user.setIdNo("999999");
    user.setName("久久");
    user.setPwd("999999");
    this.usersMapper.insert(user);
  }
}</code>

Test results are shown in the following images.

Data Source and Assisted Query Column

Data Source Configuration

The initial connection‑pool settings were not applied; adding a hikari block fixes the issue.

<code>spring:
  datasource:
    driverClassName: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingphere:classpath:config.yaml
    name: EncryptHikariCP
    hikari:
      minimumIdle: 10
      maximumPoolSize: 200
      autoCommit: true
      idleTimeout: 30000
      poolName: BaseHikariCP
      maxLifetime: 1800000
      connectionTimeout: 30000
      connectionTestQuery: SELECT 1</code>

Assisted Query Column

When the assisted query column is not configured, the generated SQL uses the encrypted column; after configuring the assisted column, the SQL uses the assist column, as illustrated below.

All steps completed.