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 java.sql.*; import java.util.logging.Level; import java.util.logging.Logger; import javax.servlet.ServletContext; 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 org.json.*; @WebServlet(name = "MovieControl", urlPatterns = { "/MovieControl" }) public class MovieControl extends HttpServlet { @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String action = request.getParameter("action"); if (action.equals("listMovies")) { this.doListMovies(request, response); } else if (action.equals("listMovieByID")) { this.doListMovieByID(request, response); } } protected void doListMovies(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_q1 = "SELECT * FROM Movie;"; 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 = ?;"; String db_q2 = "SELECT DISTINCT c.cinemaID, c.cinemaName, c.cinemaAddress, c.numOfVote, c.rank, c.image1" + " FROM Cinema c, Movie m, House h, MovieSession ms" + " WHERE h.cinemaID = c.cinemaID" + " AND h.houseID = ms.houseID" + " AND ms.movieID = m.movieID" + " AND ms.movieID = ?;"; String db_q3 = "SELECT DISTINCT h.houseID, h.cinemaID, h.seatNums" + " FROM House h, Movie m, MovieSession ms" + " WHERE h.cinemaID = ?" + " AND h.houseID = ms.houseID" + " AND ms.movieID = m.movieID" + " AND m.movieID = ?;"; String db_q4 = "SELECT ms.*" + " FROM movieSession ms, house h" + " WHERE ms.houseID = h.houseID" + " AND h.houseID = ?" + " AND ms.movieID = ?;"; try { Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); Statement statmt1 = conn.createStatement(); ResultSet rs1 = statmt1.executeQuery(db_q1); ResultSetMetaData rsmd1 = rs1.getMetaData(); int numOfColumns1 = rsmd1.getColumnCount(); JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("movies", jsa0); while (rs1.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns1; i++) { //add movie.* jso1.put(rsmd1.getColumnName(i), rs1.getString(i)); } int movieID = rs1.getInt("movieID"); //add categories JSONArray jsa1_0 = new JSONArray(); jso1.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(); jso1.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(); jso1.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(); jso1.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)); } } //add cinemas JSONArray jsa1 = new JSONArray(); jso1.put("cinemas", jsa1); PreparedStatement statmt2 = conn.prepareStatement(db_q2); statmt2.setInt(1, movieID); 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++) { //add cinema.* jso2.put(rsmd2.getColumnName(j), rs2.getString(j)); } //add houses JSONArray jsa2 = new JSONArray(); jso2.put("houses", jsa2); PreparedStatement statmt3 = conn.prepareStatement(db_q3); int cinemaID = rs2.getInt("cinemaID"); statmt3.setInt(1, cinemaID); statmt3.setInt(2, movieID); if (statmt3.execute()) { ResultSet rs3 = statmt3.executeQuery(); 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++) { //add house.* jso3.put(rsmd3.getColumnName(k), rs3.getString(k)); } //add moviesessions JSONArray jsa3 = new JSONArray(); jso3.put("movieSessions", jsa3); PreparedStatement statmt4 = conn.prepareStatement(db_q4); int houseID = rs3.getInt("houseID"); statmt4.setInt(1, houseID); statmt4.setInt(2, 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++) { //add moviesession.* jso4.put(rsmd4.getColumnName(l), rs4.getString(l)); } // format playtime String playtime = jso4.getString("playtime"); jso4.put("playdate", playtime.substring(0, 10)); jso4.put("time", playtime.substring(11, 16)); } rs4.close(); } } rs3.close(); } } rs2.close(); } // format value jso1.put("releaseDate", rs1.getString("releaseDate").substring(0, 10)); } rs1.close(); out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(MovieControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(MovieControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(MovieControl.class.getName()).log(Level.SEVERE, null, ex); } } protected void doListMovieByID(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_q1 = "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 = ?;"; String db_q2 = "SELECT DISTINCT c.cinemaID, c.cinemaName, c.cinemaAddress, c.numOfVote, c.rank, c.image1" + " FROM Cinema c, Movie m, House h, MovieSession ms" + " WHERE h.cinemaID = c.cinemaID" + " AND h.houseID = ms.houseID" + " AND ms.movieID = m.movieID" + " AND ms.movieID = ?;"; String db_q3 = "SELECT DISTINCT h.houseID, h.cinemaID, h.seatNums" + " FROM House h, Movie m, MovieSession ms" + " WHERE h.cinemaID = ?" + " AND h.houseID = ms.houseID" + " AND ms.movieID = m.movieID" + " AND m.movieID = ?;"; String db_q4 = "SELECT ms.*" + " FROM movieSession ms, house h" + " WHERE ms.houseID = h.houseID" + " AND h.houseID = ?" + " AND ms.movieID = ?;"; int movieID = Integer.parseInt(request.getParameter("movieID")); try { Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); PreparedStatement statmt1 = conn.prepareStatement(db_q1); statmt1.setInt(1, movieID); statmt1.execute(); ResultSet rs1 = statmt1.getResultSet(); ResultSetMetaData rsmd1 = rs1.getMetaData(); int numOfColumns1 = rsmd1.getColumnCount(); JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); jso0.put("movies", jsa0); if (rs1.next()) { JSONObject jso1 = new JSONObject(); jsa0.put(jso1); for (int i = 1; i <= numOfColumns1; i++) { //add movie.* jso1.put(rsmd1.getColumnName(i), rs1.getString(i)); } //add categories JSONArray jsa1_0 = new JSONArray(); jso1.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(); jso1.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(); jso1.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(); jso1.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)); } } //add cinemas JSONArray jsa1 = new JSONArray(); jso1.put("cinemas", jsa1); PreparedStatement statmt2 = conn.prepareStatement(db_q2); statmt2.setInt(1, movieID); 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++) { //add cinema.* jso2.put(rsmd2.getColumnName(j), rs2.getString(j)); } //add houses JSONArray jsa2 = new JSONArray(); jso2.put("houses", jsa2); PreparedStatement statmt3 = conn.prepareStatement(db_q3); int cinemaID = rs2.getInt("cinemaID"); statmt3.setInt(1, cinemaID); statmt3.setInt(2, movieID); if (statmt3.execute()) { ResultSet rs3 = statmt3.executeQuery(); 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++) { //add house.* jso3.put(rsmd3.getColumnName(k), rs3.getString(k)); } //add moviesessions JSONArray jsa3 = new JSONArray(); jso3.put("movieSessions", jsa3); PreparedStatement statmt4 = conn.prepareStatement(db_q4); int houseID = rs3.getInt("houseID"); statmt4.setInt(1, houseID); statmt4.setInt(2, 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++) { //add moviesession.* jso4.put(rsmd4.getColumnName(l), rs4.getString(l)); } // format playtime String playtime = jso4.getString("playtime"); jso4.put("playdate", playtime.substring(0, 10)); jso4.put("time", playtime.substring(11, 16)); } rs4.close(); } } rs3.close(); } } rs2.close(); } // format value jso1.put("releaseDate", rs1.getString("releaseDate").substring(0, 10)); } rs1.close(); out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(MovieControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(MovieControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(MovieControl.class.getName()).log(Level.SEVERE, null, ex); } } }