1. 定义表结构

CREATE TABLE `c_user` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `pwd_plain` varchar(256) DEFAULT NULL,
  `pwd_cipher` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 定义实体类

import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name = "c_user")
public class CUser implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "pwd")//逻辑列名
    private String pwd;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
}

3. 添加依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

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

4. 添加配置

spring.profiles.active=encryptor
spring.shardingsphere.props.sql.show=true


#datasource
spring.shardingsphere.datasource.names=ds0

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/demo1
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

#encrypt 脱敏主要涉及如下6个配置！！！

# 逻辑列名pwd与明文列的对应关系，value值为表中字段名; 如果不需要明文列可以注释掉该配置
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.plain-column=pwd_plain
# 逻辑列名pwd与密文列的对应关系，value值为表中字段名
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.cipher-column=pwd_cipher

# 定义加密规则
# zidingyi_pwd: 规则名称
spring.shardingsphere.encrypt.encryptors.zidingyi_pwd.type=aes
# zidingyi_pwd的aes加密秘钥
spring.shardingsphere.encrypt.encryptors.zidingyi_pwd.props.aes.key.value=1234

# 绑定加密字段与加密规则关系
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.encryptor=zidingyi_pwd

# 查询时 true：用密文字段查询；false：用明文字段查询。 默认为true
spring.shardingsphere.props.query.with.cipher.column=false

5. 定义dao

public interface CUserRepository extends JpaRepository<CUser,Long> {
    public List<CUser> findByPwd(String pwd);
}

6. 测试用例

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestEncryptor {

    @Resource
    private CUserRepository userRepository;

    @Test
    @Repeat(2)
    public void testAdd(){
        CUser user = new CUser();
        user.setName("tiger");
        user.setPwd("abc");
        userRepository.save(user);
    }

    @Test
    public void testFind(){
        // 通过配置：spring.shardingsphere.props.query.with.cipher.column=false 查看sql语句有什么不同
        List<CUser> list = userRepository.findByPwd("abc");
        list.forEach(cUser -> {
            System.out.println(cUser.getId()+" "+cUser.getName()+" "+cUser.getPwd());
        });
    }

}

image.png

image.png

image.png

image.png