Mybatis sql改写
近期有项目需要,需要把update转化成insert,网上搜索了下发现mybatis的拦截器可以实现该功能。
一、mybatis拦截器
实现一个拦截器拦截所有update方法
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.apache.commons.lang3.StringUtils;
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.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
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;
/**
* 实现接口Interceptor
* 加入注解 @Intercepts 该注解的使用要注意,其中的@Signature 有三个参数,具体是使用情搜索
**/
@Intercepts(@Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }))
public class UpdateToInsertInterceptor implements Interceptor{
// 为了在update转成insert时,主键重复的问题,这里去掉update时的自增主键
private List<String> excludeField = new ArrayList<String>(Arrays.asList("id"));
@Override
public Object intercept(Invocation invocation) throws Throwable {
//mapper.update(invocation.getArgs()[]);
// 拦截sql
Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
if (!isUpdate(statement)) {
return invocation.proceed();
}
Object parameterObject = args[1];
BoundSql boundSql = statement.getBoundSql(parameterObject);
String sql = boundSql.getSql();
if (StringUtils.isBlank(sql)) {
return invocation.proceed();
}
// update转换成insert
// sql交由处理类处理 对sql语句进行处理 此处是范例 不做任何处理
String tableName = findTable(sql);
List<String> paramNames = findParams(boundSql.getParameterMappings());
String sql2Reset = buildSql(tableName, paramNames);
// 包装sql后,重置到invocation中
resetSql2Invocation(invocation, sql2Reset);
return invocation.proceed();
}
public boolean isExcludeField(String fieldName) {
return excludeField.contains(fieldName.toLowerCase());
}
private String buildSql(String tableName, List<String> paramNames) {
StringBuilder sb = new StringBuilder("INSERT INTO ");
sb.append(tableName);
sb.append("(");
int excludeCounter = 0;
for (int i = 0; i < paramNames.size(); i++) {
String paramName = paramNames.get(i);
if (isExcludeField(paramName)) {
excludeCounter++;
break;
}
if (i == 0) {
sb.append(paramName);
} else {
sb.append(",").append(paramName);
}
}
sb.append(") values (");
int counter = paramNames.size() - excludeCounter;
for (int i = 0; i < counter; i++) {
if (i == 0) {
sb.append("?");
} else {
sb.append(",").append("?");
}
}
sb.append(")");
return sb.toString();
}
private List<String> findParams(List<ParameterMapping> list) {
List<String> params = new ArrayList<>();
for (ParameterMapping parameterMapping : list) {
params.add(parameterMapping.getProperty());
}
return params;
}
private String findTable(String sql) {
String tmp = sql.trim().toUpperCase();
String table = tmp.substring(tmp.indexOf("UPDATE") + 6, tmp.indexOf("SET")).trim();
return table;
}
private void resetSql2Invocation(Invocation invocation, String sql) throws SQLException {
final Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
Object parameterObject = args[1];
final BoundSql boundSql = statement.getBoundSql(parameterObject);
// 重新new一个查询语句对像
BoundSql newBoundSql = new BoundSql(statement.getConfiguration(), sql, boundSql.getParameterMappings(),
parameterObject);
// 把新的查询放到statement里
MappedStatement newStatement = copyFromMappedStatement(statement, new BoundSqlSqlSource(newBoundSql));
for (ParameterMapping mapping : boundSql.getParameterMappings()) {
String prop = mapping.getProperty();
if (isExcludeField(prop)) {
break;
}
if (boundSql.hasAdditionalParameter(prop)) {
newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
}
}
// 去除指定的列
newBoundSql.getParameterMappings().removeIf(e -> isExcludeField(e.getProperty().toLowerCase()));
args[0] = newStatement;
}
private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource,
ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
private boolean isUpdate(MappedStatement ms) {
SqlCommandType commondType = ms.getSqlCommandType();
if (commondType.compareTo(SqlCommandType.UPDATE) == 0) {
return true;
}
return false;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
// TODO Auto-generated method stub
}
class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
二、配置拦截器
在sqlsessionfactorybean 中配置拦截器
<!--3 会话工厂bean sqlSessionFactoryBean -->
<bean id="sqlSessionFactoryMybatis"
class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 数据源 -->
<property name="dataSource" ref="dataSource"></property>
<!-- 别名 -->
<property name="typeAliasesPackage" value="com.ck.demo.model"></property>
<!-- sql映射文件路径 -->
<property name="mapperLocations"
value="classpath:conf/mapper/*.xml"></property>
<property name="configLocation"
value="classpath:conf/db/mybatis-config.xml" />
<!-- 在这里加入sql拦截器 -->
<property name="plugins">
<list>
<bean
class="com.ck.demo.dao.interceptor.UpdateToInsertInterceptor"></bean>
</list>
</property>
</bean>
三、遗留问题
将update转换成insert会面临主键重复的问题,因此目标表上需要处理这个问题,可以考虑新增将自增主键一列去掉,用业务序列号来做主键。