Sharding-JDBC 实现数据脱敏

使用sharding-jdbc实现数据脱敏，比如用户表中的password字段，日常开发都会进行数据脱敏，对其进行加密。

附：shardingsphere官网-数据脱敏

项目结构： 

 

pom.xml:

1.  

   <?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 https://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>2.2.6.RELEASE</version>
           <relativePath/> <!-- lookup parent from repository -->
       </parent>
       <groupId>com.lucifer</groupId>
       <artifactId>sharding-jdbc-demo</artifactId>
       <version>0.0.1-SNAPSHOT</version>
       <name>sharding-jdbc-demo</name>
       <description>Demo project for Spring Boot</description>
    
       <properties>
           <java.version>1.8</java.version>
       </properties>
    
       <dependencies>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter</artifactId>
           </dependency>
           <dependency>
               <groupId>mysql</groupId>
               <artifactId>mysql-connector-java</artifactId>
               <version>8.0.19</version>
           </dependency>
           <dependency>
               <groupId>org.mybatis.spring.boot</groupId>
               <artifactId>mybatis-spring-boot-starter</artifactId>
               <version>2.1.1</version>
           </dependency>
           <dependency>
               <groupId>org.projectlombok</groupId>
               <artifactId>lombok</artifactId>
           </dependency>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-web</artifactId>
           </dependency>
           <dependency>
               <groupId>com.alibaba</groupId>
               <artifactId>druid-spring-boot-starter</artifactId>
               <version>1.1.20</version>
           </dependency>
           <dependency>
               <groupId>org.apache.shardingsphere</groupId>
               <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
               <version>4.0.0-RC1</version>
           </dependency>
    
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-test</artifactId>
               <scope>test</scope>
               <exclusions>
                   <exclusion>
                       <groupId>org.junit.vintage</groupId>
                       <artifactId>junit-vintage-engine</artifactId>
                   </exclusion>
               </exclusions>
           </dependency>
       </dependencies>
    
       <build>
           <plugins>
               <plugin>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-maven-plugin</artifactId>
               </plugin>
           </plugins>
       </build>
    
   </project>

    

userDao:操作数据库接口 

1.  

   package com.lucifer.sharding.dao;
   
   
   import com.lucifer.sharding.pojo.User;
   
   import org.apache.ibatis.annotations.Insert;
   
   import org.apache.ibatis.annotations.Param;
   
   import org.apache.ibatis.annotations.Select;
   
   
   public interface UserDao {
   
   
   @Insert("INSERT INTO t_user (user_name,password) VALUES (#{userName}, #{password})")
   
   void addUser(@Param("userName") String userName, @Param("password") String password);
   
   
   @Select("SELECT id,user_name as userName,password FROM t_user WHERE user_name=#{userName} and password= #{password}")
   
   User selectUser(@Param("userName") String userName, @Param("password") String password);
   
   
   }

    

User实体类 :

1.  

   package com.lucifer.sharding.pojo;
   
   
   import lombok.Data;
   
   
   import java.io.Serializable;
   
   
   /**
   
   * t_user
   
   * @author
   
   */
   
   @Data
   
   public class User implements Serializable {
   
   
   private Long id;
   
   
   private String userName;
   
   
   private String password;
   
   
   }

    

SpringBoot启动类：

    用MapperScan注解开启扫描dao层

1.  

   package com.lucifer.sharding;
   
   
   import org.mybatis.spring.annotation.MapperScan;
   
   import org.springframework.boot.SpringApplication;
   
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   
   import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
   
   
   @MapperScan(value = "com.lucifer.sharding.dao")
   
   @SpringBootApplication
   
   public class ShardingJdbcDemoApplication {
   
   
   public static void main(String[] args) {
   
   SpringApplication.run(ShardingJdbcDemoApplication.class, args);
   
   }
   
   
   }

    

application.yml:

############################数据脱敏#################################
#服务端口
server:
  port: 56081
#服务名
spring:
  application:
    name: sharding-jdbc-examples
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:     #数据源配置
      names: ds
      ds:
        url: jdbc:mysql://192.168.24.140:3306/test1?useSSL=false&useUnicode=true&serverTimezone=UTC
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
    encrypt:
      encryptors:
        encryptor_aes:
          type: aes  #加解密器类型，可自定义或选择内置类型：MD5/AES
          props:
            aes.key.value: 123456abc  #属性配置, 注意：使用AES加密器，需要配置AES加密器的KEY属性：aes.key.value
          qualifiedColumns: t_user.password
      tables:
        t_user:
          columns:
            password:
              cipherColumn: password  #存储密文的字段
              encryptor: encryptor_aes  #加解密器名字
    props:
      sql:
        show: true  #是否开启SQL显示，默认值: false
      query:
        with:
          cipher:
            column: true #是否使用密文列查询
 
 
#日志打印
logging:
  level:
    root: info
    org.springframework.web: info
    com.lucifer.sharding.dao: debug
    druid.sql: debug

1.  

    

测试类： 

package com.lucifer.sharding;
 
import com.lucifer.sharding.dao.UserDao;
import com.lucifer.sharding.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
 
@SpringBootTest
class ShardingJdbcDemoApplicationTests {
 
    @Autowired
    private UserDao userDao;
 
    @Test
    void add() {
        userDao.addUser("张三","123456");
    }
 
    @Test
    public void query() {
        User user = userDao.selectUser("张三", "123456");
        System.out.println(user);
    }
    
}

测试1： add（）:

1. 2020-05-02 23:33:29.553 DEBUG 24528 --- [ main] c.lucifer.sharding.dao.UserDao.addUser : ==> Preparing: INSERT INTO t_user (user_name,password) VALUES (?, ?)

2. 2020-05-02 23:33:29.565 DEBUG 24528 --- [ main] c.lucifer.sharding.dao.UserDao.addUser : ==> Parameters: 张三(String), 123456(String)

3. 2020-05-02 23:33:30.535 DEBUG 24528 --- [ main] c.lucifer.sharding.dao.UserDao.addUser : <== Updates: 1

测试2：query():

1. 2020-05-02 23:35:37.137 DEBUG 9036 --- [ main] c.l.sharding.dao.UserDao.selectUser : ==> Preparing: SELECT id,user_name as userName,password FROM t_user WHERE user_name=? and password= ?

2. 2020-05-02 23:35:37.154 DEBUG 9036 --- [ main] c.l.sharding.dao.UserDao.selectUser : ==> Parameters: 张三(String), 123456(String)

3. 2020-05-02 23:35:38.151 DEBUG 9036 --- [ main] c.l.sharding.dao.UserDao.selectUser : <== Total: 1

4. User(id=3, userName=张三, password=123456)