数据库数据加密主要通过mybtisplus的特性实现，同时mybaitsplus3.1.1不支持，仅在3.1.2以及以后进行支持
编写加密算法以及逻辑继承BaseTypeHandler类
主要实现入库与查询的逻辑，仅仅对mybaitsplus的数据库代码生效，对自己手写的在xml里的sql不生效，需要手动配置。
public class TypeHandler extends BaseTypeHandler<String> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
if (ObjectUtils.isEmpty(parameter)) {
ps.setString(i, null);
return;
}
ps.setString(i, SM4Util.encrypt(parameter));
}
@Override
public String getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
return SM4Util.decrypt(resultSet.getString(columnName));
}
@Override
public String getNullableResult(ResultSet resultSet, int i) throws SQLException {
return SM4Util.decrypt(resultSet.getString(i));
}
@Override
public String getNullableResult(CallableStatement ca, int i) throws SQLException {
return SM4Util.decrypt(ca.getString(i));
}
}
实体类编写逻辑
主要表中加上@TableName(value = “TB_CA”,autoResultMap = true)，其中autoResultMap = true是必加的
还有在需要进行加密的字段上加上 @TableField(typeHandler = TypeHandler.class)注解
package cn.byzk.appsignservice2.common.model;
import cn.byzk.appsignservice2.common.config.mybatis.TypeHandler;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.util.Date;
/**
* ca表信息.
*
* @author 会飞的大象 2022-03-15
* @version 1.0
*/
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
@TableName(value = "TB_CA",autoResultMap = true)
@KeySequence("SEQUENCE_TB_CA")
public class TbCaModel {
/**
* 自增id使用序列.
*/
@TableId(value = "ID", type = IdType.INPUT)
private Long id;
/**
* 公司名称.
*/
private String companyName;
/**
* 根证书.
*/
private String rootCert;
/**
* s
* 官网
*/
private String officialWebsite;
/**
* 详细地址
*/
private String detailsAddress;
/**
* 联系人
*/
@TableField(typeHandler = TypeHandler.class)
private String contacts;
/***
* 联系方式
*/
@TableField(typeHandler = TypeHandler.class)
private String contactWay;
/**
* 户名.
*/
private String accountName;
/**
* 开户银行.
*/
private String accountBank;
/**
* 开户账号
*/
private String account;
/**
* 收费通知单.
*/
private String chargeNote;
/**
* 公司备注.
*/
private String companyRemark;
/**
* 创建时间.
*/
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
/**
* 状态0正常，1删除.
*/
private Integer status = 0;
}
手写的sql处理方式
编写resultMap，并在查询要解密的数据指定typeHandler=“cn.byzk.appsignservice2.common.config.mybatis.TypeHandler”
<?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="cn.byzk.appsignservice2.common.mapper.CaMapper">
<sql id="base">
id
,company_name,root_cert,official_website,details_address,contacts,contact_way,
account_name,account_bank,account,charge_note,company_remark,create_time,status
</sql>
<resultMap id="caResultMap" type="cn.byzk.appsignservice2.common.model.TbCaModel">
<id column="id" property="id"/>
<result column="company_name" property="companyName" />
<result column="root_cert" property="rootCert" />
<result column="official_website" property="officialWebsite" />
<result column="details_address" property="detailsAddress" />
<result column="contacts" property="contacts" typeHandler="cn.byzk.appsignservice2.common.config.mybatis.TypeHandler" />
<result column="contact_way" property="contactWay" typeHandler="cn.byzk.appsignservice2.common.config.mybatis.TypeHandler" />
<result column="account_name" property="accountName" />
<result column="account_bank" property="accountBank" />
<result column="account" property="account" />
<result column="company_remark" property="companyRemark" />
<result column="create_time" property="createTime" />
<result column="status" property="status" />
</resultMap>
<select id="getAll" resultMap="caResultMap">
SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM (
select tb1.id,tb1.company_name,tb1.official_website,tb1.details_address,tb1.contacts,
tb1.contact_way,tb1.account_name,tb1.account_bank,tb1.account,
<!-- tb2.content as charge_note,tb1.root_cert,-->
tb1.company_remark,tb1.create_time,tb1.status from TB_CA tb1
<!-- left join TB_FILE tb2 on tb1.charge_note=tb2.unique_number-->
<where>
1 = 1
<if test="caQueryDto.companyName != null and caQueryDto.companyName != '' ">
and tb1.company_name like '%' || #{caQueryDto.companyName} || '%'
</if>
<if test="caQueryDto.contacts != null and caQueryDto.contacts != ''">
and tb1.contacts like '%' || #{caQueryDto.contacts,typeHandler=cn.byzk.appsignservice2.common.config.mybatis.TypeHandler} || '%'
</if>
</where>
order by tb1.create_time desc
) TMP ) WHERE ROW_ID <= ${caQueryDto.page*caQueryDto.size} and ROW_ID > ${(caQueryDto.page - 1) * caQueryDto.size}
</select>
<select id="getAllCount" resultType="long">
select count(1) from TB_CA
<where>
1 = 1
<if test="caQueryDto.companyName != null and caQueryDto.companyName != '' ">
and company_name like '%' || #{caQueryDto.companyName} || '%'
</if>
<if test="caQueryDto.contacts != null and caQueryDto.contacts != ''">
and contacts like '%' || #{caQueryDto.contacts,typeHandler=cn.byzk.appsignservice2.common.config.mybatis.TypeHandler} || '%'
</if>
</where>
</select>
<select id="getInfoById" resultMap="caResultMap">
select tb1.id,
tb1.company_name,
tb1.root_cert,
tb1.official_website,
tb1.details_address,
tb1.contacts,
tb1.contact_way,
tb1.account_name,
tb1.account_bank,
tb1.account,
tb2.content as charge_note,
tb1.company_remark,
tb1.create_time,
tb1.status
from TB_CA tb1
left join TB_FILE tb2 on tb1.charge_note = tb2.unique_number
where
tb1.id=#{id}
</select>
<select id="getAllAvailableCa" resultType="cn.byzk.appsignservice2.common.model.TbCaModel">
select id,company_name,company_remark,status from TB_CA
where status=#{status}
</select>
</mapper>
评论区