Team project/대학학사관리 - Chelsea University
학생 및 교수 조회 (검색과 페이징 처리)
개발자공부
2024. 10. 25. 17:59
검색
LIKE 쿼리문, ORDER BY를 이용해 DESC(내림차순)으로 정렬합니다.
더보기
LIKE 구문은 쿼리문 WHERE절에 주로 사용되며, 부분적으로 일치하는 컬럼을 찾을 때 사용한다.
[A로 시작하는 문자를 찾기]
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A%'
[A로 끝나는 문자 찾기]
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A'
[A를 포함하는 문자 찾기]
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A%'
[A로 시작하는 두글자 문자 찾기]
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A_'
[첫번째 문자가 'A''가 아닌 모든 문자열 찾기]
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE'[^A]'
[첫번째 문자가 'A'또는'B'또는'C'인 문자열 찾기]
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[ABC]'
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[A-C]'페이징 처리
SQL문에서 LIMIT과 OFFSET을 이용했습니다.
페이징 처리를 할 때 현재 페이지 번호, 전체 페이지 수, 시작할 게시글 번호(offset) 가 필요합니다.
offset을 계산하기 위해서 (page - 1) * pageSize 식을 사용하면 되는데, 여기서 페이지 사이즈는 limit을 의미합니다.
더보기
LIMIT : 결과 중 처음부터 몇 개까지만 가져올지
SELECT * FROM 테이블명 LIMIT 10; -- 처음 부터 10개만 출력하기 (1 ~ 10)
SELECT * FROM 테이블명 LIMIT 100, 10; -- 100번째부터 그 후 10개 출력하기 (101 ~ 110)
OFFSET : 어디서부터 가져올지
SELECT * FROM 테이블명 ORDERS LIMIT 20 OFFSET 5; -- 5번째 행 부터 25행 까지 출력 (6 ~ 25)
SELECT * FROM 테이블명 ORDERS LIMIT 5, 20 (limit 5, 20 과 같다고 보면 된다.)코드
더보기
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ include file="/WEB-INF/views/home/staffHeader.jsp"%>
<link rel="stylesheet" href="${pageContext.request.contextPath}/resources/css/main.css">
<link rel="stylesheet" href="${pageContext.request.contextPath}/resources/css/sidebar.css">
<link rel="stylesheet" href="${pageContext.request.contextPath}/resources/css/pagination.css">
<style>
.table-container {
padding: 10px;
border-spacing: 10px;
}
.table-container td {
padding-right: 30px;
}
.table-container input {
padding: 5px;
outline: none;
}
.essential-sign {
color: red;
}
.submit-btn {
border: none;
padding: 5px 10px;
color: #fff;
background-color: #001489;
border-radius: 5px;
}
</style>
<div class="d-flex justify-content-center align-items-start" style="display:flex; min-width: 100em;">
<div class="sub--menu">
<div class="sub--menu--top">
<h2>학사관리</h2>
</div>
<div class="sub--menu--mid">
<table class="sub--menu--table" border="1">
<tr>
<td><a href="${pageContext.request.contextPath}/user/studentList" class="selected--menu">학생 명단 조회</a></td>
</tr>
<tr>
<td><a href="${pageContext.request.contextPath}/user/professorList">교수 명단 조회</a></td>
</tr>
<tr>
<td><a href="${pageContext.request.contextPath}/user/student">학생 등록</a></td>
</tr>
<tr>
<td><a href="${pageContext.request.contextPath}/user/professor">교수 등록</a></td>
</tr>
<tr>
<td><a href="${pageContext.request.contextPath}/tuition/bill">등록금 고지서 발송</a></td>
</tr>
<tr>
<td><a href="${pageContext.request.contextPath}/break/list/staff">휴학 처리</a></td>
</tr>
<tr>
<td><a href="${pageContext.request.contextPath}/sugang/period">수강신청 기간 설정</a></td>
</tr>
</table>
</div>
</div>
<main style="width: 100%; padding: 20px;">
<h1 class="sub--title">학생 명단 조회</h1>
<div class="split--div"></div>
<br>
<div class="sub--filter">
<form action="${pageContext.request.contextPath}/user/studentList" method="get">
<div>
<label for="dept_id">학과 번호 <input type="search" id="dept_id" name="dept_id"></label>
<label for="stu_id">학번 <input type="search" id="stu_id" name="stu_id"></label>
<button type="submit" class="submit-btn">조회</button>
</div>
</form>
</div>
<form action="">
<button type="submit" class="submit-btn">새학기 적용</button>
</form>
<table class="table table-striped sub--list--table">
<thead>
<tr>
<th>학번</th>
<th>이름</th>
<th>생년월일</th>
<th>성별</th>
<th>주소</th>
<th>전화번호</th>
<th>이메일</th>
<th>학과번호</th>
<th>학년</th>
<th>학기</th>
<th>입학일</th>
<th>졸업일(졸업예정일)</th>
</tr>
</thead>
<tbody>
<c:forEach var="student" items="${studentList}">
<tr>
<td>${student.id}</td>
<td>${student.name}</td>
<td>${student.birthDate}</td>
<td>${student.gender}</td>
<td>${student.address}</td>
<td>${student.tel}</td>
<td>${student.email}</td>
<td>${student.deptId}</td>
<td>${student.grade}</td>
<td>${student.semester}</td>
<td>${student.entranceDate}</td>
<td>${student.graduationDate}</td>
</tr>
</c:forEach>
</tbody>
</table>
<div class="pagination">
<c:forEach begin="1" end="${totalPages}" var="i">
<c:choose>
<c:when test="${i == page}">
<span class="page-item current-page">${i}</span>
</c:when>
<c:otherwise>
<span class="page-item"><a href="${pageContext.request.contextPath}/user/studentList?page=${i}" class="page-link">${i}</a></span>
</c:otherwise>
</c:choose>
</c:forEach>
</div>
</main>
</div>
<%@ include file="footer.jsp"%>;더보기
@WebServlet("/user/*")
public class UserController extends HttpServlet {
private static final long serialVersionUID = 1L;
private StudentRepository studentRepository;
private ProfessorRepository professorRepository;
private StudentListForm studentListForm;
private ProfessorListForm professorListForm;
private UserRepository userRepository;
private NoticeRepository noticeRepository;
private ScheuleRepository scheuleRepository;
private StuStatRepository stuStatRepository;
@Override
public void init() throws ServletException {
studentRepository = new StudentRepositoryImpl();
professorRepository = new ProfessorRepositoryImpl();
studentListForm = new StudentListForm();
professorListForm = new ProfessorListForm();
userRepository = new UserRepositoryImpl();
noticeRepository = new NoticeRepositoryImpl();
scheuleRepository = new ScheuleRepositoryImpl();
stuStatRepository = new StuStatRepositoryImpl();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String action = request.getPathInfo();
HttpSession session = request.getSession();
switch (action) {
case "/signin":
showSignIn(request, response, session);
break;
case "/studentList":
showStudentListPage(request, response, session);
break;
case "/professorList":
showProfessorListPage(request, response, session);
break;
case "/student":
showStudentCreatePage(request, response, session);
break;
case "/professor":
showProfessorCreatePage(request, response, session);
break;
case "/findid":
showFindIdPage(request, response, session);
break;
case "/findpassword":
showFindPasswordPage(request, response, session);
break;
case "/password":
password(request, response, session);
break;
case "/home":
showHomePage(request, response, session);
break;
case "/check":
showCheckPage(request, response, session);
break;
case "/myinfo":
showMyinfoPage(request, response, session);
break;
default:
response.sendError(HttpServletResponse.SC_NOT_FOUND);
break;
}
}
/**
* 교직원 -> 학생 명단 조회
*
* @param request
* @param response
* @param session
* @throws IOException
* @throws ServletException
*/
private void showStudentListPage(HttpServletRequest request, HttpServletResponse response, HttpSession session)
throws ServletException, IOException {
int page = 1;
int pageSize = 20; // limit
try {
String pageStr = request.getParameter("page");
if (pageStr != null) {
page = Integer.parseInt(pageStr);
}
} catch (Exception e) {
page = 1;
}
int offset = (page - 1) * pageSize; // 시작 위치 계산
String deptId = request.getParameter("dept_id");
String studentId = request.getParameter("stu_id");
List<Student> studentList = null;
int totalStudents = 0;
if (deptId == null && studentId == null) {
studentList = studentRepository.selectStudentList(pageSize, offset);
totalStudents = studentRepository.selectStudentAmount();
} else {
studentList = studentRepository.selectStudentList(deptId, studentId, pageSize, offset);
totalStudents = studentRepository.selectStudentAmount(deptId, studentId);
}
int totalPages = (int) Math.ceil((double) totalStudents / pageSize);
request.setAttribute("totalPages", totalPages);
request.setAttribute("studentList", studentList);
request.setAttribute("currentPage", page);
request.setAttribute("deptId", deptId);
request.setAttribute("stuId", studentId);
request.getRequestDispatcher("/WEB-INF/views/staff/studentList.jsp").forward(request, response);
}
}더보기
각 검색 조건별로 쿼리문을 만들었습니다.
public class StudentRepositoryImpl implements StudentRepository {
// 검색 쿼리
public static final String SELECT_ALL_STUDENT_SQL = " SELECT * FROM student_tb ORDER BY id LIMIT ? OFFSET ? ";
public static final String COUNT_ALL_STUDENT_SQL = " SELECT count(*) FROM student_tb ";
public static final String SELECT_STUDENT_BY_DEPT_ID = " SELECT * FROM student_tb WHERE dept_id LIKE ? ";
public static final String SELECT_STUDENT_BY_ID = " SELECT * FROM student_tb WHERE id LIKE ?";
public static final String SELECT_STU_DEPT_AND_STU_ID = " SELECT * FROM student_tb WHERE id LIKE ? AND dept_id LIKE ? LIMIT ? OFFSET ? ";
public static final String COUNT_STU_BY_ID = " SELECT count(*) FROM student_tb where id LIKE ? AND dept_id LIKE ? ";
}