팀프로젝트 4일차 스프링부트_사원목록 조회, 검색, 페이징 기능
DTO
package com.study.springboot.member.model;
import java.time.LocalDateTime;
import com.study.springboot.member.service.WrongIdPasswordException;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
@Getter
@NoArgsConstructor
@Setter
public class Member {
private Long mno;
private String name;
private String password;
private String phone;
private LocalDateTime hiredate;
private String job;
private Integer deptno;
private String dname;
private String loc;
// 검색 필터
private String type;
private String keyword;
public Member(Long mno, String name, String password, String phone, LocalDateTime hiredate, String job, Integer deptno) {
this.mno = mno;
this.name = name;
this.password = password;
this.phone = phone;
this.hiredate = hiredate;
this.job = job;
this.deptno = deptno;
}
// setHiredate 메소드를 수정
public void setHiredate(LocalDateTime hiredate) {
this.hiredate = hiredate;
}
// setDepartment 메소드를 수정
public void setDepartment(Department department) {
this.deptno = department.getDeptno();
}
public void changePassword(String oldPassword, String newPassword) {
if (!password.equals(oldPassword)) {
throw new WrongIdPasswordException();
}
this.password = newPassword;
}
public boolean matchPassword(String password) {
return this.password.equals(password);
}
public void setMno(Long mno) {
this.mno = mno;
}
}
페이징
package com.study.springboot.member.model;
import java.util.List;
public class MemberPage {
@Override
public String toString() {
return "BoardPage [total=" + total + ", currentPage=" + currentPage + ", content=" + content + ", totalPages="
+ totalPages + ", startPage=" + startPage + ", endPage=" + endPage + "]";
}
private int total;
private int currentPage;
private List<Member> content;
private int totalPages;
private int startPage;
private int endPage;
public MemberPage(int total, int currentPage, int size, List<Member> content) {
this.total = total;
this.currentPage = currentPage;
this.content = content;
if (total == 0) {
totalPages = 0;
startPage = 0;
endPage = 0;
} else {
totalPages = total / size;
if (total % size > 0) {
totalPages++;
}
int modVal = currentPage % 5;
startPage = currentPage / 5 * 5 + 1;
if (modVal == 0) startPage -= 5;
endPage = startPage + 4;
if (endPage > totalPages) endPage = totalPages;
}
}
public int getTotal() {
return total;
}
public boolean hasNoArticles() {
return total == 0;
}
public boolean hasArticles() {
return total > 0;
}
public int getCurrentPage() {
return currentPage;
}
public int getTotalPages() {
return totalPages;
}
public List<Member> getContent() {
return content;
}
public int getStartPage() {
return startPage;
}
public int getEndPage() {
return endPage;
}
}
DAO
// 사원 목록 출력
public List<Member> listAll(int page, int size) {
int offset = (page - 1) * size;
String sql = "SELECT m.mno, m.name, m.phone, m.hiredate, m.job, d.dname, d.loc " +
"FROM member m " +
"LEFT JOIN department d ON m.deptno = d.deptno " +
"LIMIT ?, ?";
return jdbcTemplate.query(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, offset);
ps.setInt(2, size);
return ps;
}
},
new RowMapper<Member>() {
@Override
public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
Member member = new Member();
member.setMno((long) rs.getInt("mno"));
member.setName(rs.getString("name"));
member.setPhone(rs.getString("phone"));
// hiredate 컬럼을 LocalDateTime으로 변환
Timestamp hiredateTimestamp = rs.getTimestamp("hiredate");
member.setHiredate(hiredateTimestamp != null ? hiredateTimestamp.toLocalDateTime() : null);
member.setJob(rs.getString("job"));
member.setDname(rs.getString("dname"));
member.setLoc(rs.getString("loc"));
return member;
}
});
}
// 페이징 기능에 사용
public int getTotalMemberCount() {
String sql = "SELECT COUNT(*) FROM member";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
// 검색
public List<Member> searchMembers(String type, String keyword) {
// 검색 쿼리 작성 (예: 이름으로 검색)
String sql = "SELECT m.mno, m.name, m.phone, m.hiredate, m.job, d.dname, d.loc " +
"FROM member m " +
"LEFT JOIN department d ON m.deptno = d.deptno " +
"WHERE " + type + " LIKE ?";
String likeKeyword = "%" + keyword + "%";
return jdbcTemplate.query(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, likeKeyword);
return ps;
}
},
new RowMapper<Member>() {
@Override
public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
Member member = new Member();
member.setMno((long) rs.getInt("mno"));
member.setName(rs.getString("name"));
member.setPhone(rs.getString("phone"));
// hiredate 컬럼을 LocalDateTime으로 변환
Timestamp hiredateTimestamp = rs.getTimestamp("hiredate");
member.setHiredate(hiredateTimestamp != null ? hiredateTimestamp.toLocalDateTime() : null);
member.setJob(rs.getString("job"));
member.setDname(rs.getString("dname"));
member.setLoc(rs.getString("loc"));
return member;
}
});
}
Service
package com.study.springboot.member.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.study.springboot.member.dao.MemberDao;
import com.study.springboot.member.model.Member;
@Service
public class MemberService {
@Autowired
private MemberDao memberDao;
public MemberInfo authenticate(Long mno, String password) {
Member member = memberDao.selectByMno(mno);
if (member == null) {
throw new WrongIdPasswordException();
}
if (!member.matchPassword(password)) {
throw new WrongIdPasswordException();
}
return new MemberInfo(member.getMno(),
member.getName(), member.getPhone(), member.getJob(), member.getDeptno());
}
public List<Member> searchMembers(String type, String keyword) {
return memberDao.searchMembers(type, keyword);
}
}
Controller
package com.study.springboot.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import com.study.springboot.member.dao.MemberDao;
import com.study.springboot.member.model.Member;
import com.study.springboot.member.service.MemberService;
import lombok.extern.slf4j.Slf4j;
@Controller
@Slf4j
public class MemberListController {
@Autowired
private MemberDao memberDao;
@Autowired
private MemberService memberService;
@GetMapping("/memberList")
public String listMembers(Model model,
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "10") int size) {
int totalMemberCount = memberDao.getTotalMemberCount();
int totalPages = (int) Math.ceil((double) totalMemberCount / size);
List<Member> members = memberDao.listAll(page, size);
model.addAttribute("members", members);
model.addAttribute("page", page);
model.addAttribute("size", size);
model.addAttribute("totalPages", totalPages);
log.info("Listing members: {}", members);
System.out.println(members);
return "/login/memberList";
}
@GetMapping("/getSearchList")
public String getSearchList(@RequestParam("type") String type,
@RequestParam("keyword") String keyword,
Model model){
List<Member> members = memberService.searchMembers(type, keyword);
model.addAttribute("members", members);
return "/login/memberList";
}
}
JS
$(document).ready(function () {
// 검색 버튼을 클릭했을 때
$('.search').click(function (event) {
event.preventDefault(); // 기본 동작 중단
// 입력한 데이터 가져오기
var type = $('.type').val();
var keyword = $('.keyword').val();
if (keyword == null) {
alert("검색내용을 입력하세요.");
return; // 함수 종료
}
// AJAX 요청
$.ajax({
url: '/getSearchList', // 적절한 URL로 수정
type: 'Get',
data: {
type: type,
keyword: keyword,
page: 1 // 검색 결과를 첫 번째 페이지로 설정
},
success: function (res) {
$('.mainView').html(res);
},
error: function () {
// AJAX 요청이 실패한 경우 처리할 내용
alert('오류가 발생했습니다.');
}
});
});
// 페이지 번호 링크를 클릭했을 때의 처리
$(document).on('click', '.search_page', function (event) {
event.preventDefault(); // 기본 동작 중단
var pageUrl = $(this).attr('href');
// AJAX 요청
$.ajax({
url: pageUrl,
type: 'GET',
success: function (data) {
// AJAX 요청이 성공하면 data를 적절한 방식으로 처리하여 페이지 업데이트
$('.mainView').html(data);
},
error: function () {
// AJAX 요청이 실패한 경우 처리할 내용
alert('오류가 발생했습니다.');
}
});
});
});
JSP
<%@ page contentType="text/html; charset=utf-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 목록</title>
<style>
.pagination {
display: inline-block;
padding: 0;
}
.pagination li {
display: inline;
margin-right: 5px;
}
</style>
</head>
<body>
<h2>사원 목록</h2>
<table border="1">
<thead>
<tr>
<th>사번</th>
<th>이름</th>
<th>핸드폰</th>
<th>입사일</th>
<th>직급</th>
<th>부서명</th>
<th>부서 위치</th>
</tr>
</thead>
<tbody id ="usertable">
<c:forEach items="${members}" var="member">
<tr>
<td>${member.mno}</td>
<td>${member.name}</td>
<td>${member.phone}</td>
<td>${member.hiredate}</td>
<td>${member.job}</td>
<td>${member.dname}</td>
<td>${member.loc}</td>
</tr>
</c:forEach>
</tbody>
</table>
<!-- 페이지 번호 표시 부분 -->
<div class="page-box">
<c:if test="${totalPages > 1}">
<ul class="pagination">
<li class="<c:if test="${page == 1}">disabled</c:if>">
<a class="search_page" href="/memberList?page=${page - 1}" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
<c:forEach begin="1" end="${totalPages}" var="pageNumber">
<li class="<c:if test="${page == pageNumber}">active</c:if>">
<a class="search_page" href="/memberList?page=${pageNumber}">${pageNumber}</a>
</li>
</c:forEach>
<li class="<c:if test="${page == totalPages}">disabled</c:if>">
<a class="search_page" href="/memberList?page=${page + 1}" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</ul>
</c:if>
</div>
<div>
<select name="type" class="type">
<option selected value="">검색 내용 선택</option>
<option value="mno">사번 검색</option>
<option value="name">이름 검색</option>
<option value="dname">부서명 검색</option>
</select>
<input type="text" class="keyword" name="keyword" value="" />
<button class="search">검색</button>
</div>
<!-- <script>
function getSearchList() {
$.ajax({
type: 'GET',
url: "/getSearchList",
data: $("form[name=search-form]").serialize(),
success: function (result) {
$(#usertable).html(result);
}
});
return false; // 페이지 새로고침 방지
}
</script> -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="js/memberList.js"></script>
</body>
</html>
결과
사번, 이름, 부서 검색 기능
'프로젝트 기반 자바(JAVA) 응용 SW개발자 취업과정' 카테고리의 다른 글
2023-09-11 80일차 (0) | 2023.09.11 |
---|---|
2023-09-08 79일차 (0) | 2023.09.08 |
2023-09-06 77일차 (0) | 2023.09.06 |
2023-09-05 76일차 (0) | 2023.09.05 |
2023-09-04 75일차 (0) | 2023.09.04 |