List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:azkaban.jobtype.ReportalTeradataRunner.java
@Override protected void runReportal() throws Exception { System.out.println("Reportal Teradata: Setting up Teradata"); List<Exception> exceptions = new ArrayList<Exception>(); Class.forName("com.teradata.jdbc.TeraDriver"); String connectionString = props.getString("reportal.teradata.connection.string", null); String user = props.getString("reportal.teradata.username", null); String pass = props.getString("reportal.teradata.password", null); if (user == null) { System.out.println("Reportal Teradata: Configuration incomplete"); throw new RuntimeException("The reportal.teradata.username variable was not defined."); }// www . ja v a2s .c o m if (pass == null) { System.out.println("Reportal Teradata: Configuration incomplete"); throw new RuntimeException("The reportal.teradata.password variable was not defined."); } DataSource teraDataSource = new TeradataDataSource(connectionString, user, pass); Connection conn = teraDataSource.getConnection(); String sqlQueries[] = cleanAndGetQueries(jobQuery, proxyUser); int numQueries = sqlQueries.length; for (int i = 0; i < numQueries; i++) { try { String queryLine = sqlQueries[i]; // Only store results from the last statement if (i == numQueries - 1) { PreparedStatement stmt = prepareStatement(conn, queryLine); stmt.execute(); ResultSet rs = stmt.getResultSet(); outputQueryResult(rs, outputStream); stmt.close(); } else { try { PreparedStatement stmt = prepareStatement(conn, queryLine); stmt.execute(); stmt.close(); } catch (NullPointerException e) { // An empty query (or comment) throws a NPE in JDBC. Yay! System.err.println( "Caught NPE in execute call because report has a NOOP query: " + queryLine); } } } catch (Exception e) { // Catch and continue. Delay exception throwing until we've run all queries in this task. System.out.println("Reportal Teradata: SQL query failed. " + e.getMessage()); e.printStackTrace(); exceptions.add(e); } } if (exceptions.size() > 0) { throw new CompositeException(exceptions); } System.out.println("Reportal Teradata: Ended successfully"); }
From source file:com.haulmont.cuba.core.app.UniqueNumbers.java
protected Object executeScript(String domain, String sqlScript) { EntityManager em = persistence.getEntityManager(getDataStore(domain)); StrTokenizer tokenizer = new StrTokenizer(sqlScript, SequenceSupport.SQL_DELIMITER); Object value = null;/* w w w .ja va 2s .c om*/ Connection connection = em.getConnection(); while (tokenizer.hasNext()) { String sql = tokenizer.nextToken(); try { PreparedStatement statement = connection.prepareStatement(sql); try { if (statement.execute()) { ResultSet rs = statement.getResultSet(); if (rs.next()) value = rs.getLong(1); } } finally { DbUtils.closeQuietly(statement); } } catch (SQLException e) { throw new IllegalStateException("Error executing SQL for getting next number", e); } } return value; }
From source file:edu.umd.cs.psl.database.rdbms.RDBMSDataStoreMetadata.java
public int getMaxPartition() { int max = 0;/*from w ww. ja v a 2 s .co m*/ try { PreparedStatement stmt = conn.prepareStatement("SELECT MAX(CAST(value as INT)) from " + mdTableName + " WHERE namespace = 'Partition' AND keytype = 'name'"); stmt.execute(); ResultSet rs = stmt.getResultSet(); if (rs.next()) { max = Integer.parseInt(rs.getString(1)); } } catch (Exception e) { log.error("Could not get max partition - " + e.getMessage()); return 0; } return max; }
From source file:org.castor.cpa.test.test29.TestDependentOrder.java
public void testRun() throws PersistenceException { _db.begin();// w ww .ja va2 s . c om LOG.debug("Build master object and its dependent objects"); // no ids needed, they come from the key-gen DependMaster master = new DependMaster(); Depend1 depend1 = new Depend1(); master.setDepend1(depend1); Depend2 depend2 = new Depend2(); master.addDepend2(depend2); LOG.debug("Create object tree in db"); _db.create(master); _db.commit(); LOG.debug("depend1_id after creation : " + master.getDepend1().getId()); _db.begin(); try { LOG.debug("read depend1_id from db"); PreparedStatement pstmt = _db.getJdbcConnection() .prepareStatement("select depend1_id from test29_depend_master where id=?"); LOG.debug("master id: " + master.getId()); pstmt.setInt(1, master.getId()); pstmt.execute(); ResultSet result = pstmt.getResultSet(); if (!result.next()) { LOG.error("Master object not created"); fail("Master object not created"); } LOG.debug("depend1_id in db : " + result.getInt("depend1_id")); if (result.getInt("depend1_id") == 0) { LOG.error("Depend1 object not linked to Master object"); fail("Depend1 object not linked to Master object"); } } catch (SQLException e) { LOG.error("Exception when checking master object row", e); fail("Exception when checking master object row: " + e); } _db.commit(); // test for bug 973 Dependent objects deletion order problem try { LOG.debug("Deleting master object"); _db.begin(); master = _db.load(DependMaster.class, new Integer(master.getId())); _db.remove(master); _db.commit(); } catch (Exception e) { LOG.error("Exception thrown", e); fail("Exception thrown " + e); } }
From source file:servlet.CustomerControl.java
protected void doListTickets(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); JSONObject jso0 = new JSONObject(); JSONArray jsa0 = new JSONArray(); try {// w w w. ja v a 2s. c o m jso0.put("tickets", jsa0); } catch (JSONException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } User user = (User) session.getAttribute("user"); int userID; int userCredit = 0; int userLP = 0; if (user != null) { userID = user.getUserID(); userCredit = user.getCredit(); userLP = user.getTradePoint(); } else { out.println(jso0.toString()); return; } 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"), db_q_tickets = "SELECT DISTINCT t.ticketID, t.state, s.rowName, s.seatName," + " (ms.price + s.surcharge) AS 'price', ms.playtime, m.*, h.houseName," + " c.cinemaName, c.cinemaDistrict, c.cinemaAddress, c.tel, c.image1" + " FROM Ticket t, MovieSession ms, Movie m, Seat s, House h, Cinema c" + " WHERE t.msID = ms.msID" + " AND ms.houseID = h.houseID" + " AND h.cinemaID = c.cinemaID" + " AND h.houseID = s.houseID" + " AND t.seatID = s.seatID" + " AND ms.movieID = m.movieID" + " AND t.userID = ?;"; try { Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); PreparedStatement statmt = conn.prepareStatement(db_q_tickets); statmt.setInt(1, userID); if (statmt.execute()) { ResultSet rs = statmt.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.getColumnLabel(i), rs.getString(i)); } String playtime = jso1.getString("playtime"); jso1.put("date", playtime.substring(0, 10)); jso1.put("time", playtime.subSequence(11, 16)); } } conn.close(); out.println(jso0.toString()); } catch (ClassNotFoundException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } catch (JSONException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:edu.umd.cs.psl.database.rdbms.RDBMSDataStoreMetadata.java
public Map<String, String> getAllValuesByType(String mdTableName, String space, String type) { Map<String, String> vals = null; try {/*from w ww .j av a 2s . c o m*/ PreparedStatement stmt = conn.prepareStatement( "SELECT (key,value) from " + mdTableName + " WHERE namespace = ? AND keytype = ?"); stmt.setString(1, space); stmt.setString(2, type); stmt.execute(); ResultSet rs = stmt.getResultSet(); vals = new HashMap<String, String>(); while (rs.next()) { vals.put(rs.getString(1), rs.getString(2)); } } catch (Exception e) { log.error("Error getting all values for type " + type + e.getMessage()); } return vals; }
From source file:edu.umd.cs.psl.database.rdbms.RDBMSDataStoreMetadata.java
protected String getValue(String mdTableName, String space, String type, String key) { try {/*from w w w.j a v a 2 s. co m*/ PreparedStatement stmt = conn.prepareStatement( "SELECT value from " + mdTableName + " WHERE namespace = ? AND keytype = ? AND key = ?"); stmt.setString(1, space); stmt.setString(2, type); stmt.setString(3, key); stmt.execute(); ResultSet rs = stmt.getResultSet(); if (rs.next()) { return rs.getString(1); } } catch (Exception e) { log.info("Error getting value for key " + key + " - " + e.getMessage()); return null; } return null; }
From source file:servlet.CustomerControl.java
protected void doPurchaseTicket(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); String req_msID = request.getParameter("msID"); String req_seatIDs = request.getParameter("seatID"); int msID = Integer.parseInt(req_msID); User user = (User) session.getAttribute("user"); int userID;/*from w ww .j ava 2 s . c o m*/ int userCredit = 0; int userLP = 0; if (user != null) { userID = user.getUserID(); userCredit = user.getCredit(); userLP = user.getTradePoint(); } else { out.println("require login"); return; } 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_chk_credit = "SELECT SUM(s.surcharge + ms.price)" + " FROM Seat s, House h, MovieSession ms" + " WHERE s.houseID = h.houseID" + " AND h.houseID = ms.houseID" + " AND ms.msID = ?" + " AND s.seatID IN (" + req_seatIDs + ");"; String db_q_update_credit = "UPDATE \"User\" SET credit = credit - ?, tradePoint = tradePoint + ? WHERE userID = ?;"; String db_q_update_loyaltyPoints = "UPDATE \"User\" SET tradePoint = tradePoint - ? WHERE userID = ?;"; String db_q_check = "SELECT t.* FROM Ticket t WHERE t.userID = ? AND t.msID = ? AND t.seatID = ?;"; String db_q_insert = "INSERT INTO Ticket (\"userID\", \"msID\", \"seatID\", \"state\") VALUES (?, ?, ?, 'purchased');"; String db_q_insert_2 = "INSERT INTO Ticket (\"userID\", \"msID\", \"seatID\", \"state\") VALUES (?, ?, ?, 'redeemed');"; String db_q_update = "UPDATE Ticket SET state = 'refunded' WHERE ticketID = ?;"; String paymentMethod = request.getParameter("paymentMethod"); try { Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); PreparedStatement statmt1 = conn.prepareStatement(db_q_chk_credit); statmt1.setInt(1, msID); if (statmt1.execute()) { ResultSet rs1 = statmt1.getResultSet(); if (rs1.next()) { int totalAmount = rs1.getInt(1); if (paymentMethod.equals("Cash")) { if (totalAmount < userCredit) { // purchase String[] seatIDs = req_seatIDs.split(","); for (int i = 0; i < seatIDs.length; i++) { PreparedStatement statmt2 = conn.prepareStatement(db_q_check); int seatID = Integer.parseInt(seatIDs[i]); statmt2.setInt(1, userID); statmt2.setInt(2, msID); statmt2.setInt(3, seatID); statmt2.execute(); ResultSet rs2 = statmt2.getResultSet(); boolean hasNext = rs2.next(); if (!hasNext || (hasNext && rs2.getString("state").equals("refunded"))) { PreparedStatement statmt3 = conn.prepareStatement(db_q_insert); statmt3.setInt(1, userID); statmt3.setInt(2, msID); statmt3.setInt(3, seatID); statmt3.executeUpdate(); } else { out.println("Seat is reserved"); } } PreparedStatement statmt4 = conn.prepareStatement(db_q_update_credit); statmt4.setInt(1, totalAmount); statmt4.setInt(2, totalAmount / 10); statmt4.setInt(3, userID); statmt4.executeUpdate(); out.println("true"); } else { out.println("Your credit is not enough!"); } } else if (paymentMethod.equals("Loyalty Point")) { // pay by loyalty points if (totalAmount < userLP) { // purchase String[] seatIDs = req_seatIDs.split(","); for (int i = 0; i < seatIDs.length; i++) { PreparedStatement statmt2 = conn.prepareStatement(db_q_check); int seatID = Integer.parseInt(seatIDs[i]); statmt2.setInt(1, userID); statmt2.setInt(2, msID); statmt2.setInt(3, seatID); statmt2.execute(); ResultSet rs2 = statmt2.getResultSet(); boolean hasNext = rs2.next(); if (!hasNext || (hasNext && rs2.getString("state").equals("refunded"))) { PreparedStatement statmt3 = conn.prepareStatement(db_q_insert_2); statmt3.setInt(1, userID); statmt3.setInt(2, msID); statmt3.setInt(3, seatID); statmt3.executeUpdate(); } else { out.println("Seat is reserved"); } } PreparedStatement statmt4 = conn.prepareStatement(db_q_update_loyaltyPoints); statmt4.setInt(1, totalAmount); statmt4.setInt(2, userID); statmt4.executeUpdate(); out.println("true"); } else { out.println("Your loyalty points is not enough!"); } } } } conn.close(); } catch (SQLException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } catch (ClassNotFoundException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:edu.education.ucsb.muster.MusterServlet.java
private String getOutputAsJson(String database, String query, long limit) throws SQLException { // The output string StringBuffer out = new StringBuffer(); // Cache StringBuffer length as needed int len;// w w w .j av a 2 s. c om // Database operations DatabaseDefinition db = conf.getDatabase(database); // //register the driver registerDriver(db.driver, db.url); // // Connect to the database Connection connection = DriverManager.getConnection(db.url, db.username, db.password); // // Perform the query PreparedStatement statement = connection.prepareStatement(query); statement.execute(); ResultSet results = statement.getResultSet(); // Get and write the column names ResultSetMetaData meta = results.getMetaData(); int columnCount = meta.getColumnCount(); LinkedList<String> columns = new LinkedList<String>(); for (int i = 1; i < columnCount + 1; i++) { // We're only dealing with JSON, so the column names should be // JavaScript-friendly. columns.add(StringEscapeUtils.escapeJavaScript(meta.getColumnName(i))); } out.append("{\n \"columns\" : [ "); // Add column names in JSON format for (String column : columns) { out.append('"' + column + "\", "); } // remove the trailing ", " and add a line break and close the array len = out.length(); out.delete(len - 2, len); out.append(" ],\n"); // Add column values out.append(" \"results\" : [ \n"); for (int i = 0; i < limit && results.next(); i++) { out.append(rowAsJson(results, columns)); } // remove the trailing ", " len = out.length(); out.delete(len - 2, len); out.append("\n ]\n"); out.append("}"); return out.toString(); }
From source file:servlet.CustomerControl.java
protected void doRefundTicket(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); User user = (User) session.getAttribute("user"); int userID;/* ww w.j av a 2s . c om*/ int userCredit = 0; int userLP = 0; if (user != null) { userID = user.getUserID(); userCredit = user.getCredit(); userLP = user.getTradePoint(); } else { userID = 3; userCredit = 3000; userLP = 500; } 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"), db_q_update = "UPDATE Ticket SET state = 'request refundment' WHERE ticketID = ? AND state = 'purchased';", db_q_update2 = "UPDATE \"User\" SET credit = credit + ? WHERE userID = ?"; int ticketID = Integer.parseInt(request.getParameter("ticketID")); try { Class.forName(db_driver); Connection conn = DriverManager.getConnection(db_url, db_user, db_password); PreparedStatement statmt1 = conn.prepareStatement(db_q_update); statmt1.setInt(1, ticketID); statmt1.executeUpdate(); PreparedStatement statmt2 = conn.prepareStatement( "SELECT ms.price FROM Ticket t, MovieSession ms WHERE t.msID = ms.msID AND ticketID = ?;"); statmt2.setInt(1, ticketID); statmt2.execute(); ResultSet rs2 = statmt2.getResultSet(); PreparedStatement statmt3 = conn.prepareStatement(db_q_update2); rs2.next(); statmt3.setInt(1, rs2.getInt("price")); statmt3.setInt(2, userID); statmt3.executeUpdate(); this.doListTickets(request, response); } catch (ClassNotFoundException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(CustomerControl.class.getName()).log(Level.SEVERE, null, ex); } }