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

Dear 丶 2024-03-26 23:14 67阅读 0赞

##  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));
        }
    }

> **只要数据库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

##  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));
        }

##  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;
    }

### 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;
    
    }

@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;
        }

EntityDemoThree typeHandler(EncryptField phone);

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

<select id="typeHandler3" resultType="com.example.poi.entity.EntityDemoThree">
            select *
            from entity_demo
            where phone = #{phone}
        </select>

##  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>

##  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;
        }
    }