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及参数值

代码部分：

拦截器基础：

配置：

<plugin interceptor="xxx.mybatis.plugin.PaginationInterceptor">
            <property name="dialect" value="oracle" />
            <property name="pageSqlId" value=".*ListPage.*" />
            <property name="exampleDialect" value="exampleOracle" />
            <property name="examplePageSqlId" value="selectByExample" />
        </plugin>

sqlMapper配置

<select id="getListPage" resultMap="BaseResultMap">
	 	select <include refid="Base_Column_List" />
	 	from syslog
	 	where 1=1
	 		<if test="directoryId != null and directoryId !='' ">
	    		and DIRECTORY_ID = #{directoryId}
	    	</if>
	    	<if test="operRemark != null and operRemark != '' ">
	    		and OPER_REMARK LIKE '%' || #{operRemark} || '%'  
	    	</if>

拦截器主体代码：

		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");

<span style="white-space:pre">		</span>//修改后的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.Entry<String, Object> entry : 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);
		
		List<String> replaceEscape=new ArrayList<String>();
		List<String> replaceFiled=new ArrayList<String>();
		
		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 '/' ");
		}
		//修改参数
		HashMap<String,Object> paramMab=(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及其参数值