首页 > itarticle > mybatis dynamic sql

mybatis dynamic sql

admin 1月 29, 2021 0

MYBATIS Dynamic SQL

Dynamic SQL is a very powerful feature of MyBatis. It enables to build queries based on the scenario dynamically.

choose (when, otherwise)

trim (where, set)

foreach

The if Statement

This statement provides an optional text search type of functionality.

<select id = "getRecByName_Id" parameterType = "Student" resultType = "Student">
   SELECT * FROM STUDENT      
   <if test = "name != null">
      WHERE name LIKE #{name}
   </if>
   <if test = "id != null">
      AND id LIKE #{id}
   </if> 
</select>

The choose, when, and otherwise Statements

a choose element, which is similar to Java’s switch statement.

<select id = "getName_Id_phone" parameterType = "Student" resultType = "Student">
   SELECT * FROM STUDENT
 
   <where>
      <if test = "id != null">
         id = #{id}
      </if>
      <if test = "name != null">
         AND name LIKE #{name}
      </if>
   </where>
       
</select>

The foreach Statement

foreach element allows you to specify a collection and declare item and index variables that can be used inside the body of the element

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