mybatis编写sql语句时的问题

Question

打印attrList的结果为：[{11=32}, {12=33}]
执行：

    <select id="searchSkus" resultMap="SkuResultMap">
        SELECT DISTINCT s.id, s.sku_name, s.price, s.sku_default_img
        FROM sku s
        INNER JOIN sku_attr_value sav ON s.id = sav.sku_id
        <where>
            s.is_deleted = 0
            <if test="keyword != null and keyword != ''">
                AND s.sku_name LIKE CONCAT('%', #{keyword}, '%')
            </if>
            <if test="tmId != null and tmId != ''">
                AND s.tm_id = #{tmId}
            </if>
            <if test="attrList != null and !attrList.isEmpty()">
                AND (
                <foreach collection="attrList" item="pair" separator=" OR ">
                    (sav.attr_id = #{pair.key} AND sav.value_id = #{pair.value})
                </foreach>
                )
            </if>
        </where>
        <choose>
            <when test="order == '1:asc'">
                ORDER BY s.id ASC
            </when>
            <when test="order == '1:desc'">
                ORDER BY s.id DESC
            </when>
            <when test="order == '2:asc'">
                ORDER BY s.price ASC
            </when>
            <when test="order == '2:desc'">
                ORDER BY s.price DESC
            </when>
            <otherwise>
                ORDER BY s.id
            </otherwise>
        </choose>
        LIMIT #{offset}, #{pageSize}
    </select>

之后，用idea的mybatis log插件打印出的sql语句为：

    SELECT
        DISTINCT s.id,
        s.sku_name,
        s.price,
        s.sku_default_img 
    FROM
        sku s 
    INNER JOIN
        sku_attr_value sav 
            ON s.id = sav.sku_id 
    WHERE
        s.is_deleted = 0 
        AND s.sku_name LIKE CONCAT('%', '手机', '%') 
        AND (
            (
                sav.attr_id = 11 
                AND sav.value_id = 32
            ) 
            OR (
                sav.attr_id = 12 
                AND sav.value_id = 33
            ) 
        ) 
    ORDER BY
        s.id DESC LIMIT 0,
        10;

但将

            <if test="attrList != null and !attrList.isEmpty()">
                AND (
                (sav.attr_id = 11 AND sav.value_id = 32)
                OR
                (sav.attr_id = 12 AND sav.value_id = 33)
                )
            </if>

换为：

            <if test="attrList != null and !attrList.isEmpty()">
                AND (
                <foreach collection="attrList" item="pair" separator=" OR ">
                    (sav.attr_id = #{pair.key} AND sav.value_id = #{pair.value})
                </foreach>
                )
            </if>

之后，打印的sql语句却变成了：

    SELECT
        DISTINCT s.id,
        s.sku_name,
        s.price,
        s.sku_default_img 
    FROM
        sku s 
    INNER JOIN
        sku_attr_value sav 
            ON s.id = sav.sku_id 
    WHERE
        s.is_deleted = 0 
        AND s.sku_name LIKE CONCAT('%', ?, '%') 
        AND (
            (
                sav.attr_id = '手机' 
                AND sav.value_id = null
            ) 
            OR (
                sav.attr_id = null 
                AND sav.value_id = null
            ) 
        ) 
    ORDER BY
        s.id DESC LIMIT null,
        0,
        10;

这个sql语句完全错了，为什么？难道

            <if test="attrList != null and !attrList.isEmpty()">
                AND (
                (sav.attr_id = 11 AND sav.value_id = 32)
                OR
                (sav.attr_id = 12 AND sav.value_id = 33)
                )
            </if>

与

            <if test="attrList != null and !attrList.isEmpty()">
                AND (
                <foreach collection="attrList" item="pair" separator=" OR ">
                    (sav.attr_id = #{pair.key} AND sav.value_id = #{pair.value})
                </foreach>
                )
            </if>

不等价吗？还是attrList的格式需要更改？

Answer 1

想问下题主，这个attrList里面的对象类型是map吗？