结果集映射 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>