MyBatis - 동적 SQL 구현하기(if / choose / trim/ foreach)

 

 

JDBC MVC 패턴으로 DB를 수정, 편집하기 위해서 sql.xml에 SQL문을 저장해두고 사용하는 경우가 많다. 다만 이렇게 개발을 할 때, 상황에 따라 요구하는 컬럼이 다르거나 특정조건을 추가할 때마다 새로운 sql문을 만들고 key값을 설정해줘야했다. 동적 SQL을 구현할 수는 있지만 매우 복잡했다.

하지만 마이바티스를 이용하면 동적 SQL을  JSTL을 사용하여 좀 더 간단하게 처리할 수 있다. 대표적인 문법을 마이바티스 홈페이지를 참조하여 소개하고 구현하고자 한다.

 

 

1. if

• 조건문 if는 조건절 WHERE의 일부로 포함되어 조건문 사용에 유동성을 줄 수 있다.
• 아래 조건문은 게시판 테이블에서 게시글에 대한 모든 정보를 가져오는 sql문이다. 해당 sql문은 삭제되지 않은 모든 게시물 정보를 불러온다.
• 이 구문은 선택적으로 문자열을 검색 기능을 제공한다. 만약 title값이 없다면 모든 active 상태의 게시글이 리턴되럯이다. 하지만 title 값이 있다면 그 값과 비슷한 데이터를 찾게 될 것이다.
• writer도 마찬가지이다. 아래와 같이 if문을 중복하여 사용하여 조건문을 유연하게 사용할 수 있다.

<select id="boardDetail" resultType="board">
  SELECT * FROM BOARD WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="writer != null and writer.name != null">
    AND writer_name like #{writer.name}
  </if>
</select>

 

 

 

2. choose, when, otherwise

• 자바에서 switch와 유사한 기능을을 한다. 자바에서와 마찬가지로 if문은 단순한 조건문으로 조건식이 참(true)이면 if문 안에 있는 코드 블록이 실행되고 조건식이 거짓(false)이면 실행되지 않는다. 
• switch문은 여러 개의 조건 중 하나를 선택해서 실행할 때 사용한다. switch는 기본적으로 변수나 표현식의 값을 검사해서 일치하는 case문을 찾아 해당하는 코드 블록을 실행하고 일치하는 case가 없으면 default를 실행한다.
• 아래 코드는 BOARD 테이블에서 STATE가 'ACTIVE'인 레코드를 검색하고, 선택적으로 title 혹은 writer의 이름으로 검색한다.
• 위 코드에서는 title 변수가 null이 아니면, 즉 값이 있다면 AND title like #{title} 구문이 추가된다. 그렇지 않고 writer.name 변수가 null이 아니면 AND writer_name like #{writer.name} 구문이 추가된다.
• 둘 다 null이면 AND featured = 1 구문이 추가됩니다.
• <when>요소는 switch와 마찬가지로 여러 조건 중 해당하는 하나만 선택되며 먼저 서술되어있을 수록 우선 순위가 높다. 즉, title 변수가 값이 있다면, AND title like #{title} 구문이 실행되고 writer.name 변수는 첫 번째 <when> 요소가 false인 경우에만 검사된다.

 

<select id="boardDetailSearch"  resultType="board">
  SELECT * FROM BOARD WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="writer != null and writer.name != null">
      AND writer_name like #{writer.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

 

 

3. trim, where, set

 

1) where

<select id="boardDetail" resultType="board">
  SELECT * FROM BOARD
  WHERE
  <if test="state != null">
    state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="writer != null and writer.name != null">
    AND writer_name like #{writer.name}
  </if>
</select>

• 다음의 sql문 중 어떤 조건에도 해당하지 않는다면 출력되는 sql문은 [SELECT * FROM BOARD WHERE] 일 것 이다.
• 이 경우 SQL문은 정상적으로 수행되지 않는다. 이때 <where> 를 사용할 수 있는데 <where>엘리먼트는 태그에 의해 컨텐츠가 리턴되면 단순히 "WHERE"를 추가하여 출력한다. 조건이 없는 경우 where는 생성되지 않는다.
• 다만, 컨텐츠가 “AND”나 “OR”로 시작한다면 그 “AND”나 “OR”를 지워버린다.

<select id="boardDetail" resultType="board">
  SELECT * FROM BOARD
  <where>
  <if test="state != null">
    state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="writer != null and writer.name != null">
    AND writer_name like #{writer.name}
  </if>
  </where>
</select>

 

2) trim

• <trm>은 <where>절과 달리 where뿐만 아니라 여러 요소를 동적으로 생성할 때 사용 된다. SQL 쿼리의 시작, 끝 또는 어디든지 사용할 수 있다.
• 또한 불필요한 공백 문자도 제거할 수 있다.
• override 속성은 오버라이드하는 텍스트의 목록을 제한한다. 결과는 override 속성에 명시된 것들을 지우고 with 속성에 명시된 것을 추가한다.
• 따라서, 아래 코드는 'AND' 와 'OR'의 접두사가 있는 경우 이를 지우고 접두사 WHERE를 출력하라는 뜻이다. 이를 통해 조건절이 불필요하게 'AND' 와 'OR'로 시작하지 않고 'WHERE'로 시작하는 것을 보장할 수 있다.

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

 

 

 

3) set

 

• 아래 쿼리는 'Writer'테이블에서 id가 일치하는 레코드를 업데이트 한다.
• <set> 요소는 업데이트할 컬럼과 값을 지정하는 블록이다. <set> 요소 내에는 <if> 요소가 포함되어 있고 각 <if> 요소는 해당 열의 값을 동적으로 저장한다.
• 각 <if> 요소에는 test 속성이 있으며, 이 속성은 해당 열이 업데이트되어야 하는지 여부를 판단하는 조건을 주는데 데 사용된다.
• 예를 들어, username이 null이 아니면 해당 열을 업데이트하고, null이면 업데이트하지 않는다.

 

<update id="updateWriterIfNecessary">
  update Writer
    <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>

 

 

 

4. foreach

• foreach는 동적 SQL에서 collection에 대한 반복처리를 할 때 사용된다. 
• 아래 쿼리는 ID가 list에 포함된 값 중 하나와 일치하는 레코드를 조회한다. 각 속성에 대한 설명은 다음과 같다.
• list:  'foreach'가 반복할 컬렉션을 지정
• item: 반복 중 현재 항목에 대한 참조를 지정한다.
• index: 반복 중 현재 항목에 대한 인덱스에 대한 참조를 지정한한다.
• open, separator, close 속성은 반복되는 SQL 구문의 시작, 구분자 및 끝을 지정한다. 위 코드에서는 ID in (로 시작하고, ,로 구분하며, )로 끝나는 SQL 구문이 반복된다.
• nullable: 컬렉션이 null인 경우 'foreach'가 무시되도록 한다.

 

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

 

• 예를 들어, list가 {1, 3, 5}라면, 아래와 같은 SQL문이 실행된다. 
• 만약 list가 비어있거나 null이라면, 아무런 레코드도 조회되지 않는다.

SELECT *
FROM POST P
WHERE ID IN (1, 3, 5)