映射器配置文件

布满荆棘的人生 2022-08-22 15:35 173阅读 0赞

# 映射器配置文件 #

--------------------

## 案例背景 ##

## 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="com.xuyi.usemybatis.dao.DeptMapper">
        
        <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 id="Base_Column_List">
            id, dept_name, dept_info
        </sql>
    
        
        <select id="selectByPrimaryKey" resultMap="BaseResultMap"
            parameterType="java.lang.Integer">
            select
            <include refid="Base_Column_List" />
            from dept
            where id = #{id,jdbcType=INTEGER}
        </select>
        
    
        
        <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>

**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 元素为：

类似的用于动态更新语句的解决方案叫做 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;
    }