mybatis(三)之高级用法

一、动态 SQL

  MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

  通常使用动态 SQL 不可能是独立的一部分，MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形，这种语言可以被用在任意的 SQL 映射语句中。

  动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多的元素需要来了解。MyBatis3 大大提升了它们，现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

  mybatis 的动态 sql 语句是基于 OGNL 表达式的。可以方便的在 sql 语句中实现某些逻辑。总体说来 mybatis 动态 SQL 语句主要有以下几类：
  1.if 语句 (简单的条件判断)
  2.choose (when，otherwize)，相当于 java 语言中的 switch，与 jstl 中的 choose 很类似。
  3.trim (对包含的内容加上 prefix，或者 suffix 等，前缀，后缀)
  4.where (主要是用来简化 sql 语句中 where 条件判断的，能智能的处理 and or，不必担心多余导致语法错误)
  5.set (主要用于更新时)
  6.foreach (在实现 in 语句查询时特别有用)
  在实际开发中我们经常封装用于包含所有查询条件的询条件类，有时条件类也可以使用 map 代替，只是使用 map 代码可读性较差。

public class ConditionPet {
    private String name;
    private int minWeight;
    private int maxWeight;
}

if 的用法
  用于根据参数的条件，动态拼接 SQL。if 里面的 test 就是对应的条件，当条件满足，if 中的 SQL 就会正常拼接。

案例：

<select id="findByCondition" resultType="cn.hxzy.entity.Pet">
    select * from pet
    <if test="name != null and name != ''">
        where name like #{name}
    </if>
</select>

案例2：

<select id="findByCondition" resultType="cn.hxzy.entity.Pet">
        select * from pet where true
        <if test="name != null">
          and  name = #{name}
        </if>
        <if test="minWeight != null">
           and weight >= #{minWeight}
        </if>
    </select>

二、choose(when,otherwize)

  相当于 java 语言中的 if-else/switch，与 jstl 中 的 choose 很类似。但是它与 if 的区别是，如下案例中三条分支语句程序只能走其中一条。

  案例：

<select id="findByCondition" resultType="cn.hxzy.entity.Pet">
    select * from pet where
    <choose>
        <when test="name != null">
            name = #{name}
        </when>
        <when test="minWeight != null">
            weight >= #{minWeight}
        </when>
        <otherwise>
            id = 1
        </otherwise>
    </choose>
</select>

三、trim

  mybatis 的 trim 标签一般用于去除 sql 语句中多余的 and 关键字，逗号，或者给 sql 语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作，如果要同时去除多个使用管道符连接 prefixOverrides=“AND|OR”。

  案例1：去除多余的 and 或者 where

select * from pet
<trim prefix="WHERE" prefixOverrides="AND">
    <if test="name != null">
        name = #{name}
    </if> 
    <if test="minWeight != null">
        AND weight >= #{minWeight}
    </if>
    <if test="maxWeight != null">
        AND weight <![CDATA[ <=]]> #{maxWeight}
    </if>
</trim>

prefix：前缀

prefixOverrides：去除 SQL 语句前面的关键字或者字符。

suffixOverrides：去除 SQL 语句结束位置的关键字或者字符。

案例2：使用 trim 标签去除结尾多余的逗号

insert into pet
<trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="name != null">name,</if>
    <if test="weight != null">weight,</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="name!=null">#{name},</if>
    <if test="weight!=null">#{weight},</if>
</trim>

四、where

  where 会自动判断后面的条件，如果没有条件则不在 SQL 中拼接 where 关键字。同时还能自动去掉 where 最前面多出的 and 或者 or 关键字。

  案例：

select * from pet
<where>
    <if test="name != null">and name like #{name}</if>
    <if test="weight != null">and weight = #{weight}</if>
</where>

练习：

  1.在 resource 表中增加如下的查询条件，并使用一条查询语句完成，参数统一使用 map。

  一、用户传入 page 与 size 参数时分页查询。

  二、用户传入 beginTime 用于查询创建时间在 beginTime 的之后创建的资源。

  三、传入 url 后可以根据 url 模糊查询。

  四、传入 name 后可以根据 name 模糊查询。

  参考代码：

<resultMap id="BaseResultMap" type="cn.hxzy.entity.Resource">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <result column="url" jdbcType="VARCHAR" property="url"/>
    <result column="pid" jdbcType="INTEGER" property="pid"/>
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    <result column="order_number" jdbcType="INTEGER" property="orderNumber"/>
</resultMap>
<sql id="Base_Column_List">
id, `name`, url, pid, create_time, update_time, order_number
</sql>
<select id="select" parameterType="map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from resource
    <where>
        <if test="name != null and name !=''">
            and `name` like #{name}
        </if>
        <if test="url != null and url != ''">
            and url like #{url}
        </if>
        <if test="pid != null">
            and pid = #{pid}
        </if>
        <if test="beginTime != null">
            and create_time >= #{beginTime }
        </if>
    </where>
    <if test="page!=null and size!=null">
        limit #{page},#{size}
    </if>
</select>

五、set的用法

  与 trim 类似 set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

  案例：

update pet
<set>
    <if test="name != null">name = #{name},</if>
    <if test="weight != null">weight = #{weight},</if>
</set>
where id =#{id}

练习：

  1.在 resource 表中增加如下的修改方法，使其能完成只修改不为空的属性，参数统一使用 map。

  参考代码：

<update id="updateByPrimaryKeySelective" parameterType="cn.hxzy.entity.Resource">
    update resource
    <set>
        <if test="name != null">
            `name` = #{name},
        </if>
        <if test="url != null">
            url = #{url},
        </if>
        <if test="pid != null">
            pid = #{pid},
        </if>       
        <if test="orderNumber != null">
            order_number = #{orderNumber},
        </if>
        update_time = now()
    </set>
    where id = #{id}
</update>

六、bind

  bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + parameter.titie + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

七、foreach

  foreach 主要用于数组、集合的遍历，foreach 提供大量属性用于满足开发需求如：collection 用于指定遍历的集合名称、 item 正在遍历的那个对象、 open 遍历前拼接的字符串、 close 结束时拼接的字符串、 separator 间隔字符和 index 指定遍历的索引。

  案例：

where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
    #{id}
</foreach>

等价于

where id in (
<foreach collection="ids" item="id" separator=",">
    #{id}
</foreach>
)

练习：

  1.在修改时经常使用到批量修改操作，创建如下方法完成 resource 批量修改。

  int updateAll(int[]ids, Resource resource);

  ids 为修改的数据id集合

  resource 包含修改的属性，不为空的属性才修改。

  参考代码：

<update id="updateAll">
    update resource
    <set>
        <if test="param1.name != null and param1.name != ''">
            name = #{param1.name},
        </if>
        <if test="param1.url != null and param1.url != ''">
            url = #{param1.url},
        </if>
        <if test="param1.pid != null">
            pid = #{param1.pid},
        </if>

        <if test="param1.orderNumber != null">
            order_number = #{param1.orderNumber},
        </if>
        update_time = now()
    </set>
    where id
    <foreach collection="param2" open="in (" close=")" separator="," item="i">#{i}</foreach>
</update>

接口方法

int update(Resource resource,List<Integer> ids);

八、collection 深入学习

  foreach 中的 collection 在实际开发中有如下使用方法：

  1.如果传入的是单参数且参数类型是一个 array 数组的时候，collection 的属性值为 array
  2.如果传入的是单参数且参数类型是一个 List 的时候，collection 属性值为 list
  3.如果传入的参数是多个的时候，我们就需要把它们封装成一个 Map，当然单参数也可，如果传入的参数是多个的时候，我们也可以放在实体类中（这种实际用到也是非常多的）

1.普通数组

<select id="select" parameterType="int">
    select * from person 
    where id in
    <foreach collection="array" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

使用时

int[] ids = {4,6};		
mapper.select(int [] ids);

2.List 集合

<select id="select" parameterType="int">
    select * from person
    where id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

使用时

List<Integer> list = new ArrayList<Integer>();
list.add(4);
list.add(6);
mapper.select(list);

3.map 传值

<select id="select" parameterType="map">
    delete from person
    where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>		
</select>

使用时

Map<String,Object> map = new HashMap<String,Object>();
int[] ids = {4,6};
map.put("ids", ids);		
mapper.select(map);

九、script（自）

  要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如：

@Update({"<script>",
      "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}",
      "</script>"})
void updateAuthorValues(Author author);

章节练习：（默写 30 分钟）

  1.使用动态标签库完成如下方法的编写，所写代码尽可能通用。Selective 表示实体属性为空时常用数据库默认值。

int deleteByPrimaryKey(Integer id);
int insert(User user);
int insertSelective(User user);
User selectByPrimaryKey(Integer id);
List<User> selectByPrimaryKeys(Collection<Integer> ids);
List<User> selectAll();
List<User> selectAllByPage(@Param("start") int start, @Param("size") int size);
int updateByPrimaryKeySelective(User user);
int updateByPrimaryKey(User user);

  参考代码：

<?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="cn.hxzy.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="cn.hxzy.entity.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="login_name" jdbcType="VARCHAR" property="loginName"/>
        <result column="login_password" jdbcType="VARCHAR" property="loginPassword"/>
        <result column="role_id" jdbcType="INTEGER" property="roleId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, `name`, login_name, login_password, role_id, create_time, update_time
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where id = #{id}
    </select>
    <select id="selectByPrimaryKeys" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
    <select id="selectAll" resultType="cn.hxzy.entity.User" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
    </select>
    <select id="selectAllByPage" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        limit #{start},#{size}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user
        where id = #{id}
    </delete>
    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cn.hxzy.entity.User" useGeneratedKeys="true">
        insert into user (`name`, login_name, login_password,
            role_id, create_time
        )
        values (#{name}, #{loginName}, #{loginPassword}, 
            #{roleId}, now()
        )
    </insert>
    <insert id="insertSelective" keyProperty="id" parameterType="cn.hxzy.entity.User"
            useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                `name`,
            </if>
            <if test="loginName != null">
                login_name,
            </if>
            <if test="loginPassword != null">
                login_password,
            </if>
            <if test="roleId != null">
                role_id,
            </if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="loginName != null">
                #{loginName},
            </if>
            <if test="loginPassword != null">
                #{loginPassword},
            </if>
            <if test="roleId != null">
                #{roleId},
            </if>
            now()
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="cn.hxzy.entity.User">
        update user
        <set>
            <if test="name != null">
                `name` = #{name},
            </if>
            <if test="loginName != null">
                login_name = #{loginName},
            </if>
            <if test="loginPassword != null">
                login_password = #{loginPassword},
            </if>
            <if test="roleId != null">
                role_id = #{roleId},
            </if>
            update_time = now()
        </set>
        where id = #{id}
    </update>
    <update id="updateByPrimaryKey" parameterType="cn.hxzy.entity.User">
        update user
        set `name` = #{name},
          login_name = #{loginName},
          login_password = #{loginPassword},
          role_id = #{roleId},
          update_time = now()
        where id = #{id}
  </update>
</mapper>