MyBatis--11-- 判断 Integer类型，值为0动态SQL不生效

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

MyBatis标签： Integer类型，值为0动态SQL不生效

1.现象

status tinyint(4) DEFAULT ‘0’ COMMENT ‘处理状态:0未处理 1已处理’

<select id="selectDtoList">
        SELECT
        id,
        channel_id AS channelId,
        channel_name  AS  channelName,
        group_name AS groupName,
        phone AS phone,
        ehr_no AS ehrNo,
        opinion_content AS opinionContent,
        create_time AS createTime,
        status AS status,
        handle_time  AS handleTime
        FROM
        t_opinion_record
        WHERE 1 = 1
         <if test="dto.status != null and dto.status != ''">
            AND `status` = #{dto.status}
        </if>
        <if test="dto.startTime != null and dto.startTime != ''">
            AND `create_time` &gt;= #{dto.startTime}
        </if>
        <if test="dto.endTime != null and dto.endTime != ''">
            AND  `create_time` &lt;= #{dto.endTime}
        </if>
        ORDER BY create_time DESC
    </select>

情况 1：status =1 时 或者其他值时，程序正常拼接sql
情况 2：status =0 时，程序不拼接 ，动态SQL不生效

2.分析原因

Mbatis源码

• 再看org.apache.ibatis.scripting.xmltags.ExpressionEvaluator：evaluateBoolean 这个方法、我们看到了如果是number类型、会与0做判断比较

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

由此可以发现、当动态sql参数是int类型、并且传入0时、是使用OgnlCache获取的value、而OgnlCache是对OGNL的封装

在OGNL的官网上面可以看到如下解释
任何需要布尔值的对象都可以使用。OGNL将对象解释为布尔值，如下所示：
·如果对象是布尔值，则提取并返回其值；
·如果对象是Number，则将其双精度浮点值与零进行比较；非零视为真，零视为假；
·如果对象是Character，则其布尔值为true，当且仅当其char值为非零时；
否则，它的布尔值为true，当且仅当它不是null时。
Any object can be used where a boolean is required. OGNL interprets objects as booleans like this:
· If the object is a Boolean, its value is extracted and returned;
· If the object is a Number, its double-precision floating-point value is compared with zero; non-zero is treated as true, zero as false;
· If the object is a Character, its boolean value is true if and only if its char value is non-zero;
Otherwise, its boolean value is true if and only if it is non-null.

3.解决办法

去掉判断条件 dto.status != ‘’"即可

<select id="selectDtoList">
        SELECT
        id,
        channel_id AS channelId,
        channel_name  AS  channelName,
        group_name AS groupName,
        phone AS phone,
        ehr_no AS ehrNo,
        opinion_content AS opinionContent,
        create_time AS createTime,
        status AS status,
        handle_time  AS handleTime
        FROM
        t_opinion_record
        WHERE 1 = 1
         <if test="dto.status != null ">
            AND `status` = #{dto.status}
        </if>
        <if test="dto.startTime != null and dto.startTime != ''">
            AND `create_time` &gt;= #{dto.startTime}
        </if>
        <if test="dto.endTime != null and dto.endTime != ''">
            AND  `create_time` &lt;= #{dto.endTime}
        </if>
        ORDER BY create_time DESC
    </select>

测试结果都正常