[MyBatis] 동적 SQL

• MaBatis 공식 홈페이지 - Dynamic SQL

 

mybatis – MyBatis 3 | Dynamic SQL

 

---

1. 동적 SQL

 

JDBC에서 동적으로 쿼리문을 만드는 것은 꽤나 고통스럽다.

 

공백 문자와 콤마 하나도 신경 써야 한다.

 

MyBatis를 사용하면 고통을 줄일 수 있다.

 

MyBatis의 Dynamic SQL은 JSTL과 그 사용법이 비슷하다.

 

아래 예시는 MySQL을 기준으로 한다.

 

---

2. if

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

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

조건을 충족하면 동적으로 AND title like #{title}를 추가한다.

 

---

3. choose, when, otherwise

 

switch문과 비슷하다.

 

주어진 옵션들 가운데 하나의 옵션만 적용되길 원하다면 choose-when-otherwise를 사용하면 된다.

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like CONCAT('%', #{title}, '%')
    </when>
    <when test="author != null and author.name != null">
      AND author_name like CONCAT('%', #{author.name}, '%')
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

 

---

4. trim, where, set

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  <if test="state != null">
    state = #{state}
  </if>
  <if test="title != null">
    AND title like CONCAT('%', #{title}, '%')
  </if>
  <if test="author != null and author.name != null">
    AND author_name like CONCAT('%', #{author.name}, '%')
  </if>
</select>

 

state, title, author(또는 author.name) 모두 null이라면 최종적으로 완성된 SQL문은 다음과 같다:

SELECT * FROM BLOG
WHERE
-- 망했다.

 

또한 title만 null이 아니라면 SQL문은 다음과 같다:

SELECT * FROM BLOG
WHERE
AND title like CONCAT('%', ‘someTitle’, '%)
-- 망했다.

AND 때문에 망했다.

 

이처럼 경우에 따라서 WHERE과 AND를 알.잘.딱하게 넣어거나 빼주어야 한다.

 

MyBatis에서는 trim, where, set을 제공하고 있다.

 

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like CONCAT('%', #{title}, '%')
    </if>
    <if test="author != null and author.name != null">
        AND author_name like CONCAT('%', #{author.name}, '%')
    </if>
  </where>
</select>

• where : 필요하다면 WHERE을 넣고, 불필요한 AND 또는 OR을 적절하게 제거한다.

 

<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을 넣고, 불필요한 ,을 적절하게 제거한다.

 

MyBatis 측에서는 90%의 케이스에서 문제없이 동작한다고 주장한다.

 

문제가 발생하는 10%의 케이스는 trim을 사용한 custumizing을 통해서 해결할 수 있다고 한다.

<where> 
    ... 
</where>
<!-- 둘은 같음 -->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

<set>
    ...
</set>
<!-- 둘은 같음 -->
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

• prefix : WHERE, SET과 같이 필요에 따라서 추가할 것.
• prefixOverrides : AND , OR와 같이 불필요 시 제거할 것. 그중에서도 문장 앞에 위치한 것.
• suffixOverrides : ,와 같이 불필요 시 제거할 것. 그중에서도 문장 끝에 위치한 것.

 

---

5. foreach

 

Dynamic SQL문을 완성하기 위해서 Collection을 순회하고 싶다면 foreach를 사용할 수 있다.

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

• open="ID in(" : 문장의 시작에 삽입.
• separator="," : item과 item 사이에 ,를 삽입한다.
• close=”)” : 문장의 끝에 삽입.

 

list에 1, 2, 3, 4가 저장되어 있다면 다음과 같은 SQL이 완성된다:

SELECT *
FROM POST P
WHERE ID in (1,2,3)

List, Set, Map, Array 등을 순회할 수 있다.

 

이때 Iterable 또는 Array라면 index는 몇 번째 순회인지 표시한다.

 

하지만 Map 또는 Collection of Map.Entry라면 index는 key를 item은 value를 가진다.

 

---

6. script

 

mapper class에서 dynamic 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);

 

---

7. bind

 

bind를 사용하면 SQL에 삽입되기 전에 값을 조작할 수 있다.

<select id="selectBlogsLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE title LIKE CONCAT('%', #{title}, '%')
</select>

MySQL 기준으로 LIKE '%아무타이틀%'을 구현하기 위해서는 CONCAT('%', #{title}, '%')라고 붙이고 있다.

 

만약 사용하는 DB가 변경되거나 추가되면 망한다.

 

이때 bind를 사용하면 해결할 수 있다.

 

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

이렇게 bind를 사용하면 SQL에 삽입되기 전에 값을 조작할 수 있다.

 

---

8. insert 동적 쿼리

 

MyBatis에서 insert 문에 동적 쿼리를 사용하는 예시:

<insert id="insertAuthor">
  INSERT INTO Author
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="username != null">username,</if>
    <if test="password != null">password,</if>
    <if test="email != null">email,</if>
    <if test="bio != null">bio,</if>
  </trim>
  values
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="username != null">#{username},</if>
    <if test="password != null">#{password},</if>
    <if test="email != null">#{email},</if>
    <if test="bio != null">#{bio},</if>
  </trim>
</insert>

이 예제에서는 username, password, email, bio 필드를 가진 Author 테이블에 데이터를 동적으로 삽입한다.

 

각 필드의 값이 null이 아닐 경우만 쿼리에 포함되며, 각 필드가 null일 경우 해당 필드는 쿼리에 포함되지 않는다.

 

이를 통해 필요한 필드만 동적으로 쿼리에 포함할 수 있다.

 

---