MySQL拦截器获取xml id_MyBatis拦截器动态修改SQL语句及参数值（场景：查询中字段值中特殊字符自动转义）...

解决问题的场景：执行人名字中含有_ 的，在查询中用_或含有_ 的字段，列表中查不到信息

解决问题的思路：获取从MyBatis的mapper Xml文件中，找到原始的Sql语句(条件判断处理已好的SQL)，在含有Like的查询字段值里若包含特殊字符，则转义 ，并在查询条件后面添加 " ESCAPE '/'。处理后的Sql语句类似：select * from from  syslog where t like '%\_条件值%' " ESCAPE '/'  and  OPER_REMARK  like '%\_条件值%' " ESCAPE '/'

关键点：要获取原始SQL ,根据查询条件动态地修改SQL及参数值

代码部分：

拦截器基础：

配置：

sqlMapper配置

select

from syslog

where 1=1

and DIRECTORY_ID = #{directoryId}

and OPER_REMARK LIKE '%' || #{operRemark} || '%'

拦截器主体代码：

public Object intercept(Invocation invocation) throws Throwable {

StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY);

MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");

MetaObject metaMappedStatement = MetaObject.forObject(mappedStatement, DEFAULT_OBJECT_FACTORY2,DEFAULT_OBJECT_WRAPPER_FACTORY2);

//调用代码获取原始SQL并将其修改，参数值也

BoundSql boundSql = statementHandler.getBoundSql();//原始sql对象

// 修改参数值

SqlNode sqlNode = (SqlNode) metaMappedStatement

.getValue("sqlSource.rootSqlNode");

//修改后的SQL对象

boundSql = PaginationInterceptor.getBoundSql(mappedStatement.getConfiguration(),

boundSql.getParameterObject(), sqlNode);

获取SQL

public static BoundSql getBoundSql(Configuration configuration,Object parameterObject,SqlNode sqlNode) {

DynamicContext context = new DynamicContext(configuration, parameterObject);

//DynamicContext context = new DynamicContext(mappedStatement.getConfiguration(), boundSql.getParameterObject());

//mappedStatement.getSqlSource().

sqlNode.apply(context);

String countextSql=context.getSql();

//System.out.println("context.getSql():"+countextSql);

SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);

Class> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();

String sql=modifyLikeSql(countextSql, parameterObject);

SqlSource sqlSource = sqlSourceParser.parse(sql, parameterType, context.getBindings());

BoundSql boundSql = sqlSource.getBoundSql(parameterObject);

for (Map.Entryentry : context.getBindings().entrySet()) {

boundSql.setAdditionalParameter(entry.getKey(), entry.getValue());

}

return boundSql;

}修改参数值：

public static String modifyLikeSql(String sql,Object parameterObject)

{

if(parameterObject instanceof HashMap){

}else{

return sql;

}

if(!sql.toLowerCase().contains("like"))

return sql;

//sql=" and OPER_REMARK LIKE '%' || #{operRemark} || '%' \n " +"and OPER_U_NAME LIKE #{operUName} || '%' ";

//原始表达式：\s\w+\sLIKE\s('%'\s\|{2})?\s*(#\{\w+\})\s*(\|{2}\s*'%')

String reg="\\s\\w+\\sLIKE\\s*('%'\\s*\\|{2}\\s*)?(#\\{\\w+\\})(\\s*\\|{2}\\s*'%')?";//"order\\s+by\\s+.+"

Pattern pattern = Pattern.compile(reg,Pattern.CASE_INSENSITIVE);

Matcher matcher = pattern.matcher(sql);

ListreplaceEscape=new ArrayList();

ListreplaceFiled=new ArrayList();

while(matcher.find()){

replaceEscape.add(matcher.group());

int n = matcher.groupCount();

for (int i = 0; i <= n; i++)

{

String output = matcher.group(i);

if(2==i&&output!=null)

{

replaceFiled.add(output.trim());

}

}

}

//sql = matcher.replaceAll(reg+" 1111");

for(String s:replaceEscape)

{

sql=sql.replace(s, s+" ESCAPE '/' ");

}

//修改参数

HashMapparamMab=(HashMap)parameterObject;

for(String s:replaceFiled)

{

//sql=sql.replace(s, " ? ");

// #{operUName} -->operUName

String key=s.replace("#{", "").replace("}", "");

Object val =paramMab.get(key);

if(val!=null &&val instanceof String&&(val.toString().contains("%")||val.toString().contains("_")))

{

val=val.toString().replaceAll("%", "/%").replaceAll("_", "/_");

paramMab.put(key.toString(), val);

}

}

return sql;

}

源代码文件如下：

MyBatis拦截器分页与动态修改SQL及其参数值