引言：
因现在信息保密化规范越来越严格，需对数据库中多项涉及个人信息的数据进行加密
包含历史数据处理、对新数据插入、历史数据更新和查询解密显示等功能。
但因各系统代码规范不同，本着增加适用性少修改原有代码的原则采用mybatis拦截器方式满足需求。
适用环境：
此方案适用于mysql数据库，当然也可以简单修改适用其他类型数据库，适用于任何spring+mybatis项目，和多数据源的项目，单一数据源也适用。
1.准备工作
整理那些表那些字段需要加密，因为加密后字符长度会增大，和变成varchar格式同步整理修改字段类型和长度的脚本。这里要注意有些字段有没有索引，mysql有的索引不能超过某个长度，这里会跟加密后字符长度冲突，要评估一下是否去掉索引
最后会形成两个文件
1. 加密配置文件，需放到项目配置文件中。
示例：系统名-sm4config.properties 多系统的话多个配置文件可以根据系统名进行区分自动装配
#表名.字段名=是否加密
user_info.name=true
user_info.phone=true
loan_record.id_card=ture
loan_record.password=ture2.数据库结构修改文件
ALTER TABLE `user_info`
MODIFY COLUMN `name` varchar(200) COMMENT '用户名称' ,
MODIFY COLUMN `email` varchar(1000) COMMENT '电子邮箱地址' ;
2.通用加密工具类
为所有加密提供能力的类
//DefaultSM4TypeHandler 是自己封装的一个加密实现类，底层是hutool加密很简单这里不贴了。
@Component
public class SM4Handler extends DefaultSM4TypeHandler {
public static final Logger log = LoggerFactory.getLogger(SM4Handler.class);
private static final Charset charset = StandardCharsets.UTF_8;
//加密秘钥从配置中取
@Autowired
public void init(@Value("${sm4.key}") String selfKey) {
//如不需要存储到redis可以把redis这块去掉
String s = JedisTool.instance().get("redis.sm4.key");
if(null==s||"".equals(s)){
JedisTool.instance().set("redis.sm4.key",selfKey);
}
super.setCharset(charset);
super.setKEYS(JedisTool.instance().get("redis.sm4.key").getBytes(charset));
}
public SM4Handler() {
}
public static <T> T decryptData(T t) {
log.info("key:{}", JedisTool.instance().get("redis.sm4.key"));
String decryptData = DefaultSM4CryptoUtil.decrypt(JedisTool.instance().get("redis.sm4.key"), (String) t, charset);
return decryptData == null ? null : (T) decryptData;
}
public static String SM4encrypt( String parameter) {
return DefaultSM4CryptoUtil.encrypt(JedisTool.instance().get("redis.sm4.key"), parameter, charset);
}
public static String SM4decrypt(String parameter) {
return DefaultSM4CryptoUtil.decrypt(JedisTool.instance().get("redis.sm4.key"), parameter, charset);
}
//加解密示例
public static void main(String[] args) {
System.out.println(DefaultSM4CryptoUtil.encrypt("123456xxxxxcdefgh", "1101xxxxxxx0015", charset));
System.out.println(DefaultSM4CryptoUtil.encrypt("123456xxxxxcdefgh", "4213xxxxxx6210", charset));
System.out.println(DefaultSM4CryptoUtil.encrypt("123456xxxxxcdefgh", "1330xxxx0001", charset));
System.out.println(DefaultSM4CryptoUtil.encrypt("123456xxxxxcdefgh", "张三", charset));
System.out.println(DefaultSM4CryptoUtil.decrypt("123456xxxxxcdefgh", "nxf5mYJOIk1VH4aKeOsGexxxxxYcrcSBgaE17994Is=", charset));
}
}3.配置拦截器
拦截指定数据源的查询更新语句，这部分可以多数据源通用，并能处理复杂语句。注意，不能处理sql中函数处理过的字符串，比如sql中的脱敏等。示例：（CONCAT(LEFT (id_card,0),'**************' ,RIGHT(id_card,4))AS id_card）。因为解密是在查询结果得出后进行的，这里进行脱敏后得出的结果已经解不出来了。改造下这部分在代码里面进脱敏。
还有AS别名尽量规范 别乱起，如果有不匹配的 调整下即可。
还有处理mybatis自动生成的各个表的Example类作为查询条件有设计加密字段的，这里封装了一个反射自动装配的类来处理这个复杂类，只需要对所有的Example类里面的子类Criterion加上setValue方法即可
示例：
public void setValue(Object value) {
this.value=value;
}
import com.alibaba.fastjson.JSON;
import xxx.SM4Handler;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Intercepts({
@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})
})
@Slf4j
public class XXXSM4Interceptor implements Interceptor {
private final Map<String, List<String>> encryptFieldRules = new HashMap<>();
public XXXSM4Interceptor() {
loadEncryptionRules();
}
private void loadEncryptionRules() {
PropertiesConfiguration config;
try {
config = new PropertiesConfiguration("conf/xxx-sm4config.properties");
} catch (ConfigurationException e) {
throw new RuntimeException(e);
}
try {
for (Iterator<String> it = config.getKeys(); it.hasNext(); ) {
String keyObj = it.next();
String value = config.getProperty(keyObj).toString();
if ("true".equalsIgnoreCase(value)) {
String[] parts = keyObj.split("\\.");
if (parts.length == 2) {
String tableName = parts[0];
String fieldName = parts[1];
encryptFieldRules.computeIfAbsent(tableName, k -> new ArrayList<>()).add(fieldName);
}
}
}
} catch (Exception e) {
throw new RuntimeException("Failed to load encryption configuration.", e);
}
}
private Set<String> getTableNamesFromSql(String sql) {
// 创建一个集合来存储找到的表名
// 使用 LinkedHashSet 以保持插入顺序
Set<String> tables = new LinkedHashSet<>();
// 正则表达式用于匹配 FROM, JOIN, INSERT INTO 和 UPDATE 子句中的表名
// 这里考虑了可能存在的别名、模式名（如 schema.table）、子查询和反引号
String regex = "(?i)(?:FROM|JOIN|INTO|UPDATE)\\s+((?:`?\\w+`?\\.?`?\\w*`?\\s*(?:AS\\s*`?\\w+`?)?|\\(.*?\\)\\s*(?:AS\\s*`?\\w+`?)?)\\b)";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(sql);
while (matcher.find()) {
String tableWithAlias = matcher.group(1).trim();
// 去除可能的别名
if (tableWithAlias.contains(" AS ")) {
tableWithAlias = tableWithAlias.split(" AS ")[0].trim();
}
// 如果是子查询，则递归处理子查询
if (tableWithAlias.startsWith("(") && tableWithAlias.endsWith(")")) {
String subQuery = tableWithAlias.substring(1, tableWithAlias.length() - 1).trim();
tables.addAll(getTableNamesFromSql(subQuery));
} else {
// 去除反引号
tableWithAlias = tableWithAlias.replaceAll("`", "").trim();
// 去除模式名
if (tableWithAlias.contains(".")) {
tableWithAlias = tableWithAlias.substring(tableWithAlias.indexOf('.') + 1);
}
// 添加到集合中，集合会自动去重
tables.add(tableWithAlias);
}
}
return tables;
}
private Object processQueryResults(List<?> results, Set<String> tableNames) {
if (results.isEmpty()) {
return results;
}
Object firstResult = results.get(0);
if (firstResult instanceof Map) {
return processQueryResultsForMaps((List<Map<String, Object>>) results, tableNames);
} else if (firstResult instanceof List) {
return processQueryResultsForLists((List<?>) firstResult, tableNames);
} else {
return processQueryResultsForEntities(results, tableNames);
}
}
private Object processQueryResultsForMaps(List<Map<String, Object>> results, Set<String> tableNames) {
for (Map<String, Object> resultMap : results) {
for (String tableName : tableNames) {
List<String> encryptFields = encryptFieldRules.get(tableName);
if (encryptFields != null) {
for (String field : encryptFields) {
String alias = containsKeyIgnoreCaseAndUnderscore(resultMap,field) ? field : resultMap.keySet().stream()
.filter(k -> k.toLowerCase().contains(field.toLowerCase()))
.findFirst()
.orElse(null);
if (alias != null && containsKeyIgnoreCaseAndUnderscore(resultMap,alias)) {
String encryptedValue = (String) resultMap.get(alias);
String decryptedValue = decryptPassword(encryptedValue);
resultMap.put(alias, decryptedValue);
}
}
}
}
}
return results;
}
private Object processQueryResultsForLists(List<?> results, Set<String> tableNames) {
for (Object item : results) {
if (item instanceof Map) {
processQueryResultsForMaps(Collections.singletonList((Map<String, Object>) item), tableNames);
} else if (item instanceof List) {
processQueryResultsForLists((List<?>) item, tableNames);
} else {
processQueryResultsForEntities(Collections.singletonList(item), tableNames);
}
}
return results;
}
// 辅助方法：将下划线命名法转换为驼峰命名法
private static String toCamelCase(String fieldName) {
if (fieldName == null || fieldName.isEmpty()) {
return fieldName;
}
StringBuilder camelCaseName = new StringBuilder();
boolean nextUpperCase = false;
for (char c : fieldName.toCharArray()) {
if (c == '_') {
nextUpperCase = true;
} else if (nextUpperCase) {
camelCaseName.append(Character.toUpperCase(c));
nextUpperCase = false;
} else {
camelCaseName.append(c);
}
}
return camelCaseName.toString();
}
// 处理查询结果的方法
private Object processQueryResultsForEntities(List<?> results, Set<String> tableNames) {
for (Object entity : results) {
if (entity instanceof Map) {
continue; // 已经处理过了
}
for (String tableName : tableNames) {
List<String> encryptFields = encryptFieldRules.get(tableName);
if (encryptFields != null) {
for (String field : encryptFields) {
try {
// 规范化字段名
String camelCaseField = toCamelCase(field);
// 获取并设置字段值
Field f = entity.getClass().getDeclaredField(camelCaseField);
f.setAccessible(true);
String encryptedValue = (String) f.get(entity);
String decryptedValue = decryptPassword(encryptedValue);
f.set(entity, decryptedValue);
} catch (NoSuchFieldException | IllegalAccessException e) {
log.error("Error processing field decryption: {}", e.getMessage());
}
}
}
}
}
return results;
}
Object parameterAll ;
BoundSql boundSqlAll ;
@Override
public Object intercept(Invocation invocation) throws Throwable {
parameterAll = null;
boundSqlAll = null;
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameter = null;
if (invocation.getArgs().length > 1) {
parameter = invocation.getArgs()[1];
}
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
String sql = boundSql.getSql();
Set<String> tableNames = getTableNamesFromSql(sql); // 获取所有表名
// 对于更新操作，处理加密
if ("update".equals(invocation.getMethod().getName())) {
for (String tableName : tableNames) {
List<String> encryptFields = encryptFieldRules.get(tableName);
if (encryptFields != null) {
handleEncryption(parameter, encryptFields);
}
}
// 提取 WHERE 子句中的字段
Set<String> whereFields = extractFieldsFromWhereClause(sql);
if (whereFields != null && !whereFields.isEmpty()) {
for (String whereField : whereFields) {
for (String tableName : tableNames) {
List<String> encryptFields = encryptFieldRules.get(tableName);
if (encryptFields != null && containsIgnoreCaseAndUnderscore(encryptFields,whereField)) {
handleEncryptionForWhereClause(parameter, whereField, encryptFields);
}
}
}
}
}
// 对于查询操作，先处理WHERE条件加密，再执行查询，最后处理解密
if ("query".equals(invocation.getMethod().getName())) {
// 处理WHERE条件中的加密
processWhereConditions(boundSql, parameter, tableNames);
if(null!=parameterAll){
parameter=parameterAll;
invocation.getArgs()[1]=parameter;
invocation=new Invocation(invocation.getTarget(), invocation.getMethod(), invocation.getArgs());
}
// 执行查询
List<?> result = (List<?>) invocation.proceed();
// 处理解密
return processQueryResults(result, tableNames);
}
return invocation.proceed();
}
/**
* 处理下划线 忽略大小写对比字段
* @param s1
* @param s2
* @return
*/
public static boolean equalsIgnoreUnderscoreAndCase(String s1, String s2) {
if (s1 == null || s2 == null) {
return s1 == s2; // 如果两者都是null，则认为相等；如果一个是null另一个不是，则不相等
}
// 移除所有下划线（如果有的话），并转换为小写
String normalizedS1 = s1.replace("_", "").toLowerCase();
String normalizedS2 = s2.replace("_", "").toLowerCase();
// 比较处理后的字符串
return normalizedS1.equals(normalizedS2);
}
public static boolean containsIgnoreCaseAndUnderscore(List<String> list, String target) {
// 将目标字符串转换为小写并移除所有下划线
String normalizedTarget = target.replace("_", "").toLowerCase();
// 遍历列表中的每个元素，进行同样的转换后比较
for (String element : list) {
String normalizedElement = element.replace("_", "").toLowerCase();
if (normalizedElement.equals(normalizedTarget)) {
return true; // 如果找到匹配项，则返回true
}
}
return false; // 如果没有找到匹配项，则返回false
}
public static <K, V> boolean containsKeyIgnoreCaseAndUnderscore(Map<K, V> map, String key) {
if (key == null) {
return false; // 如果键是null，则直接返回false
}
// 将目标键转换为小写并移除所有下划线
String normalizedKey = key.replace("_", "").toLowerCase();
// 遍历map中的每个键，进行同样的转换后比较
for (K mapKey : map.keySet()) {
if (mapKey instanceof String) {
String mapKeyStr = (String) mapKey;
String normalizedMapKey = mapKeyStr.replace("_", "").toLowerCase();
if (normalizedMapKey.equals(normalizedKey)) {
return true; // 如果找到匹配项，则返回true
}
}
}
return false; // 如果没有找到匹配项，则返回false
}
private void handleEncryptionForWhereClause(Object parameter, String whereField, List<String> encryptFields) {
if (parameter instanceof Map) {
Map<String, Object> mapParameter = (Map<String, Object>) parameter;
if (containsKeyIgnoreCaseAndUnderscore(mapParameter,whereField)) {
String value = (String) mapParameter.get(whereField);
if (value != null) {
String encryptedValue = encryptPassword(value);
mapParameter.put(whereField, encryptedValue);
}
}
} else {
// 处理实体对象
for (String field : encryptFields) {
if (equalsIgnoreUnderscoreAndCase(field,whereField)) {
try {
// 规范化字段名
String camelCaseField = toCamelCase(field);
// 获取并设置字段值
Field f = parameter.getClass().getDeclaredField(camelCaseField);
f.setAccessible(true);
String value = (String) f.get(parameter);
if (value != null) {
String encryptedValue = encryptPassword(value);
f.set(parameter, encryptedValue);
}
} catch (NoSuchFieldException | IllegalAccessException e) {
log.error("Error processing field encryption in WHERE clause: {}", e.getMessage());
}
}
}
}
}
public Set<String> extractFieldsFromWhereClause(String sql) {
// 创建一个集合来存储找到的字段名
Set<String> fields = new LinkedHashSet<>();
// 正则表达式用于匹配 WHERE 子句中的字段名
String regex = "(?i)(?:WHERE|AND|OR)\\s+((?:`?\\w+`?\\.?`?\\w*`?|\\(.*?\\))(?=\\s*(?:=|<|>|LIKE|IN|BETWEEN|IS)))";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(sql);
while (matcher.find()) {
String fieldWithAlias = matcher.group(1).trim();
// 去除反引号
fieldWithAlias = fieldWithAlias.replaceAll("`", "").trim();
// 如果是子查询，则递归处理子查询
if (fieldWithAlias.startsWith("(") && fieldWithAlias.endsWith(")")) {
String subQuery = fieldWithAlias.substring(1, fieldWithAlias.length() - 1).trim();
fields.addAll(extractFieldsFromWhereClause(subQuery));
} else {
//取出引用名
String[] split = fieldWithAlias.split("\\.");
if(split.length>1){
fieldWithAlias=split[1];
}else {
fieldWithAlias=split[0];
}
// 添加到集合中，集合会自动去重
fields.add(fieldWithAlias);
}
}
return fields;
}
private void processWhereConditions(BoundSql boundSql, Object parameter, Set<String> tableNames ) {
// 获取SQL语句中的参数映射
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if (parameterMappings == null || parameterMappings.isEmpty()) {
return;
}
for (ParameterMapping mapping : parameterMappings) {
String propertyName = mapping.getProperty();
if (propertyName == null) continue;
for (String tableName : tableNames) {
List<String> encryptFields = encryptFieldRules.get(tableName);
if(null==encryptFields){
continue;
}
String jsonString = JSON.toJSONString(parameter);
if(jsonString.contains("oredCriteria")){
processExample(parameter,encryptFields,jsonString);
parameterAll=parameter;
return;
}
if (encryptFields != null && containsIgnoreCaseAndUnderscore(encryptFields,propertyName)) {
try {
if (parameter instanceof Map) {
// 如果是Map类型，直接从Map中获取并加密
Map<String, Object> params = (Map<String, Object>) parameter;
if (params.containsKey(propertyName)) {
String value = (String) params.get(propertyName);
if (value != null) {
String encryptedValue = encryptPassword(value);
params.put(propertyName, encryptedValue);
}
}
} else {
// 如果是实体类，使用反射获取并加密
// 规范化字段名
String camelCaseField = toCamelCase(propertyName);
// 获取并设置字段值
Field field = parameter.getClass().getDeclaredField(camelCaseField);
field.setAccessible(true);
String value = (String) field.get(parameter);
if (value != null) {
String encryptedValue = encryptPassword(value);
field.set(parameter, encryptedValue);
}
}
} catch (NoSuchFieldException | IllegalAccessException e) {
log.error("Error processing field encryption: {}", e.getMessage());
}
}
}
}
}
public void processExample(Object parameter, List<String> encryptFields, String jsonString) {
if (jsonString == null || !jsonString.contains("oredCriteria")) {
return;
}
// 获取 getOredCriteria 方法
Method getOredCriteriaMethod = findMethodByName(parameter.getClass(), "getOredCriteria");
if (getOredCriteriaMethod == null) {
log.info("未知的对象类型");
return;
}
try {
// 调用 getOredCriteria 方法
List<?> oredCriteria = (List<?>) getOredCriteriaMethod.invoke(parameter);
for (Object criteria : oredCriteria) {
// 获取 getAllCriteria 方法
Method getAllCriteriaMethod = findMethodByName(criteria.getClass(), "getAllCriteria");
if (getAllCriteriaMethod == null) {
continue;
}
// 调用 getAllCriteria 方法
List<?> allCriteria = (List<?>) getAllCriteriaMethod.invoke(criteria);
for (Object criterion : allCriteria) {
// 获取 getCondition 和 getValue 方法
Method getConditionMethod = findMethodByName(criterion.getClass(), "getCondition");
Method getValueMethod = findMethodByName(criterion.getClass(), "getValue");
Method setValueMethod = findMethodByName(criterion.getClass(), "setValue", Object.class);
Method getBetweenValue = findMethodByName(criterion.getClass(), "isBetweenValue");
Method getListValue = findMethodByName(criterion.getClass(), "isListValue");
Method getNoValue = findMethodByName(criterion.getClass(), "isNoValue");
Method getSingleValue = findMethodByName(criterion.getClass(), "isSingleValue");
if (getConditionMethod == null || getValueMethod == null || setValueMethod == null
|| getBetweenValue == null|| getListValue == null|| getNoValue == null|| getSingleValue == null) {
continue;
}
boolean getBetweenValueinvoke = (boolean) getBetweenValue.invoke(criterion);
boolean getListValueinvoke = (boolean) getListValue.invoke(criterion);
boolean getNoValueinvoke = (boolean) getNoValue.invoke(criterion);
boolean getSingleValueinvoke = (boolean) getSingleValue.invoke(criterion);
if(getBetweenValueinvoke||getListValueinvoke||getNoValueinvoke||getSingleValueinvoke==false){
continue;
}
// 获取条件并拆分
String condition = (String) getConditionMethod.invoke(criterion);
String[] parts = condition.split(" ");
if (parts != null && encryptFields.contains(parts[0])) {
// 获取原始值
Object originalValue = getValueMethod.invoke(criterion);
if (originalValue != null) {
// 加密值
String encryptedValue = encryptPassword(originalValue.toString());
// 设置加密后的值
setValueMethod.invoke(criterion, encryptedValue);
}
}
}
}
} catch (Exception e) {
log.error("处理 Example 对象时发生错误", e);
}
}
// 辅助方法：根据方法名查找方法
private <T> Method findMethodByName(Class<T> clazz, String methodName) {
for (Method method : clazz.getDeclaredMethods()) {
if (method.getName().equals(methodName)) {
method.setAccessible(true);
return method;
}
}
return null;
}
// 辅助方法：根据方法名和参数类型查找方法
private <T> Method findMethodByName(Class<T> clazz, String methodName, Class<?>... parameterTypes) {
try {
Method method = clazz.getMethod(methodName, parameterTypes);
method.setAccessible(true);
return method;
} catch (NoSuchMethodException e) {
return null;
}
}
private void handleEncryption(Object parameter, List<String> encryptFields) {
if (parameter instanceof Map) {
handleEncryption((Map<String, Object>) parameter, encryptFields);
} else {
// 处理实体对象
for (String field : encryptFields) {
try {
// 规范化字段名
String camelCaseField = toCamelCase(field);
// 获取并设置字段值
Field f = parameter.getClass().getDeclaredField(camelCaseField);
f.setAccessible(true);
String value = (String) f.get(parameter);
if (value != null) {
String encryptedValue = encryptPassword(value);
f.set(parameter, encryptedValue);
}
} catch (NoSuchFieldException | IllegalAccessException e) {
log.error("Error processing field encryption: {}", e.getMessage());
}
}
}
}
private void handleEncryption(Map<String, Object> params, List<String> encryptFields) {
for (String field : encryptFields) {
if (containsKeyIgnoreCaseAndUnderscore(params,field)) {
String value = (String) params.get(field);
if (value != null) {
String encryptedValue = encryptPassword(value);
params.put(field, encryptedValue);
}
}
}
}
private String encryptPassword(String password) {
// 加密逻辑
return SM4Handler.SM4encrypt(SM4Handler.SM4decrypt(password));
}
private String decryptPassword(String encryptedPassword) {
// 解密逻辑
return SM4Handler.SM4decrypt(encryptedPassword);
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
// 如果需要，可以从配置文件中读取属性
}
}4.数据源配置
数据源配置：
<!--XXX系统数据源-->
<bean id="dataSource6" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc.url"/>
<property name="username" value="jdbc.username"/>
<property name="password" value="jdbc.password"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="1"/>
<property name="minIdle" value="1"/>
<property name="maxTotal" value="20"/>
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWaitMillis" value="60000"/>
<!-- 验证查询 -->
<property name="validationQuery" value="SELECT 1"/>
<!-- 启用借用连接时的验证 -->
<property name="testOnBorrow" value="true"/>
<!-- 启用返回连接时的验证 -->
<property name="testOnReturn" value="true"/>
<!-- 启用空闲连接时的验证 -->
<property name="testWhileIdle" value="true"/>
<!-- 空闲连接验证周期 -->
<property name="timeBetweenEvictionRunsMillis" value="60000"/> <!-- 每60秒 -->
<!-- 最小可驱逐空闲时间 -->
<property name="minEvictableIdleTimeMillis" value="300000"/> <!-- 5分钟 -->
<!-- 每次驱逐运行时的测试次数 -->
<property name="numTestsPerEvictionRun" value="3"/>
</bean>sqlSessionFactory和事务处理器配置：
<bean id="transactionManager6" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource6" />
</bean>
<bean id="sqlSessionFactory6" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 加载mybatis的全局配置文件 -->
<property name="configLocation" value="classpath:conf/mybatis-config-xxx.xml" />
<!-- 数据库连接池 -->
<property name="dataSource" ref="dataSource6" />
<property name="mapperLocations">
<array>
<value>classpath:xxxmap/*.xml</value>
</array>
</property>
</bean>拦截器配置：到这个文件中，或者直接配置到这个文件引入的地方
conf/mybatis-config-xxx.xml（关于mybatis的部分配置可根据个人需求选择，只需关注 <plugins>标签里面配置的拦截器即可，也可以把这个拦截器配置在引入配置文件的地方，就少个配置文件了）
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置mybatis的缓存，延迟加载等等一系列属性 -->
<settings>
<!-- 全局映射器启用缓存 -->
<setting name="cacheEnabled" value="true"/>
<!-- 查询时，关闭关联对象即时加载以提高性能 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 -->
<setting name="multipleResultSetsEnabled" value="true"/>
<!-- 允许使用列标签代替列名 -->
<setting name="useColumnLabel" value="true"/>
<!-- 不允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 -->
<setting name="useGeneratedKeys" value="false"/>
<!-- 给予被嵌套的resultMap以字段-属性的映射支持 FULL,PARTIAL -->
<setting name="autoMappingBehavior" value="PARTIAL"/>
<!-- 对于批量更新操作缓存SQL以提高性能 BATCH,SIMPLE -->
<!-- <setting name="defaultExecutorType" value="BATCH" /> -->
<!-- 数据库超过25000秒仍未响应则超时 -->
<!-- <setting name="defaultStatementTimeout" value="25000" /> -->
<!-- Allows using RowBounds on nested statements -->
<setting name="safeRowBoundsEnabled" value="false"/>
<!-- Enables automatic mapping from classic database column names A_COLUMN to camel case classic Java property names aColumn. -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- MyBatis uses local cache to prevent circular references and speed up repeated nested queries. By default (SESSION) all queries executed during a session are cached. If localCacheScope=STATEMENT
local session will be used just for statement execution, no data will be shared between two different calls to the same SqlSession. -->
<setting name="localCacheScope" value="SESSION"/>
<!-- Specifies the JDBC type for null values when no specific JDBC type was provided for the parameter. Some drivers require specifying the column JDBC type but others work with generic values
like NULL, VARCHAR or OTHER. -->
<setting name="jdbcTypeForNull" value="OTHER"/>
<!-- Specifies which Object's methods trigger a lazy load -->
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
<!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<typeAliases>
<package name="com.xxx.xxx.xxx.model"/>
</typeAliases>
<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql" />
</plugin>
<plugin interceptor="com.xx.xx.xxx.xxx.XXXSM4Interceptor">
</plugin>
</plugins>
</configuration>此上，运行时的加解密自动处理就结束了，核心都在拦截器上，下面开始处理历史数据。
5.历史数据加密处理
入口类,多数据源的统一入口，通过传入xxx 库名来自动匹配去装配
示例链接：http://ip:8080/xx/xx-xxx-xxxx/sm4/sm4Encrypt/xx/100000/10000
其他信息字段 index 多少条之前加密 basename 要加密的数据库库名 number要加密多少条数据
默认一次性加密配置文件中所有要加密列的值，这块自己可以根据自己需求去改。
@Controller
@RequestMapping("/xx-xxx-xxxx/sm4")
public class SM4Controller {
@Resource
AllOldSM4Service allOldSM4Service;
@RequestMapping("/sm4Encrypt/{basename}/{index}/{number}")
@ResponseBody
public String sm4Encrypt(@PathVariable("basename") String basename,@PathVariable("index") String index,@PathVariable("number") String number){
JdbcTemplate jdbcTemplate=null;
if (basename.equals("xx")){
jdbcTemplate= new JdbcTemplate(SpringContextHolder.getBean("dataSourcexx"));
}else if (basename.equals("xxx")){
jdbcTemplate= new JdbcTemplate(SpringContextHolder.getBean("dataSourcexxx"));
}else if (basename.equals("xxxx")){
jdbcTemplate= new JdbcTemplate(SpringContextHolder.getBean("dataSourcexxxx"));
}
PropertiesConfiguration config;
HashMap<String, ArrayList<String>> encryptFieldRules = new HashMap<>();
try {
config = new PropertiesConfiguration("conf/"+basename+"-sm4config.properties");
} catch (ConfigurationException e) {
throw new RuntimeException(e);
}
try {
for (Iterator<String> it = config.getKeys(); it.hasNext(); ) {
String keyObj = it.next();
String value = config.getProperty(keyObj).toString();
if ("true".equalsIgnoreCase(value)) {
String[] parts = keyObj.split("\\.");
if (parts.length == 2) {
String tableName = parts[0];
String fieldName = parts[1];
encryptFieldRules.computeIfAbsent(tableName, k -> new ArrayList<>()).add(fieldName);
}
}
}
HashMap<String, String> infoMap=new HashMap<>();
infoMap.put("basename",basename);
infoMap.put("index",index);
infoMap.put("number",number);
allOldSM4Service.runAllSm4(encryptFieldRules,jdbcTemplate,infoMap);
} catch (Exception e) {
throw new RuntimeException("Failed to load encryption configuration.", e);
}
return "1";
}
@RequestMapping("/sm4Decrypt")
@ResponseBody
public String sm4Decrypt(){
return "1";
}
}加密能力提供业务层：通用
@Service
@Slf4j
public class AllOldSM4Service {
/**
*
* @param tableMap 表名为key 表要加密的字段为value
* @param jdbcTemplate 对应的数据库链接
* @param infoMap 其他信息字段 index 多少条之前加密 basename 要加密的数据库库名 number要加密多少条数据
*/
public void runAllSm4(HashMap<String, ArrayList<String>> tableMap, JdbcTemplate jdbcTemplate, HashMap<String, String> infoMap) {
Set<String> tables = tableMap.keySet();
for (String table : tables) {
// 读取表主键
String keyNameSql = "SELECT COLUMN_NAME " +
"FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " +
"WHERE TABLE_SCHEMA = '" + infoMap.get("basename") + "' " +
"AND TABLE_NAME = '" + table + "' " +
"AND CONSTRAINT_NAME = 'PRIMARY';";
List<Map<String, Object>> keyNameMaps = jdbcTemplate.queryForList(keyNameSql);
String keyName = String.valueOf(keyNameMaps.get(0).get("COLUMN_NAME"));
// 拼接需要加密的字段查询出未加密的值
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(keyName);
for (String s : tableMap.get(table)) {
stringBuffer.append("," + s);
}
String sqlTemplate = "SELECT %s FROM %s LIMIT %s,%s;";
String sql = "";
int number = Integer.parseInt(infoMap.get("number"));
List<Map<String, Object>> maps = null;
// 起始id
int index = 0;
while (true) {
if (number > 1000) {
sql = String.format(sqlTemplate, stringBuffer, table, index, 1000);
number = number - 1000;
} else {
sql = String.format(sqlTemplate, stringBuffer, table, index, number % 1000);
}
maps = jdbcTemplate.queryForList(sql);
for (Map<String, Object> map : maps) {
StringBuffer updateSQL = new StringBuffer("UPDATE ");
updateSQL.append(table).append(" SET ");
// 将 keySet 转换成 List
List<String> keysAsList = new ArrayList<>(map.keySet());
for (int i = 1; i < keysAsList.size(); i++) {
Object value = map.get(keysAsList.get(i));
if (value != null && !"".equals(value.toString())) { // 检查是否为null或空字符串
updateSQL.append(keysAsList.get(i)).append("= '")
.append(SM4Handler.SM4encrypt(SM4Handler.SM4decrypt(String.valueOf(value))))
.append("'");
} else {
updateSQL.append(keysAsList.get(i)).append("= NULL"); // 如果是null或者空字符串则设置为NULL
}
if (i < keysAsList.size() - 1) {
updateSQL.append(", ");
}
}
updateSQL.append(" WHERE ").append(keysAsList.get(0)).append(" = '")
.append(map.get(keysAsList.get(0))).append("'");
log.info("加密的sql：" + updateSQL);
jdbcTemplate.batchUpdate(updateSQL.toString());
}
if (index < Integer.parseInt(infoMap.get("index")) - 1 && Integer.parseInt(infoMap.get("index")) > 1000) {
index = index + 1000;
} else {
break;
}
}
}
}
}至此完毕，如你功能有特殊处理，可以私信我交流。
扫一扫
1191
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
