mybatis 基本使用 —— 动态SQL

Mr.Hdd大约 6 分钟...

mybatis 动态 SQL

【动态 SQL】解决的问题：SQL语句的拼接。

if

主要用于某些判断条件为 null 或者为 "" (空字符串)可能会导致的查询失败。

【参数】：

test : 在其中书写判断语句，判断语句中可以使用 == ，!= ，> ，>= 比较符号，对于复合条件，可以使用 and 和 or 进行连接。
- 当 test 中的条件成立时，在 <if></if> 中的 SQL 语句会生效；
- 不成立时，其中的 SQL 语句不会生效。

例如：查询某一个班上学生成绩大于某个分数的学生的信息。

查询时需要传递两个参数，一个是班级，一个是成绩。如果未传入成绩时，可以查询这个班所有学生的成绩。

<select id="findScoreGe" resultType="com.pojo.Student">
  SELECT * FROM all_students
  WHERE class_name = #{className}
  <if test="score!=null">
    AND score > #{score};
  </if>
</select>

public interface StudentMapper {
   /**
   * 查询某一个班上学生成绩大于某个分数的学生的信息
   * @return 符合条件的学生信息
   */
   List<Student> findScoreGe(@Param("className") String className, 
                             @Param("score") Integer score);
}

相关信息

在上面语句中，如果传入的 score==null ，那么就不将其中的 SQL 语句进行拼接。if 中的 test 中写的就是判定条件。

例如：查询某一个班上学生成绩大于某个分数且排名大于某个名次的学生的信息。

<select id="findScoreGeOneHundred" resultType="com.pojo.Student">
  SELECT * FROM all_students
  WHERE class_name = #{className}
  <if test="score!=null and rank!=null">
    AND score > #{score}
    AND rank > #{rank}
  </if>
</select>

public interface StudentMapper {
   /**
   * 查询某一个班上学生成绩大于某个分数且排名大于某个名词的学生的信息
   * @return 符合条件的学生信息
   */
   List<Student> findScoreGeOneHundred(@Param("className") String className, 
                             				   @Param("score") Integer score,
                                       @Param("rank") Integer rank);
}

相关信息

<if></if> 也可以连续多次使用，这样如果某个 <if></if> 中的条件满足时，就可以将其中的 SQL 语句加上。

choose、when 、otherwise

在 SQL 语句中有时不想使用所有的条件，而是从其中选择一个使用，就像 switch 语句一样。那么此时可以使用这套组合。

【基本结构】

 <choose>
    <when test="...">
      ...
    </when>
    <when test="...">
      ...
    </when>
   	<!-- 可以有多个 when -->
    <otherwise>
      ...
    </otherwise>
  </choose>

相关信息

依次执行 when 中的 test 判定条件，如果满足就会执行，并且跳出 choose ，若所有的 when 都没有执行，那么就是执行 otherwise

例如：根据学生的学号或学生的姓名进行查询，当两者都为空时，返回一个预置学生( id=0)

<select id="findStudent"
     resultType="Student">
  SELECT * FROM all_students
  WHERE class_name = #{className}
  <choose>
    <when test="id != null">
      AND id = #{id}
    </when>
    <when test="studentName != null">
      AND studentName = #{studentName}
    </when>
    <otherwise>
      AND id = 0
    </otherwise>
  </choose>
</select>

public interface StudentMapper {
   /**
   * 根据学生的学号或学生的姓名进行查询
   * @return 符合条件的学生信息
   */
   List<Student> findStudent(@Param("className") String  className,
     												 @Param("id") Long id, 
                             @Param("studentName") String studentName);
}

trim、where、 set

相关信息

使用 <if></if> 和 <choose></choose> 解决了 SQL 多个筛选条件时，后续的 AND 语句可能失败的情况，那么如果一开始的 WHERE 接一个判断语句就有问题的话，该如何解决？

例如下面情况。

<select id="findStudent"
     resultType="Student">
  SELECT * FROM all_students
  WHERE 
  	<if test="className != null">
  		class_name = #{className}
  	</if>
    <if test="id != null">
      AND id = #{id}
    </if>
    <if test="studentName != null">
      AND studentName = #{studentName}
    </if>
</select>

如果上面语句中 className ，id ，studentName 都为空的话，会被解析为如下 SQL语句：

SELECT * FROM all_students
WHERE

那么就这会导致查询失败，或者 className 为空，id 为 34 时，会解析为如下：

SELECT * FROM all_students
WHERE
AND id = 34

同样也会导致查询失败

【解决方案一】

使用 <where></where> ：

<select id="findStudent"
     resultType="Student">
  SELECT * FROM all_students
  <where> 
  	<if test="className != null">
  		class_name = #{className}
  	</if>
    <if test="id != null">
      AND id = #{id}
    </if>
    <if test="studentName != null">
      AND studentName = #{studentName}
    </if>
  </where>
</select>

相关信息

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

注意

方案一中，对于后续的所有判断语句，必须以 AND 或者 OR 来开头，这对 SQL 的规范性有所要求，如果这个 AND 或者 OR 正好出现在上一条件的末尾。而不是开头，那么其就会无法被去掉，进而造成 SQL 语句的执行失败。

【解决方案二】

使用 <trim></trim>

参数：

prefix trim 中整体语句的前缀
suffix trim 中整体语句的后缀
prefixOverrides trim 中每个独立语句想要省去的前缀
suffixOverrides trim 中每个独立语句想要省去的后缀

相关信息

当 trim 中没有任何语句时，prefix 和 suffix 中的内容就不会生效。

与 <where></where> 等价的写法：

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

相关信息

prefixOverrides="AND | OR " 标签前缀为 AND 或者 OR 的都会省去

<select id="findStudent"
     resultType="Student">
  SELECT * FROM all_students
  <trim prefix="WHERE" prefixOverrides="AND | OR ">
  	<if test="className != null">
  		class_name = #{className}
  	</if>
    <if test="id != null">
      AND id = #{id}
    </if>
    <if test="studentName != null">
      AND studentName = #{studentName}
    </if>
  </trim>
</select>

在更新语句中，也可以使用 <trim></trim> 元素来替代 <set></set> 元素，并且可以将条件不成立语句后面的 , 省去。

对于 <set></set> 元素本身来说，若子句的开头为【,】 , 则会将其去除。

<update id="modifyUserInfo">
  update smbms.smbms_user
  <trim prefix="SET" suffixOverrides="," suffix="WHERE id = #{id}">
    <if test="userCode != null">userCode = #{user.userCode},</if>
    <if test="userName != null">userName = #{user.userName},</if>
    <if test="userPassword != null">userPassword = #{user.userPassword},</if>
  </trim>
</update>

foreach

对于需要迭代的集合，数组，那么就可以使用 foreach 元素。

【参数】

collection 为可迭代的类型
item 为迭代的每个一个元素
index 每个迭代元素的索引
separator 为每个迭代元素之间的间隔符
open 为第一迭代元素前加的字符
close 为最后一个迭代元素后加的字符

例如：通过 id 数组批量删除用户

public interface UserMapper {
  /**
   * 通过 id 数组批量删除用户
   * @param ids id 数组
   * @return 影响行数
   */
  Integer deleteUsersById(@Param("ids") Long[] ids);
}

<delete id="deleteUsersById">
  DELETE FROM smbms.smbms_user
  WHERE smbms_user.id IN
  <foreach collection="ids" item="id"
           separator="," open="(" close=")">
    #{id}
  </foreach>
</delete>

# 上面 xml 等价的 sql 语句
DELETE FROM smbms.smbms_user 
WHERE smbms_user.id IN ( ? , ? , ?, ...)

<delete id="deleteUsersById">
  DELETE FROM smbms.smbms_user
  WHERE smbms_user.id =
  <foreach collection="ids" item="id" separator="or">
    #{id}
  </foreach>
</delete>

# 上面 xml 等价的 sql 语句
DELETE FROM smbms.smbms_user 
WHERE smbms_user.id = ? or ? or ? ...

例如：批量添加用户

public interface UserMapper {
  /**
   * 批量添加用户
   * @param users 用户集合
   * @return 影响行数
   */
  Integer insertUsers(@Param("users") List<User> users);
}

<insert id="insertUsers">
  INSERT INTO smbms.smbms_user VALUES
  <foreach collection="users" item="user" separator=",">
    (#{user.id}, #{user.userCode}, #{user.userName},null, null)
  </foreach>
</insert>

# 上面 xml 等价的 sql 语句
INSERT INTO smbms.smbms_user 
VALUES (?, ?, ?,null, null),
			 (?, ?, ?,null, null),	
       ...

上述的的元素常用于各个 SQL 语句中，还有

你认为这篇文章怎么样？

昵称

邮箱

网址

按正序
按倒序
按热度

mybatis 基本使用 —— 动态SQL

# if

# choose、when 、otherwise

# trim、where、 set

# foreach

预览:

if

choose、when 、otherwise

trim、where、 set

foreach