映射器配置文件

---

案例背景

employee员工表、dept部门表

基本配置

deptMapper.xml配置如下:

<?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是和Mapper接口全限定名一致的 --><mapper namespace="com.xuyi.usemybatis.dao.DeptMapper">    <!-- 映射javabean的类型 -->    <resultMap id="BaseResultMap" type="com.xuyi.usemybatis.pojo.Dept">        <id column="id" property="id" jdbcType="INTEGER" />        <result column="dept_name" property="deptName" jdbcType="VARCHAR" />        <result column="dept_info" property="deptInfo" jdbcType="VARCHAR" />    </resultMap>    <!-- 简化sql语句 -->    <sql id="Base_Column_List">        id, dept_name, dept_info    </sql>    <!-- 根据主键查询表记录,查询返回结果类型通常使用resultMap类型 -->    <select id="selectByPrimaryKey" resultMap="BaseResultMap"        parameterType="java.lang.Integer">        select        <include refid="Base_Column_List" />        from dept        where id = #{id,jdbcType=INTEGER}    </select>    <!--#{} 是表示占位符类似jdbc中的 ? 占位符-->    <!-- 根据主键删除表记录 -->    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">        delete from dept        where id = #{id,jdbcType=INTEGER}    </delete>    <!-- 插入表记录(非空插入) -->    <insert id="insertSelective" parameterType="com.xuyi.usemybatis.pojo.Dept">        insert into dept        <trim prefix="(" suffix=")" suffixOverrides=",">            <if test="id != null">                id,            </if>            <if test="deptName != null">                dept_name,            </if>            <if test="deptInfo != null">                dept_info,            </if>        </trim>        <trim prefix="values (" suffix=")" suffixOverrides=",">            <if test="id != null">                #{id,jdbcType=INTEGER},            </if>            <if test="deptName != null">                #{deptName,jdbcType=VARCHAR},            </if>            <if test="deptInfo != null">                #{deptInfo,jdbcType=VARCHAR},            </if>        </trim>    </insert>    <!-- 根据主键更新(指定属性更新非空属性更新) -->    <update id="updateByPrimaryKeySelective" parameterType="com.xuyi.usemybatis.pojo.Dept">        update dept        <set>            <if test="deptName != null">                dept_name = #{deptName,jdbcType=VARCHAR},            </if>            <if test="deptInfo != null">                dept_info = #{deptInfo,jdbcType=VARCHAR},            </if>        </set>        where id = #{id,jdbcType=INTEGER}    </update>    <!-- 根据主键更新(不提推荐这种方式) -->    <update id="updateByPrimaryKey" parameterType="com.xuyi.usemybatis.pojo.Dept">        update dept        set        dept_name = #{deptName,jdbcType=VARCHAR},        dept_info =        #{deptInfo,jdbcType=VARCHAR}        where id = #{id,jdbcType=INTEGER}    </update></mapper>

---

动态SQL

if

The most common thing to do in dynamic SQL is conditionally include a part of a where clause. For example:

<select id="findActiveBlogWithTitleLike" resultType="Blog">    SELECT * FROM user    WHERE 1=1    <if test="userId != null">    AND user_id =#{userId}    </if></select><!--注意1=1这句是精髓啊，o(^▽^)o -->

choose, when, otherwise

Sometimes we don’t want all of the conditionals to apply, instead we want to choose only one case among many options. Similar to a switch statement in Java, MyBatis offers a choose element.

<select id="findActiveBlogLike" resultType="Blog">  SELECT * FROM BLOG WHERE state = ‘ACTIVE’      <choose>            <when test="title != null">              AND title like #{title}            </when>            <when test="author != null and author.name != null">              AND author_name like #{author.name}            </when>            <otherwise>              AND featured = 1            </otherwise>      </choose></select>   

trim, where, set

The previous examples have been conveniently dancing around a notorious dynamic SQL challenge. Consider what would happen if we return to our “if” example, but this time we make “ACTIVE = 1” a dynamic condition as well.

<select id="findActiveBlogLike" resultType="Blog">  SELECT * FROM BLOG       <where>             <if test="state != null">                 state = #{state}            </if>             <if test="title != null">                AND title like #{title}            </if>            <if test="author != null and author.name != null">                AND author_name like #{author.name}            </if>      </where></select>

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">    ... </trim>

类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的。

<update id="updateAuthorIfNecessary">  update Author    <set>      <if test="username != null">username=#{username},</if>      <if test="password != null">password=#{password},</if>      <if test="email != null">email=#{email},</if>      <if test="bio != null">bio=#{bio}</if>    </set>  where id=#{id}</update>同理:<trim prefix="SET" suffixOverrides=",">  ...</trim>

foreach

Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition. For example:

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectPostIn" resultType="domain.blog.Post">  SELECT *  FROM POST P  WHERE ID in      <foreach item="item" index="index" collection="list"          open="(" separator="," close=")">            #{item}      </foreach></select>The foreach element is very powerful, and allows you to specify a collection, declare item and index variables that can be used inside the body of the element. It also allows you to specify opening and closing strings, and add a separator to place in between iterations. The element is smart in that it won’t accidentally append extra separators.NOTE You can pass any Iterable object (for example List, Set, etc.), as well as any Map or Array object to foreach as collection parameter. When using an Iterable or Array, index will be the number of current iteration and value item will be the element retrieved in this iteration. When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object.This wraps up the discussion regarding the XML configuration file and XML mapping files. The next section will discuss the Java API in detail, so that you can get the most out of the mappings that you’ve created.

---

一对多和多对一

其实在互联网企业中，很少在javabean中体现出关联关系(使用引用),而是保存一个关联的记录的主键属性即可。

一对多举例:

//员工表public class Employee {    private int id;    private String name;    //站在数据库角度通常是这样设计的    private int deptId;}//部门表public class Dept {    private int id;    private String name;    //站在oop思想上这样子设计是更合理的.    //private List<Employee> employees;}