프로그래밍/- 남굼성의 Spring 교육
spring 페이징, SQL_CALC_FOUND_ROWS
즐겁게 하하하
2022. 2. 10. 00:20
728x90
페이징을 하기 전에 부트스트랩의 pagination 을 들어가서 css 를 적용한다.
https://getbootstrap.kr/docs/5.1/components/pagination/#%ED%81%AC%EA%B8%B0-%EC%A1%B0%EC%A0%88
페이지네이션
여러 페이지에 일련의 관련 내용이 있음을 나타내는 페이지네이션을 사용한 문서와 예시입니다.
getbootstrap.kr
준비가 끝나면 페이징을 구현한다.
아래 사진은 작업한 파일 경로들을 캡쳐해 놓았다.
UserDTO
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ToString
@EqualsAndHashCode
public class UserDTO {
private String user_no;
private String user_id;
private String user_pw;
private String user_nm;
private String user_tel;
private String user_salt;
private String user_email;
private String user_isshow;
private int fail_count;
private Date modify_pw;
private Date req_dt;
private String command;
}
Criteria
package com.house.handler.paging;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.springframework.web.util.UriComponentsBuilder;
@Getter
@Setter
@ToString
public class Criteria { // 페이징에 필요한 클래스
private int pageNum; // 페이지
private int amount; // 한번에 보여줄 게시물 수
private String type; // 검색종류
private String keyword; // 검색어
public Criteria() { this(1, 10); }
public Criteria(int pageNum, int amount) {
this.pageNum = pageNum;
this.amount = amount;
}
public String[] getTypeArr() {
return type == null ? new String[] {} : type.split("");
}
public String getListLink() {
UriComponentsBuilder builder = UriComponentsBuilder.fromPath("")
.queryParam("pageNum", this.pageNum)
.queryParam("amount", this.amount)
.queryParam("type", this.type)
.queryParam("keyword", this.keyword);
return builder.toUriString();
}
}
PageMaker
package com.house.handler.paging;
import lombok.Getter;
import lombok.ToString;
@Getter
@ToString
public class PageMaker {
// 보고있는 페이지에서 시작번호 ( 예) 10개씩이라고하면 1 , 11 , 21 , 31 ..... )
private int startPage;
// 보고있는 페이지에서 끝번호 ( 예) 10개씩이라고하면 10, 20, 30 ,40 ,50 .....)
private int endPage; // 이번페이지가 있는지 다음페이지가 있는지 확인
private boolean prev,next;
private int lastPage; // 전체 게시글 수
private int total; // 요청이 온 페이지의 페이지번호와 몇개씩 보고 싶은지
private Criteria cri;
public PageMaker(Criteria cri, int total) {
this.cri = cri; // 요청 정보
this.total = total; // 전체 게시글 수
this.endPage = (int) (Math.ceil(cri.getPageNum() / 10.0))*10;
this.startPage = this.endPage - 9;
int realEnd = (int) (Math.ceil((total*1.0)/cri.getAmount()));
lastPage = realEnd;
if(realEnd < this.endPage) {
this.endPage = realEnd;
}
this.prev = this.startPage > 1;
this.next = this.endPage < realEnd;
}
}Controller
@Controller
public class BasicController extends AdminController{
@Autowired
private UserService userService;
/** 환경설정 - 권한관리 List page 이동 */
@GetMapping("/basic/auth")
public ModelAndView userManagementPage(HttpSession session , Criteria cri , HttpServletRequest request) throws Exception{
//검색 param
UserDTO userDTO = new UserDTO();
userDTO.setUser_id(request.getParameter("search_id"));
userDTO.setUser_nm(request.getParameter("search_nm"));
//메뉴 및 권한 loading
String user_id = (String)session.getAttribute("user_id");
Map<String, List<MenuDTO>> menu = gnbList(user_id);
//사용자 list loading
Map<String,Object> map = userService.listUser(cri , userDTO);
PageMaker pageMaker = new PageMaker(cri,(Integer)map.get("total_count"));
ModelAndView mv = new ModelAndView();
mv.addObject("setMenu",menu);
mv.addObject("totalCount",map.get("total_count"));
mv.addObject("listUser",map.get("listUser"));
mv.addObject("pageMaker",pageMaker);
mv.setViewName("basic/auth");
return mv;
}
}
Service
public interface UserService {
Map<String,Object> listUser(Criteria criteria , UserDTO userDTO);
}
ServiceImpl
@Service("UserService")
public class UserServiceImpl implements UserService{
@Autowired
private UserDAO userDAO;
public Map<String,Object> listUser(Criteria criteria , UserDTO userDTO) { return userDAO.listUser(criteria , userDTO); } // 사용자 리스트
}
UserDAO
SQL_CALC_FOUND_ROWS , FOUND_ROWS() 를 사용하기 위해서 트랜젝션을 오픈한다.
@Repository
public class UserDAO extends SqlSessionDaoSupport {
@Autowired
public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
super.setSqlSessionFactory(sqlSessionFactory);
}
//사용자 리스트
public Map<String,Object> listUser(Criteria criteria , UserDTO userDTO){
int pageNum = criteria.getPageNum();
int amount = criteria.getAmount();
if(pageNum < 0) pageNum = 1;
int startNum = (pageNum-1) * amount;
SqlSession session = getSqlSessionFactory().openSession();
Map<String,Object> map2 = new LinkedHashMap<>();
try{
HashMap<String,Object> map = new HashMap();
map.put("startNum",startNum);
map.put("endNum",amount);
map.put("userDTO",userDTO);
List<UserDTO> listUser = session.selectList("listUser" , map);
int total_count = session.selectOne("queryRowCount");
map2.put("listUser",listUser);
map2.put("total_count",total_count);
}finally {
session.close();
}
return map2;
}
}
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias type="com.house.handler.dto.UserDTO" alias="userConfig" />
</typeAliases>
</configuration>
user-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="userConfig">
<select id="queryRowCount" resultType="int" >
SELECT FOUND_ROWS() AS total_cnt
</select>
<select id="listUser" parameterType="hashmap" resultType="userConfig">
select
SQL_CALC_FOUND_ROWS
*
from
(
select
@rownum:=@rownum+1 as num
, mc.*
from user mc
,(SELECT @rownum:=0) r
where <![CDATA[ mc.user_no > 0 ]]>
<if test="userDTO.user_id != null" >
and mc.user_id LIKE CONCAT('%', #{userDTO.user_id}, '%')
</if>
<if test="userDTO.user_nm != null" >
and mc.user_nm LIKE CONCAT('%', #{userDTO.user_nm}, '%')
</if>
order by mc.user_no desc
)ee
order by num desc
LIMIT #{startNum} , #{endNum}
</select>
</mapper>
jsp
<form name="requestForm" id="requestForm" method="post">
<input type="hidden" name="checkList" id="checkList" value="">
<div class="row justify-content-between col-xl-12 col-md-12 col-sm-12 mb-2">
<div class="px-3">
<span id="searchDetailLabel" data-toggle="dropdown" class="btn btn-sm btn-outline-primary dropdown-toggle"><i class="fas fa-search"></i></span>
<div class="dropdown-menu px-2 container-fluid" aria-labelledby="searchDetailLabel" style="width:50%">
<div class="form-row mb-2 ml-2">
<div class="col-sm-12">
<div class="input-group input-group-lg pt-1">
<div class="input-group-prepend"><span class="input-group-text">이 름</span></div>
<input type="text" name="search_nm" id="search_nm" value="" class="form-control" aria-label="이름" aria-describedby="이름">
</div>
<div class="input-group input-group-lg pt-1">
<div class="input-group-prepend"><span class="input-group-text">아이디</span></div>
<input type="text" name="search_id" id="search_id" value="" class="form-control" aria-label="아이디" aria-describedby="아이디">
</div>
</div>
</div>
<a type="button" class="btn btn-sm btn-outline-primary btn-block active" onclick="searchDetail();" aria-label="검색"> 검색 </a>
</div>
</div>
<button type="button" class="btn btn-sm btn-outline-danger" onclick="btnDeleteCheckBox()">선택한 사용자 탈퇴</button>
</div>
<table class="table table-sm table-bordered table-hover" id="employeeTable">
<colgroup>
<col width="5%">
<col width="15%">
<col width="20%">
<col width="20%">
<col width="20%">
<col width="20%">
</colgroup>
<thead class="thead-light">
<tr>
<th scope="col" class="text-center">
<input type="checkbox" id="allCheck" name="allCheck" />
</th>
<th scope="col" class="text-center">이름 (ID)</th>
<th scope="col" class="text-center">이메일</th>
<th scope="col" class="text-center">연락처</th>
<th scope="col" class="text-center">암호 변경일</th>
<th scope="col" class="text-center">권한설정</th>
</tr>
</thead>
<tbody>
<c:if test="${totalCount gt 0 }">
<c:forEach items="${listUser}" var="user" >
<tr class="text-align bg-white" style="cursor:pointer">
<td class="text-center">
<input type="checkbox" id="chk_it_no" name="chk_it_no" value="${user.getUser_no()}" />
</td>
<td class="rowTr">
${user.getUser_nm()}
<span style="color: cadetblue"> ( ${user.getUser_id()} ) </span>
</td>
<td class="rowTr">
${user.getUser_email()}
</td>
<td class="rowTr">
${user.getUser_tel()}
</td>
<td class="rowTr">
<fmt:formatDate value='${user.getModify_pw()}' pattern='yyyy-MM-dd' var="modiDate"/>
<fmt:parseDate value="${modiDate}" var="modiDate2" pattern="yyyy-MM-dd"/>
<fmt:parseNumber value="${modiDate2.time / (1000*60*60*24)}" integerOnly="true" var="modiDate3"/>
<jsp:useBean id="today" class="java.util.Date" />
<fmt:formatDate value='${today}' pattern='yyyy-MM-dd' var="nowDate"/>
<fmt:parseDate value="${nowDate}" var="nowDate2" pattern="yyyy-MM-dd"/>
<fmt:parseNumber value="${nowDate2.time / (1000*60*60*24)}" integerOnly="true" var="nowDate3"/>
<c:set var="check_pw" value="${nowDate3 - modiDate3}" />
${modiDate}
<c:if test="${check_pw gt 30}" >
<span class="ml-3" style="font-family: 'AmeriGarmnd BT';color:brown"><b>30일 초과</b></span>
</c:if>
</td>
<td class="">
<button type="button" class="btn btn-sm btn-outline-info btnGrant">권한설정</button>
<button type="button" class="btn btn-sm btn-outline-danger btnPwClear">접속 실패 ${user.getFail_count()} 회</button>
</td>
</tr>
</c:forEach>
</c:if>
<c:if test="${totalCount eq 0 }">
<tr class="center">
<td scope='row' colspan='10'>검색 결과가 없습니다.</td>
</tr>
</c:if>
</tbody>
</table>
// 페이징 ==========================================
<ul class="pagination">
<c:if test="${pageMaker.prev }">
<li class="page-item"><a class="page-link" href="<c:url value="/basic/auth?pageNum=1"/>">맨앞</a></li>
</c:if>
<c:if test="${pageMaker.prev }">
<li class="page-item">
<a class="page-link" href="<c:url value="/basic/auth?pageNum=${pageMaker.cri.pageNum-1}"/>">이전</a>
</li>
</c:if>
<c:forEach var="num" begin="${pageMaker.startPage }" end="${pageMaker.endPage }">
<li class="page-item ${pageMaker.cri.pageNum == num? "active":"" }">
<a class="page-link" href=" <c:url value="/basic/auth?pageNum=${num}"/>">${num}</a>
</li>
</c:forEach>
<c:if test="${pageMaker.next }">
<li class="page-item"><a class="page-link" href="<c:url value="/basic/auth?pageNum=${pageMaker.cri.pageNum+1}"/>">다음</a></li>
</c:if>
<c:if test="${pageMaker.next }">
<li class="page-item"><a class="page-link" href="<c:url value="/basic/auth?pageNum=${pageMaker.lastPage}"/>">맨끝</a></li>
</c:if>
</ul>
</form>728x90