mybatis - 동적SQL(if / choose / where / set / foreach)

1. if

<select id=”findActiveBlogLike” parameterType=”Blog” resultType=”Blog”>
 
SELECT * FROM BLOG WHERE state = ‘ACTIVE’ 
 
<if test=”title != null”> 
AND title like #{title} 
</if> 
 
<if test=”author != null and author.name != null”> 
AND author_name like #{author.name}
</if>
</select>

2. choose

<select id=”findActiveBlogLike”
parameterType=”Blog” resultType=”Blog”>
 
 SELECT * FROM BLOG WHERE state = ‘ACTIVE’ 
 
<choose> 
<when test=”title != null”> 
    AND title like #{title}
</when>
 
<when test=”author != null and author.name != null”>
    AND author_name like #{author.name} 
</when> 
<otherwise> 
    AND featured = 1 
</otherwise> 
</choose> 
</select>
 

3. where

 
<select id=”findActiveBlogLike”
     parameterType=”Blog” resultType=”Blog”>
 
     SELECT * FROM BLOG 
<where>
     <if test=”state != null”>
     state = #{state} 
    </if>
     <if test=”title != null”>
     AND title like #{title}
     </if>
     <if test=”author != null and author.name != null”>
     AND author_name like #{author.name}
     </if>
 </where>
 </select>

where 요소는 태그에 의해 컨텐츠가 리턴되면 단순히 “WHERE” 만을 추가한다.

게다가, 컨텐츠가 “AND” 나 “OR” 로 시작한다면, 

그 “AND” 나 “OR”를 지워버린다.

4. set

<update id="updateAuthorIfNecessary" parameterType="domain.blog.Author"> 
 
update Author 
 
<set> 
    <if test="username != null">username=#{username},
    </if> 
    <if test="password != null">password=#{password},
    </if>
    <if test="email != null">email=#{email},
    </if>
    <if test="bio != null">bio=#{bio}
    </if>
</set> 
    where id=#{id}
</update>

여기서 set 요소는 동적으로 SET 키워드를 붙히고, 필요없는 콤마를 제거한다.

5. foreach

<select id="selectPostIn" resultType="domain.blog.Post"> 
 
    SELECT * FROM POST P WHERE ID in 
 
<foreach item="item" index="index" collection="list" open="(" separator="," close=")"> 
 
    #{item} 
 
</foreach> 
</select>