Mybatis日常使用小结

这里主要介绍自己在项目中使用到的动态sql和一些注意事项。

动态SQL

简介：动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

我这里引用Mybatis官网对于常见的用法和我自己项目中的实际sql。

if

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如：

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

这条语句提供了可选的查找文本功能。如果不传入 “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果（细心的读者可能会发现，“title” 的参数值需要包含查找掩码或通配符字符）。

如果希望通过 “title” 和 “author” 两个参数进行可选搜索该怎么办呢？首先，我想先将语句名称修改成更名副其实的名称；接下来，只需要加入另一个条件即可。

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

choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员挑选的 Blog）。

<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

前面几个例子已经合宜地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例，这次我们将 “state = ‘ACTIVE’” 设置成动态条件，看看会发生什么。

<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>
</select>

如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：

SELECT * FROM BLOG
WHERE

这会导致查询失败。如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’

这个查询也会失败。这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决，以至于解决过的人不会再想碰到这种问题。

MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中，可以对其进行自定义以符合需求。而这，只需要一处简单的改动：

<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>

自己项目中使用的类似的：

<select id="getTotalByParams" resultType="Integer"
		parameterType="BlogQuery">
		select count(*) from hi_blog
		<where>
			<if test="title!= null and title != ''">
				title like "%"#{title}"%"
			</if>
			<if test="typeId != null and typeId != ''">
				and type_id = #{typeId}
			</if>
			<if test="isRecommend != null and isRecommend != ''">
				and is_recommend = #{isRecommend}
			</if>
		</where>


	</select>

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。

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

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

来看看与 set 元素等价的自定义 trim 元素吧：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

注意，我们覆盖了后缀值设置，并且自定义了前缀值。

foreach

动态 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>

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

提示 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

注意事项

暂时用到的sql优化

1. 查询时用到如select * from table之类的，最好将*改为全部的列名，以免*通过映射转为列名时的时间消耗。
2. 在where语句中尽量将能排除大量数据的条件写在靠前的地方

注意

1. 模糊查询时不用’’，要使用""。
   示例：

<select id="getTotalByParams" resultType="Integer"
		parameterType="BlogQuery">
		select count(*) from hi_blog
		<where>
			<if test="title!= null and title != ''">
				title like "%"#{title}"%"
			</if>
			<if test="typeId != null and typeId != ''">
				and type_id = #{typeId}
			</if>
			<if test="isRecommend != null and isRecommend != ''">
				and is_recommend = #{isRecommend}
			</if>
		</where>


	</select>

因为#{…}解析成sql语句时候，会在变量外侧自动加单引号’ '，所以这里 % 需要使用双引号" "，不能使用单引号 ’ '，不然会查不到任何结果。

2. 当数据库字段与实体类字段不匹配时，怎么处理：此时就不能在select标签中使用resultType了，因为不匹配，所以需要一个映射将两者之间关联起来，示例如下：

<resultMap type="Type" id="TypeMap">
		<result property="id" column="id" />
		<result property="typeName" column="type_name" />

</resultMap>

<select id="listType" resultMap="TypeMap">
		select id,type_name
		from hi_type
</select>

select标签中的resultMap的值为resultMap标签的id， 而resultMap中property代表实体类的属性，而column代表数据库对应表中的列名。

3. 当返回值是以下类型时：
   1、基本类型 ：resultType=基本类型
   2、List类型： resultType=List中元素的类型
   3、Map类型 ： resultType =map

---

4. 当传参时传不同类型的两个参数怎么办，我现在项目中用的最简便方式：

// dao层的方法
Integer updateType(@Param("id") Long id, @Param("typeName") String typeName);

// 对应mapper的SQL
<update id="updateType">
		update hi_type t set t.type_name = #{typeName} where
		t.id = #{id}
</update>

5. 别名的使用事项：使用了别名就不能使用原名了， 可以通过空格或者as给表起别名但是注意如果操作的数据库是Oracle的话，只能使用空格，as不符合Oracle的语法。 还有在mysql中的delete语句中，使用别名会有所变化：

// 在delete后面还要加别名名称，有几个别名写几个
delete t from hi_type t where t.id = #{id} 
// 下面写法会报错
delete from hi_type t where t.id = #{id} 

6. select，insert，update，delete标签使用：insert/update/delete标签只是一个模板，在做操作时，其实是以SQL语句为核心的 。即在做增删时 insert/update/delete标签可通用 ，但做查询时只能用select标签，我们提倡什么操作就用什么标签 。还有在mybatis中如果是select标签中使用update语句不会有返回值，，，所以得改为update

---

7. 在使用项目中有时需要使用top功能，即根据某列排序后查询出最前面的几行，在SQLServer中由top这个功能， 而mysql中通过分页中 的limit即可实现，不过和分页功能不同的是只需要传一个size参数即可，即查询top多少的数据，示例：

<select id ="listBlogTop" resultMap="BlogMap" parameterType="Integer"> 
		select id,title from hi_blog where is_published = 1 and is_recommend = 1  order by update_time desc limit #{size} </select>

8. 插入时如果不插入id,则数据库中的表的id字段得设置自动增长。

9. 如果自己项目中有很多根据一个参数查询的SQL,可以简化为一条SQL语句。
   如：

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);

@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);

@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);

// 其它的 "findByXxx" 方法

可以替换为：

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

其中 ${column} 会被直接替换，而 #{value} 会使用 ? 预处理。 这样，就能完成同样的任务：

User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");

这种方式也同样适用于替换表名的情况。

提示 用这种方式接受用户的输入，并用作语句参数是不安全的，会导致潜在的 SQL 注入攻击。因此，要么不允许用户输入这些字段，要么自行转义并检验这些参数。