Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package servlet; import java.io.IOException; import java.io.PrintWriter; import javax.servlet.*; import javax.servlet.annotation.WebServlet; import javax.servlet.http.*; import java.sql.*; import java.util.logging.Level; import java.util.logging.Logger; import org.json.*; /** * * @author user */ @WebServlet(name = "CinemaControl", urlPatterns = { "/CinemaControl" }) public class CinemaControl extends HttpServlet { @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String action = request.getParameter("action"); if (action.equals("listSeats")) { this.doListSeats(request, response); } else if (action.equals("listHousesOfCinema")) { this.doListHousesOfCinema(request, response); } else if (action.equals("listSeatsOfHouse")) { this.doListSeatsByMS(request, response); } else if (action.equals("listSeatsByIDs")) { this.doListSeatsByIDs(request, response); } else if (action.equals("listMSsOfCinemas")) { this.doListMSsOfCinemas(request, response); } } protected void doListSeats(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); ServletContext sc = getServletContext(); String db_driver = sc.getInitParameter("db_driver"), db_url = sc.getInitParameter("db_url"), db_user = sc.getInitParameter("db_user"), db_password = sc.getInitParameter("db_password"); String db_q_cinemas = "SELECT * FROM Cinema;"; String db_q_houses = "SELECT * FROM House WHERE cinemaID = ?;"; String db_q_seats = "SELECT * FROM Seat WHERE houseID = ?;"; try { JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("cinemas", jsa0); Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); Statement statmt1 = conn.createStatement(); ResultSet rs1 = statmt1.executeQuery(db_q_cinemas); ResultSetMetaData rsmd1 = rs1.getMetaData(); int numOfColumns1 = rsmd1.getColumnCount(); while (rs1.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns1; i++) { jso1.put(rsmd1.getColumnLabel(i), rs1.getString(i)); } int cinemaID = Integer.parseInt(jso1.getString("cinemaID")); JSONArray jsa1 = new JSONArray(); jso1.put("houses", jsa1); PreparedStatement statmt2 = conn.prepareStatement(db_q_houses); statmt2.setInt(1, cinemaID); if (statmt2.execute()) { ResultSet rs2 = statmt2.getResultSet(); ResultSetMetaData rsmd2 = rs2.getMetaData(); int numOfColumns2 = rsmd2.getColumnCount(); while (rs2.next()) { JSONObject jso2 = new JSONObject(); jsa1.put(jso2); for (int j = 1; j <= numOfColumns2; j++) { jso2.put(rsmd2.getColumnLabel(j), rs2.getString(j)); } int houseID = Integer.parseInt(jso2.getString("houseID")); JSONArray jsa2 = new JSONArray(); jso2.put("seats", jsa2); PreparedStatement statmt3 = conn.prepareStatement(db_q_seats); statmt3.setInt(1, houseID); if (statmt3.execute()) { ResultSet rs3 = statmt3.getResultSet(); ResultSetMetaData rsmd3 = rs3.getMetaData(); int numOfColumns3 = rsmd3.getColumnCount(); while (rs3.next()) { JSONObject jso3 = new JSONObject(); jsa2.put(jso3); for (int k = 1; k <= numOfColumns3; k++) { jso3.put(rsmd3.getColumnLabel(k), rs3.getString(k)); } } } } } } out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } } protected void doListHousesOfCinema(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); ServletContext sc = getServletContext(); String db_driver = sc.getInitParameter("db_driver"), db_url = sc.getInitParameter("db_url"), db_user = sc.getInitParameter("db_user"), db_password = sc.getInitParameter("db_password"); } protected void doListSeatsByMS(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); ServletContext sc = getServletContext(); String db_driver = sc.getInitParameter("db_driver"), db_url = sc.getInitParameter("db_url"), db_user = sc.getInitParameter("db_user"), db_password = sc.getInitParameter("db_password"); String db_q_seat_available = "SELECT DISTINCT s.seatID, s.houseID, s.rowName, s.seatName, s.surcharge, s.state, ms.price" + " FROM Seat s, MovieSession ms" + " WHERE s.seatID NOT IN" + " (SELECT seatID FROM Ticket WHERE state NOT IN ('refunded') AND msID = ?)" + " AND ms.houseID = s.houseID" + " AND ms.msID = ?"; String db_q_seat_booked = "SELECT DISTINCT s.seatID, s.houseID, s.rowName, s.seatName, s.surcharge, s.state, ms.price" + " FROM Seat s, MovieSession ms" + " WHERE s.seatID IN" + " (SELECT seatID FROM Ticket WHERE state NOT IN ('refunded') AND msID = ?)" + " AND ms.houseID = s.houseID" + " AND ms.msID = ?"; int msID = Integer.parseInt(request.getParameter("msID")); try { JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("seats", jsa0); Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); // available seat PreparedStatement statmt1 = conn.prepareStatement(db_q_seat_available); statmt1.setInt(1, msID); statmt1.setInt(2, msID); if (statmt1.execute()) { ResultSet rs = statmt1.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int numOfColumns = rsmd.getColumnCount(); while (rs.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns; i++) { jso1.put(rsmd.getColumnName(i), rs.getString(i)); } } } // unavailable seat PreparedStatement statmt2 = conn.prepareStatement(db_q_seat_booked); statmt2.setInt(1, msID); statmt2.setInt(2, msID); if (statmt2.execute()) { ResultSet rs = statmt2.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int numOfColumns = rsmd.getColumnCount(); while (rs.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns; i++) { jso1.put(rsmd.getColumnName(i), rs.getString(i)); jso1.put("state", "unavailable"); } } } conn.close(); out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } } protected void doListSeatsByIDs(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); ServletContext sc = getServletContext(); String db_driver = sc.getInitParameter("db_driver"), db_url = sc.getInitParameter("db_url"), db_user = sc.getInitParameter("db_user"), db_password = sc.getInitParameter("db_password"); String req_seatIDs = request.getParameter("seatID"); String db_q_seat = "SELECT DISTINCT s.seatID, s.houseID, s.rowName, s.seatName, s.surcharge, s.state, ms.price" + " FROM Seat s, MovieSession ms" + " WHERE s.seatID IN" + " (" + req_seatIDs + ")" + " AND ms.houseID = s.houseID" + " AND ms.msID = ?"; int msID = Integer.parseInt(request.getParameter("msID")); try { JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("seats", jsa0); Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); // available seat PreparedStatement statmt1 = conn.prepareStatement(db_q_seat); statmt1.setInt(1, msID); if (statmt1.execute()) { ResultSet rs = statmt1.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int numOfColumns = rsmd.getColumnCount(); while (rs.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns; i++) { jso1.put(rsmd.getColumnName(i), rs.getString(i)); } } } conn.close(); out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } } protected void doListMSsOfCinemas(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); ServletContext sc = getServletContext(); String db_driver = sc.getInitParameter("db_driver"), db_url = sc.getInitParameter("db_url"), db_user = sc.getInitParameter("db_user"), db_password = sc.getInitParameter("db_password"); String db_q_cinemas = "SELECT * FROM Cinema;"; String db_q_houses = "SELECT * FROM House WHERE cinemaID = ?;"; String db_q_movieSessions = "SELECT * FROM MovieSession WHERE houseID = ?;"; String db_q_movies = "SELECT * FROM Movie WHERE movieID = ?;"; String db_q1_0 = "SELECT categoryName" + " FROM category c, movie_category mc, movie m" + " WHERE c.categoryID = mc.categoryID" + " AND m.movieID = mc.movieID" + " AND m.movieID = ?;"; String db_q1_1 = "SELECT actorName" + " FROM actor a, movie_actor ma, movie m" + " WHERE a.actorID = ma.actorID" + " AND ma.movieID = m.movieID" + " AND m.movieID = ?;"; String db_q1_2 = "SELECT image FROM movie m, movieImage mi" + " WHERE m.movieID = mi.movieID" + " AND m.movieID = ?;"; String db_q1_3 = "SELECT video" + " FROM movie m, movieVideo mv" + " WHERE m.movieID = mv.movieID" + " AND m.movieID = ?;"; try { JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("cinemas", jsa0); Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); Statement statmt1 = conn.createStatement(); ResultSet rs1 = statmt1.executeQuery(db_q_cinemas); ResultSetMetaData rsmd1 = rs1.getMetaData(); int numOfColumns1 = rsmd1.getColumnCount(); while (rs1.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns1; i++) { jso1.put(rsmd1.getColumnLabel(i), rs1.getString(i)); } int cinemaID = Integer.parseInt(jso1.getString("cinemaID")); JSONArray jsa1 = new JSONArray(); jso1.put("houses", jsa1); PreparedStatement statmt2 = conn.prepareStatement(db_q_houses); statmt2.setInt(1, cinemaID); if (statmt2.execute()) { ResultSet rs2 = statmt2.getResultSet(); ResultSetMetaData rsmd2 = rs2.getMetaData(); int numOfColumns2 = rsmd2.getColumnCount(); while (rs2.next()) { JSONObject jso2 = new JSONObject(); jsa1.put(jso2); for (int j = 1; j <= numOfColumns2; j++) { jso2.put(rsmd2.getColumnLabel(j), rs2.getString(j)); } int houseID = Integer.parseInt(jso2.getString("houseID")); JSONArray jsa2 = new JSONArray(); jso2.put("movieSessions", jsa2); PreparedStatement statmt3 = conn.prepareStatement(db_q_movieSessions); statmt3.setInt(1, houseID); if (statmt3.execute()) { ResultSet rs3 = statmt3.getResultSet(); ResultSetMetaData rsmd3 = rs3.getMetaData(); int numOfColumns3 = rsmd3.getColumnCount(); while (rs3.next()) { JSONObject jso3 = new JSONObject(); jsa2.put(jso3); for (int k = 1; k <= numOfColumns3; k++) { jso3.put(rsmd3.getColumnLabel(k), rs3.getString(k)); } String playtime = jso3.getString("playtime"); jso3.put("date", playtime.substring(0, 10)); jso3.put("time", playtime.subSequence(11, 16)); int movieID = Integer.parseInt(jso3.getString("movieID")); JSONArray jsa3 = new JSONArray(); jso3.put("movies", jsa3); PreparedStatement statmt4 = conn.prepareStatement(db_q_movies); statmt4.setInt(1, movieID); if (statmt4.execute()) { ResultSet rs4 = statmt4.getResultSet(); ResultSetMetaData rsmd4 = rs4.getMetaData(); int numOfColumns4 = rsmd4.getColumnCount(); while (rs4.next()) { JSONObject jso4 = new JSONObject(); jsa3.put(jso4); for (int l = 1; l <= numOfColumns4; l++) { jso4.put(rsmd4.getColumnLabel(l), rs4.getString(l)); } //add categories JSONArray jsa1_0 = new JSONArray(); jso4.put("categories", jsa1_0); PreparedStatement statmt1_0 = conn.prepareStatement(db_q1_0); statmt1_0.setInt(1, movieID); if (statmt1_0.execute()) { ResultSet rs1_0 = statmt1_0.getResultSet(); while (rs1_0.next()) { jsa1_0.put(rs1_0.getString(1)); } } //add actors JSONArray jsa1_1 = new JSONArray(); jso4.put("actors", jsa1_1); PreparedStatement statmt1_1 = conn.prepareStatement(db_q1_1); statmt1_1.setInt(1, movieID); if (statmt1_1.execute()) { ResultSet rs1_1 = statmt1_1.getResultSet(); while (rs1_1.next()) { jsa1_1.put(rs1_1.getString(1)); } } //add images JSONArray jsa1_2 = new JSONArray(); jso4.put("movieImages", jsa1_2); PreparedStatement statmt1_2 = conn.prepareStatement(db_q1_2); statmt1_2.setInt(1, movieID); if (statmt1_2.execute()) { ResultSet rs1_2 = statmt1_2.getResultSet(); while (rs1_2.next()) { jsa1_2.put(rs1_2.getString(1)); } } //add videos JSONArray jsa1_3 = new JSONArray(); jso4.put("movieVideos", jsa1_3); PreparedStatement statmt1_3 = conn.prepareStatement(db_q1_3); statmt1_3.setInt(1, movieID); if (statmt1_3.execute()) { ResultSet rs1_3 = statmt1_3.getResultSet(); while (rs1_3.next()) { jsa1_3.put(rs1_3.getString(1)); } } } } } } } } } out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(CinemaControl.class.getName()).log(Level.SEVERE, null, ex); } } }