慷慨是友谊的精华——王尔德
今天发现Mybatis-Plus
在h2
下,同时使用UpdateWrapper
和entity
会出现
update 表名 set 字段1=xxx,字段1=xxx
这样的sql
,在mysql
下是正确的语法,h2
会抛出异常
所以写了个mybatis
拦截器,放在了streampark
里:
pr
地址:https://github.com/streamxhub/streampark/pull/1493
源码:
代码语言:javascript复制/*
* Copyright 2019 The StreamX Project
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.streamxhub.streamx.console.base.mybatis.interceptor;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
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 java.sql.Connection;
import java.util.Iterator;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import java.util.stream.Collectors;
/**
* the mybatis interceptor for update/insert/delete in h2
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class H2SQLPrepareInterceptor extends JsqlParserSupport implements Interceptor {
@Override
public Object intercept(final Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(statementHandler);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.UPDATE) {
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
mpBs.sql(parserMulti(mpBs.sql(), boundSql));
}
return invocation.proceed();
}
@Override
public Object plugin(final Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(final Properties properties) {
}
/**
* 更新
*
* @param update 更新对象
* @param index 下标
* @param sql sql
* @param obj 额外参数
*/
@Override
protected void processUpdate(Update update, int index, String sql, Object obj) {
if (obj instanceof BoundSql) {
BoundSql boundSql = (BoundSql) obj;
Object parameterObject = boundSql.getParameterObject();
if (parameterObject instanceof MapperMethod.ParamMap<?>) {
MapperMethod.ParamMap<?> paramMap = (MapperMethod.ParamMap<?>) parameterObject;
Object entity = paramMap.get(Constants.ENTITY);
if (Objects.nonNull(entity) && paramMap.containsKey(Constants.WRAPPER)) {
TableInfo tableInfo = TableInfoHelper.getTableInfo(entity.getClass());
Map<String, String> columnPropertyMap = tableInfo.getFieldList().stream().collect(Collectors.toMap(TableFieldInfo::getColumn, TableFieldInfo::getProperty));
// 去重set片段中的列
Map<String, Long> columnNameCountMap = update.getUpdateSets().stream().flatMap(set -> set.getColumns().stream())
.collect(Collectors.groupingBy(Column::getColumnName, Collectors.counting()));
columnNameCountMap.forEach((column, times) -> {
for (long i = 1L; i < times; ) {
Iterator<UpdateSet> updateSetIterator = update.getUpdateSets().iterator();
while (((Iterator<?>) updateSetIterator).hasNext()) {
UpdateSet updateSet = updateSetIterator.next();
if (updateSet.getColumns().stream().anyMatch(c -> c.getColumnName().equals(column))) {
updateSetIterator.remove();
break;
}
}
Iterator<ParameterMapping> parameterMappingIterator = boundSql.getParameterMappings().iterator();
while (parameterMappingIterator.hasNext()) {
ParameterMapping parameterMapping = parameterMappingIterator.next();
String property = columnPropertyMap.get(column);
if (Objects.nonNull(property) && parameterMapping.getProperty().equals(Constants.ENTITY_DOT property)) {
parameterMappingIterator.remove();
break;
}
}
columnNameCountMap.put(column, times--);
}
});
}
}
}
}
}
解决思路本来是打算set
实体类里的属性为null
,但是没生效,因为已经生成sql
和占位符了
最后:
- 将
sql
使用jsqlparser
操作,获取到set
部分的列,进行去重处理 - 然后将对应
boundSql.getParameterMappings()
里包含重复的属性删掉
效果还是很不错的,原先不兼容的update(entity,updateWrapper)
设置重复属性,兼容了h2