mybatis 的进阶使用(SpringBoot)
前言
不得不说,mybatis官网1文档写的比较详细了，某些具体的属性可以通过官网文档学习
字段加密
使用typeHandlers
类型处理器（typeHandlers). MyBatis 在设置预处理语句（PreparedStatement）中的参数或从结果集中取出一个值时， 都会用类型处理器将获取到的值以合适的方式转换成 Java 类型。所以可以利用这一操作做一些关于参数和返回值的自定义处理.比如将字段加密
代码
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(String.class)
public class EncryptionTypeHandler extends BaseTypeHandler<String> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {
String ens = EncryptionUtil.encrypt(s);
preparedStatement.setString(i, ens);
}
@Override
public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
return EncryptionUtil.decrypt(rs.getString(columnName));
}
@Override
public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return EncryptionUtil.decrypt(rs.getString(columnIndex));
}
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return EncryptionUtil.decrypt(cs.getString(columnIndex));
}
}
配置
// springboot项目可以在配置文件里配置typeHandlers的扫描包
mybatis.type-handlers-package: xxx
// 代码配置中也可以获取到typeHandlers
MybatisProperties.getConfiguration().getTypeHandlerRegistry().getTypeHandlers();
使用插件修改sql
MyBatis 允许你在映射语句执行过程中的某一点进行拦截调用。默认情况下，MyBatis 允许使用插件来拦截的方法调用包括：
-
Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
-
ParameterHandler (getParameterObject, setParameters)
-
ResultSetHandler (handleResultSets, handleOutputParameters)
-
StatementHandler (prepare, parameterize, batch, update, query)
代码
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args ={Connection.class, Integer.class})
})
@Component
public class MySingleTableEncryptionPlugin implements Interceptor {
// 这个是加密配置，里面是配置的要加密的表和字段
@Autowired
private EncryptionConfigure encryptionConfigure;
@Override
public Object intercept(Invocation invocation) throws ClassNotFoundException, InvocationTargetException, IllegalAccessException, JSQLParserException, NoSuchFieldException {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement =
(MappedStatement) metaObject.getValue("delegate.mappedStatement");
// 这个id是获取到的是文件标识，我们系统对于不同路径文件做了不同处理
String id = mappedStatement.getId();
String sql = boundSql.getSql();
// 如果判断到该路径无需加密就不进行操作
if(!needEncryptFromMapperPath(id)){
return invocation.proceed();
}
// 利用三方库重新解析SQL，将解析出来的SQL进行调整
Statement statement = CCJSqlParserUtil.parse(sql);
String newSql = null;
if (statement instanceof Select) {
Select select = (Select) statement;
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
String tableName = plainSelect.getFromItem().toString().replaceAll("`", "");
Set<String> encryptColumns = encryptionConfigure.getTableInfos().get(tableName);
if (CollectionUtils.isNotEmpty(encryptColumns)){
newSql = SingleSqlParseUtils.encryptSelect(select, encryptColumns);
}
} else if (statement instanceof Update) {
Update update = (Update) statement;
String tableName = update.getTable().getName().replaceAll("`", "");
Set<String> encryptColumns = encryptionConfigure.getTableInfos().get(tableName);
if (CollectionUtils.isNotEmpty(encryptColumns)){
newSql = SingleSqlParseUtils.encryptUpdate(update, encryptColumns);
}
} else if (statement instanceof Delete) {
Delete delete = (Delete) statement;
String tableName = delete.getTable().getName().replaceAll("`", "");
Set<String> encryptColumns = encryptionConfigure.getTableInfos().get(tableName);
if (CollectionUtils.isNotEmpty(encryptColumns)){
newSql = SingleSqlParseUtils.encryptDelete(delete, encryptColumns);
}
} else if (statement instanceof Insert){
Insert insert = (Insert) statement;
String tableName = insert.getTable().getName().replaceAll("`", "");
Set<String> encryptColumns = encryptionConfigure.getTableInfos().get(tableName);
if (CollectionUtils.isNotEmpty(encryptColumns)){
newSql = SingleSqlParseUtils.encryptInsert(insert, encryptColumns);
}
}
if(StringUtils.isNotBlank(newSql)){
Class<BoundSql> boundSqlClass = BoundSql.class;
Field sqlField = boundSqlClass.getDeclaredField("sql");
sqlField.setAccessible(true);
sqlField.set(boundSql,newSql);
}
return invocation.proceed();
}
/**
* 是否需要重写SQL加密字段,只加密基础生成的SQL
*
* @param id
* @return
*/
private boolean needEncryptFromMapperPath(String id) throws ClassNotFoundException {
return true;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
}
@Component
@ConfigurationProperties(prefix = "encryption")
@ToString
public class EncryptionConfigure implements Serializable {
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public Map<String, Set<String>> getTableInfos() {
return tableInfos;
}
/**加密盐*/
private String salt;
/**需要加密的表以及表的字段集合*/
private Map<String, Set<String>> tableInfos;
// 这里是因为方便本地开发想使用本地环境变量的方式注入，不会自动解析json，所以按照json字符串注入自己解析
public void setTableInfos(String s){
Map<String, Set<String>> stringSetMap = new JsonUtil().readValue(s, new TypeReference<Map<String, Set<String>>>() {
});
this.tableInfos = stringSetMap;
}
}
public class SingleSqlParseUtils {
public static String encryptSelect(Select sql,Set<String> encryptColumns) {
boolean isUpdated = false;
PlainSelect plainSelect = (PlainSelect) sql.getSelectBody();
List<SelectItem> selectItems = plainSelect.getSelectItems();
for (SelectItem selectItem : selectItems) {
if (selectItem instanceof SelectExpressionItem) {
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
Expression expression = selectExpressionItem.getExpression();
if (expression instanceof Column && encryptColumns.contains(((Column) expression).getColumnName().replaceAll("`",""))) {
if(Objects.isNull(selectExpressionItem.getAlias())){
selectExpressionItem.setAlias(new Alias(((Column) expression).getColumnName()));
}
// 找到需要添加函数的列
Function function = new Function();
function.setName("func_decrypt");
function.setParameters(new ExpressionList(Collections.singletonList(expression)));
selectExpressionItem.setExpression(function);
isUpdated = true;
}
}
}
Expression where = plainSelect.getWhere();
if(!Objects.isNull(where)){
// 构造一个 Visitor 对象，修改指定条件的值
WhereValueUpdater whereValueUpdater = new WhereValueUpdater(encryptColumns);
where.accept(whereValueUpdater);
isUpdated = isUpdated || whereValueUpdater.isUpdated();
}
return isUpdated ? sql.toString() : null;
}
public static String encryptDelete(Delete sql,Set<String> encryptColumns) {
boolean isUpdated = false;
Expression where = sql.getWhere();
if(!Objects.isNull(where)){
// 构造一个 Visitor 对象，修改指定条件的值
WhereValueUpdater whereValueUpdater = new WhereValueUpdater(encryptColumns);
where.accept(whereValueUpdater);
isUpdated = isUpdated || whereValueUpdater.isUpdated();
}
return isUpdated ? sql.toString() : null;
}
public static String encryptUpdate(Update sql, Set<String> encryptColumns) {
boolean isUpdated = false;
ArrayList<UpdateSet> updateSets = sql.getUpdateSets();
for (UpdateSet updateSet : updateSets) {
ArrayList<Column> columns = updateSet.getColumns();
ArrayList<Expression> expressions = updateSet.getExpressions();
for (int i = 0; i < columns.size(); i++) {
Column column = columns.get(i);
if(encryptColumns.contains(column.getColumnName().replaceAll("`",""))){
Expression expression = expressions.get(i);
Function function = new Function();
function.setName("func_encrypt");
if (expression instanceof StringValue || expression instanceof JdbcParameter) {
function.setParameters(new ExpressionList(Collections.singletonList(expression)));
}
expressions.set(i,function);
isUpdated = true;
}
}
}
Expression where = sql.getWhere();
if(!Objects.isNull(where)){
// 构造一个 Visitor 对象，修改指定条件的值
WhereValueUpdater whereValueUpdater = new WhereValueUpdater(encryptColumns);
where.accept(whereValueUpdater);
isUpdated = isUpdated || whereValueUpdater.isUpdated();
}
return isUpdated ? sql.toString() : null;
}
public static String encryptInsert(Insert sql,Set<String> encryptColumns) {
boolean isUpdated = false;
List<Column> columns = sql.getColumns();
List<Expression> valueList = sql.getItemsList(ExpressionList.class).getExpressions();
Expression expression = valueList.get(0);
if (expression instanceof RowConstructor){
for (int i = 0; i <columns.size(); i++) {
if(encryptColumns.contains(columns.get(i).getColumnName().replaceAll("`",""))){
for (Expression valuesExpression : valueList) {
ExpressionList exprList = ((RowConstructor) valuesExpression).getExprList();
List<Expression> expressions = exprList.getExpressions();
Function function = new Function();
function.setName("func_encrypt");
function.setParameters(new ExpressionList(Collections.singletonList(expressions.get(i))));
expressions.set(i,function);
}
isUpdated = true;
}
}
}else{
for (int i = 0; i <columns.size(); i++) {
if(encryptColumns.contains(columns.get(i).getColumnName().replaceAll("`",""))){
Function function = new Function();
function.setName("func_encrypt");
function.setParameters(new ExpressionList(Collections.singletonList(valueList.get(i))));
valueList.set(i,function);
}
isUpdated = true;
}
}
return isUpdated ? sql.toString() : null;
}
}
class WhereValueUpdater extends ExpressionVisitorAdapter {
private Set<String> encryptColumns;
private Boolean updatedFlag = false;
public Boolean isUpdated() {
return this.updatedFlag;
}
public WhereValueUpdater(Set<String> encryptColumns){
this.encryptColumns = encryptColumns;
}
@Override
public void visit(InExpression inExpression) {
// 如果当前条件为 InExpression 类型，则进行修改
if (inExpression.getLeftExpression() instanceof Column && encryptColumns.contains(((Column) inExpression.getLeftExpression()).getColumnName().replaceAll("`",""))) {
// 获取当前 InExpression 中的值列表
List<Expression> expressions = ((ExpressionList) inExpression.getRightItemsList()).getExpressions();
// 构造一个新的值列表，修改其中的值
List<Expression> modifiedExpressions = expressions.stream()
.map(expression -> {
Function function = new Function();
function.setName("func_encrypt");
if (expression instanceof StringValue || expression instanceof JdbcParameter) {
function.setParameters(new ExpressionList(Collections.singletonList(expression)));
}
return function;
}).collect(Collectors.toList());
// 构造一个新的 InExpression 对象，替换原有的 InExpression
inExpression.setRightItemsList(new ExpressionList(modifiedExpressions));
this.updatedFlag = true;
}
}
@Override
public void visit(EqualsTo equalsTo) {
if (equalsTo.getLeftExpression() instanceof Column && encryptColumns.contains(((Column) equalsTo.getLeftExpression()).getColumnName().replaceAll("`",""))) {
Expression rightExpression = equalsTo.getRightExpression();
Function function = new Function();
function.setName("func_encrypt");
if (rightExpression instanceof StringValue || rightExpression instanceof JdbcParameter) {
function.setParameters(new ExpressionList(Collections.singletonList(rightExpression)));
}
equalsTo.setRightExpression(function);
this.updatedFlag = true;
}
}
}
配置
上述代码中的@Intercepts 已经申明为mybatis插件，一般不需要额外配置，但是!，如果有自定义DataSource的，重写了SqlSessionFactory，就需要手动将插件添加到mybatis的配置中
@Primary
@Bean("sqlSessionFactory")
public SqlSessionFactory japptrSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource, @Qualifier("dataSourceProperties") MybatisProperties properties) throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
org.apache.ibatis.session.Configuration configuration = properties.getConfiguration();
configuration.addInterceptor(mySingleTableEncryptionPlugin);
Collection<TypeHandler<?>> typeHandlers = configuration.getTypeHandlerRegistry().getTypeHandlers();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setConfiguration(properties.getConfiguration());
sqlSessionFactory.setMapperLocations(properties.resolveMapperLocations());
return sqlSessionFactory.getObject();
}
替换参数
我们系统中较多使用mybatis-generator生成代码，关于某个值的修改有些情况下会使用到selectByPrimaryKey后直接updateByPrimaryKeySelective，就导致数据库内的update_time字段即使设置了根据时间戳更新依然不执行更新操作。考虑后打算在要执行更新update_time的时候去修改时间为当前时间进行兜底。方式是上面使用的插件
使用插件修改参数
代码
@Intercepts({
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class,
Object.class})
})
@Component
public class MybatisUpdateTimeIntercepter implements Interceptor {
@Autowired
private EncryptionConfigure encryptionConfigure;
@Override
public Object intercept(Invocation invocation) throws InvocationTargetException, IllegalAccessException,ClassNotFoundException{
MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
String id = mappedStatement.getId();
if(!needUpdateTimeMapperPath(id)){
return invocation.proceed();
}
if (SqlCommandType.UPDATE.equals(sqlCommandType)) {
Object[] args = invocation.getArgs();
for (int i = 1; i < args.length; i++) {
Class<?> clazz = args[i].getClass();
updateField(clazz.getDeclaredFields(),args[i]);
}
} else if (SqlCommandType.INSERT.equals(sqlCommandType)){
Object[] args = invocation.getArgs();
for (int i = 1; i < args.length; i++) {
Class<?> clazz = args[i].getClass();
updateField(clazz.getDeclaredFields(),args[i]);
}
}
return invocation.proceed();
}
private void updateField(Field[] declaredFields,Object parameter) throws IllegalAccessException {
for (Field field: declaredFields){
if (field.getName().equals("updateTime")){
field.setAccessible(true);
if(Objects.isNull(field.get(parameter))){
return;
}
if(Date.class.isAssignableFrom(field.getType())){
field.set(parameter,new Date());
}else if (LocalDateTime.class.isAssignableFrom(field.getType())){
field.set(parameter,LocalDateTime.now());
}else if (LocalDate.class.isAssignableFrom(field.getType())){
field.set(parameter,LocalDate.now());
}else if (Long.class.isAssignableFrom(field.getType())){
field.set(parameter,new Date().getTime());
}
}
}
}
/**
* 是否需要重写SQL,只重写基础生成的SQL
*
* @param id
* @return
*/
private boolean needUpdateTimeMapperPath(String id) throws ClassNotFoundException {
return true;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
}
配置
像上面的插件一样，代码中的@Intercepts 已经申明为mybatis插件，一般不需要额外配置，但是!，如果有自定义DataSource的，重写了SqlSessionFactory，就需要手动将插件添加到mybatis的配置中
@Primary
@Bean("sqlSessionFactory")
public SqlSessionFactory japptrSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource, @Qualifier("dataSourceProperties") MybatisProperties properties) throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
org.apache.ibatis.session.Configuration configuration = properties.getConfiguration();
configuration.addInterceptor(mybatisUpdateTimeIntercepter);
Collection<TypeHandler<?>> typeHandlers = configuration.getTypeHandlerRegistry().getTypeHandlers();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setConfiguration(properties.getConfiguration());
sqlSessionFactory.setMapperLocations(properties.resolveMapperLocations());
return sqlSessionFactory.getObject();
}
注意
另外，这两个插件原先打算是合二为一的，但是增强不同方法的插件获取到的实体不一样，在prepare时获取不到参数值，所以写了另一个update的插件。不排除是作者太菜，时间有限没有具体的查看文档，直接选了一个省时的写法
https://mybatis.org/mybatis-3/zh_CN/sqlmap-xml.html ↩︎
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
