PK

FK

• movies 영화 정보

• users 사용자 테이블

• reviews 사용자 영화 후기 테이블

• theaters 극장 정보

• theater_details 극장 상세 정보

• reserves 예약자 영화 등록 테이블

Movie SQL토글을 눌러서 SQL을 확인할 수 있습니다.

Movie SQL

-- INSERT
INSERT INTO theaters VALUES (1, "롯데시네마 월드타워", "잠실 월드타워");
INSERT INTO theater_details(theater_no, movie_no, theater_detail_standard_date, theater_detail_time, theater_detail_remain_seats, theater_detail_seats) 
VALUES (1, 1, STR_TO_DATE('20050208', '%Y%m%d') , "21:00", 65, 65);
INSERT INTO theater_details(theater_no, movie_no, theater_detail_standard_date, theater_detail_time, theater_detail_remain_seats, theater_detail_seats) 
VALUES (1, 1, STR_TO_DATE('20050208', '%Y%m%d') , "6:00", 65, 65);
INSERT INTO users(user_id, user_email, user_name, user_password) VALUES('test', '[email protected]', '테스트', 'test');
INSERT INTO reserves(user_no, movie_no, reserve_time, reserve_enter_count) VALUES(1, 1, STR_TO_DATE("1200", '%H%i%s'), 5);

-- UPDATE
UPDATE theater_details SET theater_detail_remain_seats = 5 WHERE movie_no = 1;

test 용

INSERT INTO reviews(movie_no, user_no, review_title, review_content, review_rate) VALUES(1,1,'.','토르 4편 너무 재밌어요! 가족끼리 보기에도 딱이네요 ㅎㅎ 마이티 토르의 활약도 멋졌습니다!',4);
INSERT INTO reviews(movie_no, user_no, review_title, review_content, review_rate) VALUES(2,1,'.','탑건 2022 최고의 영화..! 탑건 1편을 보고 시청하세요!!',5);
INSERT INTO reviews(movie_no, user_no, review_title, review_content, review_rate) VALUES(3,1,'.','네게 미결로 남고 싶은 내 삶의 흔적',4);
INSERT INTO reviews(movie_no, user_no, review_title, review_content, review_rate) VALUES(5,1,'.','코난 극장판 역시 킬링타임용으로 딱..!',3);

ReviewController

package com.example.lottemoviereservation.controller;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.example.lottemoviereservation.dao.MovieDao;
import com.example.lottemoviereservation.dao.ReviewDao;
import com.example.lottemoviereservation.dao.UserDao;
import com.example.lottemoviereservation.dto.MovieTitleDto;
import com.example.lottemoviereservation.dto.ReviewDto;
import com.example.lottemoviereservation.dto.UserDto;
import com.example.lottemoviereservation.dto.UserNameDto;
import org.json.JSONObject;

import java.util.List;

/**
 * Servlet implementation class FindIdServlet
 */
@WebServlet("/review")
public class ReviewController extends HttpServlet {

    private final String reviewTitle = ".";

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doProcess(req, resp);
    }

    public void doProcess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setContentType("text/html;charset=UTF-8");

        request.setCharacterEncoding("utf-8");

        HttpSession session = request.getSession(true);
        Object objLoginCheck = session.getAttribute("login");
//        if (objLoginCheck == null) {
//            String msg = "NO";
//            response.sendRedirect("message.jsp?msg=" + msg);
//        }
        UserDto user = (UserDto) objLoginCheck;

        String param = request.getParameter("param");

//        int userNo = 1;
//        String reviewContent = request.getParameter("reviewContent");
//        int rating = Integer.parseInt(request.getParameter("rating"));

        ReviewDao reviewDao = ReviewDao.getInstance();
        MovieDao movieDao = MovieDao.getInstance();
        UserDao userDao = UserDao.getInstance();

        if (param.equals("movielist")) {
            int movieNo = Integer.parseInt(request.getParameter("movieno"));
            int page = Integer.parseInt(request.getParameter("page"));

            JSONObject obj = new JSONObject();

            List<ReviewDto> reviewList = reviewDao.getReviewPageListByMovieNo(movieNo, page);
            List<UserNameDto> userNameList = userDao.getUserNameByReview(reviewList);

            int reviewCount = reviewDao.getReviewCountByMovieNo(movieNo);

            if (!reviewList.isEmpty()) {
                obj.put("reviewList", reviewList);
                obj.put("list", reviewList);
                obj.put("userNameList", userNameList);
                obj.put("reviewCount", reviewCount);
            }
            response.setContentType("application/x-json; charset=utf-8;");
            response.getWriter().println(obj);

        } else if (param.equals("insert")) {
            int movieNo = Integer.parseInt(request.getParameter("movieno"));

            int rating = Integer.parseInt(request.getParameter("rating"));
            String reviewContent = request.getParameter("reviewContent");

            ReviewDto reviewDto = new ReviewDto(movieNo, user.getUserNo(), reviewTitle, reviewContent, rating);
            reviewDao.writeReview(reviewDto);
        } else if (param.equals("update")) {

            int reviewNo = Integer.parseInt(request.getParameter("reviewno"));

            String updateContent = request.getParameter("updateContent");

            reviewDao.updateReview(reviewNo, updateContent);

        } else if (param.equals("delete")) {

            int reviewNo = Integer.parseInt(request.getParameter("reviewno"));

            reviewDao.deleteReview(reviewNo, user.getUserNo());
        }else if (param.equals("userlist")) {
            int userNo = Integer.parseInt(request.getParameter("userno"));
            int page = Integer.parseInt(request.getParameter("page"));

            JSONObject obj = new JSONObject();

            List<ReviewDto> reviewList = reviewDao.getReviewPageListByUserNo(userNo, page);
            List<MovieTitleDto> movieTitleList = movieDao.getMovieTitleByReview(reviewList);

            int reviewCount = reviewDao.getReviewCountByUserNo(userNo);

            if (!reviewList.isEmpty()) {
                obj.put("reviewList", reviewList);
                System.out.println("reviewList");
                System.out.println(reviewList);

                obj.put("list", reviewList);

                System.out.println(reviewList);

                obj.put("movieTitleList", movieTitleList);
                obj.put("reviewCount", reviewCount);
            }
            response.setContentType("application/x-json; charset=utf-8;");
            response.getWriter().println(obj);

        }

    }
}

ReviewDao

package com.example.lottemoviereservation.dao;

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

import com.example.lottemoviereservation.db.DBClose;
import com.example.lottemoviereservation.db.DBConnection;
import com.example.lottemoviereservation.dto.ReviewDto;
//reviews rate int ....

public class ReviewDao {
    private static ReviewDao dao = new ReviewDao();

    private ReviewDao() {

    }

    public static ReviewDao getInstance() {
        return dao;
    }

    public List<ReviewDto> getReviewList() {
        //(int movieNo, int userNo, String reviewTitle, String reviewContent, double reviewRate
        String sql = " select review_no, movie_no, user_no, review_title, review_content, review_rate "
                + " from reviews "
                + " order by review_no ";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        List<ReviewDto> list = new ArrayList<>();

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();

            while (rs.next()) {
                ReviewDto dto = new ReviewDto(
                        rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getInt(6)
                );
                list.add(dto);
            }

        } catch (SQLException e) {
            System.out.println("Get reviewList fail");
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }
        return list;
    }

    public List<ReviewDto> getReviewListByMovieNo(int movieNo) {
        //(int movieNo, int userNo, String reviewTitle, String reviewContent, double reviewRate
        String sql = " select review_no, movie_no, user_no, review_title, review_content, review_rate "
                + " from reviews "
                + " where movie_no=? "
                + " order by review_no ";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        List<ReviewDto> list = new ArrayList<>();

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, movieNo);
            rs = psmt.executeQuery();

            while (rs.next()) {
                ReviewDto dto = new ReviewDto(
                        rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getInt(6)
                );
                list.add(dto);
            }

        } catch (SQLException e) {
            System.out.println("Get reviewList fail");
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }
        return list;
    }

    public List<ReviewDto> getReviewPageListByMovieNo(int movieNo, int page) {
        //(int movieNo, int userNo, String reviewTitle, String reviewContent, double reviewRate
        String sql = " select review_no, movie_no, user_no, review_title, review_content, review_rate "
                + " from ( "
                + " select row_number()over(order by review_no desc) as rnum, review_no, "
                + " movie_no, user_no, review_title, review_content, review_rate "
                + " from reviews "
                + " where movie_no=? "
                + " order by review_no desc ) a ";
        sql += " where rnum between ? and ? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        List<ReviewDto> list = new ArrayList<>();

        int startPage = 1;
        int endPage = page * 5;

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, movieNo);
            psmt.setInt(2, startPage);
            psmt.setInt(3, endPage);
            rs = psmt.executeQuery();

            while (rs.next()) {
                ReviewDto dto = new ReviewDto(
                        rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getInt(6)
                );
                list.add(dto);
            }

        } catch (SQLException e) {
            System.out.println("Get reviewList fail");
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }
        return list;
    }

    public List<ReviewDto> getReviewPageListByUserNo(int userNo, int page) {
        //(int movieNo, int userNo, String reviewTitle, String reviewContent, double reviewRate
        String sql = " select review_no, movie_no, user_no, review_title, review_content, review_rate "
                + " from ( "
                + " select row_number()over(order by review_no desc) as rnum, review_no, "
                + " movie_no, user_no, review_title, review_content, review_rate "
                + " from reviews "
                + " where user_no=? "
                + " order by review_no desc ) a ";
        sql += " where rnum between ? and ? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        List<ReviewDto> list = new ArrayList<>();

        int startPage = 1;
        int endPage = page * 5;

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, userNo);
            psmt.setInt(2, startPage);
            psmt.setInt(3, endPage);
            rs = psmt.executeQuery();

            while (rs.next()) {
                ReviewDto dto = new ReviewDto(
                        rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getInt(6)
                );
                list.add(dto);
            }

        } catch (SQLException e) {
            System.out.println("Get reviewList fail");
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }
        return list;
    }

    public boolean writeReview(ReviewDto dto) {
        //(int movieNo, int userNo, String reviewTitle, String reviewContent, double reviewRate
        String sql = " insert into reviews (movie_no, user_no, review_title, review_content, review_rate) "
                + " values(?, ?, ?, ?, ?) ";

        Connection conn = null;
        PreparedStatement psmt = null;

        int count = 0;
        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);

            psmt.setInt(1, dto.getMovieNo());
            psmt.setInt(2, dto.getUserNo());
            psmt.setString(3, dto.getReviewTitle());
            psmt.setString(4, dto.getReviewContent());
            psmt.setInt(5, dto.getReviewRate());

            count = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println("Write Review fail");
            DBClose.close(conn, psmt, null);
        }
        return count > 0;
    }

    public ReviewDto getReview(int review_no) {
        String sql = " select review_no, movie_no, user_no, review_title, review_content, review_rate "
                + " from reviews "
                + " where review_no=? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        ReviewDto dto = null;

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, review_no);

            rs = psmt.executeQuery();
            if (rs.next()) {
                int n = 1;
                dto = new ReviewDto(
                        rs.getInt(n++),
                        rs.getInt(n++),
                        rs.getInt(n++),
                        rs.getString(n++),
                        rs.getString(n++),
                        rs.getInt(n++)
                );
            }
        } catch (SQLException e) {
            System.out.println("GET REVIEW FAIL ! ! ! ");
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }
        return dto;
    }

    public boolean updateReview(int review_no, String content) {
        String sql = " update reviews "
                + " set review_title='.', review_content=? "
                + " where review_no=? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        int count = 0;

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, content);
            psmt.setInt(2, review_no);

            count = psmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, null);
        }
        return count > 0;
    }

    public boolean deleteReview(int reviewNo, int userNo) {
        String sql = " delete from reviews "
                + " where review_no=? and user_no=? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        int count = 0;

        try {
            conn = DBConnection.getConnection();
            psmt = conn.prepareStatement(sql);

            psmt.setInt(1, reviewNo);
            psmt.setInt(2, userNo);

            count = psmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("FAIL DELETE REVIEW");
            e.printStackTrace();
        }

        return count > 0;
    }
    public int getReviewCountByMovieNo(int movieNo){
        String sql = " select count(*) from reviews where movie_no=? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        int len = 0;

        try {
            conn = DBConnection.getConnection();

            psmt = conn.prepareStatement(sql);
            psmt.setInt(1,movieNo);

            rs = psmt.executeQuery();
            if (rs.next()) {
                len = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }

        return len;
    }

    public int getReviewCountByUserNo(int userNo){
        String sql = " select count(*) from reviews where user_no=? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        int len = 0;

        try {
            conn = DBConnection.getConnection();

            psmt = conn.prepareStatement(sql);
            psmt.setInt(1,userNo);

            rs = psmt.executeQuery();
            if (rs.next()) {
                len = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBClose.close(conn, psmt, rs);
        }

        return len;
    }
}