Mybatis의 동적 SQL 표현식 (IF, CHOOSE, WHEN, LIKE, FOREACH etc)

Mybatis의 동적 SQL 표현식 (IF, CHOOSE, WHEN, LIKE, FOREACH etc)

Mybatis를 사용하면서 동적 SQL 표현식을 많이 사용하시죠?
어떻게 사용하는지 예제를 통해서 확인해볼까요?

if 문

if 문 :

동적 SQL에서 가장 흔하게 사용하는 표현식으로 조건을 나타냅니다.

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

위의 경우는 여러개의 조건을 사용할때 사용하는 방식이죠.
하지만, WHERE절에 조건이 아무것도 없거나 여러개의 조건을 사용하고자 할때는 다음과 같이 표현해주죠.
'WHERE 1 = 1' 이후에 if 문을 사용하여 여러개의 조건을 추가하였습니다.
이렇게 하게 되면, 경우에 따라서 조건에 아무것도 없을 수도 있고, 최대 4개의 조건이 붙을수도 있겠죠.

<select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE 1 = 1
  <if test="state != null">
    AND state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="searchStartDate != '' and searchStartDate != null">
    AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') <![CDATA[>=]]> #{searchStartDate}
  </if>
  <if test="searchEndDate != '' and searchEndDate != null">
    AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') <![CDATA[<=]]> #{searchEndDate}
  </if>
</select>

 

choose, when, otherwise

choose, when, otherwise :

WHERE 조건절에 java에서의 switch문과 유사한 문법을 적용해보고 싶은 경우에 사용합니다.
title이 있는 경우 title의 조건을 추가하고, author의 name이 있는 경우 author.name을 조건으로 추가합니다.
title과 author.name 두가지 모두가 없을 경우에는 otherwise의 featured 조건을 사용하는 방식입니다.

<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

trim, where, set : 

동적 SQL을 작성할때 발생할 수 있는 문제중에서 흔하게 실수 할 수 있는 아래와 같은 SQL입니다.
어떤 오류가 발생 할 수 있을까요?

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

위의 쿼리를 실행할 경우 state, title, author의 값에 따라서 두가지 형태의 잘못된 쿼리가 만들어질 수 있을 겁니다.
WHERE절에 아무런 조건이 붙어 있지 않은 경우 : FAIL

SELECT * FROM BLOG
WHERE

WHERE절에서 'WHERE AND'와 같은 구문이 만들어지는 경우 : FAIL

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’

 

이러한 경우를 방지하기 위해서 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 #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

위에서 문제가 될만한게 뭐가 있는지 보이시나요?
전달 파라미터의 조건에 따라서 아래와 같은 결과가 나올 수 있겠죠?
이런 경우 WHERE절에서 'WHERE AND'와 같은 구문이 만들어지는 경우 : FAIL

  SELECT * FROM BLOG
  WHERE
        AND title like #{title}

그래서 이런 오류가 발생하지 않도록 trim문을 사용해줍니다.

 

trim :

아래의 trim문은 prefix로 where를 기본으로 붙여주고,
prefixOverrides로 되어있는 "AND | OR"가 나오면 해당 문자열을 지워주는 역할을 합니다.

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <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>
    </trim>
</select>

 

다음으로 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의 맨 마지막에 쉼표( , )가 붙을 수 있겠죠?
다음과 같은 경우입니다. Query문 오류가 발생하겠죠.

  update Author
    set
      username=#{username},
      password=#{password},
      email=#{email},
  where id=#{id}

그래서 이러한 오류가 발생하지 않도록
trim구문을 통해 set 표현을 함께 사용해주기도 합니다.

아래의 trim문은 prefix로 set를 기본으로 붙여주고,
suffixOverrides로 되어있는 ","가 맨 끝에 나오면 해당 문자열을 지워주는 역할을 합니다.

<update id="updateAuthorIfNecessary">
  update Author
    <trim prefix="set" suffixOverrides=",">
      <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>
    </trim>
  where id=#{id}
</update>

mysatis 구문을 작성하시는 분들중에 쉼표(,)를 앞에 쓰시고자 하시는 분들도 계시던데.
그런 경우에는 다음과 같이 사용하시면 되겠습니다.
아래의 trim문은 prefix로 set를 기본으로 붙여주고,
prefixOverrides로 되어있는 ","가 맨 앞에 나오면 해당 문자열을 지워주는 역할을 합니다.

<update id="updateAuthorIfNecessary">
  update Author
    <trim prefix="set" prefixOverrides=",">
      <if test="userId != null">userId=#{userId}</if>
      <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>
    </trim>
  where id=#{id}
</update>

 

trime에서 사용하는 구문중에 suffix에 대해서도 알아볼께요.
어떤 경우가 있을까요?

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  <if test="state != null">
    state = #{state}
  </if>
  <trim prefix="AND (" prefixOverrides="AND | OR" suffix=")">
    <if test="title != null">
      OR title like #{title}
    </if>
    <if test="author != null and author.name != null">
      OR author_name like #{author.name}
    </if>
    <if test="category != null and category != null">
      OR category like #{category}
    </if>
  </trim>
</select>

위의 trim 구문을 해석해보면
맨 앞에 "AND ("를 붙이고
trim내부문의 맨 앞에 "AND" 또는 "OR"가 있으면 제거하고,
맨 끝에 ")"를 붙여줍니다.

 

자~~
그러면 위에서 작성한 예제들을 토대로 trim문에 사용하는 속성 값에 대해서 정리를 해볼까요?

prefix	trim문에서 작성하는 쿼리의 맨 앞에 해당 문자를 붙여준다.
prefixOverrides	trim문에서 작성하는 쿼리의 맨 앞에 오는 문자들을 삭제한다.
suffixOverrides	trim문에서 작성하는 쿼리의 맨 끝에 오는 문자들을 삭제한다.
suffix	trim문에서 작성하는 쿼리의 맨 끝에 해당 문자를 붙여준다.

 

foreach

foreach :

쿼리문에서 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>

위의 구문을 해석하면 "list"형태의 값을 구분자 ","로 구분하고,
"("과 ")"로 감싸주도록 합니다.
정리하면 이런식이겠죠.

  SELECT *
  FROM POST P
  WHERE ID in (val1, val2, val3, val4)

 

bind

bind :

bind구문을 사용하면 OGNL표현식에서 변수를 생성해서
쿼리문에서 LIKE문을 표현할때 유용하게 사용하실 수 있습니다.
사용예는 아래와 같습니다.

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