MyBatis 4.动态SQL

最新推荐文章于 2024-10-17 11:58:45 发布

Vincent_Li315

最新推荐文章于 2024-10-17 11:58:45 发布

阅读量284

点赞数

分类专栏： MyBatis 文章标签： mybatis

MyBatis 专栏收录该内容

5 篇文章

订阅专栏

本文探讨了在MyBatis中进行复杂条件查询的最佳实践，包括如何使用if、choose、trim和set标签来构建动态SQL，提高查询效率，并介绍了bind、foreach等高级用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if

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

<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

<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

以下语句存在不合理的地方, 如果第一个if为false , 则语句失败. 考虑使用以下的两种方法

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

1.使用where, 如果where第一个碰到AND或者OR开头 , 就会自动替换成where.

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

2.使用trim

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

update操作的时候 , 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的写法如下:

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

foreach 遍历集合

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

bind绑定上下文中的一个元素

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

读数据库支持

可以使用databaseIdProvider中设定的_databaseId变量决定数据库的查询.

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>