4-7, 4-8 검색기능 추가

1. 게시판 검색

1) 동적 쿼리
2) 페이지 이동

<form action="/ch4/board/list" class="search-form" method="get">
	<select class="search-option" name="option">
		<option value="A" selected>제목+내용</option>
		<option value="T" >제목만</option>
		<option value="W" >작성자</option>
	</select>
	
	<input type="text" name="keyword" class="search-input" type="text" value="">
	<input type="submit" class="search-button" value="검색">
</form>

2. MyBatis의 동적 쿼리 (1) - <sql>과 <include>

공통 부분을 로 정의하고 로 포함시켜 재사용

//원래 방법
<select id="select" parameterType="int" resultType="BoardDto">
	SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
	FROM board
	WHERE bno = #{bno}
</select>

<select id="selectPage" parameterType="map">
	SELECT bno, title, content, writer, view_cnt, commnet_cnt, reg_date
	FROM board
	ORDER BY reg_date DESC, bno DESC
	LIMIT #{offset}, #{pageSize}
</select>

//공통 부분을 <sql>로 정의하고 
<sql id="selectFromBoard">
	SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
	FROM board
</sql>

//<include>로 포함시켜 재사용
<select id="select" parameterType="int" resultType="BoardDto">
	<include refid="selectFromBoard"/>
	WHERE bno = #{bno}
</select>

//<include>로 포함시켜 재사용
<select id="selectPage" parameterType="map" resultType="BoardDto">
	<include refid="selectFromBoard"/>
	ORDER BY reg_date DESC, bno DESC
	LIMIT #{offset}, #{pageSize}
</select>

2. MyBatis의 동적 쿼리 (2) - <if>

//중복 될 수 있어서 아래처럼 <choose> <when>사용이 더 적합
<select id="searchResultCnt" paramterType="SearchCondition" resultType="int">
	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=="W"'>
		AND writer LIKE concat('%', #{keyword}, '%')
	</if>
</select>

2. MyBatis의 동적 쿼리 (3) - <choose> <when>

와일드카드
Oracle : %(여러글자, 0개 이상), ?(한글자)
MySql : %(여러글자, 0개 이상), (한글자)
ex) 'title%' ==> title2(O), title(O)
ex) 'title' ==> title2(O), title(X)

//if-else if
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
	SELECT count(*)
	FROM board
	WHERE true
	<choose>
		<when test='option=="T"'>
			AND title LIKE concat('%', #{keyword}, '%')
		</when>
		<when test='option=="W"'>
			AND writer LIKE concat('%', ${keyword}, '%')
		</when>
		<otherwise>
			AND (title LIKE concat('%', #{keyword}, '%')
			OR content LIKE concat('%', #{keyword}, '%'))
		</otherwise>
	</choose>
</select>

2. MyBatis의 동적 쿼리 (4) - <foreach>

where bno in (1, 2, 3)

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

//Repository 계층(DAO)
public List<BoardDto> getSelected(Integer[] bnoArr) throws Exception {
	return session.selectList(namespace + "getSelected", bnoArr);
}

//Service 계층
List<BoardDto> list = boardDao.getSelected(new Integer[]{1,2,3});