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;
}
%>'JAVA > JSP' 카테고리의 다른 글
| JSP 강의평가 웹 사이트 - 기초 XSS 시큐어 코딩 (0) | 2020.05.11 |
|---|---|
| JSP 강의평가 웹 사이트 - 평가 검색 및 출력 구현 (0) | 2020.05.10 |
| JSP로 강의평가 웹 사이트 - 평가 등록 및 신고 구현 (0) | 2020.05.09 |
| JSP로 강의평가 웹 사이트 - 로그인 및 로그아웃 구현하기 (0) | 2020.05.08 |
| JSP 강의평가 웹사이트 회원가입 및 이메일 인증 (0) | 2020.05.07 |
