首页 > 代码库 > 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查询处理%_符号
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。