Mybatis拼接sql出错及源码解析

一、引言

        项目里写了一条查询sql在入参没有的情况下多拼接了一个条件，看了一会儿都感觉不出来哪里不对，一个惊讶的猜想浮现，在foreach设置的别名mybatis是不是给设置进去了。

二、排查

        原代码如下

select
        <include refid="Base_Column_List"/>
        from t_aac_shop_exempt_apply
        where 1 = 1
        <if test="exemptNoList != null and exemptNoList.size > 0">
            and exempt_no in
            <foreach collection="exemptNoList" item="exemptNo" open="(" close=")" separator=",">
                #{exemptNo}
            </foreach>
        </if>
        <if test="exemptNo != null and exemptNo!=''">
            and exempt_no = #{exemptNo}
        </if>
        <if test="applyUserCode != null and applyUserCode!=''">
            and apply_user_code = #{applyUserCode}
        </if>
        <if test="agentNo != null and agentNo!=''">
            and agent_no = #{agentNo}
        </if>
        <if test="serviceAgentNoList != null and serviceAgentNoList.size > 0">
            and agent_no in
            <foreach collection="serviceAgentNoList" item="agentNo" open="(" close=")" separator=",">
                #{agentNo}
            </foreach>
        </if>
        <if test="compensateNo != null and compensateNo!=''">
            and compensate_no = #{compensateNo}
        </if>
        <if test="month != null">
            and month = #{month}
        </if>
        <if test="status != null">
            and status = #{status}
        </if>
        <if test="statusList != null and statusList.size > 0">
            and status in
            <foreach collection="statusList" item="status" open="(" close=")" separator=",">
                #{status}
            </foreach>
        </if>
        <if test="startTime != null">
            and gmt_create <![CDATA[   >=  ]]> #{startTime}
        </if>
        <if test="endTime != null">
            and  #{endTime} <![CDATA[   >=  ]]> gmt_create
        </if>
        and is_deleted = 0

        入参是

{
    "model":
    {
         "exemptNoList":["MPSQ11739550014177280","MPSQ11750861482491904"],
         "agentNo":"","compensateNo":"","applyUserCode":"",
         "startTime":"2022-04-23 00:00:00",
         "endTime":"2022-07-21 23:59:59"
    },
    "pageIndex":1,"pageSize":10,"queryCount":true,"start":0,"startPos":0
}

        拼接的sql结果却是这样的

SELECT
	id,
	exempt_no,
	instance_code,
	agent_no,
	agent_name,
	compensate_no,
	MONTH,
	quantity,
	amount,
	reason,
	apply_user_name,
	apply_user_code,
	remark,
	act_status,
	STATUS,
	gmt_create,
	gmt_modify
FROM
	t_aac_shop_exempt_apply
WHERE
	1 = 1
AND exempt_no IN (?, ?)
AND exempt_no = ?
AND gmt_create >= ?
AND ? >= gmt_create
AND is_deleted = 0
LIMIT 10

        问题来了，为什么在exemptNo没有值的情况下，这个判断条件会被添加进去？

<if test="exemptNo != null and exemptNo!=''"> and exempt_no = #{exemptNo} </if>

        在这个集合遍历 的时候，博主把集合元素的别名命名为exemptNo,那么是不是mybatis会将别名和值注入到它的空间对象中，并且在下一个拼接判断使用了？

<if test="exemptNoList != null and exemptNoList.size > 0">
    and exempt_no in
    <foreach collection="exemptNoList" item="exemptNo" open="("close=")" separator=",">
        #{exemptNo}
    </foreach>
</if>

博主把别名换了一下，sql就拼接准确了

select
        <include refid="Base_Column_List"/>
        from t_aac_shop_exempt_apply
        where 1 = 1
        <if test="exemptNoList != null and exemptNoList.size > 0">
            and exempt_no in
            <foreach collection="exemptNoList" item="exempt" open="(" close=")" separator=",">
                #{exempt}
            </foreach>
        </if>
        <if test="exemptNo != null and exemptNo!=''">
            and exempt_no = #{exemptNo}
        </if>
        <if test="applyUserCode != null and applyUserCode!=''">
            and apply_user_code = #{applyUserCode}
        </if>
        <if test="agentNo != null and agentNo!=''">
            and agent_no = #{agentNo}
        </if>
        <if test="serviceAgentNoList != null and serviceAgentNoList.size > 0">
            and agent_no in
            <foreach collection="serviceAgentNoList" item="agentNo" open="(" close=")" separator=",">
                #{agentNo}
            </foreach>
        </if>
        <if test="compensateNo != null and compensateNo!=''">
            and compensate_no = #{compensateNo}
        </if>
        <if test="month != null">
            and month = #{month}
        </if>
        <if test="status != null">
            and status = #{status}
        </if>
        <if test="statusList != null and statusList.size > 0">
            and status in
            <foreach collection="statusList" item="st" open="(" close=")" separator=",">
                #{st}
            </foreach>
        </if>
        <if test="startTime != null">
            and gmt_create <![CDATA[   >=  ]]> #{startTime}
        </if>
        <if test="endTime != null">
            and  #{endTime} <![CDATA[   >=  ]]> gmt_create
        </if>
        and is_deleted = 0

SELECT
	id,
	exempt_no,
	instance_code,
	agent_no,
	agent_name,
	compensate_no,
	MONTH,
	quantity,
	amount,
	reason,
	apply_user_name,
	apply_user_code,
	remark,
	act_status,
	STATUS,
	gmt_create,
	gmt_modify
FROM
	t_aac_shop_exempt_apply
WHERE
	1 = 1
AND exempt_no IN (?, ?)
AND gmt_create >= ?
AND ? >= gmt_create
AND is_deleted = 0
LIMIT 10

三、原理

        知道了原因之后就可以看一下mybatis的源码了，看看他到底是怎么做的，这其实算是一个bug了。

断点打到org.apache.ibatis.scripting.xmltags的DynamicSqlSource的getBoundSql方法

         可以看到exemptNo是空值，这个条件不可能被拼接。

                接下来看rootSqlNode.apply(context)，这一步是出问题的地方

@Override
  public boolean apply(DynamicContext context) {
    for (SqlNode sqlNode : contents) {
      sqlNode.apply(context);
    }
    return true;
  }

@Override
  public boolean apply(DynamicContext context) {
      判断是否拼接到sql
    if (evaluator.evaluateBoolean(test, context.getBindings())) {
      contents.apply(context);
      return true;
    }
    return false;
  }
public boolean evaluateBoolean(String expression, Object parameterObject) {
    Object value = OgnlCache.getValue(expression, parameterObject);
    if (value instanceof Boolean) {
      return (Boolean) value;
    }
    if (value instanceof Number) {
        return !new BigDecimal(String.valueOf(value)).equals(BigDecimal.ZERO);
    }
    return value != null;
  }

public static Object getValue(String expression, Object root) {
    try {
      Map<Object, OgnlClassResolver> context = Ognl.createDefaultContext(root, new OgnlClassResolver());
      return Ognl.getValue(parseExpression(expression), context, root);
    } catch (OgnlException e) {
      throw new BuilderException("Error evaluating expression '" + expression + "'. Cause: " + e, e);
    }
  }

        重点在这里

@Override
  public boolean apply(DynamicContext context) {
    Map<String, Object> bindings = context.getBindings();
    final Iterable<?> iterable = evaluator.evaluateIterable(collectionExpression, bindings);
    if (!iterable.iterator().hasNext()) {
      return true;
    }
    boolean first = true;
    applyOpen(context);
    int i = 0;
    //将参数值进行遍历设置
    for (Object o : iterable) {
      DynamicContext oldContext = context;
      if (first) {
        context = new PrefixedContext(context, "");
      } else if (separator != null) {
        context = new PrefixedContext(context, separator);
      } else {
          context = new PrefixedContext(context, "");
      }
      int uniqueNumber = context.getUniqueNumber();
      // Issue #709 
      if (o instanceof Map.Entry) {
        @SuppressWarnings("unchecked") 
        Map.Entry<Object, Object> mapEntry = (Map.Entry<Object, Object>) o;
        applyIndex(context, mapEntry.getKey(), uniqueNumber);
        applyItem(context, mapEntry.getValue(), uniqueNumber);
      } else {
        applyIndex(context, i, uniqueNumber);
        //将别名与参数值存入键值对
        applyItem(context, o, uniqueNumber);
      }
      contents.apply(new FilteredDynamicContext(configuration, context, index, item, uniqueNumber));
      if (first) {
        first = !((PrefixedContext) context).isPrefixApplied();
      }
      context = oldContext;
      i++;
    }
    applyClose(context);
    return true;
  }

        此时mybatis使用的DynamicContext对象中的ContextMap键值对只有两个参数，在集合遍历时applyItem方法会将别名作为键，参数值作为value存入ContextMap以供mybatis临时使用，但是用完之后没有被去掉。

        这里可以看一下mybatis定义的通用对象，ContextMap继承了HashMap，用于存储sql参数以及mybatis拼接之后的sql键值对

public static final String PARAMETER_OBJECT_KEY = "_parameter";
  public static final String DATABASE_ID_KEY = "_databaseId";

  static {
    OgnlRuntime.setPropertyAccessor(ContextMap.class, new ContextAccessor());
  }

  private final ContextMap bindings;
  private final StringBuilder sqlBuilder = new StringBuilder();
  private int uniqueNumber = 0;

  public DynamicContext(Configuration configuration, Object parameterObject) {
    if (parameterObject != null && !(parameterObject instanceof Map)) {
      MetaObject metaObject = configuration.newMetaObject(parameterObject);
      bindings = new ContextMap(metaObject);
    } else {
      bindings = new ContextMap(null);
    }
    bindings.put(PARAMETER_OBJECT_KEY, parameterObject);
    bindings.put(DATABASE_ID_KEY, configuration.getDatabaseId());
  }


static class ContextMap extends HashMap<String, Object> {
    private static final long serialVersionUID = 2977601501966151582L;

    private MetaObject parameterMetaObject;
    public ContextMap(MetaObject parameterMetaObject) {
      this.parameterMetaObject = parameterMetaObject;
    }

    @Override
    public Object get(Object key) {
      String strKey = (String) key;
      if (super.containsKey(strKey)) {
        return super.get(strKey);
      }

      if (parameterMetaObject != null) {
        // issue #61 do not modify the context when reading
        return parameterMetaObject.getValue(strKey);
      }

      return null;
    }
  }

         问题很明显了，在集合遍历时将别名存入了键值对map，这其实是没有必要的，因为这是一次性使用的。

 四、总结

        mybatis的这个问题告诉我们sql的别名不能与其他参数相同，他并不是临时性的，如果有sql的其他错误，同学们也可以跟着博主的步骤debug看看。