ch4-7, ch4-8 - 게시판 검색 기능 구현하기

게시판 검색

키워드, option을 action의 URL과 맵핑된 컨트롤러의 메서드에서 받아야 한다.

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

공통 부분을 <sql>로 정의하고 <include>로 포함시켜 재사용.

mapper.xml

<sql id="selectFromBoard">
	SELECT bno, title, content, writer, view_cnt, commnet_cnt, reg_date
	FROM board
</sql>

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

<select id="selectPage" parameterType="map" resultType="BoardDto">
	<include refid="selectFromBoard"/>
	OERDER BY reg_date DESC, bno DESC
	LIMIT #{offset}, #{pageSize}
</select>

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

<select id="searchResultCnt" parameterType="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=="W"'>
		AND writer LIKE concat('%', #{keyword}, '%')
	</if>
	<if test='option=="W"'>
		AND writer LIKE concat('%', #{keyword}, '%')
	</if>
</select>

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

<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
	SELECT count(*)
	FROM board
	WHERE true
	<choose>
		<when test='option=="T"'>
			AND title LIKE concat('%', #{keywork}, '%')
		</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>

와일드 카드

MySql : '%' - 여러글자(0개 가능), '_' - 무조건 한글자
Oracle : '%' - 여러글자(0개 가능), '?' - 무조건 한글자

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

<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>

//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});

IN

WHERE bno IN (1, 2, 3) - bno가 1 또는 2 또는 3이면