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>