用TypeHandler实现字符串和集合的转换、敏感数据脱敏

一、集合和字符互转

• 首先实现自定义TypeHandler

package com.peanut.youfupay.typehandler;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.springframework.util.StringUtils;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @author peanut
 */
@Slf4j
public class SetStringTypeHandler extends BaseTypeHandler<Set> {
    /**
     * 分隔符
     */
    private static final String SPLIT_CHAR = ",";


    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Set set, JdbcType jdbcType) throws SQLException {
        StringBuilder builder = new StringBuilder();
        set.forEach(a -> builder.append(a + SPLIT_CHAR));
        preparedStatement.setString(i, builder.toString());
    }

    @Override
    public Set getNullableResult(ResultSet resultSet, String s) throws SQLException {
        String string = resultSet.getString(s);
        if (StringUtils.isEmpty(string)) {
            return new HashSet();
        }
        String[] split = string.split(",");
        Set set = Arrays.stream(split).collect(Collectors.toSet());
        return set;
    }

    @Override
    public Set getNullableResult(ResultSet resultSet, int i) throws SQLException {
        String string = resultSet.getString(i);
        if (StringUtils.isEmpty(string)) {
            return new HashSet();
        }
        String[] split = string.split(",");
        Set set = Arrays.stream(split).collect(Collectors.toSet());
        return set;
    }

    @Override
    public Set getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        String string = callableStatement.getString(i);
        if (StringUtils.isEmpty(string)) {
            return new HashSet();
        }
        String[] split = string.split(",");
        Set set = Arrays.stream(split).collect(Collectors.toSet());
        return set;
    }
}

• 然后在Mapper.xml 中使用自定义的 TypeHandler

<mapper namespace="com.peanut.youfupay.mapper.ApiConfigMapper">
    <resultMap id="BaseResultMap" type="com.peanut.youfupay.entity.ApiConfig">
        <id column="id" jdbcType="DECIMAL" property="id"/>
        <result column="enterprise_id" jdbcType="VARCHAR" property="enterpriseId"/>
        <result column="ip_list" jdbcType="VARCHAR" property="ipList"
                typeHandler="com.peanut.youfupay.typehandler.SetStringTypeHandler"/>
    </resultMap>
    <insert id="insert">
        insert into emax_enterprise_api_config
                (enterprise_id, ip_list)
        values (#{enterpriseId}, #{ipList,typeHandler=com.peanut.youfupay.typehandler.SetStringTypeHandler})
    </insert>
    <select id="selectById" resultMap="BaseResultMap">
        select id, enterprise_id, ip_list
        from emax_enterprise_api_config
        where enterprise_id = #{enterpriseId}
    </select>
</mapper>

• 测试代码和效果如下

@SpringBootTest
@Slf4j
public class TestHandler {
    @Autowired
    ApiConfigMapper apiConfigMapper;

    @Test
    void testInsert() {
        ApiConfig apiConfig = new ApiConfig();
        apiConfig.setEnterpriseId(11111112L);
        HashSet<String> objects = new HashSet<>();
        objects.add("127.0.0.1");
        objects.add("127.0.0.2");
        objects.add("127.0.0.3");
        objects.add("127.0.0.4");
        apiConfig.setIpList(objects);
        System.out.println(apiConfig);
        apiConfigMapper.insert(apiConfig);
    }

    @Test
    void testSelect() {
        ApiConfig apiConfig = apiConfigMapper.selectById(11111112L);
        System.out.println(apiConfig);

    }

• 可以看见数据库中的IPlist字段为字符串形式，以逗号分割

二、敏感数据脱敏的TypeHandler

• 当你的公司达到一定规模的时候，会有专门的人员来审计你们公司的数据，尤其是支付公司，财税公司，当审计人员发现你们的数据库中所有人的真实信息都是明文存储时，肯定是不可以的。此时，我们就可以通过自定义TypeHandler来实现敏感数据的脱敏（加解密）

• 使用方式和上面是一样的，只需要在mapper.xml 中适当配置就可以使用了

package com.peanut.youfupay.typehandler;

import com.peanut.youfupay.util.AESUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 敏感信息（例如:姓名/身份证/银行卡号）脱敏存入数据库
 * 本文采用AES加解密，实际情况可根据需要自行选择加解密方式
 * 数据库CRUD时如何使用？
 */
@Slf4j
public class SensitiveDataTypeHandler extends BaseTypeHandler<String> {
    /**
     * 密钥
     */
    private static final String encryptKey = "123456789";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        try {
            ps.setString(i, AESUtil.encryptData(parameter, encryptKey));
        } catch (Exception e) {
            log.info("SensitiveDataTypeHandler异常", e);
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String columnValue = rs.getString(columnName);
        try {
            return StringUtils.isBlank(columnValue) ? columnValue : AESUtil.decryptData(columnValue, encryptKey);
        } catch (Exception e) {
            log.info("SensitiveDataTypeHandler异常", e);
            return columnValue;
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String columnValue = rs.getString(columnIndex);
        try {
            return StringUtils.isBlank(columnValue) ? columnValue : AESUtil.decryptData(columnValue, encryptKey);
        } catch (Exception e) {
            log.info("SensitiveDataTypeHandler异常", e);
            return columnValue;
        }
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String columnValue = cs.getString(columnIndex);
        try {
            return StringUtils.isBlank(columnValue) ? columnValue : AESUtil.decryptData(columnValue, encryptKey);
        } catch (Exception e) {
            log.info("SensitiveDataTypeHandler异常", e);
            return columnValue;
        }
    }
}