Spring #5 : 마이바티스 - 동적 SQL문(230118)

마이바티스 동적 SQL문

-  주로 SQL문의 조건절에서 사용

- 조건절(WHERE절)에 조건을 동적으로 추가

- JSTL과 XML 기반으로 동적 SQL문 작성

 

• if
• choose(when, otherwise)
• trim(where, set)
• foreach

<where>
    <if test='조건식'>
        추가구문
    </if>
</where>

 

	<!-- 동적 SQL문 -->
	<select id="searchMember" parameterType="memberVO" resultMap="memResult">
		<![CDATA[ 
			select * from t_member
		]]>
		<where>
			<choose>
				<when test="name != '' and name != null and email != '' and email != null">
					name=#{name} and email=#{email}
				</when>
				<when test=" name != '' and name != null">
					name=#{name}
				</when>
				<when test=" email != '' and email != null">
					email=#{email}
				</when>
			</choose>
		</where>
		order by joinDate desc
	</select>
	
	<select id="foreachSelect" resultMap="memResult" parameterType="java.util.Map">
		<![CDATA[ 
			select * from t_member
		]]>
		where name in
		<foreach item="item" collection="list" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by joinDate desc
	</select>
	
	<insert id="foreachInsert" parameterType="java.util.Map"> 
			insert INTO t_member(id, pwd, name, email)
			VALUES
			<foreach item="item" collection="list" index="index" separator=",">
			(#{item.id}, #{item.pwd}, #{item.name}, #{item.email})
		</foreach>
	</insert>
	
	<!-- like 검색 -->
	<select id="selectLike" resultMap="memResult" parameterType="String">
		<![CDATA[ 
			select * from t_member
			where
			name like '%' || #{name} || '%'
		]]>
	</select>