一、背景
我司最近在做等保,要求数据库里面的手机号、微信号、身份证号等等这些在数据库中都不能是明文,所以需要把数据库中的涉及到这些的加密保存,但是查询和保存的接口传参不受影响,之前业务给前端返回是什么样子依然保持不变,这样前端不受影响。
二、实现方式的思考
1.可以直接代码修改,代码中涉及的敏感数据接口在查询和插入、更新时进行加解密。缺点是工作量大,代码侵入多。
2.在mybatis中进行统一拦截,上层业务调用不需要再考虑敏感数据的加解密问题,可以考虑配置文件中配置需要加解密的表和字段,或者注解的方式。
也看了一些博客,最终采用了第二种方式,通过拦截器+注解的方式,实现敏感数据自动加解密
三、mybatis插件的原理:
这里也可以自行查询,资料也很多。
Mybatis的插件,是采用责任链机制,通过JDK动态代理来实现的。默认情况下,Mybatis允许使用插件来拦截四个对象:
Executor:执行CURD操作;
StatementHandler:处理sql语句预编译,设置参数等相关工作;
ParameterHandler:设置预编译参数用的;
ResultSetHandler:处理结果集。
四、代码实现
设置参数时对参数中含有敏感字段的数据进行加密
对查询返回的结果进行解密处理
按照对Executor和ResultSetHandler
进行切入,这里
也参考了一些博主,大部分是按照对ParameterHandler
和ResultSetHandler
进行切入,但在实践中发现ParameterHandler
在某些场景支持(比如传参对象中有字段为list或者mapper中是list传入的)的不好,但我司项目中是有很多这种情况的。后面通过跟源码发现是在改值之前分页插件已经赋值了查询的参数,所以后面对list改值之后并未生效。
实体类中有加解密字段时使用@SensitiveData注解,单个需加解密的字段使用@SensitiveField注解
注解:
SensitiveData
注解:用在实体类上,表示此类有些字段需要加密,需要结合@SensitiveField
一起使用SensitiveField
注解:用在类的字段上或者方法的参数上,表示该字段或参数需要加密
1.SensitiveData注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created on 2024/1/9
* * 该注解定义在类上
* * 插件通过扫描类对象是否包含这个注解来决定是否继续扫描其中的字段注解
* * 这个注解要配合SensitiveField注解
* @author www
* @version V1.0
* @apiNote
*/
@Inherited
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface SensitiveData {
}
2.SensitiveField注解
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created on 2024/1/9
* 该注解有两种使用方式
* ①:配合@SensitiveData加在类中的字段上
* ②:直接在Mapper中的方法参数上使用
* @author www
* @version V1.0
* @apiNote
*/
@Documented
@Inherited
@Target({ElementType.FIELD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface SensitiveField {
}
插件实现代码:
1.入参处理
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.AnnotationUtils;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
@Intercepts(
{
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
}
)
@Component
public class BarExecuteInterceptor implements Interceptor {
private static final Logger log = LoggerFactory.getLogger(BarExecuteInterceptor.class);
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
if (invocation.getTarget() instanceof Executor) {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameterObject = invocation.getArgs()[1];
if (parameterObject != null) {
//批量保存
if (parameterObject instanceof Map) {
Map map = (Map) parameterObject;
for (Object key : map.keySet()) {
Object value = map.get(key);
if (value != null){
if (value instanceof List) {
List list = (List) value;
for (Object item : list) {
//list<phone>
if (item instanceof String) {
if (isMethodParameterAnnotated(mappedStatement, (String) key)) {
String encryptedItem = EncryptUtil.encryptValue((String) item);
//当list中只有一个值时可能是singletonList,
if (list != null && list.size() == 1){
list = new ArrayList<>(list);
}
// 替换原有值
list.set(list.indexOf(item), encryptedItem);
}
} else {
//OBJECT 类型 走这个方法
// 递归处理其他类型的对象
processSensitiveFields(mappedStatement, item);
}
}
map.put(key,list);
} else if (value instanceof String) {
if (isMethodParameterAnnotated(mappedStatement, (String) key)) {
String encryptedItem = EncryptUtil.encryptValue((String) value);
map.put(key, encryptedItem);
}
} else if (value instanceof Map) {
// Map入参情况,这种不用处理,实际map的也没办法加加密的注解
continue;
} else {
processSensitiveFields(mappedStatement, value);
}
}
}
} else if (parameterObject instanceof List) {
// 检查是否为TestForm实例或者包含TestForm实例的列表
// 如果参数是列表,检查列表中的每个元素是否为TestForm实例
List<?> parameterList = (List<?>) parameterObject;
for (Object param : parameterList) {
processSensitiveFields(mappedStatement, param);
}
} else if (parameterObject instanceof String) {
if (isMethodSingleParameterAnnotated(mappedStatement)) {
String encryptedItem = EncryptUtil.encryptValue((String) parameterObject);
invocation.getArgs()[1] = encryptedItem;
}
} else {
//通用的保存单条数据时,对象查询
processSensitiveFields(mappedStatement, parameterObject);
}
}
// 继续执行原始方法
return invocation.proceed();
}
} catch (Exception e) {
log.error("加密参数处理发生异常:{}",e.getMessage(),e);
}
// 如果不是Executor,继续执行原始方法
return invocation.proceed();
}
private void processSensitiveFields(MappedStatement mappedStatement, Object obj) throws IllegalAccessException {
if (obj != null) {
// 如果是Map类型,检查其中的每个值
if (obj instanceof Map) {
Map<?, ?> map = (Map<?, ?>) obj;
for (Object value : map.values()) {
processSensitiveFields(mappedStatement, value);
}
} else if (obj instanceof List) {
List list = (List) obj;
for (Object item : list) {
if (item instanceof String) {
if (isMethodParameterAnnotated(mappedStatement, (String) item)) {
String encryptedItem = EncryptUtil.encryptValue((String) item);
//当list中只有一个值时可能是singletonList,singletonList是不可变列表,不能set
if (list != null && list.size() == 1){
list = new ArrayList<>(list);
}
// 替换原有值
list.set(list.indexOf(item), encryptedItem);
}
} else {
// 递归处理其他类型的对象
processSensitiveFields(mappedStatement, item);
}
}
} else {
// 如果是普通对象,检查是否有@SensitiveField注解的字段
Class<?> clazz = obj.getClass();
SensitiveData tempSensitiveData = AnnotationUtils.findAnnotation(clazz, SensitiveData.class);
if (Objects.nonNull(tempSensitiveData)) {
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (isSensitiveField(mappedStatement, field)) {
field.setAccessible(true);
Object value = field.get(obj);
if (value != null) {
if (value instanceof String) {
// 加密字符串字段
String encryptedValue = EncryptUtil.encryptValue((String) value);
field.set(obj, encryptedValue);
} else if (value instanceof List) {
// 如果是列表,对列表中的每个元素进行加密
List list = (List) value;
for (Object item : list) {
if (item instanceof String) {
String encryptedItem = EncryptUtil.encryptValue((String) item);
//当list中只有一个值时可能是singletonList,singletonList是不可变列表,不能set
if (list != null && list.size() == 1){
list = new ArrayList<>(list);
}
// 替换原有值
list.set(list.indexOf(item), encryptedItem);
} else {
// 递归处理其他类型的对象
processSensitiveFields(mappedStatement, item);
}
}
field.set(obj, list);
} else {
// 递归处理其他类型的对象
processSensitiveFields(mappedStatement, value);
}
}
}
}
}
}
}
}
private boolean isSensitiveField(MappedStatement mappedStatement, Field field) {
// 如果参数对象是当前字段所在类的实例
return field.isAnnotationPresent(SensitiveField.class) || isMethodParameterAnnotated(mappedStatement, field.getName());
}
private boolean isMethodSingleParameterAnnotated(MappedStatement mappedStatement) {
// 获取方法参数上的注解
Method method = getMethod(mappedStatement);
if (method == null) {
return false;
}
Annotation[][] parameterAnnotations = method.getParameterAnnotations();
if (parameterAnnotations.length == 0) {
return false;
}
Parameter[] parameters = method.getParameters();
for (int i = 0; i < parameterAnnotations.length; i++) {
for (Annotation annotation : parameterAnnotations[i]) {
if (annotation instanceof SensitiveField) {
return true;
}
}
}
return false;
}
private boolean isMethodParameterAnnotated(MappedStatement mappedStatement, String parameterName) {
// 获取方法参数上的注解
Method method = getMethod(mappedStatement);
if (method == null) {
return false;
}
Annotation[][] parameterAnnotations = method.getParameterAnnotations();
if (parameterAnnotations.length == 0) {
return false;
}
Parameter[] parameters = method.getParameters();
for (int i = 0; i < parameterAnnotations.length; i++) {
for (Annotation annotation : parameterAnnotations[i]) {
if (annotation instanceof SensitiveField) {
boolean flag = false;
//判断是否有@param,没有@param,直接判断参数名和当前参数是否相等
//有@param 判断@param里的value值是否和当前参数相等(不能直接判断参数名,因为@param可能value为其他与当前参数名不一定一致)
for (Annotation annotation1 : parameterAnnotations[i]) {
if (annotation1 instanceof Param) {
Param param = (Param)annotation1;
flag = parameterName.equals(param.value());
if (flag){
return flag;
}
}
}
//param的value不相等再判断参数名
return parameterName.equals(parameters[i].getName());
}
}
}
return false;
}
private Method getMethod(MappedStatement mappedStatement) {
try {
// 获取MappedStatement的id,格式为namespace.id
String statementId = mappedStatement.getId();
// 获取namespace和id
int lastDot = statementId.lastIndexOf(".");
String namespace = statementId.substring(0, lastDot);
String methodId = statementId.substring(lastDot + 1);
// 获取接口类型
Class<?> mapperInterface = Class.forName(namespace);
// 获取接口方法
for (Method method : mapperInterface.getDeclaredMethods()) {
if (method.getName().equals(methodId)) {
return method;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
}
2.返回数据处理
import org.apache.commons.collections4.CollectionUtils;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.stereotype.Component;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Objects;
import java.util.Properties;
@Component
@Intercepts({
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})
})
public class ResultSetInterceptor implements Interceptor {
private final DecryptUtil decryptUtil = new DecryptUtil();
@Override
public Object intercept(Invocation invocation) throws Throwable {
//取出查询的结果
Object resultObject = invocation.proceed();
if (Objects.isNull(resultObject)) {
return null;
}
//基于selectList
if (resultObject instanceof ArrayList) {
@SuppressWarnings("unchecked")
ArrayList<Objects> resultList = (ArrayList<Objects>) resultObject;
for (int i = 0; i < resultList.size(); i++) {
if (null == resultList.get(i)){
resultList.remove(i);
i = i-1;
}
}
if (!CollectionUtils.isEmpty(resultList) && needToDecrypt(resultList.get(0))) {
for (Object result : resultList) {
//逐一解密
decryptUtil.decrypt(result);
}
}
//基于selectOne
} else {
if (needToDecrypt(resultObject)) {
decryptUtil.decrypt(resultObject);
}
}
return resultObject;
}
private boolean needToDecrypt(Object object) {
Class<?> objectClass = object.getClass();
SensitiveData sensitiveData = AnnotationUtils.findAnnotation(objectClass, SensitiveData.class);
return Objects.nonNull(sensitiveData);
}
@Override
public Object plugin(Object target) {
return Interceptor.super.plugin(target);
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
}
3.加解密工具类:
代码中有考虑避免重复加解密的问题,即使加解密失败也不会影响程序,只是会打印错误日志
1.加密工具类
我们这里为了能够满足系统里面模糊查询,所以手机号如果7位以上前三周四明文,中间加密,用@拼接保存。其他的全部加密保存。如果4位以上后四位明文前面加密,用@拼接保存。4位的话直接明文保存。超过24位(这种肯定有问题了,当然也是为了防止重复加密)不加密直接保存
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
@Component
public class EncryptUtil {
private static final Logger logger = LoggerFactory.getLogger(EncryptUtil.class);
public static String encryptValue(String value) {
try {
String encrypt;
//如果为空直接返回,为24位及以上(任何字符加密后为24位,如果11位手机号按我们的规则加密后为24位以上)表示加密过,直接返回。这里超过24位的手机号也不会再加密
if (StringUtils.isBlank(value) || (StringUtils.isNotBlank(value) && value.length() >= 24)) {
return value;
}
//如果大于7位,并全部为数字
if (value.length() >7 && value.matches("\\d+")) {
String firstThreeDigits = value.substring(0, 3);
String middleDigits = value.substring(3, value.length() - 4);
String lastFourDigits = value.substring(value.length()-4);
String encryptMiddle = DBAESUtil.encrypt(middleDigits);
encrypt = firstThreeDigits + "@" + encryptMiddle + "@" + lastFourDigits;
//4位以上数字
} else if (value.length() >=4 && value.matches("\\d+")){
if (value.length() ==4){
return value;
}
String lastFour = value.substring(value.length() - 4);
String beforeLastFour = value.substring(0, value.length() - 4);
String encrypted = DBAESUtil.encrypt(beforeLastFour);
encrypt = encrypted + "@" + lastFour;
} else {
encrypt = DBAESUtil.encrypt(value);
}
return encrypt;
} catch (Exception e) {
logger.error("解密发生异常:{}",e.getMessage(),e);
}
return value;
}
}
2.解密工具类
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Component
public class DecryptUtil{
private final Logger logger = LoggerFactory.getLogger(getClass());
/**
* 解密
*
* @param result resultType的实例
*/
public <T> T decrypt(T result) throws IllegalAccessException {
//取出resultType的类
Class<?> resultClass = result.getClass();
Field[] declaredFields = resultClass.getDeclaredFields();
for (Field field : declaredFields) {
//取出所有被SensitiveField注解的字段
SensitiveField sensitiveField = field.getAnnotation(SensitiveField.class);
if (!Objects.isNull(sensitiveField)) {
field.setAccessible(true);
Object object = field.get(result);
//String的解密
if (object instanceof String) {
String value = (String) object;
if (StringUtils.isNotBlank(value)){
//如果中间有逗号,分割进行解密
if (value.contains(",")){
StringBuilder res = new StringBuilder();
String[] list = value.split(",");
for (String s : list) {
String decryptVal = this.handleDecrypt(s);
res.append(decryptVal).append(",");
}
// 去掉最后一个逗号
if (res.length() > 0) {
res.deleteCharAt(res.length() - 1);
}
field.set(result, res.toString());
}else {
String decryptVal = this.handleDecrypt(value);
//修改字段值
field.set(result, decryptVal);
}
}
}
}
}
return result;
}
private String handleDecrypt(String value){
//对注解的字段进行逐一解密
try {
if (StringUtils.isBlank(value)){
return value;
}
if (StringUtils.isNotBlank(value) && value.length() >= 24 && value.matches("\\d+")){
return value;
}
if (StringUtils.isNotBlank(value) && value.length() < 24){
//11位并全部为数字
if (value.length()==11 && value.matches("\\d+")){
logger.info("出现明文手机号:"+value);
return value;
}else {
logger.info("无须解密:"+value);
return value;
}
}
Pattern pattern = Pattern.compile("@([^@]+)@");
Matcher matcher = pattern.matcher(value);
if (matcher.find()) {
String decryptVal = DBAESUtil.decrypt(matcher.group(1));
return value.replaceAll("@([^@]+)@", decryptVal);
} else if (value.contains("@")) {
int atIndex = value.indexOf("@");
String encryptedStr = value.substring(0, atIndex);
String decryptVal = DBAESUtil.decrypt(encryptedStr);
return decryptVal + value.split("@")[1];
}else {
return DBAESUtil.decrypt(value);
}
} catch (Exception e) {
logger.error("解密发生异常:{}",e.getMessage(),e);
}
return value;
}
}
3.DEA加解密工具类
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.crypto.Cipher;
import javax.crypto.KeyGenerator;
import javax.crypto.spec.SecretKeySpec;
import java.util.Base64;
public class DBAESUtil {
//这里配置为自己项目定义的key
private static final String KEY = "1111";
//参数分别代表 算法名称/加密模式/数据填充方式
private static final String ALGORITHMSTR = "AES/ECB/PKCS5Padding";
private static Logger logger;
public DBAESUtil() {
this.logger = LoggerFactory.getLogger(this.getClass());
}
/**
* 加密
* @param content 加密的字符串
* @param encryptKey key值
* @return
* @throws Exception
*/
public static String encrypt(String content, String encryptKey) {
try {
KeyGenerator kgen = KeyGenerator.getInstance("AES");
kgen.init(128);
Cipher cipher = Cipher.getInstance(ALGORITHMSTR);
cipher.init(Cipher.ENCRYPT_MODE, new SecretKeySpec(encryptKey.getBytes(), "AES"));
byte[] b = cipher.doFinal(content.getBytes("utf-8"));
return Base64.getEncoder().encodeToString(b);
}catch (Exception e){
logger.error("加密失败"+e.getMessage());
return content;
}
}
/**
* 解密
* @param encryptStr 解密的字符串
* @param decryptKey 解密的key值
* @return
* @throws Exception
*/
public static String decrypt(String encryptStr, String decryptKey) {
try {
KeyGenerator kgen = KeyGenerator.getInstance("AES");
kgen.init(128);
Cipher cipher = Cipher.getInstance(ALGORITHMSTR);
cipher.init(Cipher.DECRYPT_MODE, new SecretKeySpec(decryptKey.getBytes(), "AES"));
byte[] encryptBytes = Base64.getDecoder().decode(encryptStr);
byte[] decryptBytes = cipher.doFinal(encryptBytes);
return new String(decryptBytes);
}catch (Exception e){
logger.error("解密失败"+e.getMessage());
return encryptStr;
}
}
/**
* 加密
* @param content
* @return
*/
public static String encrypt(String content) {
if (StringUtils.isBlank(content)){
return content;
}
return encrypt(content, KEY);
}
/**
* 解密
* @param encryptStr
* @return
*/
public static String decrypt(String encryptStr) {
if (StringUtils.isBlank(encryptStr)){
return encryptStr;
}
return decrypt(encryptStr, KEY);
}
}
4.使用及测试方法
下面的代码可能没有覆盖实际测试的全部场景,核心测试除了基本的加解密的实现外,保证了以下全部场景测试没有问题:
(1)mapper中查询单个值,多个值,有@param没有@param,@param中的value值和参数值一致或不一致
(2)mapper参数中为实体对象,单个实体对象或对象的集合,参数为对象时有@param没有@param,@param中的value值和参数值一致或不一致
(3)mapper中参数同时有实体对象和单个字段的情况
(4)单条数据和多条数据
(5)list为singleList时(singleList不可重新修改赋值)
controller:
@RestController
@RequestMapping("/test")
public class TestController {
@Autowired
TTestWriteMapper testWriteMapper;
@Autowired
TTestReadMapper testReadMapper;
@PostMapping(value = "/insertAll")
public void insert(@RequestParam("num") Integer num, @RequestParam("isBatch") boolean isBatch) {
String phone = "15311078880";
Integer phoneInt = 1531107888;
List<Ttest> tests = new ArrayList<>();
if (isBatch) {
for (int i = 0; i < num; i++) {
Ttest test = new Ttest();
test.setPhone(phone);
test.setType("1");
test.setAreaCode("0086");
tests.add(test);
phone = phoneInt - 1 + "0";
phoneInt = phoneInt - 1;
}
testWriteMapper.batchInsert(tests);
} else {
for (int i = 0; i < num; i++) {
Ttest test = new Ttest();
test.setPhone(phone);
test.setType("1");
test.setAreaCode("0086");
tests.add(test);
testWriteMapper.insert(test);
phone = phoneInt - 1 + "0";
phoneInt = phoneInt - 1;
}
}
}
@GetMapping(value = "/selectByPage")
public Object selectByPage(int num, int count,boolean isPage) {
if (isPage){
PageHelper.startPage(num, count);
List<Ttest> test = testReadMapper.selectAll();
return new PageInfo<>(test);
}else {
List<Ttest> test = testReadMapper.selectAll();
return test;
}
}
@PostMapping(value = "/insert")
public void insert(@RequestBody Ttest testForm) {
testWriteMapper.insert(testForm);
}
@PostMapping(value = "/insertTest")
public void insertTest(@RequestBody Ttest testForm) {
testWriteMapper.insertTest(testForm);
}
@PostMapping(value = "/update")
public void update(@RequestBody Ttest testForms) {
testWriteMapper.updateByPrimaryKey(testForms);
}
@PostMapping(value = "/insertList")
public void insertList(@RequestBody List<Ttest> testForm) {
testWriteMapper.batchInsert(testForm);
}
@PostMapping(value = "/updateList")
public void updateList(@RequestBody List<Ttest> testForm) {
testWriteMapper.batchUpdate(testForm);
}
@GetMapping(value = "/selectOne")
public Ttest selectOne(@RequestParam("pkid") Integer pkid) {
Ttest test = testReadMapper.selectByPrimaryKey(pkid);
return test;
}
@GetMapping(value = "/selectAll")
public List<Ttest> selectAll() {
List<Ttest> test = testReadMapper.selectAll();
return test;
}
//查询一条反馈vo
@GetMapping(value = "/selectOneBySql")
public TestForm selectOneBySql(@RequestParam("pkid") Integer pkid) {
TestForm testForm = new TestForm();
testForm.setPkid(pkid);
testForm.setPhone("18333601111");
TestForm test = testReadMapper.selectOneBySql(testForm);
return test;
}
//查询返回volist
//查询返回vo,区号-手机号 区号-手机号逗号拼接 -通过
//查询phone -通过
//查询phonlist
@GetMapping(value = "/selectBySql")
public List<TestVo> selectBySql(TestForm form) {
form.setPhoneList(Collections.singletonList("15311072801"));
return testReadMapper.selectBySql("0086",form);
}
@GetMapping(value = "/selectByPhoneSql")
public List<TestVo> selectByPhoneSql(@RequestParam("phone") String phone) {
PageHelper.startPage(1, 2);
return testReadMapper.selectByPhoneSql(phone);
}
@GetMapping(value = "/selectByMap")
public List<TestVo> selectByMap(@RequestParam("phone") String phone) {
Map<String, Object> map = new HashMap<>();
map.put("phone", phone);
return testReadMapper.selectByMap(map);
}
@PostMapping(value = "/insertByListMap")
public int insertByListMap(@RequestBody List<Map<String, Object>> params) {
return testReadMapper.insertByListMap(params);
}
@GetMapping(value = "/selectByPhoneListSql")
public List<TestVo> selectByPhoneListSql(@RequestParam("phoneList") List<String> phones) {
List<String> phone = Collections.singletonList("15311112222");
return testReadMapper.selectByPhoneListSql(phone);
}
//param的情况,有其他不需要加密的参数
@GetMapping(value = "/selectByPhoneSql1")
public List<TestVo> selectByPhoneSql1(Integer pkid, String phone) {
return testReadMapper.selectByPhoneSql1(pkid, phone);
}
//param的情况,有其他不需要加密的参数
@GetMapping(value = "/selectByPhoneListSql1")
public List<TestVo> selectByPhoneListSql1(@RequestParam("pkids") List<Integer> pkids, @RequestParam("phoneList") List<String> phones) {
return testReadMapper.selectByPhoneListSql1(null,pkids, phones);
}
//param的情况,有其他不需要加密的参数
@GetMapping(value = "/selectByPhoneListSql2")
public List<TestVo> selectByPhoneListSql2(@RequestParam("pkids") List<Integer> pkids) {
return testReadMapper.selectByPhoneListSql1("0086",pkids, Collections.singletonList("15311112222"));
}
mapper:
public interface TTestReadMapper extends LandzMapper<Ttest> {
TestForm selectOneBySql(TestForm form);
List<TestVo> selectBySql(String areaCode,@Param("form") TestForm form);
List<TestVo> selectByPhoneSql(@SensitiveField@Param("phone") String phone);
List<TestVo> selectByPhoneSql1(@Param("pkid1") Integer pkid,String phone);
List<TestVo> selectByPhoneListSql(@SensitiveField @Param("phones") Collection<String> phoneList);
List<TestVo> selectByPhoneListSql1(String areaCode,@Param("pkids") List<Integer> pkids,@SensitiveField@Param("phoneList")List<String> phoneList);
List<TestVo> selectByMap(Map<String,Object> form);
int insertByListMap(List<Map<String,Object>> forms);
}
mapper.xml:
<select id="selectOneBySql" resultType="com.landz.module.requirement.request.TestForm">
select * from LANDZCMS.T_TEST t
<where>
<if test="pkid != null">
t.pkid = #{pkid}
</if>
<if test="phone != null and phone != ''">
and t.phone = #{phone}
</if>
</where>
</select>
<select id="selectBySql" resultType="com.landz.module.requirement.vo.TestVo">
select
wmsys.wm_concat( AREA_CODE|| '-' ||phone) areaPhones,
wm_concat(phone) phones
from LANDZCMS.T_TEST t
<where>
<if test="form.pkid != null">
t.pkid = ${form.pkid}
</if>
<if test="form.phone != null and form.phone != ''">
and t.phone = #{form.phone}
</if>
<if test="form.phoneList != null and form.phoneList.size()>0">
AND
<foreach collection="form.phoneList" index="index" item="item" open="(" separator=" or " close=")">
instr(phone,#{item})>0
</foreach>
</if>
<if test="areaCode != null and areaCode != ''">
and AREA_CODE = #{areaCode}
</if>
</where>
</select>
<select id="selectByPhoneSql" resultType="com.landz.module.requirement.vo.TestVo">
select * from LANDZCMS.T_TEST t
<where>
<if test="phone != null and phone != ''">
and t.phone = #{phone}
</if>
</where>
</select>
<select id="selectByPhoneListSql" resultType="com.landz.module.requirement.vo.TestVo">
select * from LANDZCMS.T_TEST t
<where>
<if test="phones != null and phones.size()>0">
AND phone in
<foreach collection="phones" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
</where>
</select>
<select id="selectByPhoneSql1" resultType="com.landz.module.requirement.vo.TestVo">
select * from LANDZCMS.T_TEST t
<where>
<if test="pkid1 != null">
t.pkid = #{pkid1}
</if>
<if test="phone != null and phone != ''">
and t.phone = #{phone}
</if>
</where>
</select>
<select id="selectByPhoneListSql1" resultType="com.landz.module.requirement.vo.TestVo">
select * from LANDZCMS.T_TEST t
<where>
<if test="pkids != null and pkids.size()>0">
AND pkid in
<foreach collection="pkids" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="phoneList != null and phoneList.size()>0">
AND phone in
<foreach collection="phoneList" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="areaCode != null and areaCode != ''">
and AREA_CODE = #{areaCode}
</if>
</where>
</select>
<select id="selectByMap" resultType="com.landz.module.requirement.vo.TestVo">
select * from LANDZCMS.T_TEST t
<where>
<if test="phone != null and phone != ''">
and t.phone = #{phone}
</if>
</where>
</select>
<insert id="insertByListMap">
INSERT INTO LANDZCMS.T_TEST t (pkid, phone, AREA_CODE)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.pkid}, #{item.phone}, #{item.areaCode})
</foreach>
</insert>
注解使用:
@Data
@SensitiveData
public class TestVo {
private Integer pkid;
@SensitiveField
private String phone;
@SensitiveField
private String areaPhones;
@SensitiveField
private String phones;
private String areaCode;
private String type;
}
@Data
@SensitiveData
public class TestForm {
private Integer pkid;
@SensitiveField
private String phone;
@SensitiveField
private List<String> phoneList;
private String areaCode;
private String type;
}
@SensitiveData
@Data
@Table(name = "LANDZCMS.T_TEST")
public class Ttest {
@Id
@Column(name = "PKID")
private Integer pkid;
@SensitiveField
@Column(name = "PHONE")
private String phone;
@Column(name = "AREA_CODE")
private String areaCode;
@Column(name = "TYPE")
private String type;
4.注意的点:
- 如果没有对dao方法的参数重命名,则mybatis会把
users
、list
、collection
(都是List)封装到一个ParamMap
中,并且list和collection与user指向同一个内存地址,即数据完全相同 -
如果使用了@Param("users")对dao层的方法参数进行了重命名,则mybatis会把list封装到一个ParamMap中,这个map中不会有list和collection,而是存入当前参数名users和param1的list(若有多个参数则继续 user2、param2…),如果重命名和参数名不一致这里判断是否有注解要注意下
-
如果入参为list,并且赋值为单个值Collections.singletonList时要注意重新赋值下,singletonList为不可变类型
4.防止重复加解密,这里通过位数来判断的,系统中需要加密的字段没有超过24位的
5.空值的处理
6.业务代码如果前面更新或者保存的时候把参数中手机号登信息加密了,后续业务逻辑又用到了这个值,但是已经是加密的值了,可能对业务有影响,这里需要特别注意一下。
参考链接:
mybatis 插件原理_mybatis插件原理-CSDN博客【Mybatis】基于Mybatis插件+注解,实现敏感数据自动加解密_注解实现 mybatis 字段加解密-CSDN博客