Dynamic Query
개요
일반적으로 검색 기능이나 다중 입력 처리 등을 수행할 경우, SQL을 실행하는 DAO를 여러 번 호출하거나 batch 기능을 이용하여 버퍼에 담아서 한번에 실행 시키는 방식으로 쿼리를 구현했다.
MyBatis에서는 이를 동적으로 제어할 수 있는 구문을 제공하여 좀 더 쉽게 쿼리를 쉽게 구현할 수 있는 기능을 지원한다.
xml에서 지원 구문 종류
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
OGNL (Object Graph Navigation Language)
single quatation('') 구간의 값은 리터럴 값으로 보고 그게 아닌 이름은 객체의 필드 또는 변수로 인식하게 작성하는 기법이다.
- gte (>=) : greater than equal
- gt (>) : greater than
- lte (<=) : less than equal
- lt (<) : less than
- eq (==) : equal
- neq (!=) : not equal
위와 같은 OGNL 기법들이 있으며 용어가 헷갈리지 않게끔 작성해뒀다. 아래부터는 지원 구문에 대해 활용해보자.
1. if
- 동적 쿼리를 구현할 때 가장 기본적으로 사용되는 구문이다.
- (test 속성으로 작성된) 특정 조건을 만족할 경우 내부의 구문을 쿼리에 포함한다.
<select id="searchBoard" resultType="arraylist">
SELECT * FROM BOARD
WHERE writer = 'admin'
<if test="title != null">
AND title like #{title}
</if>
</select>다중 if 구문도 가능하다.
<select id="searchBoard" resultType="arraylist">
SELECT * FROM BOARD
WHERE writer = 'admin'
<if test="title != null">
AND title like #{title}
</if>
<if test="location != null">
AND location like #{location}
</if>
</select>이전까지 하던 메뉴 데이터를 가지고 사용한 if 구문도 함께 보자.
메뉴 or 카테고리 명으로 검색해서 메뉴 목록을 보여주는 것을 구현해보았다.
condition, value 을 입력받아 아래와 같이 구현된 if 구문 속에서 쿼리를 포함하거나 포함하지 않는 식으로 사용해서 데이터를 출력할 수 있다.
<select id="searchMenu" parameterType="SearchCriteria" resultMap="menuResultMap">
SELECT
A.MENU_CODE
, A.MENU_NAME
, A.MENU_PRICE
, A.CATEGORY_CODE
, A.ORDERABLE_STATUS
FROM TBL_MENU A
<if test="condition == 'category'">
JOIN TBL_CATEGORY B ON (A.CATEGORY_CODE = B.CATEGORY_CODE)
</if>
WHERE A.ORDERABLE_STATUS = 'Y'
<if test="condition == 'category'">
AND B.CATEGORY_NAME = #{ value }
</if>
<if test="condition == 'name'">
AND A.MENU_NAME LIKE CONCAT('%', #{ value }, '%')
</if>
</select>xml 속 parameterType 에 있는 SearchCreiteria
public class SearchCriteria {
private String condition;
private String value;
public SearchCriteria() {
}
public SearchCriteria(String condition, String value) {
this.condition = condition;
this.value = value;
}
}출력 결과
name과 밥이 각각 condition, value 이다.
2. choose (when, otherwise)
Java의 if-else, switch 문과 비슷한 구문이다.
사용법
- <choose> 안의 <when>은 if 또는 case의 역할을, <otherwise>는 else / default의 역할을 한다.
<when>태그는<if>태그와 형식이 흡사하며, 다중 사용할 수 있으나 하나의 조건 만족에 대해서만 구문을 추가한다.<otherwise>태그는<when>태그의 조건 중 어떤 것도 만족하지 않는 경우이다.
<choose>
<when test=" 조건식 ">
쿼리 구문
</when>
<when test=" 조건식 ">
쿼리 구문
</when>
. . .
<otherwise>
쿼리 구문
</otherwise>
</choose>사용 예시
<select id="searchBoard" resultType="arraylist">
SELECT * FROM BOARD
WHERE COMMENT != ''
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="writer != null">
AND writer like #{writer}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>3. Trim (where, set 태그)
Trim에 대해 배우기 전에
<select id="searchBoard" resultType="arraylist">
SELECT * FROM BOARD
WHERE
<if test="writer != null">
writer = #{writer}
</if>
<if test="title != null">
AND title like #{title}
</if>
</select>- if 조건 중 하나의 조건도 만족하지 못했을 때
- 첫 번째 조건은 만족 X면서, 2번째 조건은 만족할 때
위 2가지 다 오류가 생긴다.
1번의 경우
SELECT * FROM BOARD
WHERE2번의 경우
SELECT * FROM BOARD
WHERE
AND title LIKE ' OOO '위 2가지 경우로 인해 오류가 발생할 때 ⇒ trim (where, set) 으로 해결할 수 있다.
<trim>: 쿼리의 구문의 특정 부분을 없앨 때 쓰인다.
태그 안의 구문에 처음 시작할 단어와 시작 시 제거해야 할 단어를 명시하여 쿼리를 완성하도록 한다.
trim 태그의 속성
- prefix : 처리 후 엘리먼트의 내용이 있으면 가장 앞에 붙여주는 내용 기술
- prefixOverrides : 처리 후 엘리먼트 내용 중 가장 앞에 속성값에 해당하는 문자를 자동 삭제
- suffix : 처리 후 엘리먼트의 내용이 있으면 가장 뒤에 붙여주는 내용 기술
- suffixOverrides : 처리 후 엘리먼트 내용 중 가장 뒤에 속성값에 해당하는 문자를 자동 삭제
사용 예시
<select id="searchBoard" resultType="arraylist">
SELECT * FROM BOARD
<trim prefix="WHERE" prefixOverrides="AND | OR">
<if test="writer != null">
writer LIKE CONCAT('%', #{ writer }, '%')
</if>
<if test="title != null">
AND title LIKE CONCAT('%', #{ title }, '%')
</if>
</trim>
</select>if 구문의 각각의 조건이 만족한다면 <trim>태그 내의 구문을 생성하고, 내용이 하나라도 있으면 구문의 가장 앞에 WHERE 를 붙인다.
근데 위에 잘 보면 prefixOverrides="AND | OR" 부분이 있는데 이건 만약 구문의 시작이 AND or OR 인 경우에 해당 문자를 떼고, WHERE을 붙여서 쿼리를 완성한다는 뜻이다.
단, 엘리먼트 내에 작성하는 구문에 WHERE를 작성하지 않아야 한다. 그렇지 않으면 WHERE이 2개 생성(WHERE WHERE)될 수 있다.
엘리먼트 내용이 없으면 생성이 되지 않는다.
- 둘다 만족할 시
WHERE writer LIKE CONCAT('%', #{ writer }, '%')
AND title LIKE CONCAT('%', #{ title }, '%')- 2번째 조건만 만족할 시
WHERE title LIKE CONCAT('%', #{ title }, '%')Trim 에 속하는 WHERE 구문
<where>: 기존 쿼리의 WHERE 절을 동적으로 구현할 때 사용한다.
<where>태그는 단순히 WHERE만을 추가하지만, 만약 태그 안의 내용이 'AND' 나 'OR'로 시작할 경우 'AND'나 'OR'를 제거한다.
단, 엘리먼트 내에 작성하는 구문에 WHERE를 작성하지 않아야 한다. 그렇지 않으면 WHERE이 2개 생성(WHERE WHERE)될 수 있다.
where엘리먼트 내 모두 쿼리문이 추가되지 않는 상황이면 where를 무시한다.
<select id="searchBoard" resultType="arraylist">
SELECT * FROM BOARD
<where>
<if test="writer != null">
writer = #{writer}
</if>
<if test="title != null">
AND title like #{title}
</if>
</where>
</select>Trim 에 속하는 SET 구문
<set>: 기존 쿼리의 UPDATE SET 절을 동적으로 구현할 때 사용한다.
-
고정으로 모든 컬럼을 변경하지 않고, 주어진 일부 값에 대해서만 변경할 수 있는 동적 update 쿼리 구현을 돕는다.
-
쿼리 실행 시 엘리먼트 내 구문 앞에 SET을 붙이고 마지막에 끝나는 문장의 ',' 를 제거한다.
<update id="updateUser">
update USER
<set>
<if test="username != null">
username=#{username},
</if>
<if test="password != null">
password=#{password},
</if>
</set>
where id=#{id}
</update>위 예시를 trim 구문으로 변경해보자.
<update id="updateUser">
update USER
<trim prefix="SET" surffixOverrides=",">
<if test="username != null">
username=#{username},
</if>
<if test="password != null">
password=#{password},
</if>
</trim>
where id=#{id}
</update>surffixOverrides 속성을 ','으로 설정해 구문의 마지막에 제거할 값을 명시하면 된다.
set과 마찬가지로 마지막에 ','가 제거된다.
foreach
Java의 for문과 같은 역할을 하는 것으로, 동적 쿼리를 구현할 때 collection에 대한 반복 처리를 제공한다.
| 속성명 | 설명 |
|---|---|
| item | 반복될 때 접근 가능한 각 객체 변수 (반복을 수행할 때마다 꺼내올 값의 이름) |
| index | 반복 횟수를 가리키는 변수 |
| collection | 반복에 쓰일 Collection 객체 (반복을 수행할 대상) |
| open | 첫 반복 시 포함할 여는 문자열 즉, <foreach> 엘리먼트 구문의 가장 앞에 올 문자 ex) ' ( ' abc, efg, … ) |
| separator | 반복되는 객체를 나열할 때 item 사이에 사용할 구분자 ex) ( abc ' ,' … ) |
| close | 마지막 반복 시 포함할 닫는 문자열 즉, <foreach> 엘리먼트 구문의 마지막에 올 문자 ex) ( abc, efg, …' ) ' |
<select id="searchBadwords" resultType="arraylist">
SELECT * FROM BOARD
WHERE TITLE IN
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>결과
SELECT * FROM BOARD
WHERE TITLE IN ( 'XXX' , '사행성', '욕설', … )- parameter 객체로 값을 받아오지 않고, static 필드 혹은 static 메소드에 접근하여 직접 반환 받아 사용하는 것도 가능하다.
이렇게 static 필드 혹은 메소드에 직접 접근해 사용하면 mapper 인터페이스와 service에서 파라미터 넘겨주지 않아도 된다.
- static 필드 접근 시 collection 속성 : @풀클래스명@필드명
- static 메소드 접근 시 collection 속성 : @풀클래스명@메소드명()
그냥 말로만 나타내면 헷갈릴 것이다.
<foreach collection="@com.section01.xml.Application@createRandomMenuCodeList()" item="menuCode" open="(" separator=", " close=")">
#{ menuCode }
</foreach>이런 식으로 작성해주면 된다.
bind
특정 문장을 미리 생성하여 쿼리에 적용해야 할 경우 사용한다.
_parameter 를 통해 전달 받은 값에 접근하여 구문을 생성한다.
<select id="searchBoard" resultType="arraylist">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BOARD
WHERE title LIKE #{pattern}
</select>