JSP 강의평가 웹 사이트 - 평가 검색 및 출력 구현

목표: 데이터베이스에 등록된 강의평가 글을 홈페이지에 출력하고, 검색할 수 있게 한다.

 

EvaluationDAO.java에 검색 함수 추가하기

package evaluation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import util.DatabaseUtil;

public class EvaluationDAO {

	public int write(EvaluationDTO evaluationDTO) {
		String SQL = "INSERT INTO EVALUATION VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)"; // EvaluationID는
																									// auto increament
																									// 설정이 되있기 때문에
																									// NULL값을 넣어주면 차례대로
																									// 증가함
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DatabaseUtil.getConnection();
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, evaluationDTO.getUserID());
			pstmt.setString(2, evaluationDTO.getLectureName());
			pstmt.setString(3, evaluationDTO.getProfessorName());
			pstmt.setInt(4, evaluationDTO.getLectureYear());
			pstmt.setString(5, evaluationDTO.getSemesterDivide());
			pstmt.setString(6, evaluationDTO.getLectureDivede());
			pstmt.setString(7, evaluationDTO.getEvaluationTitle());
			pstmt.setString(8, evaluationDTO.getEvaluationContent());
			pstmt.setString(9, evaluationDTO.getTotalScore());
			pstmt.setString(10, evaluationDTO.getCreditScore());
			pstmt.setString(11, evaluationDTO.getComfortableScore());
			pstmt.setString(12, evaluationDTO.getLectureScore());
			return pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public ArrayList<EvaluationDTO> getList (String lectureDivede, String searchType, String search, int pageNumber) {
    	   if(lectureDivede.equals("전체")) {
    		  lectureDivede = "";
    	   }
			ArrayList<EvaluationDTO> evaluationList = null;
			String SQL = "";
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
    		  try {		
    			  if(searchType.equals("최신순")) {
    				 SQL = "SELECT * FROM EVALUATION WHERE lectureDivede Like ? AND CONCAT(lectureName, professorName, evaluationTitle, evaluationContent) LIKE " +
    			            "? ORDER BY evaluationID DESC LIMIT " + pageNumber * 5 + ", " + pageNumber * 5 + 6;       
    			  } else if (searchType.equals("추천순")) {
    				  SQL = "SELECT * FROM EVALUATION WHERE lectureDivede Like ? AND CONCAT(lectureName, professorName, evaluationTitle, evaluationContent) LIKE " +
    			            "? ORDER BY likeCount DESC LIMIT " + pageNumber * 5 + ", " + pageNumber * 5 + 6;
    			  }
    			  conn = DatabaseUtil.getConnection(); 
    			  pstmt = conn.prepareStatement(SQL); 
    			  pstmt.setString(1, "%" + lectureDivede + "%");
    			  pstmt.setString(2, "%" + search + "%");
    			  rs = pstmt.executeQuery();
    			  evaluationList = new ArrayList<EvaluationDTO>();
    			  while(rs.next()) {
    				 EvaluationDTO evaluation = new EvaluationDTO(
    						 rs.getInt(1),
    						 rs.getString(2),
    						 rs.getString(3),
    						 rs.getString(4),
    						 rs.getInt(5),
    						 rs.getString(6),
    						 rs.getString(7),
    						 rs.getString(8),
    						 rs.getString(9),
    						 rs.getString(10),
    						 rs.getString(11),
    						 rs.getString(12),
    						 rs.getString(13),
    						 rs.getInt(14)
    					);
    				    evaluationList.add(evaluation);
    			  }
    		  } catch (Exception e) {
    			   e.printStackTrace();
    		  } finally {
    			try {if(conn != null) conn.close();} catch (Exception e) {e.printStackTrace();}
    			try {if(pstmt != null) pstmt.close();} catch (Exception e) {e.printStackTrace();}
    			try {if(rs != null) rs.close();} catch (Exception e) {e.printStackTrace();}
    		 }
    		  return evaluationList;
       }
}

index.jsp 수정

-> '최신순', '추천순'으로 사용자 요청에 따른 쿼리로 정렬한다.

-> 페이지당 5개의 글을 출력하도록 구성

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.io.PrintWriter" %>
<%@ page import="user.UserDAO" %>
<%@ page import="evaluation.EvaluationDTO" %>
<%@ page import="evaluation.EvaluationDAO" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.net.URLEncoder" %>
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; "charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>강의평가 웹 사이트</title> 
    <!-- 부트스트랩 CSS 추가 -->
    <link rel="stylesheet" href="./css/bootstrap.min.css">
    <!-- 커스텀 CSS 추가 -->
    <link rel="stylesheet" href="./css/custom.css">
    
</head>
<body>
<%
   request.setCharacterEncoding("UTF-8");
   String lectureDivede = "전체";
   String searchType="최신순";
   String search = "";
   int pageNumber = 0;
   if(request.getParameter("lectureDivede") != null) {
	   lectureDivede = request.getParameter("lectureDivede");
   }
   if(request.getParameter("searchType") != null) {
	   searchType = request.getParameter("searchType");
   }
   if(request.getParameter("search") != null) {
	   search = request.getParameter("search");
   }
   if(request.getParameter("pageNumber") != null) {
	   try {
		   pageNumber = Integer.parseInt(request.getParameter("pageNumber"));
	   } catch (Exception e) {
		   System.out.println("검색 페이지 번호 오류");
	   }
	   
   }   
 
   String userID = null;
   if(session.getAttribute("userID") != null) {
      userID = (String) session.getAttribute("userID");
   }
   if(userID == null) {
	   PrintWriter script = response.getWriter();
	   script.println("<script>");
	   script.println("alert('로그인을 해주세요.');");
	   script.println("location.href = 'userLogin.jsp';");
	   script.println("</script>");
	   script.close();
	   return;
   }
   boolean emailChecked = new UserDAO().getUserEmailChecked(userID);
   if(emailChecked == false) {
	   PrintWriter script = response.getWriter();
	   script.println("<script>");
	   script.println("location.href = 'emailSendConfirm.jsp';");
	   script.println("</script>");
	   script.close();
	   return;
   }
 %>
     <nav class="navbar navbar-expand-lg navbar-light bg-light">
     <a class="navbar-brand" href="index.jsp">강의평가 웹 사이트</a>
     <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbar" aria-controls="navbar" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
       </button>
       
       <div id="navbar" class="collapse navbar-collapse">
          <ul class="navbar-nav mr-auto">
                 <li class="nav-item active">
                      <a class="nav-link" href="index.jsp">메인</a>
                 </li>
                 <li class="nav-item dropdown">
                    <a class="nav-link dropdown-toggle" id="dropdown" data-toggle="dropdown">
                                               회원관리
                    </a>
                 <div class="dropdown-menu" aria-labelledby="dropdown">
<%
 if(userID == null) {
%>
                        <a class="dropdown-item" href="userLogin.jsp">로그인</a>
                        <a class="dropdown-item" href="userJoin.jsp">회원가입</a>
<%
 } else {
%>
                        <a class="dropdown-item" href="userLogout.jsp">로그아웃</a>
<%
    }
%>
                 </div>
                 </li>
            </ul>
            <form action="./index.jsp" method="get" class="form-inline my-2 my-lg-0">
               <input class="form-control mr-sm-2" type="search" placeholder="내용을 입력하세요" aria-label="Search">
                <button class="btn btn-outline-success my-2 my-sm-0" type="submit">검색</button>
            </form>
       </div>
     </nav>
	<section class="container">
		<!-- 검색창 -->
		<form method="get" action="./index.jsp" class="form-inline mt-3">
			<!-- get 방식으로 어떠한 내용을 서버로 전달 'mt-3'는 위쪽으로 3만큼 마진 -->
			<select name="lectureDivede" class="form-control mx=1 mt-2">
				<!-- select를 이용해 강의 구분을 서버로 전달 -->
				<option value="전체">전체</option>
				<!-- option으로 서버로 내용 전달 -->
				<option value="전공"
					<% if(lectureDivede.equals("전공")) out.println("selected"); %>>전공</option>
				<option value="교양"
					<% if(lectureDivede.equals("교양")) out.println("selected"); %>>교양</option>
				<option value="기타"
					<% if(lectureDivede.equals("기타")) out.println("selected"); %>>기타</option>
			</select> <select name="searchType" class="form-control mx=1 mt-2">
				<!-- select를 이용해 강의 구분을 서버로 전달 -->
				<option value="최신순">최신순</option>
				<!-- option으로 서버로 내용 전달 -->
				<option value="추천순"
					<% if(searchType.equals("추천순")) out.println("selected"); %>>추천순</option>
			</select> <input type="text" name="search" class="form-control mx-1 mt-2"
				placeholder="Search">
			<!-- input을 이용해 사용자가 실질적으로 입력하도록 -->
			<button type="submit" class="btn btn-primary mx-1 mt-2">검색</button>
			<a class="btn btn-primary mx-1 mt-2" data-toggle="modal"
				href="#registerModal">등록하기</a>
			<!-- modal은 웹페이지 위에 나오는 것 -->
			<a class="btn btn-danger mx-1 mt-2" data-toggle="modal"
				href="#reportModal">신고</a>
		</form>
		<%
       ArrayList<EvaluationDTO> evaluationList = new ArrayList<EvaluationDTO>();
       evaluationList = new EvaluationDAO().getList(lectureDivede, searchType, search, pageNumber);
       if(evaluationList != null) {
    	   for(int i = 0; i < evaluationList.size(); i++) {
    		   if(i == 5) break;
    		   EvaluationDTO evaluation = evaluationList.get(i);
         %>
		<div class="card bg-light mt-3">
			<div class="card-header bg-light">
				<div class="row">
					<div class="col-8 text-left"><%=evaluation.getLectureName() %>&nbsp;<small><%= evaluation.getProfessorName() %></small>
					</div>
					<div class="col-4 text-right">
						종합 <span style="color: red;"><%= evaluation.getTotalScore() %></span>
					</div>
				</div>
			</div>
		<div class="card-body">
			<h5 class="card-title">
				<%= evaluation.getEvaluationTitle() %>
				&nbsp;<small>(<%= evaluation.getLectureYear() %>년 <%= evaluation.getSemesterDivide() %>)</small>
			</h5>
			<p class="card-text"><%= evaluation.getEvaluationContent() %></p>
			<div class="row">
				<div class="col-9 text-left">
					성적<span style="color: red;"><%= evaluation.getCreditScore() %></span>
					널널<span style="color: red;"><%= evaluation.getComfortableScore() %></span>
					강의<span style="color: red;"><%= evaluation.getLectureScore() %></span>
					<span style="color: green;">(추천:<%= evaluation.getLikeCount() %>
					</span>
				</div>
				<div class="col-3 text-right">
					<a onclick="return confirm('추천하시겠습니까?')"
						href="./likeAction.jsp?evaluationID=">추천</a> <a
						onclick="return confirm('삭제하시겠습니까?')"
						href="./deleteAction.jsp?evaluationID=">삭제</a>
				</div>
			</div>
		</div>
	</div>

<%
       } 
       }
%>

	</section>
	<ul class="pagination justify-content-center mt-3">
		<li class="page-item">
<%   
   if(pageNumber <= 0) {
%> 
   <a class="page-link disabled">이전</a> 
<%
    } else {
%> 
            <a class="page-link" href="./index.jsp?lectureDivede=<%= URLEncoder.encode(lectureDivede, "UTF-8") %>&searchType=
			<%= URLEncoder.encode(searchType, "UTF-8") %>&search=<%= URLEncoder.encode(search, "UTF-8") %>
			&pageNumber=<%= pageNumber -1 %>">이전</a> 
<%
    }  
%>
		</li>
		
		<li>
<%
    if(evaluationList.size() < 6) {
%> 
   <a class="page-link disabled">다음</a> 
<%
    } else {
%> 
           <a class="page-link" href="./index.jsp?lectureDivede=<%= URLEncoder.encode(lectureDivede, "UTF-8") %>
           &searchType=<%= URLEncoder.encode(searchType, "UTF-8") %>&search=<%= URLEncoder.encode(search, "UTF-8") %>
			&pageNumber=<%= pageNumber +1 %>">다음</a> 
<%
    }
%>
		</li>
	</ul>
	
	<div class="modal fade" id="registerModal" tabindex="-1" role="dialog" aria-labelledby="modal" aria-hidden="true"> <!-- modal 양식 modal fade를 사용하는 게 일반적 -->
       <div class="modal-dialog"> <!-- modal-dialog를 이용해 모달창임을 알려주는 것 -->
           <div class="modal-content">
              <div class="modal-header"> <!-- 모달창 제목 -->
               <h5 class="modal-title" id="modal">평가등록</h5>
               <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <!-- 닫기 버튼 -->
                   <span aria-hidden="true">&times;</span> <!-- 닫기 아이콘 -->
               </button>
              </div>
              <div class="modal-body">
                  <form action="./evaluationRegisterAction.jsp" method="post"> <!--jsp 페이지로 post방식을 이용해 보낸다-->
                    <div class="form-row"> <!-- 사용자가 입력할 수 있는 내용을 한줄씩 들어가도록 만든다. row는 하나의 행을 여러개의 열로 나눌 때 사용 -->
                      <div class="form-group col-sm-6">
                        <label>강의명</label>
                        <input type="text" name="lectureName" class="form-control" maxlength="20"> <!-- 강의명 최대 20자 -->
                      </div>
                      <div class="form-group col-sm-6">  <!-- 일반적으로 한개의 행은 12열이 배당된다. 여기선 위 아래로 반반씩 나누었다 -->
                        <label>교수명</label>
                        <input type="text" name="professorName" class="form-control" maxlength="20"> <!-- 강의명 최대 20자 -->
                      </div>
                    </div>
                     <div class="form-row">
                       <div class="form-group col-sm-4">
                       <label>수강연도</label>
                       <select name="lectureYear" class="form-control">
                           <option value="2011">2011</option>
                           <option value="2012">2012</option>
                           <option value="2013">2013</option>
                           <option value="2014">2014</option>
                           <option value="2015">2015</option>
                           <option value="2016">2016</option>
                           <option value="2017">2017</option>
                           <option value="2018" selected>2018</option>
                           <option value="2019">2019</option>
                           <option value="2020">2020</option>
                           <option value="2021">2021</option>
                           <option value="2022">2022</option>
                           <option value="2023">2023</option>
                       </select>
                       </div>
                       <div class="form-group col-sm-4">
                         <label>수강학기</label>
                       <select name="semesterDivide" class="form-control">
                            <option value="1학기" selected>1학기</option>
                            <option value="여름학기">여름학기</option>
                            <option value="2학기">2학기</option>
                            <option value="겨울학기">겨울학기</option>     
                        </select>
                      </div>
                      <div class="form-group col-sm-4">
                       <label>강의구분</label>
                       <select name="lectureDivede" class="form-control">
                            <option value="전공"selected>전공</option>
                            <option value="교양">교양</option>
                            <option value="기타">기타</option>     
                       </select>
                       </div>
                     </div>
                     <div class="form-group">
                            <label>제목</label>
                            <input type="text" name="evaluationTitle" class="form-control" maxlength="30">                     
                     </div>
                     <div class="form-group">
                            <label>내용</label>
                            <textarea name="evaluationContent" class="form-control" maxlength="2048" style="height: 180px;"></textarea>                     
                     </div>
                     <div class="form-row"><!-- 하나의 행을 나눌 때 사용 -->
                      <div class="form-group col-sm-3">
                            <label>종합</label>
                            <select name="totalScore" class="form-control">
                                <option value="A" selected>A</option>
                                <option value="B" >B</option>
                                <option value="C" >C</option>
                                <option value="D" >D</option>
                                <option value="E" >E</option>
                                <option value="F" >F</option>
                            </select>
                      </div>
                      <div class="form-group col-sm-3">
                            <label>성적</label>
                            <select name="creditScore" class="form-control">
                                <option value="A" selected>A</option>
                                <option value="B" >B</option>
                                <option value="C" >C</option>
                                <option value="D" >D</option> 
                                <option value="E" >E</option>
                                <option value="F" >F</option>
                            </select>
                     </div>
                     <div class="form-group col-sm-3">
                            <label>널널</label>
                            <select name="comfortableScore" class="form-control">
                                <option value="A" selected>A</option>
                                <option value="B" >B</option>
                                <option value="C" >C</option>
                                <option value="D" >D</option> 
                                <option value="E" >E</option>
                                <option value="F" >F</option>
                            </select>
                     </div>
                     <div class="form-group col-sm-3">
                            <label>강의</label>
                            <select name="lectureScore" class="form-control">
                                <option value="A" selected>A</option>
                                <option value="B" >B</option>
                                <option value="C" >C</option>
                                <option value="D" >D</option> 
                                <option value="E" >E</option>
                                <option value="F" >F</option>
                            </select>
                     </div>
                     </div>
                     <div class="modal-footer">
                          <button type="button" class="btn btn-secondary" data-dismiss="modal">취소</button>
                          <button type="submit" class="btn btn-primary">등록하기</button>
                      </div>
                  </form>
              </div>           
           </div>
       </div>
     </div>
     
    <div class="modal fade" id="reportModal" tabindex="-1" role="dialog" aria-labelledby="modal" aria-hidden="true"> <!-- modal 양식 modal fade를 사용하는 게 일반적 -->
       <div class="modal-dialog"> <!-- modal-dialog를 이용해 모달창임을 알려주는 것 -->
           <div class="modal-content">
              <div class="modal-header"> <!-- 모달창 제목 -->
               <h5 class="modal-title" id="modal">신고하기</h5>
               <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <!-- 닫기 버튼 -->
                   <span aria-hidden="true">&times;</span> <!-- 닫기 아이콘 -->
               </button>
              </div>
              <div class="modal-body">
                  <form action="./reportAction.jsp" method="post"> <!--jsp 페이지로 post방식을 이용해 보낸다-->                    
                     <div class="form-group">
                            <label>신고 제목</label>
                            <input type="text" name="reportTitle" class="form-control" maxlength="30">                     
                     </div>
                     <div class="form-group">
                            <label>신고 내용</label>
                            <textarea name="reportContent" class="form-control" maxlength="2048" style="height: 180px;"></textarea>                     
                     </div>
                     <div class="modal-footer">
                          <button type="button" class="btn btn-secondary" data-dismiss="modal">취소</button>
                          <button type="submit" class="btn btn-danger">신고하기</button>
                      </div>
                  </form>
                  </div>
              </div>           
           </div>
       </div>
       <footer class="bg-dark mt-4 p-5 text-center" style="color: #FFFFFF;">
         Copyright &copy; 2020 한승표 ALL RIGHT Reserved.
         </footer>
   
   
   
   
   
     <!-- 제이쿼리 자바스크립트 추가 -->
     <script src="./js/jquery.min.js"></script>
     <!-- 파퍼 자바스크립트 추가 -->
     <script src="./js/popper.js"></script>
     <!-- 부트스트랩 자바스크립트 추가 -->
     <script src="./js/bootstrap.min.js"></script>
    
 </body>
</html>