동적쿼리

동적 Query 작성

 

<if> : 조건 처리

 

<if test="shopNo > 0">

WHERE shop_np = #{shopNo}

 

<choose> :    선택

<choose>

<when test="shopStatus != null and shopStatus == 'O'.toString()">

WHERE SHOP_STATUS = 'Y'

</when>

<when test="shopStatus != null and shopStatus == 'X'.toString()">

WHERE SHOP_STATUS = 'N'

</when>

<otherwise>

WHERE SHOP_STATUS IS NULL

</otherwise>

<choose>

 

<where> where절을 동적으로 추가할때 사용

<where>

<if test="shopNo > 0">

WHERE SHOP_NO=#{shopNo}

</if>

<if test="shopStatus != null and shopStatus != ''">

WHERE SHOP_STATUS = #{shopStatus}

</if>

</where>

 

<trim> 사용자가 원하는 문자나 문자열을 명시적으로 추가 혹은 삭제

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

<if test="shopNo > 0">

SHOP_NO=#{shopNo}

</if>

<if test="shopStatus != null and shopStatus != ''">

SHOP_STATUS = #{shopStatus}

</if>

<tirm>

 cf)

suffix : 구문 맨 뒤에 추가되는 문자나 문자열 지어

suffixOverrides : suffix 속성값 앞에 바로오는 생략 문자열을 지정

 

<set> set절을 동적으로 추가

<set>

<if test="shopName != null and shopName !='">

SET SHOP_NAME = #{shopName},

</if>

<if test="shopStatus != null and shopStatus !=''">

SET SHOP_STATUS = #{shopStatus},

</if>

</set>

 

<foreach> 반복 구성 요소 :IN 절 지정시 사용

 

SELECT SHOP_NO, SHOP_NAME, SHOP_LOCATION, SHOP_STATUS

FROM SHOP

<where>

<if test="shopNo > 0">

AND SHO_NO = #{shopNo}

</if>

<foreach collection="shopStatuses" item="shopStatus" index="count"

open="AND SHOP_STATUS IN (" separator="," close=")">

#{shopStatus}

</foreach>

</where>

 

=>

SELECT SHOP_NO, SHOP_NAME, SHOP_LOCATION, SHOP_STATUS

FROM SHOP

WHERE SHOP_NO =? AND SHOP_STAUTS IN ( ? , ? )