mybatis各种查询 Plugins入库字段数据加密查询解密

INSERT INTO `ry`.`sys_user`(`user_id`, `dept_id`, `login_name`, `user_name`, `user_type`, `email`, `phonenumber`, `sex`, `avatar`, `password`, `salt`, `status`, `del_flag`, `login_ip`, `login_date`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, 103, 'admin', 'eHl4', '00', 'ry@163.com', '15888888888', '1', '', '29c67a30398638269fe600f73a054934', '111111', '0', '0', '127.0.0.1', '2024-02-28 17:49:51', 'admin', '2018-03-16 11:33:00', 'ry', '2024-02-28 17:49:51', '管理员');
INSERT INTO `ry`.`sys_user`(`user_id`, `dept_id`, `login_name`, `user_name`, `user_type`, `email`, `phonenumber`, `sex`, `avatar`, `password`, `salt`, `status`, `del_flag`, `login_ip`, `login_date`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, 105, 'ry', 'eHl4', '00', 'ry@qq.com', '15666666666', '1', '', '8e6d98b90472783cc73c17047ddccf36', '222222', '0', '0', '127.0.0.1', '2018-03-16 11:33:00', 'admin', '2018-03-16 11:33:00', 'ry', '2018-03-16 11:33:00', '测试员');

数据库该字段数据配置好base64加密的数据或者先注释掉DecryptionTypeHandler方便测试
配置注册插件二选一。

mybatis:
  configuration:
    plugins:
      - com.example.EncryptionPlugin
      
@Configuration
public class MyBatisConfig{
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception
{
    String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
    String mapperLocations = env.getProperty("mybatis.mapperLocations");
    String configLocation = env.getProperty("mybatis.configLocation");
    typeAliasesPackage = setTypeAliasesPackage(typeAliasesPackage);
    VFS.addImplClass(SpringBootVFS.class);

    final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(dataSource);
    sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
    sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
    sessionFactory.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
    // 注册插件
    sessionFactory.setPlugins(new Interceptor[]{new EncryptionPlugin()});
    return sessionFactory.getObject();
  }
}

拦截执行sql

import java.nio.charset.StandardCharsets;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Base64;
import java.util.Properties;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
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.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import com.ruoyi.framework.aspectj.lang.annotation.EncryptedField;
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})})
public class EncryptionPlugin  implements Interceptor {

	// @Autowired
	// private EncryptionService encryptionService;

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		Object target = invocation.getTarget();
		if (target instanceof Executor) {
			MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
			System.out.println(ms);
			Object parameter = invocation.getArgs()[1];

			// 插入或更新操作时，加密字段
			MetaObject metaParam = SystemMetaObject.forObject(parameter);
			Class<?> paramClass = parameter.getClass();
			Arrays.stream(paramClass.getDeclaredFields())
					.filter(field -> field.isAnnotationPresent(EncryptedField.class)).forEach(field -> {
						field.setAccessible(true);
						String originalValue = (String) metaParam.getValue(field.getName());
						if (originalValue != null) {
							EncryptedField annotation = field.getAnnotation(EncryptedField.class);
							System.out.println(annotation.value());
							switch (annotation.value()) {
		                     case USER_NAME: 
		                    	 //base64加密
		                    	 String encrypted = Base64.getEncoder().encodeToString(originalValue.getBytes(StandardCharsets.UTF_8));
								 //解密base64 
		                    	 //new String(Base64.getDecoder().decode("eHl4"), StandardCharsets.UTF_8)
		                    	 metaParam.setValue(field.getName(), encrypted);
		                         break;
		                   }	
						}
					});
		} else if (target instanceof ResultSetHandler) {
//		        Statement stmt = (Statement) invocation.getArgs()[0];
//		        // 获取结果集
//		        ResultSet rs = stmt.getResultSet();
//		        // 假设我们知道哪些列需要解密
//		        while (rs.next()) {
//		            // 获取列名
//		            ResultSetMetaData metaData = rs.getMetaData();
//		            for (int i = 1; i <= metaData.getColumnCount(); i++) {
//		                String columnName = metaData.getColumnName(i);
//	                    String encryptedValue = rs.getString(columnName);
//		                System.out.println(columnName+"\t"+encryptedValue);
// 			        	rs.updateString(columnName, decryptedValue);
//
//		        }
//			}
		}
		return invocation.proceed();
	}

	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	@Override
	public void setProperties(Properties properties) {
	}
}

字段注解以及使用

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface EncryptedField {

	Types value() default Types.DEFAULT;

	enum Types {
		PHONE_NUMBER, ID_CARD, DEFAULT,USER_NAME
	}
}

@EncryptedField(Types.USER_NAME)
private String userName;

上述字段解密

//配置加载转换器在MyBatisConfig中
@Bean
public TypeHandlerRegistry typeHandlerRegistry() {
    TypeHandlerRegistry registry = new TypeHandlerRegistry();
    registry.register(String.class, DecryptionTypeHandler.class);
    return registry;
}

这里通过mapper.xml配置拦截的字段

<resultMap type="User" id="UserResult">
		<id     property="userId"       column="user_id"      />
		<result property="deptId"       column="dept_id"      />
		<result property="userName"     column="user_name"    typeHandler="com.ruoyi.framework.config.DecryptionTypeHandler" />
</resultMap>

解密转换器

import java.nio.charset.StandardCharsets;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Base64;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
//针对user_name解密。如果多个字段那就要配置多个BaseTypeHandler
public class DecryptionTypeHandler extends  BaseTypeHandler<String> {
    //@Autowired
	//private EncryptionService encryptionService;

    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        // 在这里实现加密逻辑
        String encryptedValue = Base64.getEncoder().encodeToString(parameter.getBytes(StandardCharsets.UTF_8));
        ps.setString(i, encryptedValue);
    }

    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        // 在这里实现解密逻辑
        String encryptedValue = rs.getString(columnName);
        return new String(Base64.getDecoder().decode(encryptedValue), StandardCharsets.UTF_8);
    }

    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        // 在这里实现解密逻辑
        String encryptedValue = rs.getString(columnIndex);
        return new String(Base64.getDecoder().decode(encryptedValue), StandardCharsets.UTF_8);
    }

    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        // 在这里实现解密逻辑
        String encryptedValue = cs.getString(columnIndex);
        return new String(Base64.getDecoder().decode(encryptedValue), StandardCharsets.UTF_8);
    }
}

批量更新对每行数据通过连接多个id

<mapper namespace="com.example.mapper.PersonMapper">
   <update id="batchUpdate">
        UPDATE person
        SET name = CASE
       <foreach collection="list" item="person" separator=" ">
            WHEN id = #{person.id} THEN CONCAT_WS(' ', #{person.name}, #{person.age}, #{person.city})
        </foreach>
        END
        WHERE id IN
       <foreach collection="list" item="person" open="(" separator="," close=")">
            #{person.id}
        </foreach>
    </update>
</mapper>