MyBatis DynamicSQL Select 문 예제(XML)

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.thinker.bbs.BbsMapper">

<insert id="create" parameterType="org.thinker.bbs.BbsVO">

insert into tbl_bbs (bbsno, title, content, writer)

values (seq_bbs.nextval, #{title},#{content},#{writer})

</insert>

<select id="selectDynamic" parameterType="org.thinker.bbs.BbsCriteria" resultType="org.thinker.bbs.BbsVO">

<![CDATA[

select * from tbl_bbs 

where bbsno > 0 

and rownum <= 10

]]>

<foreach collection="types" item="type" index="idx" open=" and ("  close=")"  separator=" OR ">

<if test="type eq 't'.toString()">

title like '%'|| #{keyword}||'%'

</if>

<if test="type eq 'w'.toString()">

writer like '%'|| #{keyword}||'%'

</if>

<if test="type eq 'c'.toString()">

content like '%'|| #{keyword}||'%'

</if>

</foreach>

</select>

<!--bind 쓰는 버전-->

<select id="selectDynamic2" parameterType="org.thinker.bbs.BbsCriteria2" resultType="org.thinker.bbs.BBSVO2">

<bind name="t" value="criMap.get('t'.toString())"/>

<bind name="w" value="criMap.get('w'.toString())"/>

<bind name="c" value="criMap.get('c'.toString())"/>

<![CDATA[

select * from tbl_bbs 

where bno > 0 

and rownum <= 10

]]>

<foreach collection="criMap.keySet()" index="idx" item="key" open="and (" separator="AND" close=")">

   <if test="key eq 't'.toString()">

      bname like '%'||#{t}||'%'

   </if>

   <if test="key eq 'w'.toString()">

      userid like '%'||#{w}||'%'

   </if>

   <if test="key eq 'c'.toString()">

      bcont like '%'||#{c}||'%'

   </if>

</foreach>

</select>

<!--bind 쓰지 않는 버전-->

<select id="selectDynamic3" parameterType="org.thinker.bbs.BbsCriteria2" resultType="org.thinker.bbs.BBSVO2">

<![CDATA[

select * from tbl_bbs 

where bno > 0 

and rownum <= 10

]]>

<!-- ${sql} -->

<foreach collection="criMap.entrySet()" index="idx" item="item" open="and (" separator="AND" close=")">

   <if test="idx eq 't'.toString()">

      bname like '%'||#{item}||'%'

   </if>

   <if test="idx eq 'w'.toString()">

      userid like '%'||#{item}||'%'

   </if>

   <if test="idx eq 'c'.toString()">

      bcont like '%'||#{item}||'%'

   </if>

</foreach>

</select>

</mapper>