MyBatis) MyBatis로 이용 가능한 동적 쿼리

MyBatis의 동적 쿼리
1) <sql> , <include>
<sql> 중복 되는 공통 쿼리를 정의한다
<include> sql로 정의한 공통 쿼리를 포함시킨다(붙여넣기)

예)

<sql id = "selectQuery">
SELECT no, title, content, id, view_cnt, comment_cnt, reg_date
FROM board
</sql>

-------

<select id="select" parameterType="int" resultType="BoardDto">
<include refid="selectQuery"/>
WHERE no = #{no}
</select>

2) <if>
여러개가 조건에 맞을 수도 있을 경우, if문 이용
(왜? if에 맞으면 다 적용되기 때문)

<select id="select" parameterType="int" resultType="BoardDto">
SELECT count(*)
FROM board
WHERE true
<if test='option=="A"'>
AND (title, LIKE concat('%', #{keyword}, '%')
OR content LIKE concat('%', #{keyword}, '%'))
</if>
<if test='option=="T"'>
AND title LIKE concat('%', #{keyword}, '%')
</if>
<if test='option=="T"'>
AND id LIKE concat('%', #{keyword}, '%')
</if>
</select>

3) <choose> <when>
<choose> 고르시오 <when> 여기에 맞는 거 하나만 (if-else 조건문)

<select id="select" parameterType="int" resultType="BoardDto">
SELECT count(*)
FROM board
WHERE true
<choose>
<when test='option=="A"'>
AND (title, LIKE concat('%', #{keyword}, '%')
OR content LIKE concat('%', #{keyword}, '%'))
</when>
<when test='option=="T"'>
AND title LIKE concat('%', #{keyword}, '%')
</when>
<otherwise> //else 위에 두 조건이 다 맞지 않을 경우
AND id LIKE concat('%', #{keyword}, '%')
</otherwise>
</choose>
</select>

4) <foreach>
매개변수(?)가 많을 때 이용함
WHERE 컬럼명 IN ( ?, ?, ? ...) 하고 싶을 때, ?를 넣기 위해 이용

<foreach collection="매개변수타입" item = "대입할 칼럼명" open = "처음에 이걸로 시작함" close = "이걸로 끝맺음" separator = "구분자"/>

<select id = "select" resultType="BoardDto">
SELECT no, title, content, id, view_cnt, comment_cnt, reg_date
FROM board
WHERE no IN
<foreach collection="array" item = "no" open="(" close=")" separator=",">
#{no}
</foreach>
ORDER BY reg_date DESC, no DESC
</select>