首页 > 代码库 > MyBatis Like查询处理%_符号

MyBatis Like查询处理%_符号

如果我们数据库中存的字段包含有"%_"这两个like查询的通配符,那么在查询的时候把"%_"当作关键字是查询不出来的,因为mybatis会把这两个字符当作通配符。解决方法是要能加转义字符

1.定义一个拦截器,如果要查询的字符串中包含"%_"则增加一个转义字符

import org.apache.ibatis.executor.Executor;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.plugin.*;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.RowBounds;import java.util.*;/** * Created by on 2016/7/1. */@Intercepts({        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,                RowBounds.class, ResultHandler.class})})public class QueryExecutorInterceptor implements Interceptor {    private static final String[] NeedEscapes = new String[]{"%", "_"};    private Properties properties;    private static Object convertSqlEscape(Object parameter, List<String> propertyList) {        if (propertyList != null && propertyList.size() > 0 && parameter instanceof Map) {            Map map = (Map) parameter;            for (Object key : map.keySet()) {                Object value = map.get(key);                if (value instanceof Map) {                    map.replace(key, convertSqlEscape(value, propertyList));                } else if (value instanceof String && propertyList.indexOf(key) > -1) {                    String val = (String) value;                    for (String needEscape : NeedEscapes) {                        if (val.contains(needEscape)) {                            val = val.replace(needEscape, "/" + needEscape);                        }                    }                    map.replace(key, val);                }            }        }        return parameter;    }    @Override    public Object intercept(Invocation invocation) throws Throwable {        Object parameter = invocation.getArgs()[1];        MappedStatement statement = (MappedStatement) invocation.getArgs()[0];        List<ParameterMapping> parameterMappingList = statement.getBoundSql(parameter).getParameterMappings();        List<String> propertyList = new ArrayList<>();        if (parameterMappingList != null) {            parameterMappingList.forEach(p -> {                propertyList.add(p.getProperty());            });        }        invocation.getArgs()[1] = convertSqlEscape(parameter, propertyList);        return invocation.proceed();    }    @Override    public Object plugin(Object target) {        return Plugin.wrap(target, this);    }    @Override    public void setProperties(Properties properties) {        this.properties = properties;    }}

2. 对面的查询mapper like后面要加escape ‘/‘

<select id="getList" resultMap="MultiResultMap" parameterType="java.util.Map">        SELECT * FROM SYS_TEST T        WHERE 1=1        <if test="_parameter.containsKey(‘key‘)">            AND UPPER(CONCAT(T.ROLE_NAME,T.ROLE_INFO)) LIKE UPPER (CONCAT(CONCAT(‘%‘, #{key, jdbcType=VARCHAR}),‘%‘)) ESCAPE ‘/‘        </if>    </select>

最好的做法是可以直接拦截SQL,然后在SQL后面自动加上ESCAPE ‘/‘,但还没有找到合适的方法

MyBatis Like查询处理%_符号