board 테이블의 전체 데이터 출력하기
SELECT * FROM BOARD
title뒤에 오는 글자가 0개 또는 여러개인 데이터를 출력해본다.
SELECT * FROM BOARD WHERE TRUE AND title LIKE concat('title', '%'); -- title%
SELECT * FROM BOARD WHERE TRUE AND title LIKE concat('title2', '%'); -- title%
title뒤에 오는 글자가 0개 또는 여러개인 데이터를 제외한 데이터를 출력한다
SELECT * FROM BOARD WHERE TRUE AND title not LIKE concat('title1', '%'); -- title%
'title3', 'title4', 'title5' 데이터 출력해보기
SELECT * FROM BOARD WHERE TRUE AND title in ('title3', 'title4', 'title5');
bno가 591, 592, 593번 데이터 출력해보기
SELECT * FROM BOARD WHERE TRUE AND bno in (591, 592, 593);
검색기능에 사용할 SQL문 작성하기
'keyword'를 포함한 title을 출력하는 쿼리를 작성한다.
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
SELECT bno, title, content
WHERE TRUE
AND title LIKE concat('%', #{keyword}, '%')
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
AND title LIKE concat('%', #{keyword}, '%')
</select>
test code 작성하기
@Test
public void searchSelectPage() throws Exception {
boardDao.deleteAll();
for(int i = 1; i <= 20; i++){
BoardDto boardDto = new BoardDto("title"+i, "asdfasdf", "asdf"+i);
boardDao.insert(boardDto);
}
//title test
SearchCondition searchCondition = new SearchCondition(1,10,"title2", "T");
List<BoardDto> list = boardDao.searchSelectPage(searchCondition);
System.out.println("list = " + list);
assertTrue(list.size()==2); //title2, title20
}