resultMap和動态SQL

結果集映射 resultMap

1.當資料庫字段名和POJO屬性名不一緻時可以使用resultMap，起一個别名

例：

<resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
        <id column="bid" property="id" />
       <result column="author_gender" property="authorGender" />
    </resultMap>
           

2.多表關聯查詢一對一使用resultMap的association

例

<resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
        <id column="bid" property="id" />
        <result column="bname" property="bname" />
        <result column="author" property="author" />
        <result column="author_gender" property="authorGender" />
        <result column="price" property="price" />
        <result column="description" property="description" />
        <association property="bookType" javaType="com.lanou3g.mybatis.bean.BookType">
            <id column="bt_id" property="id" />
            <result column="tname" property="tname" />
        </association>
    </resultMap>


    <select id="queryBooks" resultMap="book">
        select b.*,bt.*, b.id bid, bt.id bt_id from book b, booktype bt where b.btype = bt.id;
    </select>
           

多對多時使用collection

例

<mapper namespace="com.lanou3g.mybaties.dao.TushubiaoDao">
    <resultMap id="tushubiao" type="com.lanou3g.mybaties.bean.Tushubiao">
    <id column="tid" property="id"/>
    <result column="bname" property="bname"/>
    <result column="author" property="author"/>
    <result column="gender" property="gender"/>
    <result column="price" property="price"/>
    <result column="miaoshu" property="miaoshu"/>
    <collection property="btype" ofType="com.lanou3g.mybaties.bean.Leixing">
        <id column="id" property="id"/>
        <result column="tname" property="tname"/>
    </collection>
</resultMap>
    <select id="queryAllTu" resultMap="tushubiao">
        select t.*,l.*,t.id tid,l.id lid from tushubiao t,leixing l where t.btype=l.id;
    </select>
           

動态sql

**1. if标簽 **

<select id="queryStudentByCondition" resultType="Student">
        select * from student
        <where>
            <if test="sname != null">
                sname = #{sname}
            </if>
            <if test="nickName != null">
                and nick_name = #{nickName}
            </if>
            <if test="id != null">
                and id = #{id}
            </if>
        </where>
    </select>
           

**2.choose标簽 when标簽 otherwise标簽 **

有時我們不想應用到所有的條件語句，而隻想從中擇其一項。針對這種情況，MyBatis 提供了 choose 元素，它有點像 Java 中的 switch 語句

<select id="queryChooseWhen" resultType="Student">
        select * from student
        <where>
            <choose>
                <when test="sname != null">
                    and sname = #{sname}
                </when>
                <when test="nickName != null">
                    and nick_name = #{nickName}
                </when>
                <otherwise>
                  and id = 5
                </otherwise>
            </choose>
        </where>
    </select>
           

3.set标簽

set 元素會動态前置 SET 關鍵字，同時也會删掉無關的逗号(如：語句最後的逗号)

<update id="updateById" parameterType="Student">
        update student
        <set>
            <if test="sname != null">
                sname = #{sname},
            </if>
            <if test="nickName != null">
                nick_name = #{nickName},
            </if>
        </set>
        where id = #{id}
    </update>
           

4.trim标簽

常用屬性有：

prefix: 添加指定字首

prefixOverrides: 删除指定字首

suffixOverrides: 删除指定字尾

<update id="updateById" parameterType="Student">
        update student
        <trim prefix="set" suffixOverrides=",">
            <if test="sname != null">
                sname = #{sname},
            </if>
            <if test="nickName != null">
                nick_name = #{nickName},
            </if>
        </trim>
        where id = #{id}
    </update>
           

5.where标簽

當我們拼接動态SQL時，如果一個查詢條件都沒有，那我們就不需要where子句，而如果有至少一個條件我們就需要where子句。這樣，我們就需要做個判斷，而mybatis裡的标簽就省去了我們自己做這個判斷。

<select id="queryStudentByCondition" resultType="Student">
        select * from student
        <where>
            <if test="sname != null">
                sname = #{sname}
            </if>
            <if test="nickName != null">
                and nick_name = #{nickName}
            </if>
            <if test="id != null">
                and id = #{id}
            </if>
        </where>
    </select>