问题描述

公司项目线上出现了一个Bug，再查询员工时，出现了重复数据；经过排查发现

<select id="listBySort" resultType="com.meiqiling.model.entity.TDbcEmployee">
   ...
   <if test="appointmentState != null and appointmentState != ''">
      and t.APPOINTMENT_STATE = #{appointmentState}
   </if>
   ...
</select>

上述代码中,当appointmentState这个参数为0时， 0 != '' 的结果为false,即0与''相等，从而导致此条动态sql不起作用。

原因分析

我们知道，MyBatis的动态SQL是基于 OGNL 的表达式；

public class IfSqlNode implements SqlNode {
  // 表达式计算器
  private final ExpressionEvaluator evaluator;
  private final String test;
  private final SqlNode contents;

  public IfSqlNode(SqlNode contents, String test) {
    this.test = test;
    this.contents = contents;
    this.evaluator = new ExpressionEvaluator();
  }

  @Override
  public boolean apply(DynamicContext context) {
    // 表达式计算
    if (evaluator.evaluateBoolean(test, context.getBindings())) {
      contents.apply(context);
      return true;
    }
    return false;
  }

}

上面代码就是解析动态sql的if标签，具体我们在来看表达式计算器是如何计算的

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;
}

我们再看看Ognl是如何处理表达式test="appointmentState != null and appointmentState != ''"的，不直接带着大家翻代码了，就直接贴核心代码此时：v1是0，v2是" v1是我们传入的值，v2是要判断的值

    public static int compareWithConversion(Object v1, Object v2)
    {
        int result;

        if (v1 == v2) {
            result = 0;
        } else {
            // t1 = INT、 t2 = NONNUMERIC, type = NONNUMERIC
            int t1 = getNumericType(v1), t2 = getNumericType(v2), type = getNumericType(t1, t2, true);

            switch(type) {
            case BIGINT:
                result = bigIntValue(v1).compareTo(bigIntValue(v2));
                break;

            case BIGDEC:
                result = bigDecValue(v1).compareTo(bigDecValue(v2));
                break;

            case NONNUMERIC:
                 // t1不是NONNUMERIC
                if ((t1 == NONNUMERIC) && (t2 == NONNUMERIC)) {
                    if ((v1 instanceof Comparable) && v1.getClass().isAssignableFrom(v2.getClass())) {
                        result = ((Comparable) v1).compareTo(v2);
                        break;
                    } else {
                        throw new IllegalArgumentException("invalid comparison: " + v1.getClass().getName() + " and "
                                + v2.getClass().getName());
                    }
                }
                // else fall through
            case FLOAT:
            case DOUBLE:
                // 走到此处 dv1 = 0
                double dv1 = doubleValue(v1),
                // dv2 = 0.0
                dv2 = doubleValue(v2);
                返回true；
                return (dv1 == dv2) ? 0 : ((dv1 < dv2) ? -1 : 1);

            default:
                long lv1 = longValue(v1),
                lv2 = longValue(v2);

                return (lv1 == lv2) ? 0 : ((lv1 < lv2) ? -1 : 1);
            }
        }
        return result;
    }

    public static int getNumericType(Object value)
    {
        if (value != null) {
            Class c = value.getClass();
            if (c == Integer.class) return INT;
            if (c == Double.class) return DOUBLE;
            if (c == Boolean.class) return BOOL;
            if (c == Byte.class) return BYTE;
            if (c == Character.class) return CHAR;
            if (c == Short.class) return SHORT;
            if (c == Long.class) return LONG;
            if (c == Float.class) return FLOAT;
            if (c == BigInteger.class) return BIGINT;
            if (c == BigDecimal.class) return BIGDEC;
        }
        return NONNUMERIC;
    }

    public static int getNumericType(int t1, int t2, boolean canBeNonNumeric)
    {
        // 不等
        if (t1 == t2) return t1;

        if (canBeNonNumeric && (t1 == NONNUMERIC || t2 == NONNUMERIC || t1 == CHAR || t2 == CHAR)) return NONNUMERIC;

        if (t1 == NONNUMERIC) t1 = DOUBLE; // Try to interpret strings as doubles...
        if (t2 == NONNUMERIC) t2 = DOUBLE; // Try to interpret strings as doubles...

        if (t1 >= MIN_REAL_TYPE) {
            if (t2 >= MIN_REAL_TYPE) return Math.max(t1, t2);
            if (t2 < INT) return t1;
            if (t2 == BIGINT) return BIGDEC;
            return Math.max(DOUBLE, t1);
        } else if (t2 >= MIN_REAL_TYPE) {
            if (t1 < INT) return t2;
            if (t1 == BIGINT) return BIGDEC;
            return Math.max(DOUBLE, t2);
        } else return Math.max(t1, t2);
    }

    public static double doubleValue(Object value)
        throws NumberFormatException
    {
        if (value == null) return 0.0;
        Class c = value.getClass();
        if (c.getSuperclass() == Number.class) return ((Number) value).doubleValue();
        if (c == Boolean.class) return ((Boolean) value).booleanValue() ? 1 : 0;
        if (c == Character.class) return ((Character) value).charValue();
        String s = stringValue(value, true);
        // 此处返回0
        return (s.length() == 0) ? 0.0 : Double.parseDouble(s);
    }

    public static String stringValue(Object value, boolean trim)
    {
        String result;

        if (value == null) {
            result = OgnlRuntime.NULL_STRING;
        } else {
            result = value.toString();
            if (trim) {
                result = result.trim();
            }
        }
        return result;
    }

首先传入v1：0，v2："，此时通过判断获得t1 = INT、 t2 = NONNUMERIC, type = NONNUMERIC
接着走人NONNUMERIC分支，发现t1与NONNUMERIC不等，则跳转到case DOUBLE:
通过方法doubleValue，将''转为了0.0；此时v1与v2被判断为相等；
即test="appointmentState != null and appointmentState != ''"为false，导致业务出现bug；

解决

知道原因之后，解决起来就方便很多

<select id="listBySort" resultType="com.meiqiling.model.entity.TDbcEmployee">
   ...
   <if test="appointmentState != null">
      and t.APPOINTMENT_STATE = #{appointmentState}
   </if>
   ...
</select>

如果前端传过来的是'',提前做处理即可；

MyBatis传参为0时，动态sql判断0和''相等导致业务问题出错

问题描述

原因分析

解决