List of usage examples for java.sql PreparedStatement getUpdateCount
int getUpdateCount() throws SQLException;
ResultSet object or there are no more results, -1 is returned. From source file:io.cloudslang.content.database.services.SQLCommandService.java
public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)) { connection.setReadOnly(false);/* ww w . ja v a2 s. c o m*/ final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { //during a dump sybase sends back status as exceptions. if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } //For sybase, when dbcc command is executed, the result is shown in warning message else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }
From source file:com.chaosinmotion.securechat.server.commands.CreateAccount.java
/** * Process the create account request. This should receive the following * objects: the username, the password, the device ID and the public key * for the device. This adds a new entry in the account database, and * creates a new device./*from ww w. j a va2s . c om*/ * * If the user account cannot be created, this returns nil. * @param requestParams * @return */ public static UserInfo processRequest(JSONObject requestParams) throws ClassNotFoundException, SQLException, IOException { String username = requestParams.optString("username"); String password = requestParams.optString("password"); String deviceid = requestParams.optString("deviceid"); String pubkey = requestParams.optString("pubkey"); /* * Attempt to insert a new user into the database */ Connection c = null; PreparedStatement ps = null; ResultSet rs = null; try { c = Database.get(); ps = c.prepareStatement("INSERT INTO Users " + " ( username, password ) " + "VALUES " + " ( ?, ? ); SELECT currval('Users_userid_seq')"); ps.setString(1, username); ps.setString(2, password); try { ps.execute(); } catch (SQLException ex) { return null; // Can't insert; duplicate username? } int utc = ps.getUpdateCount(); int userid = 0; if ((utc == 1) && ps.getMoreResults()) { rs = ps.getResultSet(); if (rs.next()) { userid = rs.getInt(1); } rs.close(); rs = null; } ps.close(); ps = null; /* * We now have the user index. Insert the device. Note that it is * highly unlikely we will have a UUID collision, but we verify * we don't by deleting any rows in the device table with the * specified UUID. The worse case scenario is a collision which * knocks someone else off the air. (The alternative would be * to accidentally send the wrong person duplicate messages.) * * Note that we don't actually use a device-identifying identifer, * choosing instead to pick a UUID, so we need to deal with * the possibility (however remote) of duplicate UUIDs. * * In the off chance we did have a collision, we also delete all * old messages to the device; that prevents messages from being * accidentally delivered. */ ps = c.prepareStatement("DELETE FROM Messages " + "WHERE messageid IN " + " (SELECT Messages.messageid " + " FROM Messages, Devices " + " WHERE Messages.deviceid = Devices.deviceid " + " AND Devices.deviceuuid = ?)"); ps.setString(1, deviceid); ps.execute(); ps.close(); ps = null; ps = c.prepareStatement("DELETE FROM Devices WHERE deviceuuid = ?"); ps.setString(1, deviceid); ps.execute(); ps.close(); ps = null; ps = c.prepareStatement("INSERT INTO Devices " + " ( userid, deviceuuid, publickey ) " + "VALUES " + " ( ?, ?, ?)"); ps.setInt(1, userid); ps.setString(2, deviceid); ps.setString(3, pubkey); ps.execute(); /* * Complete; return the user info record */ return new Login.UserInfo(userid); } finally { if (rs != null) rs.close(); if (ps != null) ps.close(); if (c != null) c.close(); } }
From source file:org.kawanfw.test.api.client.autogeneratedkeys.InsertPrepStatementAutoKeysTest.java
/** * Do a 100 row insert inside a loop/*from w w w . ja va2s . co m*/ * * @param connection * the AceQL Connection * * @throws Exception * it any Exception occurs */ public static void insertPrepStatementExecuteUpdate(Connection connection, int valueToInsert, boolean useRawExecute, boolean autoCommitOn) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! long maxCustomerId = InsertStatementTestAutoKeysTest.getMaxCustomerId(connection); MessageDisplayer.display(""); MessageDisplayer.display("maxCustomerId: " + maxCustomerId); if (!autoCommitOn) { connection.setAutoCommit(false); } // We will do all our remote insert in a SQL Transaction try { // 1) First create a Customer String sql = "insert into customer_auto (customer_title, fname, lname, addressline, town, zipcode, phone) " + " values ( ?, ?, ?, ?, ?, ?, ? )"; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + valueToInsert + " customers..."); PreparedStatement prepStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); int i = valueToInsert; int j = 1; prepStatement.setString(j++, "Sir"); prepStatement.setNull(j++, Types.VARCHAR); prepStatement.setString(j++, "Smith_" + i); prepStatement.setString(j++, i + ", Csar Avenue"); prepStatement.setString(j++, "JavaLand_" + i); prepStatement.setString(j++, i + "45"); prepStatement.setString(j++, i + "-12345678"); int rc = -1; if (!useRawExecute) { rc = prepStatement.executeUpdate(); MessageDisplayer.display("after executeUpdate(): row count: " + rc); } else { prepStatement.execute(); rc = prepStatement.getUpdateCount(); MessageDisplayer.display("after execute(): prepStatement.getUpdateCount(): " + rc); } if (!autoCommitOn) { connection.commit(); } ResultSet keys = prepStatement.getGeneratedKeys(); long lastKey = -1; while (keys.next()) { lastKey = keys.getLong(1); } keys.close(); MessageDisplayer.display("Last Key: " + lastKey); // Don't know why: there is a bug in some engines.... // Assert.assertEquals("last key = maxCustomerId + 1", lastKey, // maxCustomerId + 1); // So do another test: Assert.assertEquals("last key >= 1", true, lastKey >= 1); prepStatement.close(); } catch (Exception e) { e.printStackTrace(); if (!autoCommitOn) { connection.rollback(); } throw e; } finally { if (!autoCommitOn) { connection.setAutoCommit(true); } } }
From source file:org.pvalsecc.jdbc.JdbcUtilities.java
/** * Execute a delete statement//from w w w .j a va 2s .c om * * @return The number of rows deleted * @throws SQLException */ public static int runDeleteQuery(String description, String sqlStatement, Connection conn, DeleteTask task) throws SQLException { PreparedStatement stmt = null; boolean queryDisplayed = false; try { //noinspection JDBCResourceOpenedButNotSafelyClosed stmt = conn.prepareStatement(sqlStatement); if (task != null) { task.setupStatement(stmt); } if (LOGGER.isDebugEnabled()) { LOGGER.debug("Executing SQL : " + sqlStatement + " (" + description + ")"); queryDisplayed = true; } long beginTime = System.currentTimeMillis(); //noinspection JDBCResourceOpenedButNotSafelyClosed boolean type = stmt.execute(); if (type) { throw new RuntimeException("Delete statement returning a result set?"); } final int count = stmt.getUpdateCount(); if (TIMING_LOGGER.isDebugEnabled()) { if (count > 0) { TIMING_LOGGER.debug("Time " + description + " (count=" + count + "): " + UnitUtilities.toElapsedTime(System.currentTimeMillis() - beginTime)); } else { TIMING_LOGGER.debug("Time " + description + ": " + UnitUtilities.toElapsedTime(System.currentTimeMillis() - beginTime)); } } return count; } catch (SQLException ex) { if (!queryDisplayed) { //add the query if it was not displayed previously (will help to debug) LOGGER.error(sqlStatement); } throw ex; } finally { safeClose(stmt); } }
From source file:org.hibernatespatial.test.DataSourceUtils.java
/** * Delete all testsuite-suite data from the database * * @throws SQLException/*from w w w . j av a 2 s .c o m*/ */ public void deleteTestData() throws SQLException { Connection cn = null; try { cn = getDataSource().getConnection(); cn.setAutoCommit(false); PreparedStatement pmt = cn.prepareStatement("delete from GEOMTEST"); if (!pmt.execute()) { int updateCount = pmt.getUpdateCount(); LOGGER.info("Removing " + updateCount + " rows."); } cn.commit(); pmt.close(); } finally { try { if (cn != null) cn.close(); } catch (SQLException e) { // nothing to do } } }
From source file:de.klemp.middleware.controller.Controller.java
/** * With this method the devices can log out. Their names will be deleted * from the tables.//from ww w . ja va 2s .c o m * * @param component * 1 or 2 * @param classes * name of the class of the device * @param name * name, the device had subscribed. */ @GET @Path("/abmelden/{component}/{classes}/{name}") public static synchronized String unsubscribe(@PathParam("component") int component, @PathParam("classes") String classes, @PathParam("name") String name) { createDBConnection(); String ok = "ok"; name.replaceAll("\"", "\\\""); try { if (component == 1) { Statement statement = conn.createStatement(); statement.execute("delete from\"" + classes + "\"where name='" + name + "');"); PreparedStatement st = conn .prepareStatement("delete from \"InputDevices\" where name=" + name + ";"); st.setString(1, classes); st.setString(2, name); st.execute(); if (st.getUpdateCount() != 1) { ok = "class or method not found"; } } if (component == 2) { PreparedStatement st = conn .prepareStatement("delete from \"OutputDevices\" where \"class\"=? and \"topic\"=?;"); st.setString(1, classes); st.setString(2, name); st.execute(); deviceActive.remove(classes + "," + name); if (st.getUpdateCount() != 1) { ok = "class or method not found"; } } } catch (SQLException e) { logger.error("SQL Exception", e); } closeDBConnection(); return ok; }
From source file:org.hibernate.spatial.testing.DataSourceUtils.java
/** * Delete all testsuite-suite data from the database * * @throws SQLException//from w ww. ja va 2s.c o m */ public void deleteTestData() throws SQLException { Connection cn = null; try { cn = getDataSource().getConnection(); cn.setAutoCommit(false); PreparedStatement pmt = cn.prepareStatement("delete from GEOMTEST"); if (!pmt.execute()) { int updateCount = pmt.getUpdateCount(); LOG.info("Removing " + updateCount + " rows."); } cn.commit(); pmt.close(); } finally { try { if (cn != null) { cn.close(); } } catch (SQLException e) { // nothing to do } } }
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 ww .j av a 2s . c o m 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:org.seasar.dbflute.s2dao.sqlhandler.TnAbstractBatchHandler.java
protected void handleBatchUpdateResultWithOptimisticLock(PreparedStatement ps, List<?> list, int[] result) { if (isCurrentDBDef(DBDef.Oracle)) { final int updateCount; try {/* w w w. java 2 s . co m*/ updateCount = ps.getUpdateCount(); } catch (SQLException e) { handleSQLException(e, ps); return; // unreachable } handleBatchUpdateResultWithOptimisticLockByUpdateCount(list, updateCount); } else { handleBatchUpdateResultWithOptimisticLockByResult(list, result); } }
From source file:com.flexive.core.storage.MySQL.MySQLSequencerStorage.java
/** * {@inheritDoc}// w w w . ja v a2 s .c o m */ @Override public void removeSequencer(String name) throws FxApplicationException { if (!sequencerExists(name)) throw new FxCreateException(LOG, "ex.sequencer.notFound", name); Connection con = null; PreparedStatement ps = null; try { con = Database.getDbConnection(); ps = con.prepareStatement(SQL_DELETE); ps.setString(1, name); ps.executeUpdate(); if (ps.getUpdateCount() == 0) throw new FxCreateException(LOG, "ex.sequencer.remove.failed", name); } catch (SQLException exc) { throw new FxCreateException(LOG, exc, "ex.sequencer.remove.failed", name); } finally { Database.closeObjects(MySQLSequencerStorage.class, con, ps); } }