08Mybatis:动态sql

08动态sql

1.IF WHERE标签的使用

 <select id="findByTitleAndDistrict02" parameterType="com.freshtest.mybatis.model.House" resultMap="ReturnMap">
        SELECT* FROM house
        <where>
            <if test="house.title!='' and house.title!=null">
                AND title LIKE CONCAT('%',#{house.title},'%')
            </if>
            <if test="house.district!='' and house.district!=null">
                AND district LIKE CONCAT('%',#{house.district},'%')
            </if>
        </where>
--         WHERE title LIKE CONCAT('%',#{title},'%')
--         AND district LIKE CONCAT('%',#{district},'%')
    </select>

2. SQL片段

提高代码复用性

 <select id="findByTitleAndDistrict02" parameterType="com.freshtest.mybatis.model.House"
            resultMap="ReturnMap">
        SELECT* FROM house
        <where>
            <include refid="WhereSql"></include>
        </where>
--         WHERE title LIKE CONCAT('%',#{title},'%')
--         AND district LIKE CONCAT('%',#{district},'%')
    </select>
    <sql id="WhereSql">
        <if test="house.title!='' and house.title!=null">
            AND title LIKE CONCAT('%',#{house.title},'%')
        </if>
        <if test="house.district!='' and house.district!=null">
            AND district LIKE CONCAT('%',#{house.district},'%')
        </if>
    </sql>

Foreach标签

IN操作

<select id="getByIds" parameterType="java.util.List" resultMap="ReturnMap">
    <!--
        使用foreach遍历传入ids
            collection:指定输入对象中集合属性
            item:每次遍历生成对象
            open：开始遍历时拼接的串
            close:结束遍历时拼接的串
            separator分隔符：遍历两个对象中需要拼接的串
            实现and id IN（1,10,16）拼接
            and id IN（#{item_id},#{item_id},#{item_id})
    -->
        SELECT * FROM house
        <where>
            <foreach collection="ids"
                     item="item_id"
                     open="and id IN("
                     separator=","
                     close=")"> #{item_id}
             </foreach>
        </where>
</select>

OR操作

<select id="getByIds" parameterType="java.util.List" resultMap="ReturnMap">
            SELECT * FROM house
            <where>
                <foreach
                    collection="ids"
                    item="item_id"
                    open="and"
                    separator=" or "
                    close=""
                >
                    id=#{item_id}
                </foreach>
            </where>
    </select>