JSP 강의평가 웹사이트 - 추천 및 삭제 기능 구현

STEP 1 LIKEY TABLE에 PRIMARY KEY 추가

STEP 2 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;
       }
	// 특정한 강의평가 글에 좋아요를 누르는 함수
	   public int like(String evaluationID) {
		   String SQL = "UPDATE EVALUATION SET likeCount = likeCount + 1 WHERE evaluationID = ?";	
			  Connection conn = null; 
			  PreparedStatement pstmt = null;
			  ResultSet rs = null; 
			  try {		  
				  conn = DatabaseUtil.getConnection(); 
				  pstmt = conn.prepareStatement(SQL);  
				  pstmt.setInt(1, Integer.parseInt(evaluationID));
			      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 int delete(String evaluationID) {
		   String SQL = "DELETE FROM EVALUATION WHERE evaluationID = ?";	
			  Connection conn = null; 
			  PreparedStatement pstmt = null;
			  ResultSet rs = null; 
			  try {		  
				  conn = DatabaseUtil.getConnection(); 
				  pstmt = conn.prepareStatement(SQL); 
				  pstmt.setInt(1, Integer.parseInt(evaluationID));
			      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 String getUserID(String evaluationID) {
		   String SQL = "SELECT userID FROM EVALUATION WHERE evaluationID = ?";	
			  Connection conn = null; 
			  PreparedStatement pstmt = null; 
			  ResultSet rs = null;
			  try {		  
				  conn = DatabaseUtil.getConnection(); 
				  pstmt = conn.prepareStatement(SQL); 
				  pstmt.setInt(1, Integer.parseInt(evaluationID));
				  rs = pstmt.executeQuery();
				  if(rs.next()) {
					  return rs.getString(1);
				  }
			  } 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 null; // 존재하지 않는 글
	   }
}

STEP 3 LIKEY package - LikeyDTO.java /LikeyDAO.java 생성

LikeyDTO.java

package likey;

public class LikeyDTO {
	
	String userID;
	int evaluationID;
	String userIP;
	
	public String getUserID() {
		return userID;
	}
	public void setUserID(String userID) {
		this.userID = userID;
	}
	public int getEvaluationID() {
		return evaluationID;
	}
	public void setEvaluationID(int evaluationID) {
		this.evaluationID = evaluationID;
	}
	public String getUserIP() {
		return userIP;
	}
	public void setUserIP(String userIP) {
		this.userIP = userIP;
	}
	public LikeyDTO() {
		
	}
	
	// 모든 변수를 초기화 해주는 함수
	public LikeyDTO(String userID, int evaluationID, String userIP) {
		super();
		this.userID = userID;
		this.evaluationID = evaluationID;
		this.userIP = userIP;
	}

}

LikeyDAO.java

package likey;

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

import util.DatabaseUtil;

public class LikeyDAO {

	public int like(String userID, String evaluationID, String userIP) {
		String SQL = "INSERT INTO LIKEY VALUES (?, ?, ?)";	
		  Connection conn = null; 
		  PreparedStatement pstmt = null; //특정한 SQL 문장을 수행 하도록 하는 class
		  ResultSet rs = null; // 특정한 SQL 문장을 수행한 이후에 나온 결과값에 대해 처리하고자 할 때 사용하는 class
		  try {		  
			  conn = DatabaseUtil.getConnection(); //Connection 객체 초기화 / DatabaseUtil.java 파일에서 리턴값에 반환된 객체들을 getConnection을 통해 연결받고 conn 객체에 담는다
			  pstmt = conn.prepareStatement(SQL); //conn 객체에서 prepareStatement를 실행하도록 준비
			  pstmt.setString(1, userID);
			  pstmt.setString(2, evaluationID);
			  pstmt.setString(3, userIP);				  
			  return pstmt.executeUpdate(); //executeQuery는 데이터를 검색할 때 사용 insert 나 delete 같은 update는 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; //추천 중복 오류
	}
}

 

STEP 4 deleteAction.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%>
<%@ page import="user.UserDAO"%>
<%@ page import="evaluation.EvaluationDAO"%>
<%@ page import="likey.LikeyDAO"%>
<%@ page import="java.io.PrintWriter"%>
<%
   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;
   }
   
   request.setCharacterEncoding("UTF-8");
   String evaluationID = null;
   if(request.getParameter("evaluationID") != null) {
	   evaluationID = request.getParameter("evaluationID");
   }
   EvaluationDAO evaluationDAO = new EvaluationDAO();
   if(userID.equals(evaluationDAO.getUserID(evaluationID))) {
	   int result = new EvaluationDAO().delete(evaluationID);
	   if (result == 1) {
		   PrintWriter script = response.getWriter();
		   script.println("<script>");
		   script.println("alert('삭제가 완료되었습니다');");
		   script.println("location.href = 'index.jsp'");
		   script.println("</script>");
		   script.close();
		   return;
	   } else {
		   PrintWriter script = response.getWriter();
		   script.println("<script>");
		   script.println("alert('데이터베이스 오류가 발생했습니다..');");
		   script.println("history.back();");
		   script.println("</script>");
		   script.close();
		   return;
	   }
   } else {
		   PrintWriter script = response.getWriter();
		   script.println("<script>");
		   script.println("alert('자신이 쓴 글만 삭제 가능합니다.');");
		   script.println("history.back();");
		   script.println("</script>");
		   script.close();
		   return;
	   }
   
   
%>

STEP 5 likeAction.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="user.UserDAO"%>
<%@ page import="evaluation.EvaluationDAO"%>
<%@ page import="likey.LikeyDAO"%>
<%@ page import="java.io.PrintWriter"%>
<%!public static String getClientIP(HttpServletRequest request) {
		String ip = request.getHeader("X-FORWARDED-FOR");
		if (ip == null || ip.length() == 0) {
			ip = request.getHeader("Proxy-Client-IP");
		}
		if (ip == null || ip.length() == 0) {
			ip = request.getHeader("WL-Proxy-Client-IP");
		}
		if (ip == null || ip.length() == 0) {
			ip = request.getRemoteAddr();
		}
		return ip;
	}%>
<%
   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;
   }
   
   request.setCharacterEncoding("UTF-8");
   String evaluationID = null;
   if(request.getParameter("evaluationID") != null) {
	   evaluationID = request.getParameter("evaluationID");
   }
   EvaluationDAO evaluationDAO = new EvaluationDAO();
   LikeyDAO likeyDAO = new LikeyDAO();
   int result = likeyDAO.like(userID, evaluationID, getClientIP(request));
	   if (result == 1) {
		   result = evaluationDAO.like(evaluationID);
		   if (result == 1) {
			   PrintWriter script = response.getWriter();
			   script.println("<script>");
			   script.println("alert('추천이 완료되었습니다.');");
			   script.println("location.href = 'index.jsp'");
			   script.println("</script>");
			   script.close();
			   return;
	   } else {
		   PrintWriter script = response.getWriter();
		   script.println("<script>");
		   script.println("alert('데이터베이스 오류가 발생했습니다..');");
		   script.println("history.back();");
		   script.println("</script>");
		   script.close();
		   return;
	   }
   } else {
		   PrintWriter script = response.getWriter();
		   script.println("<script>");
		   script.println("alert('이미 추천을 누른 글입니다.');");
		   script.println("history.back();");
		   script.println("</script>");
		   script.close();
		   return;
	   }
	  
%>