Mybatis对数据加密解密(AES加解密)操作

1：使用mybatis的BaseTypeHandler类，编写需要加密解密处理的类型

package com.example.poi.typeHandlers;

import cn.hutool.crypto.SecureUtil;
import cn.hutool.crypto.symmetric.AES;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import java.nio.charset.StandardCharsets;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author xu
 * @create 2023/9/3 23
 */
@MappedTypes(EncryptField.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class EncryptFieldTypeHandler extends BaseTypeHandler<EncryptField> {
    private static final byte[] KEYS = "balabalbalabala".getBytes(StandardCharsets.UTF_8);

    /**
     * 处理sql执行前相应的参数
     * @param 
     * @param i 参数的索引下标
     * @param parameter
     * @param jdbcType
     * @throws SQLException
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, EncryptField parameter, JdbcType jdbcType) throws SQLException {
        if (parameter == null || parameter.getValue() == null) {
            ps.setString(i, null);
            return;
        }
        AES aes = SecureUtil.aes(KEYS);
        String encrypt = aes.encryptHex(parameter.getValue());
        ps.setString(i, encrypt);
    }

    /**
     * sql语句执行后执行的操作
     * @param rs
     * @param columnName
     * @return
     * @throws SQLException
     */
    @Override
    public EncryptField getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return decrypt(rs.getString(columnName));
    }

    /**
     * sql语句执行后执行的操作
     * @param rs
     * @param columnIndex
     * @return
     * @throws SQLException
     */
    @Override
    public EncryptField getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return decrypt(rs.getString(columnIndex));
    }

    /**
     * sql语句执行后执行的操作
     * @param cs
     * @param columnIndex
     * @return
     * @throws SQLException
     */
    @Override
    public EncryptField getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return decrypt(cs.getString(columnIndex));
    }

    public EncryptField decrypt(String value) {
        if (null == value) {
            return null;
        }
        return new EncryptField(SecureUtil.aes(KEYS).decryptStr(value));
    }
}
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

只要数据库sql语句有对EncryptField类型的操作，就会触发EncryptFieldTypeHandler类的方法
@MappedTypes：表示该处理器会自动处理的java类型
@MappedJdbcTypes：表示处理器会自动处理的Jdbc类型

2： yml配置需要设置mybatis的type-handlers-package配置

mybatis-plus:
  mapper-locations: classpath*:com/example/poi/mapper/**/xml/*Mapper.xml
  global-config:
    banner: false
    db-config:
      id-type: ASSIGN_ID
      table-underline: true
  type-handlers-package: com.example.poi.typeHandlers
1
2
3
4
5
6
7
8

3：传递phone字段是明文，查出结果phone是密文

    @GetMapping("/getTypeHandler")
    public EntityDemo typeHandler(String phone) {
        EncryptField encryptField = new EncryptField(phone);
        return entityDemoService.getOne(new LambdaQueryWrapper<EntityDemo>().eq(EntityDemo::getPhone, encryptField));
    }
1
2
3
4
5

4： 如果要求传递phone字段是明文，查出结果phone是明文

4-1：EntityDemo数据库表对于字段

@Data
@Accessors(chain = true)
@TableName(value = "entity_demo")
public class EntityDemo implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId
    private Integer id;

    private String age;

    private String name;

    private String phone;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4-2：EntityDemoThree查询结果对于字段

@Data
public class EntityDemoThree implements Serializable {
    private static final long serialVersionUID = 1L;

    private Integer id;

    private String age;

    private String name;

    private EncryptField phone;

}
1
2
3
4
5
6
7
8
9
10
11
12
13

    @GetMapping("/getTypeHandler")
    public EntityDemo typeHandler(String phone) {
        EncryptField encryptField = new EncryptField(phone);
        EntityDemoThree entityDemoThree = entityDemoService.typeHandler3(encryptField);
        EntityDemo entityDemo = new EntityDemo();
        BeanUtils.copyProperties(entityDemoThree, entityDemo);
        entityDemo.setPhone(entityDemoThree.getPhone().getValue());
        return entityDemo;
    }
1
2
3
4
5
6
7
8
9

EntityDemoThree typeHandler(EncryptField phone);
1

    @Override
    public EntityDemoThree typeHandler(EncryptField phone) {
        return entityDemoMapper.typeHandler3(phone);
    }
1
2
3
4

    <select id="typeHandler3" resultType="com.example.poi.entity.EntityDemoThree">
        select *
        from entity_demo
        where phone = #{phone}
    select>
1
2
3
4
5

5：对于需要更新的字段最好进行如下条件更新操作

    <update id="refresh">
        UPDATE entity_demo
        <set >
        <if test="age != '' and age != null">
            age = #{age},
        </if>
        <if test="name != '' and name != null">
            name = #{name},
        </if>
        <if test="phone.value != '' and phone.value != null">
            phone = #{phone},
        </if>
        </set>
        WHERE id = #{id}
    </update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6：如果需要对其他类型进行加密，可以再添加多一个typeHandler类，添加一个List类加密如下：

@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(List.class)
public class ListTypeHandler extends BaseTypeHandler<List<String>> {
    
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType) throws SQLException {
        if (null == parameter || parameter.isEmpty()) {
            ps.setString(i, null);
            return;
        }
        ps.setString(i, String.join(",", parameter));
    }
    
    @Override
    public List<String> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        final String value = rs.getString(columnName);
        if (StringUtils.hasText(value)) {
            return Arrays.asList(value.split(","));
        }
        return null;
    }
    
    @Override
    public List<String> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        final String value = rs.getString(columnIndex);
        if (StringUtils.hasText(value)) {
            return Arrays.asList(value.split(","));
        }
        return null;
    }
    
    @Override
    public List<String> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        final String value = cs.getString(columnIndex);
        if (StringUtils.hasText(value)) {
            return Arrays.asList(value.split(","));
        }
        return null;
    }
}
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