Mybatis-BaseTypeHandler实现Dao层数据加解密

Mybatis官方文档

需求：数据库保存的数据需要加密，然后查询的时候解密

具体方法：继承BaseTypeHandler，然后重写具体的set、get方法

    public abstract void setNonNullParameter(PreparedStatement var1, int var2, T var3, JdbcType var4) throws SQLException;

    public abstract T getNullableResult(ResultSet var1, String var2) throws SQLException;

    public abstract T getNullableResult(ResultSet var1, int var2) throws SQLException;

    public abstract T getNullableResult(CallableStatement var1, int var2) throws SQLException;

1、Mybatis在执行Insert语句时，先创建PreparedStatement对象，然后通过setXXX方法给占位符设置对应的值，而重写setNonNullParameter方法的目的就是set的值=加密之后的值，其中setNonNullParameter(PreparedStatement var1, int var2, T var3, JdbcType var4)， var2：参数下标（从0开始），var3：对应参数（待加密的值）

2、getNullableResult(ResultSet var1, String var2)，在查询的时候解密

测试表：

create table customer_info_demo
(
  id           bigint auto_increment
  comment 'id'
    primary key,
  customer_id  varchar(32)                        not null
  comment '用户ID',
  card_id      varchar(32)                        not null
  comment '身份证号',
  created_time datetime default CURRENT_TIMESTAMP null
  comment 'created time',
  updated_time datetime default CURRENT_TIMESTAMP null
  on update CURRENT_TIMESTAMP
  comment 'updated time'
)
  comment '用户信息表'
  collate = utf8mb4_unicode_ci;

加密解密工具类：

package com.sentinel.demo.sentinel.handler;

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;

/**
 * Created by yanshao on 2020-03-06.
 */
public class CustomerDataHandler extends BaseTypeHandler<String> {
    /**
     * Insert时
     * */
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int columnIndex,String value,JdbcType jdbcType) throws SQLException {
        System.out.println("加密setNonNullParameter");
        preparedStatement.setString(columnIndex,"加密>>>" + value);
    }

    /**
     * 查询
     * */
    @Override
    public String getNullableResult(ResultSet resultSet,String columnName) throws SQLException {
        System.out.println("解密getNullableResult>>>");
        return resultSet.getString(columnName).split(">>>")[1];
    }

    /**
     * 查询
     * */
    @Override
    public String getNullableResult(ResultSet resultSet,int columnIndex) throws SQLException {
        System.out.println("解密getNullableResult>>>");
        return resultSet.getString(columnIndex).split(">>>")[1];
    }

    /**
     * 查询
     * */
    @Override
    public String getNullableResult(CallableStatement callableStatement,int i) throws SQLException {
        return null;
    }
}

对应bean：

package com.sentinel.demo.sentinel.bean;

import lombok.Data;

import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
import java.time.LocalDateTime;

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

    /**
     * id
     */
    @NotNull
    private Long id;

    /**
     * 用户ID
     */
    @NotNull
    @Size(max = 32)
    private String customerId;

    /**
     * 身份证号
     * */
    @Size(max = 64)
    private String cardId;

    /**
     * 创建时间
     */
    private LocalDateTime createdTime;

    /**
     * 修改时间
     */
    private LocalDateTime updatedTime;
}

mapper：

package com.sentinel.demo.sentinel.mapper;

import com.sentinel.demo.sentinel.bean.CustomerInfoDemo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface CustomerInfoDemoMapper {
    int insert(CustomerInfoDemo customerInfoDemo);

    List<CustomerInfoDemo> selectAll();

    CustomerInfoDemo selectByCardId(@Param("cardId") String cardId);

    CustomerInfoDemo selectByCustomerId(@Param("customerId") String customerId);

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sentinel.demo.sentinel.mapper.CustomerInfoDemoMapper">
    <resultMap id="BaseResultMap" type="com.sentinel.demo.sentinel.bean.CustomerInfoDemo">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="customer_id" jdbcType="VARCHAR" property="customerId"/>
        <result column="card_id" jdbcType="VARCHAR" property="cardId" typeHandler= "com.sentinel.demo.sentinel.handler.CustomerDataHandler"/>
        <result column="created_time" jdbcType="TIMESTAMP" property="createdTime" />
        <result column="updated_time" jdbcType="TIMESTAMP" property="updatedTime" />
    </resultMap>


    <sql id="Base_Column_List">
        id, customer_id, card_id,created_time, updated_time
    </sql>

    <insert id="insert" parameterType="com.sentinel.demo.sentinel.bean.CustomerInfoDemo">
        insert into customer_info_demo (customer_id,card_id)
        values (
          #{customerId,jdbcType=VARCHAR},
          #{cardId,jdbcType=VARCHAR,typeHandler=com.sentinel.demo.sentinel.handler.CustomerDataHandler}
          )
    </insert>

    <select id="selectAll" resultMap="BaseResultMap">
        select
         <include refid="Base_Column_List" />
        from customer_info_demo
    </select>

    <select id="selectByCardId" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from customer_info_demo
        where card_id =  #{cardId,jdbcType=VARCHAR,typeHandler=com.sentinel.demo.sentinel.handler.CustomerDataHandler}
    </select>

    <select id="selectByCustomerId" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from customer_info_demo
        where customer_id = #{customerId,jdbcType=VARCHAR}
    </select>
</mapper>

测试1:

    @Test
    public void insert() {
        CustomerInfoDemo customerInfoDemo = new CustomerInfoDemo();
        customerInfoDemo.setCustomerId("123456");
        customerInfoDemo.setCardId("411288199610011234");
        customerInfoDemoMapper.insert(customerInfoDemo);

    }

测试2:

@Test
    public void selectAll() {
        List<CustomerInfoDemo> customerInfoDemos = customerInfoDemoMapper.selectAll();
        customerInfoDemos.forEach(customerInfoDemo -> {
            System.out.println("CustomerInfoDemo>>>" + customerInfoDemo);
        });
    }

测试3:

    @Test
    public void selectByCardId(){
        CustomerInfoDemo customerInfoDemo = customerInfoDemoMapper.selectByCardId("411288199610011234");
        System.out.println("customerInfoDemo>>>" + customerInfoDemo);
    }