동적 SQL문

MyBatis에서는 xml 파일에 작성한 sql문을 동적으로 제어할 수 있는 기능을 제공하여 좀 더 쉽게 쿼리를 구현할 수 있도록 한다. 일반적으로 JDBC에서 사용자가 입력한 값에 따라 where절 등 쿼리문이 변경되는 경우 여러 개의 sql문을 작성해야 하는데 MyBatis에서 제공하는 기능을 이용하면 하나의 쿼리문을 가지고 경우에 따라서 바꿔가며 사용할 수 있다.

<if> 구문

동적 쿼리를 구현할 때 기본적으로 사용되는 조건문으로 test 속성 안에 작성한 구문이 참인 경우 실행하게 된다. <if>는 조건이 여러 개인 경우 다중으로 사용할 수 있다. 필요 조건을 작성하고 else if인 경우 다른 만족할 조건을 test 속성에 적어서 <if> 태그를 한 번 더 사용하면 된다.

<select id="selectEmployeeCount" resultType="_int" parameterType="map">
		SELECT COUNT(*) FROM EMPLOYEE
	<if test="deptCode!=null"> <!--map에 deptCode라는 key를 가진 value가 있을 때-->
		WHERE DEPT_CODE="#{deptCode}"
	</if>
	<if test="jobCode!=null"> <!--map에 jobCode라는 key를 가진 value가 있을 때-->
		WHERE JOB_CODE="#{jobCode}"
	</if>
</select>

<choose>의 <when>,<otherwist> 구문

java의 if~else, switch나 JSTL의 choose와 유사한 기능으로 주어진 조건에 만족할 때 실행할 구문은 <when> 태그 내부에 작성하고 조건이 만족하지 않는 경우 실행하는 문장은 <otherwise> 태그 내부에 작성해서 실행시킬 수 있다.

<select id="selectEmployeeCount" resultType="_int" parameterType="map">
		SELECT COUNT(*) FROM EMPLOYEE
	<choose>
		<when test="deptCode!=null">
			WHERE DEPT_CODE="#{deptCode}"
		</when>
		<otherwise>
			WHERE JOB_CODE="#{jobCode}"
		</otherwise>
	</choose>
</select>

😰 동적 SQL 사용 시 주의할 점

<select id="selectEmployeeCount" resultType="_int" parameterType="map">
		SELECT COUNT(*) FROM EMPLOYEE
	<if test="deptCode!=null"> <!--map에 deptCode라는 key를 가진 value가 있을 때-->
		⚠️WHERE DEPT_CODE="#{deptCode}"
	</if>
	<if test="jobCode!=null"> <!--map에 jobCode라는 key를 가진 value가 있을 때-->
		⚠️WHERE JOB_CODE="#{jobCode}"
	</if>
</select>

위에 작성했던 코드를 보면 문제점이 생기는 시점이 보인다. if 구문이 두 개 다 만족하는 경우 작성될 SQL문은 SELECT COUNT(*) FROM EMPLOYEE WHERE DEPT_CODE=’D1’ WHERE JOB_CODE=’J1’ 같은 말도 안 되는 문장이 만들어진다.

그렇다고 해서 나중에 작성한 if 태그 안에 WHERE를 빼버리면 위에 구문은 만족하지 않고 jobCode만 매개변수로 들어온 경우 SELECT COUNT(*) FROM EMPLOYEE JOB_CODE=’J1’ 같은 쿼리문이 만들어진다.

🤔 그럼 WHERE을 가장 위로 빼버린다면?

if 구문에 만족하는 값이 없다면 WHERE절이 없는 SELECT문이 실행되야하는데 SELECT COUNT(*) FROM EMPLOYEE WHERE 상태로 쿼리문을 실행하면서 에러가 발생하게 된다.

이런 경우를 잘 생각해서 동적 SQL문을 작성해야 하기 때문에 MyBtis에서 제공하는 <trim>, <where>, <set> 태그를 잘 활용해야 한다.

<where> 구문

where 없이 작성해도 알아서 첫 부분에 where을 추가해주는 태그로 만약 태그 안에 내용이 AND나 OR로 시작하는 경우 AND/OR을 제거해준다.

<select id="searchEmp" resultMap="employeeMap" parameterType="map">
		SELECT *
		FROM (SELECT E.*, DECODE(SUBSTR(EMP_NO,8,1),2,'F',4,'F','M') AS GENDER
				FROM EMPLOYEE E JOIN DEPARTMENT ON E.DEPT_CODE=DEPT_ID)
		<where>
			<if test="keyword!=null and keyword!=''">
			<!-- null이 아니고 공백이 들어올 수 있음 -->
				${type} LIKE '%'||#{keyword}||'%'
			</if>
		<!-- 위에 if 태그가 실행되지 않고 아래 if 태그만 실행하는 경우 AND 삭제 -->
			<if test="gender!=null and gender!=''">
				AND GENDER=#{gender}
			</if>
		</where>
</select>

💡 동적 쿼리문에서 컬럼명 처리?
위치 홀더처럼 자료형에 맞춰서 매개변수를 쿼리문에 추가하는 방법 외에 쿼리문의 하나의 문자열로 처리되게 하는 방법으로 ${매개변수명}을 사용할 수 있다.
JDBC에서 컬럼명이 경우에 따라 바뀌어야 하는 경우 작성한 쿼리문 문자열에 replaceAll() 메소드를 이용해서 바꾼 것처럼 사용할 수 있다.

<set> 구문

update문에 사용할 수 있는 컬럼으로 추가할 컬럼을 동적으로 포함시키기 위해서 사용한다. 태그 내에 작성한 문장의 첫 부분에 SET을 추가하고 불필요한 콤마(마지막에 붙는 콤마)를 제거한다.

<update id="updateEmp">
	UPDATE EMPLOYEE
	<set>
		<!-- 문장 앞에 SET 추가 -->
		<if test="userName!=null and userName!=''">
			USER_NAME=#{userName},
		</if>
		<!-- 이후에 다른 문장이 추가되지 않으면 콤마 제거 -->
		<if test="salary!=null and salary!=''">
			SALARY=#{salary},
		</if>
	</set>
</update>

<trim> 구문

작성한 SQL문의 특정 부분을 없앨 때 사용한다. prefix 속성에 처음 조건문 안에 구문이 처음 시작될 때 붙을 내용을 속성 값으로 추가하고 **prefixOverrides 속성에 해당 구문이 처음 시작할 때 제거되야할 단어를 속성 값으로 줄 수 있다.**

<select id="searchEmp" resultMap="employeeMap" parameterType="map">
		SELECT *
		FROM (SELECT E.*, DECODE(SUBSTR(EMP_NO,8,1),2,'F',4,'F','M') AS GENDER
				FROM EMPLOYEE E JOIN DEPARTMENT ON E.DEPT_CODE=DEPT_ID)
		<trim prefix="WHERE" prefixOverrides="AND|OR">
		<!-- 첫 문장 앞에 where을 붙이고 첫문장 앞에 and나 or가 있으면 삭제 -->
			<if test="keyword!=null and keyword!=''">
			<!-- null이 아니고 공백이 들어올 수 있음 -->
				${type} LIKE '%'||#{keyword}||'%'
			</if>
			<if test="gender!=null and gender!=''">
				AND GENDER=#{gender}
			</if>
		</trim>
</select>

<trim>을 이용해서 <set> 구현

suffixOverrides 속성에 마지막으로 제거할 값을 속성 값으로 작성하여 쓸 수 있다.

<update id="updateEmp">
	UPDATE EMPLOYEE
	<trim prefix="SET" suffixOverrides=",">
		<if test="userName!=null and userName!=''">
			USER_NAME=#{userName},
		</if>
		<!-- 이후에 다른 문장이 추가되지 않으면 콤마 제거 -->
		<if test="salary!=null and salary!=''">
			SALARY=#{salary},
		</if>
	</trim>
</update>

<foreach> 구문

동적 SQL문을 구현할 때 반복 처리를 제공한다. item 속성에 반복문에 사용할 이름을 작성하고 collection에는 반복문을 실행할 List, Array 형태의 객체를 작성한다. JSTL의 items 역할을 collection이, var 역할을 item이 수행한다고 볼 수 있다.

open 속성에는 반복문 실행 시작에 붙을 문구, close는 끝에 붙을 문구를 작성하고 separator 속성에는 반복문 실행 시마다 붙을 문구를 작성한다.

<select id="searchEmp" resultMap="employeeMap" parameterType="map">
		SELECT * FROM EMPLOYEE
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="deptCode!=null">
				DEPT_CODE IN
				<foreach item="depts" collection="deptCodes" open="(" separator="," close=")">
					#{depts}
				</foreach>
			</if>
		</trim>
</select>

<bind> 구문

특정 문장을 미리 생성해서 쿼리에 적용해야 하는 경우에 사용한다. _parameter를 통해서 전달 받은 값에 접근해서 구문을 생성할 수 있다.

<select id="searchBoard" resultType="arraylist">
	<bind name="pattern" value="% + _parameter.getTitle() +%"/>
	SELECT * FROM BOARD
	WHERE TITLE LIKE #{pattern}
	<!-- bind 태그의 value에 작성한 문장이 들어온다 -->
</select>