MybBatis動態SQL

mokabaka發表於2020-10-18
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sunreal.mapper.BlogMapper">
    <!--SQL片段,將常用SQL進行封裝-->
    <sql id="if-title-author">
        <if test="title != null and title != ''">
            title = #{title}
        </if>
        <if test='state != null and state != "1"'>
            and author = #{author}
        </if>
    </sql>

    <!--如果實體類(createTime),資料庫(create_time),
    需要的<settings>標籤中設定,開啟駝峰轉換
    <setting name="mapUnderscoreToCamelCase" value="true"/>,
    或使用<resultMap>標籤進行對映-->
    <insert id="addBlog" parameterType="Blog">
        INSERT INTO mybatis.blog (id, title, author, create_time, views)
        VALUES (#{id}, #{title}, #{author}, #{createTime}, #{views})
    </insert>

    <!--<where>標籤可以自動過濾多餘的and-->
    <!--注意:<test>標籤中引數要是有雙引號("")state != "1",否則無效-->
    <select id="queryBlog" resultType="Blog" parameterType="map">
        select *
        from mybatis.blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

    <!--<choose>標籤,相當於JAVA的switch語句,
    即使多個條件符合要求,也只會選擇優先順序最高的一條進行執行,
    同時也可以自動過濾多餘(and)-->
    <select id="queryBlogChoose" resultType="Blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null and title != ''">
                    title = #{title}
                </when>
                <when test="author != null and author != ''">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = '1000'
                </otherwise>
            </choose>
        </where>
    </select>

    <!--<set>標籤,在更新操作中自動過濾多餘的逗號(,)-->
    <update id="updateBlog" parameterType="map">
        UPDATE MYBATIS.BLOG
        <set>
            <if test="title != null and title != ''">
                TITLE = #{title},
            </if>
            <if test="author != null and author != ''">
                AUTHOR = #{author}
            </if>
        </set>
        WHERE ID = #{id}
    </update>

    <!--foreach,需要傳入集合(ids)-->
    <!--select * from mybatis.blog WHERE title = ? and ( id = ? or id = ? )-->
    <select id="queryBlogFReach" resultType="Blog" parameterType="map">
        select *
        from mybatis.blog
        <where>
            <if test="title != null and title != ''">
                title = #{title}
            </if>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>
</mapper>

相關文章