TIL22.03.02 - MyBatis : 동적 SQL

동적 SQL(Dynamic SQL)

• 동적 SQL(Dynamic SQL) 엘리먼트 전달값에 따라 다른 SQL 명령을 등록하는 기능
• OGNL(Object Graph Navigation Language) 표현식을 사용하여 구현
• 주의)동적 SQL 기능을 이용할 경우 parameterType 속성값으로 기본형 (Wrapper)또는 String 클래스 사용 불가능 - 속성값으로 DTO 클래스 또는 HashMap 클래스(Map 인터페이스)설정

속성:

• if : 엘리먼트 내용(SQL 문장)의 포함 여부를 설정하는 엘리먼트
• test 속성: false (SQL 문장 미포함) 또는 true(SQL 문장 포함) 중 하나를 속성값으로 설정
• → test 속성값이 [true]인 경우 SQL 문장을 SQL 명령에 포함
• →필드명 또는 맵키로 표현된 전달값을 비교한 조건식을 test 속성값으로 사용
• 문제점) 아이디와 이름이 모두 전달된 경우 에러 발생
• → 모든 if 엘리먼트의 test 속성값이 [true]이므로 where 구문이 2번 포함

<select id="selectNameDynamicHewonList" parameterType="map" resultMap="myHewonResult"
	select * from my hewon
	<if test="id!=null and id!=''">
			where hewon_id=#{id}
	</if>
	<if test="name!=null and name!=''">
			where hewon_name=#{name}
	</if>

	order by hewon_id
</select>

• 해결법 -1) 아이디와 이름이 모두 전달될 경우 하나의 WHERE 구문만 포함되도록 설정
• choose : 조건식에 의해 하나의 엘리먼트 내용(SQL 문장)이 포함되도록 설정하는 상위 엘리먼트
• →다수의 엘리먼트 중 하나의 엘리먼트 내용만 포함
• →하위 엘리먼트 : when 엘리먼트(1개 이상), otherwise 엘리먼트(0개 또는 1개)
• when : 엘리먼트 내용의 포함 여부를 설정하는 엘리먼트
• test 속성: false (SQL 문장 미포함) 또는 true(SQL 문장 포함) 중 하나를 속성값으로 설정
• → test 속성값이 [true]인 경우 SQL 문장을 SQL 명령에 포함
• otherwise : 모든 when 엘리먼트의 test 속성값이 false인 경우 엘리먼트 내용을 SELECT

<select id="selectNameDynamicHewonList" parameterType="map" resultMap="myHewonResult"
	select * from my hewon
	<choose>
		<when test="id!=null and id!=''">
				where hewon_id=#{id}
		</when>
		<when test="name!=null and name!=''">
				where hewon_name=#{name}
		</when>
	</choose>
	order by hewon_id
</select>

해결법-2)

• 아이디와 이름이 모두 전달될 경우 하나의 WHERE 구문으로 2개의 조건이 연결되어 포함되도록 설정
• trim : SQL 문장을 추가하거나 제거하는 기능을 제공하는 엘리먼트
• →if 엘리먼트를 하위 엘리먼트로 사용
• prefix 속성 : trim 엘리먼트에 SQL 문장이 존재할 경우 SQL 문장 앞부분에 추가될 단어를 속성값으로 설정
• suffix 속성 : trim 엘리먼트에 SQL 문장이 존재할 경우 SQL 문장 뒷부분에 추가될 단어를 속성값으로 설정
• prefixOverrides 속성 : trim 엘리먼트에 SQL 문장이 존재할 경우 SQL 문장의 앞부분에서 제거될 단어를 속성값으로 설정
• → | 연산자를 이용하여 제거할 단어를 나열하여 설정 가능 suffixOverrides 속성 : trim 엘리먼트에 SQL 문장이 존재할 경우 SQL 문장의 뒷부분에서 제거될 단어를 속성값으로 설정

<select id="selectDynamicHewonList" parameterType="map" resultMap="myHewonResultMap">
		select * from myhewon
		<trim prefix="where" prefixOverrides="or|and">
			<if test="id!=null and id!=''">
				hewon_id=#{id}
			</if>
			<if test="name!=null and name!=''">
				${choice} hewon_name=#{name}
			</if>
		</trim>	
		order by hewon_id
	</select>

where

• where : where 엘리먼트에 SQL 문장에 존재할 경우 앞부분에 [WHERE] 단어를 추가하는 엘리먼트 → if 엘리먼트를 하위 엘리먼트로 사용 → 엘리먼트의 문장 앞부분에 조건식이 아닌 문장이 존재할 경우 제거

<select id="selectDynamicHewonList" parameterType="map" resultMap="myHewonResultMap">
		select * from myhewon
		<where>
			<if test="id!=null and id!=''">
				hewon_id=#{id}
			</if>
			<if test="name!=null and name!=''">
				${choice} hewon_name=#{name}
			</if>
		</where>
		order by hewon_id
	</select>

set

• set : set 엘리먼트에 SQL 문장에 존재할 경우 앞부분에 [SET] 단어를 추가하는 엘리먼트
• → if 엘리먼트를 하위 엘리먼트로 사용 → 포함될 SQL 문장 뒷부분에 변경식이 아닌 문장이 존재할 경우 제거 -->

	<update id="updateDynamicHewon" parameterType="MyHewon">
		update myhewon
		<set>
			<if test="name!=null and name!=''">
				hewon_name=#{name},
			</if>
			<if test="phone!=null and phone!=''">
				hewon_phone=#{phone},
			</if>
			<if test="email!=null and email!=''">
				hewon_email=#{email},
			</if>
			<if test="state!=null and state!=0">
				hewon_state=#{state}
			</if>			
		</set>
		where hewon_id=#{id}
	</update>

foreach

parameterType 속성값이 ArrayList 클래스(List 인터페이스)로 설정된 경우 SQL 명령에서는 전달값을 [list]로 표현

foreach : List 인스턴스를 전달받아 SQL 문장을 반복적으로 SQL 명령에 포함하는 엘리먼트

collection 속성 : 반복 처리할 List 인스턴스의 전달이름을 속성값으로 설정 --item 속성 : List 인스턴스의 요소값을 제공받아 사용하기 위한 이름을 속성값으로 설정

open 속성 : foreach 엘리먼트 내용의 앞부분에 추가될 단어를 속성값으로 설정

close 속성 : foreach 엘리먼트 내용의 뒷부분에 추가될 단어를 속성값으로 설정

<select id="selectMultiDynamicHewonList" parameterType="list" resultMap="myHewonResultMap">
		select * from myhewon

		<if test="list!=null">
			where hewon_id in
			<foreach collection="list" item="id" open="(" close=")" separator=",">
				#{id}
			</foreach>
		</if>
		order by hewon_id
	</select>

interface mapper를 사용안하는 이유?

• @Results : 검색행의 컬럼값을 클래스의 필드에 매핑되어 저장할 수 있는 정보를 제공하는 어노테이션 → XML 맵퍼의 resultMap 엘리먼트와 유사한 기능 제공 →value 속성 : 매핑정보를 제공하는 Result 어노테이션의 배열로 표현하여 속성값으로 설정
• @Results 어노테이션은 다른 추상메소드의 SQL 명령에 대한 매핑 정보 제공 불가능 - 매핑정보 재사용 불가능
• @Result : 검색행의 컬럼값을 필드에 매핑하기 위한 정보를 제공하는 엘리먼트 →column 속성 : 검색행의 컬럼명을 속성값으로 설정 →property 속성 : 컬럼값을 저장할 클래스의 필드명을 속성값으로 설정

@Results(value = {
		@Result(column = "hewon_id",property = "id")	
		,@Result(column = "hewon_name",property = "name")	
		,@Result(column = "hewon_phone",property = "phone")	
		,@Result(column = "hewon_email",property = "email")	
		,@Result(column = "hewon_state",property = "state")	
	})
	@Select("select * from myhewon order by hewon_id")
	List<MyHewon> selectHewonList();

Provider 클래스

• Provider 클래스 : SQL 명령을 반환하는 메소드가 선언된 클래스
• → Java 명령으로 동적 SQL 기능을 구현하여 SQL 명령 반환
• SQL : SQL 명령을 저장하기 위한 클래스
• →SQL 명령 작성에 필요한 메소드를 호출하여 SQL 명령을 인스턴스에 저장 →익명의 내부 클래스로 인스턴스를 생성하여 메소드 호출 →SQL.toString() : SQL 인스턴스에 저장된 SQL 명령을 문자열로 변환하여 반환하는 메소드

package xyz.itwill.mapper;

import java.util.Map;

import org.apache.ibatis.jdbc.SQL;

public class MyHewonProvider {
	public String selectDynaminHewonList(Map<String, Object> map) {
		return new SQL() {{
			SELECT("*");
			FROM("myhewon");
			if(map.get("name")!=null && !map.get("name").equals("")) {
				WHERE("hewon_name=#{name}");
			}
			ORDER_BY("hewon_id");
		}}.toString();
	}
}

Framework

• 개발에 있어 기본이 되는 뼈대나 구조를 제공함
• →코딍에 기본이 되는 구조(아키텍처)
• 생상성이 향상되고 관리하기도 쉬워짐
• 확장성 용이