1. 일반 페이징
@Data
public class SearchDto {
private String by; // 무슨 방식으로 검색할지?
private String keyword; // 검색 키워드는 무엇인지?
private String userEmail;
private int tournamentIndex;
private String title;
private int goodsIndex;
private int countPerPage = 12; // 한 페이지당 보여줄 게시글 개수
private int requestPage; // 니가 요청한 페이지 번호
private int totalCount; // 전체 게시글의 개수
private int maxPage; // 조회할 수 있는 최대 페이지
private int minPage = 1; // 조회할 수 있는 최소 페이지
private int offset; // 거를 게시글 개수
private int naviSize = 5; // 한 페이지에 표시할 페이지 번호 수
private int totalPage; // 전체 페이지 수
private int beginPage; // 시작 페이지 번호
private int endPage; // 끝 페이지 번호
private boolean showPrev; // 이전 표시 여부
private boolean showNext; // 다음 표시 여부
public void setTotalCount(int totalCount) {
this.totalCount = totalCount; // 전체 갯수
maxPage = totalCount / countPerPage + (totalCount % countPerPage == 0 ? 0 : 1); // 최대 페이지
minPage = 1; // 최소 페이지
offset = countPerPage * (requestPage - 1); // 거를 갯수
totalPage = (int)(Math.ceil(totalCount/(double)countPerPage)); // 전체 페이지 수
beginPage = ((requestPage -1)/naviSize) * naviSize + 1; // 시작 페이지 번호
endPage = Math.min(beginPage + naviSize -1, totalPage); // 끝 페이지 번호
showPrev = beginPage != 1; // 이전 표시 여부
showNext = endPage != totalPage; // 다음 표시 여부
}
}
SearchDto 이다. setTotalCount() 함수로 페이징을 구현할 예정이다.
// 랭킹 코멘트 조회
public TournamentCommentDto[] getComments(int index, SearchDto search) {
search.setTournamentIndex(index);
search.setTotalCount(this.tournamentMapper.selectTournamentCommentsCount(index));
return this.tournamentMapper.selectTournamentComments(search);
}
// 랭킹 조회
@RequestMapping(value = "/ranking", method = RequestMethod.GET, produces = MediaType.TEXT_HTML_VALUE)
public ModelAndView getRanking(
@RequestParam("index") int index,
@RequestParam(value = "page",required = false, defaultValue = "1") int page,
SearchDto search
) {
search.setRequestPage(page);
search.setCountPerPage(5);
TournamentCommentDto[] comments = this.tournamentService.getComments(index, search);
ModelAndView modelAndView = new ModelAndView();
modelAndView.addObject("comments", comments);
modelAndView.addObject("paging", search);
modelAndView.setViewName("tournament/ranking");
return modelAndView;
}
다음과 같이 SearchDto 객체를 만든 후, 객체의 setTotalCount() 함수를 불러와서 전체 댓글 갯수를 전송 후, 계산된 페이징 정보들을 객체에 저장한다.
TournamentCommentDto[] selectTournamentComments(SearchDto search);
int selectTournamentCommentsCount(int tournamentIndex);
<select id="selectTournamentComments" parameterType="dev.hcs.mytournament.dtos.SearchDto" resultType="dev.hcs.mytournament.dtos.TournamentCommentDto">
SELECT `comment`.`index` AS `index`,
`comment`.`tournament_index` AS `tournamentIndex`,
`comment`.`user_email` AS `userEmail`,
`comment`.`content` AS `content`,
`comment`.`created_at` AS `createdAt`,
`comment`.`modified_at` AS `modifiedAt`,
`comment`.`is_reported` AS `isReported`,
`user`.`nickname` AS `userNickname`
FROM `worldcup`.`tournament_comment` AS `comment`
LEFT JOIN `worldcup`.`users` AS `user` on `comment`.`user_email` = `user`.`email`
WHERE `tournament_index` = #{tournamentIndex}
ORDER By `comment`.`created_at` DESC
LIMIT #{countPerPage} OFFSET #{offset}
</select>
<select id="selectTournamentCommentsCount" parameterType="int" resultType="int">
SELECT COUNT(0)
FROM `worldcup`.`tournament_comment` AS `comment`
LEFT JOIN `worldcup`.`users` AS `user` on `comment`.`user_email` = `user`.`email`
WHERE `tournament_index` = #{tournamentIndex}
ORDER By `comment`.`created_at` DESC
</select>
쿼리이다. 전체 댓글 갯수를 구한 후, 그 구한 갯수로 페이징 정보를 넣고, 페이징 객체를 파라미터로 전달하여 해당 LIMIT에 속한 댓글들을 요청받으면 된다.
<input class="currentPageHidden" th:value="${paging.getRequestPage()}" type="hidden">
<section th:if="${paging.getTotalPage() > 1}" class="paging-container">
<ul th:if="${paging.isShowPrev()}" class="front-back-ul">
<li><a th:href="@{/tournament/ranking (index=${tournament.getIndex()}, page=${paging.getBeginPage() - 1})}"><i class="fa-solid fa-arrow-left"></i></a></li>
</ul>
<ul th:each="num : ${#numbers.sequence(paging.getBeginPage(), paging.getEndPage())}" class="paging-ul">
<li><a th:class="${num}" th:href="@{/tournament/ranking (index=${tournament.getIndex()}, page=${num})}" th:text="${num}">1</a></li>
</ul>
<ul th:if="${paging.isShowNext()}" class="front-back-ul">
<li><a th:href="@{/tournament/ranking (index=${tournament.getIndex()}, page=${paging.getEndPage() + 1})}"><i class="fa-solid fa-arrow-right"></i></a></li>
</ul>
</section>
</main>
<footer th:replace="layouts/footer :: footer"></footer>
</body>
</html>
<script th:if="${paging.getTotalPage() > 1}">
const currentPageHidden = document.querySelector('.currentPageHidden').value;
const currentPageATag = document.getElementsByClassName(currentPageHidden);
console.log(currentPageATag[0]);
currentPageATag[0].style.backgroundColor='#e74c3c';
currentPageATag[0].style.color='white';
</script>
HTML에서 다음과 같이 Thymeleaf를 통해 페이징을 구현하였다.
2. 검색 페이징
// 홈 화면에 대회들 정렬(페이징과 검색)
public TournamentEntity[] getTournaments(SearchDto search) {
if (search.getKeyword() == null || search.getKeyword().length() > 50) {
search.setKeyword(null);
}
if (search.getBy() == null) {
search.setBy(null);
}
search.setTotalCount(this.tournamentMapper.getTournamentTotalCount(search));
return this.tournamentMapper.selectTournaments(search);
}
@RequestMapping(value = "/", method = RequestMethod.GET, produces = MediaType.TEXT_HTML_VALUE)
public ModelAndView getIndex(
@RequestParam(value = "by", required = false, defaultValue = "latest") String by,
@RequestParam(value = "keyword", required = false, defaultValue = "") String keyword,
@RequestParam(value = "page", required = false, defaultValue = "1") int page,
SearchDto search,
HttpSession session
) {
search.setBy(by);
search.setKeyword(keyword);
search.setRequestPage(page);
TournamentEntity[] tournaments = tournamentService.getTournaments(search);
ModelAndView modelAndView = new ModelAndView();
modelAndView.addObject("tournaments", tournaments);
modelAndView.addObject("paging", search);
modelAndView.setViewName("home/index");
return modelAndView;
}
다음과 같이 by 와 keyword를 이용하여 search 객체에 집어넣는다.
TournamentEntity[] selectTournaments(SearchDto search);
int getTournamentTotalCount(SearchDto search);
<select id="selectTournaments" parameterType="dev.hcs.mytournament.dtos.SearchDto" resultType="dev.hcs.mytournament.entities.TournamentEntity">
SELECT `index` AS `index`,
`user_email` AS `userEmail`,
`thumbnail` AS `thumbnail`,
`thumbnail_file_name` AS `thumbnailFileName`,
`thumbnail_content_type` AS `thumbnailContentType`,
`title` AS `title`,
`content` AS `content`,
`play_count` AS `playCount`,
`created_at` AS `createdAt`,
`modified_at` AS `modifiedAt`,
`is_recognized` AS `isRecognized`
FROM `worldcup`.`tournament`
WHERE `is_recognized` is true
<if test="keyword != null">
AND REPLACE(`title`, ' ', '') LIKE CONCAT('%', REPLACE(#{keyword}, ' ', ''), '%')
</if>
<if test="by != null and by.equals('latest')">
ORDER BY `created_at` DESC
</if>
<if test="by != null and by.equals('popular')">
ORDER BY `play_count` DESC
</if>
LIMIT #{countPerPage} OFFSET #{offset}
</select>
<select id="getTournamentTotalCount" parameterType="dev.hcs.mytournament.dtos.SearchDto" resultType="int">
SELECT COUNT(0)
FROM `worldcup`.`tournament`
WHERE `is_recognized` is true
<if test="keyword != null">
AND REPLACE(`title`, ' ', '') LIKE CONCAT('%', REPLACE(#{keyword}, ' ', ''), '%')
</if>
<if test="by != null and by.equals('latest')">
ORDER BY `created_at` DESC
</if>
<if test="by != null and by.equals('popular')">
ORDER BY `play_count` DESC
</if>
</select>
동적 sql문인 if문과 LIKE와 CONCAT을 이용하여 검색을 구현한다.
<section class="search-container">
<form id="search-form" method="get" action="/">
<label class="orderLabel">
<select name="by" id="">
<option th:selected="${paging.getBy() == 'latest'}" value="latest">최신순</option>
<option th:selected="${paging.getBy() == 'popular'}" value="popular">인기순</option>
</select>
</label>
<label class="searchLabel">
<input th:value="${paging.getKeyword()}" name="keyword" minlength="1" maxlength="50" placeholder="월드컵 제목을 입력하세요. (1자 이상 50자 이하)" type="search">
</label>
<input value="검색" type="submit">
</form>
</section>
<....>
<input class="currentPageHidden" th:value="${paging.getRequestPage()}" type="hidden">
<section th:if="${paging.getTotalPage() > 1}" class="paging-container">
<ul th:if="${paging.isShowPrev()}" class="front-back-ul">
<li><a th:href="@{/ (page=${paging.getBeginPage() - 1}, by=${paging.getBy()}, keyword=${paging.getKeyword()})}"><i class="fa-solid fa-arrow-left"></i></a></li>
</ul>
<ul th:each="num : ${#numbers.sequence(paging.getBeginPage(), paging.getEndPage())}" class="paging-ul">
<li><a th:class="${num}" th:href="@{/ (page=${num}, by=${paging.getBy()}, keyword=${paging.getKeyword()})}" th:text="${num}">1</a></li>
</ul>
<ul th:if="${paging.isShowNext()}" class="front-back-ul">
<li><a th:href="@{/ (page=${paging.getEndPage() + 1}, by=${paging.getBy()}, keyword=${paging.getKeyword()})}"><i class="fa-solid fa-arrow-right"></i></a></li>
</ul>
</section>
해당 키워드로 검색하여 검색 결과를 페이징하게 한다.