[MyBatis] 마이바티스 동적 쿼리 작성하기

 

🌈 마이바티스(MyBatis) 동적 쿼리 작성

 

▪ 정적쿼리(Static SQL) : 조건에 상관없이 변하지 않는 쿼리

▪ 동적쿼리(Dynamic SQL) : 특정 조건에 따라 변경되는 쿼리

 

마이바티스에서 작성할 수 있는 기본적인 동적 쿼리는 단순히 WHERE절에 파라미터를 적용한 방법이다.

 

${ } 는 파라미터 값이 그대로 들어가며 #{ } 는 파라미터 값이 작은따옴표(' ') 로 감싸서 들어간다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap"> 
    SELECT * FROM USER_TABLE 
    WHERE ${searchType} = #{keyword} 
</select>

 

 

 

 

1️⃣ IF

if는 동적 SQL 에서 사용 시 WHERE절의 일부로 포함될 수 있다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
  SELECT * FROM USER_TABLE
  WHERE USER_STATE = ‘ACTIVE’
  <if test="USER_ID != null">
    AND USER_ID = #{USER_ID}
  </if>
</select>

위 구문은 USER_ID의 유무에 따라 쿼리에 조건이 추가될 수 있다.

 

 

 

 

2️⃣ Choose, When, Otherwise

프로그래밍 언어의 switch 와 유사한 기능으로 마이바티스에서는 choose 태그를 제공한다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
    SELECT * FROM USER_TABLE
    WHERE USER_STATE = ‘ACTIVE’
    <choose>
        <when test="USER_NAME != null">
            AND USER_NAME like #{USER_NAME}
        </when>
        <when test="USER_ID != null">
            AND USER_ID = #{USER_ID}
        </when>
        <otherwise>
            AND USER_ID = 'cocobi24'
        </otherwise>
    </choose>
</select>

위 구문에서 WHERE절의 동작 순서는 아래와 같다.

1. USER_NAME이 있을 경우 WHERE의 조건으로 USER_NAME을 추가한다.

2. USER_NAME이 존재하지 않을 경우WHERE의 조건으로 USER_ID을 추가한다.

3. USER_ID도 존재하지 않을 경우 USER_ID가 cocobi24인 것을 조건으로 추가한다.

 

 

 

 

3️⃣ Trim, Where, Set

아래는 WHERE절에 여러 개의 if가 있고 기본 WHERE 조건이 존재하지 않는 경우의 예시이다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
  SELECT * FROM USER_TABLE
  WHERE 
  <if test="USER_ID != null">
    AND USER_ID = #{USER_ID}
  </if>
  <if test="USER_NAME != null">
    AND USER_NAME = #{USER_NAME}
  </if>
</select>

 

if의 모든 조건이 해당하지 않을 경우 실행되는 쿼리는 아래와 같다.

SELECT * FROM USER_TABLE
WHERE

 

if문의 두 번째 조건만 만족할 경우 실행되는 쿼리는 아래와 같다.

SELECT * FROM USER_TABLE
WHERE
    AND USER_NAME = #{USER_NAME}

 

위와 같은 경우를 피하기 위해 where 태그를 추가하면 문제가 해결된다.

또한, if문에 속하는 조건이 AND나 OR로 시작한다면 그 또한 지워주게 된다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
  SELECT * FROM USER_TABLE
  <where>
    <if test="USER_ID != null">
      AND USER_ID = #{USER_ID}
    </if>
    <if test="USER_NAME != null">
      AND USER_NAME = #{USER_NAME}
    </if>
  <where>
</select>

 

where 태그와 같은 기능을 하는 trim 태그를 정의할 수도 있다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
  SELECT * FROM USER_TABLE
  <trim prefix="WHERE" prefixOverrides="AND |OR ">
    <if test="USER_ID != null">
      AND USER_ID = #{USER_ID}
    </if>
    <if test="USER_NAME != null">
      AND USER_NAME = #{USER_NAME}
    </if>
  </trim>
</select>

 

 

set 태그는 UPDATE 대상 칼럼을 동적으로 추가하기 위해 사용될 수 있다.

또한, set 엘리먼트는 동적으로 SET 키워드를 붙이고 필요 없는 콤마를 제거한다.

<update id="updateUserInfo">
  UPDATE USER_TABLE
    <set>
      <if test="USER_NAME != null">
        USER_NAME = #{USER_NAME} ,
      </if>
      <if test="USER_AGE != null">
        USER_AGE = #{USER_AGE} ,
      </if>
      <if test="USER_MAIL != null">
        USER_MAIL = #{USER_MAIL}
      </if>
    </set>
  WHERE USER_ID = #{USER_ID}
</update>

 

where 태그와 같은 기능을 하는 trim 태그는 아래와 같다.

<update id="updateUserInfo">
  UPDATE USER_TABLE
    <trim prefix="SET" suffixOverrides=",">
      <if test="USER_NAME != null">
        USER_NAME = #{USER_NAME}
      </if>
      <if test="USER_AGE != null">
        USER_AGE = #{USER_AGE}
      </if>
      <if test="USER_MAIL != null">
        USER_MAIL = #{USER_MAIL}
      </if>
    </trim>
  WHERE USER_ID = #{USER_ID}
</update>

 

 

 

 

3️⃣ foreach

쿼리문에 IN조건을 사용할 때 foraeach를 통해 collection에 대한 반복 처리를 할 수 있다.

 

또한, 컬렉션의 파라미터로는 Map이나 배열 객체와 더불어 List, Set과 같은 객체를 전달할 수 있다.

 

배열의 경우 index값은 현재 순서를 나타내고 value항목은 반복 과정에서 가져오는 요소를 나타낸다.

Map의 경우 index는 key객체가 되고 항목은 value객체가 된다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
  SELECT * FROM USER_TABLE
  <where>
    USER_ID IN
    <foreach collection="data" item="item" separator="," open="(" close=")">
	  #{item.USER_ID}
    </foreach>
  </where>
</select>

 

 

 

 

4️⃣ bind

bind 태그는 OGNL표현을 사용해서 변수를 만든 뒤 바인딩한다.

<select id="getUser" resultType="java.util.HashMap" parameterType="java.util.HashMap">
  <bind name="pattern" value="'%' + _parameter.getId() + '%'" />
  SELECT * FROM USER_TABLE
  WHERE USER_ID LIKE #{pattern}
</select>

 

 

 

 

📘 기타 기능

◾ script

매퍼 클래스에 어노테이션을 추가하여 스크립트를 통한 동적 쿼리를 작성할 수 있다.

@Update({
  "<script>",
  "  UPDATE Author",
  "  <set>",
  "    <if test='USER_NAME != null'> USER_NAME = #{USER_NAME} , </if>",
  "    <if test='USER_AGE != null'> USER_AGE = #{USER_AGE} , </if>",
  "    <if test='USER_MAIL != null'> USER_MAIL = #{USER_MAIL} </if>",
  "  </set>",
  "  WHERE USER_ID = #{USER_ID}",
  "</script>"
})
void updateUserInfo(User user);

 

◾ Multi-db vendor support

databaseIdProvider를 동적인 코드로 사용하면 서로 다른 데이터 베이스의 구문을 사용할 수 있다.

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      SELECT 'test' FROM DUAL
    </if>
    
    <if test="_databaseId == 'db2'">
      SELECT 'test' FROM sysibm.sysdummy1
    </if>
  </selectKey>
  
  INSERT INTO USER_TBL VALUES (#{USER_ID}, #{USER_NAME})
</insert>

 

 

 

 

 

 

 

📌 참고사이트

https://mybatis.org/mybatis-3/ko/dynamic-sql.html