List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:servlet.CinemaControl.java
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 {//from www. j a v a 2 s . c om 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); } }
From source file:org.elissa.server.SimpleStorageServlet.java
public void showResource(HttpServletRequest req, HttpServletResponse res, String stencilsetURL) throws SQLException { res.setContentType("application/xhtml+xml"); PreparedStatement stmt = database.prepareStatement("SELECT ID, Site FROM sites WHERE Name = ?"); stmt.setString(1, this.currentResource); stmt.execute();/*w w w.ja v a 2s .com*/ ResultSet rs = stmt.getResultSet(); String result; if (rs.next()) result = rs.getString(2); else { result = "<div class=\"-oryx-canvas\" id=\"oryx-canvas123\" style=\"width:1200px; height:600px;\">"; result += "<a href=\"" + stencilsetURL + "\" rel=\"oryx-stencilset\"></a>"; result += "<span class=\"oryx-mode\">writeable</span>"; result += "<span class=\"oryx-mode\">fullscreen</span>"; result += "</div>"; } this.template(req, res, result); }
From source file:servlet.CinemaControl.java
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 {/*from w w w . ja v a2s . c o m*/ 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); } }
From source file:com.demandware.vulnapp.challenge.impl.SQLIChallenge.java
/** * Given a statement, pull up to MAX_RESULTS from the results of the query * @param ps prepared and executed statement (this method will not close this) * @return String containing a formatted output string * @throws SQLException//from w w w. j a v a2 s .c om */ private String generateOutputForChallengeQuery(PreparedStatement ps) throws SQLException { StringBuilder sb = new StringBuilder(); ResultSet rs = ps.getResultSet(); int i = 0; while (i < MAX_RESULTS && rs.next()) { try { sb.append("<tr>"); sb.append("<td>"); String name = rs.getString(1); sb.append(name); sb.append("</td>"); sb.append("<td>"); String blurb = rs.getString(2); sb.append(blurb); sb.append("</td>"); String picData = ""; try { Base64InputStream pic = new Base64InputStream(rs.getBinaryStream(3), true); picData = IOUtils.toString(pic); } catch (Exception e) { picData = e.getMessage(); } sb.append("<td>"); sb.append("<img src=\"data:image/jpg;base64,").append(picData).append("\"/>"); sb.append("</td>"); sb.append("</tr>"); } catch (Exception e) { e.printStackTrace(); } } return sb.toString(); }
From source file:servlet.CinemaControl.java
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 {/* w ww . j av a 2s .c om*/ 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); } }
From source file:com.googlecode.psiprobe.controllers.sql.ExecuteSqlController.java
protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request, HttpServletResponse response) throws Exception { String resourceName = ServletRequestUtils.getStringParameter(request, "resource"); String sql = ServletRequestUtils.getStringParameter(request, "sql", null); if (sql == null || sql.equals("") || sql.trim().equals("")) { request.setAttribute("errorMessage", getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.required")); return new ModelAndView(getViewName()); }/* w w w. jav a2s . c om*/ int maxRows = ServletRequestUtils.getIntParameter(request, "maxRows", 0); int rowsPerPage = ServletRequestUtils.getIntParameter(request, "rowsPerPage", 0); int historySize = ServletRequestUtils.getIntParameter(request, "historySize", 0); // store current option values and query history in a session attribute HttpSession sess = request.getSession(); DataSourceTestInfo sessData = (DataSourceTestInfo) sess.getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR); synchronized (sess) { if (sessData == null) { sessData = new DataSourceTestInfo(); sess.setAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR, sessData); } sessData.setMaxRows(maxRows); sessData.setRowsPerPage(rowsPerPage); sessData.setHistorySize(historySize); sessData.addQueryToHistory(sql); } DataSource dataSource = null; try { dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName, getContainerWrapper()); } catch (NamingException e) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } if (dataSource == null) { request.setAttribute("errorMessage", getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName })); } else { List results = null; int rowsAffected = 0; try { // TODO: use Spring's jdbc template? Connection conn = dataSource.getConnection(); try { conn.setAutoCommit(true); PreparedStatement stmt = conn.prepareStatement(sql); try { boolean hasResultSet = stmt.execute(); if (!hasResultSet) { rowsAffected = stmt.getUpdateCount(); } else { results = new ArrayList(); ResultSet rs = stmt.getResultSet(); try { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next() && (maxRows < 0 || results.size() < maxRows)) { Map record = new LinkedHashMap(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String value = rs.getString(i); if (rs.wasNull()) { value = getMessageSourceAccessor() .getMessage("probe.src.dataSourceTest.sql.null"); } else { value = HtmlUtils.htmlEscape(value); } // a work around for IE browsers bug of not displaying // a border around an empty table column if (value.equals("")) { value = " "; } // Pad the keys of columns with existing labels so they are distinct String key = metaData.getColumnLabel(i); while (record.containsKey(key)) { key += " "; } record.put(HtmlUtils.htmlEscape(key), value); } results.add(record); } } finally { rs.close(); } rowsAffected = results.size(); } } finally { stmt.close(); } } finally { conn.close(); } // store the query results in the session attribute in order // to support a result set pagination feature without re-executing the query synchronized (sess) { sessData.setResults(results); } ModelAndView mv = new ModelAndView(getViewName(), "results", results); mv.addObject("rowsAffected", String.valueOf(rowsAffected)); mv.addObject("rowsPerPage", String.valueOf(rowsPerPage)); return mv; } catch (SQLException e) { String message = getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.failure", new Object[] { e.getMessage() }); logger.error(message, e); request.setAttribute("errorMessage", message); } } return new ModelAndView(getViewName()); }
From source file:servlet.MovieControl.java
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 {/*from w w w . j ava2s .c o m*/ 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); } }
From source file:servlet.CinemaControl.java
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 {//www . ja va2 s. c om 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); } }
From source file:servlet.MovieControl.java
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 {/*from w ww . j av a2 s . c o m*/ 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); } }
From source file:ccc.migration.DbUtilsDB.java
/** {@inheritDoc} */ @Override/* w w w .j a v a2 s. c om*/ @SuppressWarnings("unchecked") public <T> T select(final SqlQuery<T> q, final Object... param) { try { log.debug("Query: " + q.getSql()); log.debug("Params: " + Arrays.asList(param)); final PreparedStatement s = _c.prepareStatement(q.getSql()); try { int index = 1; for (final Object o : param) { s.setObject(index, o); index++; } log.debug("Running query."); final boolean resultsReturned = s.execute(); log.debug("Running finished."); if (!resultsReturned) { throw new MigrationException("Query returned no results."); } final ResultSet rs = s.getResultSet(); try { final Object result = q.handle(rs); return (T) result; } catch (final SQLException e) { throw new MigrationException(e); } finally { DbUtils.closeQuietly(rs); } } catch (final SQLException e) { throw new MigrationException(e); } finally { DbUtils.closeQuietly(s); } } catch (final SQLException e) { throw new MigrationException(e); } }