java 禁止执行删除sql_关于mybatis 动态 sql 的一些陷阱：防止批量update，delete，select......

我们的解决方案是针对3.0.6版本写了一个防止批量更新的插件。另外参照该插件，还可以写一些防止delete，select无limit 条数限制的插件。通过这些插件可以避免批量更新、delete操作以及无limit限制的select操作(防止查询整个表的所有记录，尤其是大表)。

用法：

(1)在MapperConfig.xml中定义插件

interceptor=" com.qunar.base.mybatis.ext.interceptor .BatchUpdateForbiddenPlugin">

(2)在mapper文件中修改update的动态sql

在update语句的最后面添加了[presentColumn="orderNo"]，表示解析后的where条件中必须带有orderNo。因为orderNo在业务中可以标识一条记录，因此where条件带有orderNo的话，就可以保证是单条更新，

而不是批量更新。

实例：不一样的地方是添加了[presentColumn="orderNo"]

update

ibtest.orders

set

status = #{currentStatus}

]]>

and orderNo = #{orderNo, jdbcType=VARCHAR}

and status = #{preStatus, jdbcType=INTEGER}

[presentColumn="orderNo"]

异常：

当解析后的update语句如果是批量更新的sql时，会直接抛异常：

org.apache.ibatis.exceptions.PersistenceException:

### Cause: java.lang.IllegalArgumentException:

该update语句:update ibtest.orders set status = ? WHERE status = ?

是批量更新sql，不允许执行。因为它的的where条件中未包含能表示主键的字段orderNo,所以会导致批量更新。

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:124)

at org.apache.ibatis.submitted.dynsql.nullparameter.DynSqlOrderTest.testDynamicSelectWithTypeHandler(DynSqlOrderTest.java:66)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)

at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)

at org.junit.runners.ParentRunner.run(ParentRunner.java:236)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: java.lang.IllegalArgumentException:

该update语句:update ibtest.orders set status = ? WHERE status = ?

是批量更新sql，不允许执行。因为它的的where条件中未包含能表示主键的字段orderNo,所以会导致批量更新。

at org.apache.ibatis.submitted.dynsql.nullparameter.BatchUpdateForbiddenPlugin.doCheckAndResetSQL(BatchUpdateForbiddenPlugin.java:132)

at org.apache.ibatis.submitted.dynsql.nullparameter.BatchUpdateForbiddenPlugin.checkAndResetSQL(BatchUpdateForbiddenPlugin.java:103)

at org.apache.ibatis.submitted.dynsql.nullparameter.BatchUpdateForbiddenPlugin.intercept(BatchUpdateForbiddenPlugin.java:65)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:42)

at $Proxy7.update(Unknown Source)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:122)

... 25 more

源码：

package com.qunar.base.mybatis.ext.interceptor ;

import java.util.Properties;

import org.apache.commons.lang.StringUtils;

import org.apache.ibatis.executor.Executor;

import org.apache.ibatis.mapping.BoundSql;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.mapping.MappedStatement.Builder;

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;

/**

*

* 禁止批量更新的插件,只允许更新单条记录

*

*

*

* mapper示例：必须在update语句的最后面定义[presentColumn="orderNo"]，其中orderNo是能标识orders表的主键(逻辑主键或者业务主键)

*

*

* update

* orders

* set

* status = #{currentStatus}

* ]]>

*

*

* and orderNo = #{orderNo, jdbcType=VARCHAR}

*

*

* and status = #{preStatus, jdbcType=INTEGER}

*

*

* [presentColumn="orderNo"]

*

*

*

* @author yi.chen@qunar.com

* @version 0.0.1

* @createTime 2012-04-03 18:25

*/

@Intercepts({ @Signature(type = Executor.class, method = "update", args = {

MappedStatement.class, Object.class }) })

public class BatchUpdateForbiddenPlugin implements Interceptor {

private final static String presentColumnTag = "presentColumn";// 定义where条件中必须出现的字段

/**

*

* 只对update语句进行拦截

*

*

* @see org.apache.ibatis.plugin.Interceptor#intercept(org.apache.ibatis.plugin

* .Invocation)

*/

public Object intercept(Invocation invocation) throws Throwable {

// 只拦截update

if (isUpdateMethod(invocation)) {

invocation.getArgs()[0] = checkAndResetSQL(invocation);

}

return invocation.proceed();

}

/**

*

* 判断该操作是否是update操作

*

*

* @param invocation

* @return 是否是update操作

*/

private boolean isUpdateMethod(Invocation invocation) {

if (invocation.getArgs()[0] instanceof MappedStatement) {

MappedStatement mappedStatement = (MappedStatement) invocation

.getArgs()[0];

return SqlCommandType.UPDATE.equals(mappedStatement

.getSqlCommandType());

}

return false;

}

/**

*

* 检查update语句中是否定义了presentColumn，并且删除presentColumn后重新设置update语句

*

*

* @param invocation

* invocation实例

* @return MappedStatement 返回删除presentColumn之后的MappedStatement实例

*/

private Object checkAndResetSQL(Invocation invocation) {

MappedStatement mappedStatement = (MappedStatement) invocation

.getArgs()[0];

Object parameter = invocation.getArgs()[1];

mappedStatement.getSqlSource().getBoundSql(parameter);

BoundSql boundSql = mappedStatement.getBoundSql(parameter);

String resetSql = doCheckAndResetSQL(boundSql.getSql());

return getMappedStatement(mappedStatement, boundSql, resetSql);

}

/**

*

* 检查update语句中是否定义了presentColumn，并且删除presentColumn后重新设置update语句

*

*

* @param sql

* mapper中定义的sql语句(带有presentColumn的定义)

* @return 删除presentColumn之后的sql

*/

private String doCheckAndResetSQL(String sql) {

if (sql.indexOf(presentColumnTag) > 0) {

// presentColumn的定义是否在sql的最后面

if (sql.indexOf("]") + 1 == sql.length()) {

int startIndex = sql.indexOf("[");

int endIndex = sql.indexOf("]");

String presentColumnText = sql.substring(startIndex,

endIndex + 1);// [presentColumn="orderNo"]

// 剔除标记逻辑主键相关内容之后的sql，该sql才是真正执行update的sql语句

sql = StringUtils.replace(sql, presentColumnText, "");

String[] subSqls = sql.toLowerCase().split("where");

String[] keyWords = presentColumnText.split("\"");

// 获取主键,比如orderNo

String keyWord = keyWords[1];

// 判断是否带有where条件并且在where条件中是否存在主键keyWord

if (subSqls.length == 2 && subSqls[1].indexOf(keyWord) == -1) {

throw new IllegalArgumentException("该update语句:" + sql

+ "是批量更新sql，不允许执行。因为它的的where条件中未包含能表示主键的字段"

+ keyWord + ",所以会导致批量更新。");

}

} else {

throw new IllegalArgumentException("[" + presentColumnTag

+ "=\"xxx\"\"]必须定义在update语句的最后面.");

}

} else {

throw new IllegalArgumentException("在mapper文件中定义的update语句必须包含"

+ presentColumnTag + "，它用于定义该sql的主键(逻辑主键或者业务主键)，比如id");

}

return sql;

}

/**

*

* 通过验证关键字段不能为空之后的sql重新构建mappedStatement

*

*

* @param mappedStatement

* 重新构造sql之前的mappedStatement实例

* @param boundSql

* 重新构造sql之前的boundSql实例

* @param resetSql

* 验证关键字段不能为空之后的sql

* @return 重新构造之后的mappedStatement实例

*/

private Object getMappedStatement(MappedStatement mappedStatement,

BoundSql boundSql, String resetSql) {

final BoundSql newBoundSql = new BoundSql(

mappedStatement.getConfiguration(), resetSql,

boundSql.getParameterMappings(), boundSql.getParameterObject());

Builder builder = new MappedStatement.Builder(

mappedStatement.getConfiguration(), mappedStatement.getId(),

new SqlSource() {

public BoundSql getBoundSql(Object parameterObject) {

return newBoundSql;

}

}, mappedStatement.getSqlCommandType());

builder.cache(mappedStatement.getCache());

builder.fetchSize(mappedStatement.getFetchSize());

builder.flushCacheRequired(mappedStatement.isFlushCacheRequired());

builder.keyGenerator(mappedStatement.getKeyGenerator());

builder.keyProperty(mappedStatement.getKeyProperty());

builder.resource(mappedStatement.getResource());

builder.resultMaps(mappedStatement.getResultMaps());

builder.resultSetType(mappedStatement.getResultSetType());

builder.statementType(mappedStatement.getStatementType());

builder.timeout(mappedStatement.getTimeout());

builder.useCache(mappedStatement.isUseCache());

return builder.build();

}

public Object plugin(Object target) {

return Plugin.wrap(target, this);

}

public void setProperties(Properties properties) {

}

}