List of usage examples for java.sql Statement getUpdateCount
int getUpdateCount() throws SQLException;
ResultSet object or there are no more results, -1 is returned. From source file:org.mskcc.cbio.portal.dao.MySQLbulkLoader.java
/** * load the temp file maintained by the MySQLbulkLoader into the DMBS. * truncates the temp file, and leaves it open for more insertRecord() operations. * returns number of records inserted.// w ww. j a v a 2s . c om * * TODO: perhaps instead of having each program that uses a DAO that uses bulk loading call 'completeInsert', * get MySQLbulkLoader created by a factory, and have the factory remember to load all the tables from all * the temp files before the program exits. * @return number of records inserted * @throws DaoException * @throws IOException */ private int loadDataFromTempFileIntoDBMS() throws DaoException, IOException { Connection con = null; Statement stmt; PreparedStatement pstmt = null; ResultSet rs = null; try { try { // close the file, flushing all buffers before loading the DBMS tempFileWriter.flush(); tempFileWriter.close(); } catch (IOException e) { throw new DaoException(e); } con = JdbcUtil.getDbConnection(MySQLbulkLoader.class); stmt = con.createStatement(); String command = "LOAD DATA LOCAL INFILE '" + tempFileName + "'" + " INTO TABLE " + tableName; stmt.execute(command); int updateCount = stmt.getUpdateCount(); ProgressMonitor .setCurrentMessage(" --> records inserted into `" + tableName + "` table: " + updateCount); int nLines = FileUtil.getNumLines(tempFileHandle); if (nLines != updateCount) { String otherDetails = ""; if (stmt.getWarnings() != null) { otherDetails = "More error/warning details: " + stmt.getWarnings().getMessage(); } throw new DaoException("DB Error: only " + updateCount + " of the " + nLines + " records were inserted in `" + tableName + "`. " + otherDetails); } else { tempFileHandle.delete(); } // reopen empty temp file -- not necessary, this loader will be removed. //this.tempFileWriter = new BufferedWriter(new FileWriter( this.tempFileHandle, false)); return updateCount; } catch (SQLException e) { throw new DaoException(e); } finally { JdbcUtil.closeAll(MySQLbulkLoader.class, con, pstmt, rs); } }
From source file:org.openqa.selenium.server.htmlrunner.DatabaseTestResults.java
public static Object[][] sqlExecute(String driver, String statement) throws SQLException { Statement stmt = null; //ResultSet rst = null; // for Select query Object[][] result = null;//from w w w.ja va2s . c om try { Class.forName(driver); if (ctn == null) return result; stmt = ctn.createStatement(); log.debug("\nSQL: " + statement); stmt.execute(statement); int updateCount = stmt.getUpdateCount(); //String upperSqlStr = (statement.toUpperCase()).trim(); log.debug("Number of rows affected = " + updateCount); stmt.close(); } catch (Exception e) { e.printStackTrace(); System.out.print(e.getMessage()); if (stmt != null) stmt.close(); } return result; }
From source file:org.owasp.dependencycheck.data.nvdcve.ConnectionFactory.java
/** * Updates the database schema by loading the upgrade script for the version specified. The intended use is that if the * current schema version is 2.9 then we would call updateSchema(conn, "2.9"). This would load the upgrade_2.9.sql file and * execute it against the database. The upgrade script must update the 'version' in the properties table. * * @param conn the database connection object * @param appExpectedVersion the schema version that the application expects * @param currentDbVersion the current schema version of the database * @throws DatabaseException thrown if there is an exception upgrading the database schema *//*from w ww . j a v a 2s.co m*/ private static void updateSchema(Connection conn, DependencyVersion appExpectedVersion, DependencyVersion currentDbVersion) throws DatabaseException { final String databaseProductName; try { databaseProductName = conn.getMetaData().getDatabaseProductName(); } catch (SQLException ex) { throw new DatabaseException("Unable to get the database product name"); } if ("h2".equalsIgnoreCase(databaseProductName)) { LOGGER.debug("Updating database structure"); InputStream is = null; String updateFile = null; try { updateFile = String.format(DB_STRUCTURE_UPDATE_RESOURCE, currentDbVersion.toString()); is = ConnectionFactory.class.getClassLoader().getResourceAsStream(updateFile); if (is == null) { throw new DatabaseException(String.format("Unable to load update file '%s'", updateFile)); } final String dbStructureUpdate = IOUtils.toString(is, "UTF-8"); Statement statement = null; try { statement = conn.createStatement(); final boolean success = statement.execute(dbStructureUpdate); if (!success && statement.getUpdateCount() <= 0) { throw new DatabaseException(String.format("Unable to upgrade the database schema to %s", currentDbVersion.toString())); } } catch (SQLException ex) { LOGGER.debug("", ex); throw new DatabaseException("Unable to update database schema", ex); } finally { DBUtils.closeStatement(statement); } } catch (IOException ex) { final String msg = String.format("Upgrade SQL file does not exist: %s", updateFile); throw new DatabaseException(msg, ex); } finally { IOUtils.closeQuietly(is); } } else { final int e0 = Integer.parseInt(appExpectedVersion.getVersionParts().get(0)); final int c0 = Integer.parseInt(currentDbVersion.getVersionParts().get(0)); final int e1 = Integer.parseInt(appExpectedVersion.getVersionParts().get(1)); final int c1 = Integer.parseInt(currentDbVersion.getVersionParts().get(1)); if (e0 == c0 && e1 < c1) { LOGGER.warn("A new version of dependency-check is available; consider upgrading"); Settings.setBoolean(Settings.KEYS.AUTO_UPDATE, false); } else if (e0 == c0 && e1 == c1) { //do nothing - not sure how we got here, but just incase... } else { LOGGER.error( "The database schema must be upgraded to use this version of dependency-check. Please see {} for more information.", UPGRADE_HELP_URL); throw new DatabaseException("Database schema is out of date"); } } }
From source file:org.pentaho.di.core.database.Database.java
public Result execStatement(String rawsql, RowMetaInterface params, Object[] data) throws KettleDatabaseException { Result result = new Result(); // Replace existing code with a class that removes comments from the raw // SQL.//from w w w. j a va2s . c o m // The SqlCommentScrubber respects single-quoted strings, so if a // double-dash or a multiline comment appears // in a single-quoted string, it will be treated as a string instead of // comments. String sql = SqlCommentScrubber.removeComments(rawsql).trim(); try { boolean resultSet; int count; if (params != null) { PreparedStatement prep_stmt = connection.prepareStatement(databaseMeta.stripCR(sql)); setValues(params, data, prep_stmt); // set the parameters! resultSet = prep_stmt.execute(); count = prep_stmt.getUpdateCount(); prep_stmt.close(); } else { String sqlStripped = databaseMeta.stripCR(sql); // log.logDetailed("Executing SQL Statement: ["+sqlStripped+"]"); Statement stmt = connection.createStatement(); resultSet = stmt.execute(sqlStripped); count = stmt.getUpdateCount(); stmt.close(); } String upperSql = sql.toUpperCase(); if (!resultSet) { // if the result is a resultset, we don't do anything with it! // You should have called something else! // log.logDetailed("What to do with ResultSet??? (count="+count+")"); if (count > 0) { if (upperSql.startsWith("INSERT")) { result.setNrLinesOutput(count); } else if (upperSql.startsWith("UPDATE")) { result.setNrLinesUpdated(count); } else if (upperSql.startsWith("DELETE")) { result.setNrLinesDeleted(count); } } } // See if a cache needs to be cleared... if (upperSql.startsWith("ALTER TABLE") || upperSql.startsWith("DROP TABLE") || upperSql.startsWith("CREATE TABLE")) { DBCache.getInstance().clear(databaseMeta.getName()); } } catch (SQLException ex) { throw new KettleDatabaseException("Couldn't execute SQL: " + sql + Const.CR, ex); } catch (Exception e) { throw new KettleDatabaseException("Unexpected error executing SQL: " + Const.CR, e); } return result; }
From source file:org.rimudb.ScriptRunner.java
public void executeScript() throws Exception { if (getFilename() == null && getResourceName() == null) { throw new IOException("Either a resource name or a filename must be defined"); }/*from w w w. j a va 2 s . c o m*/ if (getDatabase() == null) { throw new RimuDBException("Database is not defined"); } if (!getDatabase().isConnected()) { throw new RimuDBException("Database is not connected"); } log.info("Executing script: " + getFilename()); Connection connection = null; Statement statement = null; String sqlQuery = ""; int lineNumber = 0; try { connection = getDatabase().getDatabaseConnection(); statement = connection.createStatement(); StringBuffer query = new StringBuffer(); BufferedReader reader = null; if (getResourceName() != null) { InputStream is = getClass().getResourceAsStream(getResourceName()); reader = new BufferedReader(new InputStreamReader(is)); } else if (getFilename() != null) { reader = new BufferedReader(new FileReader(getFilename())); } else { throw new IllegalArgumentException("Either a resource name or a filename needs to be set"); } while (reader.ready()) { String line = reader.readLine(); lineNumber++; // Check for EOF if (line == null) { break; } // Ignore comments if (isComment(line)) { continue; } // Change delimiter command boolean changeDelimiter = line.matches("\\{delimiter=.+\\}"); if (changeDelimiter) { String s = line.substring(11); int pos = s.indexOf("}"); String delimiter = s.substring(0, pos); setEndOfQueryDelimiter(delimiter); log.info("Changing to delimiter '" + delimiter + "'"); continue; } boolean ignoreErrorLine = (line.startsWith("{ignoreError}")); if (ignoreErrorLine) { line = line.substring(13); } boolean endOfStatement = isEndOfStatement(line); if (query.length() > 0) { query.append(" "); } query.append(line.trim()); if (endOfStatement) { // Remove the query delimiter sqlQuery = trimQueryDelimiter(query.toString()); query.setLength(0); // Execute the statement try { boolean result = statement.execute(sqlQuery); if (result) { log.warn("SQL statement returned a result set. [" + sqlQuery + "]"); } else { int updateCount = statement.getUpdateCount(); log.info("Update count = " + updateCount + " [" + sqlQuery + "]"); } } catch (SQLException sqle) { if (!continueOnFailure && !ignoreErrorLine) { throw sqle; } else { log.error("Line: " + lineNumber + " Error executing SQL=" + sqlQuery, sqle); } } } } } catch (SQLException e) { log.error("Line: " + lineNumber + " Error executing SQL=" + sqlQuery); throw new RimuDBException("Line: " + lineNumber + ": " + e.toString()); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { } } if (connection != null) { try { connection.close(); } catch (Exception e) { } } log.info("Completed script: " + getFilename()); } }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testBatchUpdateWithNoBatchSupport() throws Exception { final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2" }; MockControl ctrlStatement = MockControl.createControl(Statement.class); Statement mockStatement = (Statement) ctrlStatement.getMock(); mockStatement.getConnection();//from w w w .j av a 2 s .c o m ctrlStatement.setReturnValue(mockConnection); mockStatement.execute(sql[0]); ctrlStatement.setReturnValue(false); mockStatement.getUpdateCount(); ctrlStatement.setReturnValue(1); mockStatement.execute(sql[1]); ctrlStatement.setReturnValue(false); mockStatement.getUpdateCount(); ctrlStatement.setReturnValue(1); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class); DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock(); mockDatabaseMetaData.getDatabaseProductName(); ctrlDatabaseMetaData.setReturnValue("MySQL"); mockDatabaseMetaData.supportsBatchUpdates(); ctrlDatabaseMetaData.setReturnValue(false); mockConnection.getMetaData(); ctrlConnection.setReturnValue(mockDatabaseMetaData, 2); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); ctrlStatement.replay(); ctrlDatabaseMetaData.replay(); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource, false); int[] actualRowsAffected = template.batchUpdate(sql); assertTrue("executed 2 updates", actualRowsAffected.length == 2); ctrlStatement.verify(); ctrlDatabaseMetaData.verify(); }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testBatchUpdateWithNoBatchSupportAndSelect() throws Exception { final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "SELECT * FROM NOSUCHTABLE" }; MockControl ctrlStatement = MockControl.createControl(Statement.class); Statement mockStatement = (Statement) ctrlStatement.getMock(); mockStatement.getConnection();/*from ww w. j av a 2 s . com*/ ctrlStatement.setReturnValue(mockConnection); mockStatement.execute(sql[0]); ctrlStatement.setReturnValue(false); mockStatement.getUpdateCount(); ctrlStatement.setReturnValue(1); mockStatement.execute(sql[1]); ctrlStatement.setReturnValue(true); mockStatement.close(); ctrlStatement.setVoidCallable(); MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class); DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock(); mockDatabaseMetaData.getDatabaseProductName(); ctrlDatabaseMetaData.setReturnValue("MySQL"); mockDatabaseMetaData.supportsBatchUpdates(); ctrlDatabaseMetaData.setReturnValue(false); mockConnection.getMetaData(); ctrlConnection.setReturnValue(mockDatabaseMetaData, 2); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); ctrlStatement.replay(); ctrlDatabaseMetaData.replay(); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource, false); try { template.batchUpdate(sql); fail("Shouldn't have executed batch statement with a select"); } catch (DataAccessException ex) { // pass assertTrue("Check exception type", ex.getClass() == InvalidDataAccessApiUsageException.class); } ctrlStatement.verify(); ctrlDatabaseMetaData.verify(); }
From source file:org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.java
/** * Execute the given SQL script.//w ww . j a v a 2s . co m * <p>The script will normally be loaded by classpath. There should be one statement * per line. Any {@link #setSeparator(String) statement separators} will be removed. * <p><b>Do not use this method to execute DDL if you expect rollback.</b> * @param connection the JDBC Connection with which to perform JDBC operations * @param resource the resource (potentially associated with a specific encoding) to load the SQL script from * @param continueOnError whether or not to continue without throwing an exception in the event of an error * @param ignoreFailedDrops whether of not to continue in the event of specifically an error on a {@code DROP} */ private void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, boolean ignoreFailedDrops) throws SQLException { if (logger.isInfoEnabled()) { logger.info("Executing SQL script from " + resource); } long startTime = System.currentTimeMillis(); List<String> statements = new LinkedList<String>(); String script; try { script = readScript(resource); } catch (IOException ex) { throw new CannotReadScriptException(resource, ex); } String delimiter = this.separator; if (delimiter == null) { delimiter = DEFAULT_STATEMENT_SEPARATOR; if (!containsSqlScriptDelimiters(script, delimiter)) { delimiter = "\n"; } } splitSqlScript(script, delimiter, this.commentPrefix, statements); int lineNumber = 0; Statement stmt = connection.createStatement(); try { for (String statement : statements) { lineNumber++; try { stmt.execute(statement); int rowsAffected = stmt.getUpdateCount(); if (logger.isDebugEnabled()) { logger.debug(rowsAffected + " returned as updateCount for SQL: " + statement); } } catch (SQLException ex) { boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop"); if (continueOnError || (dropStatement && ignoreFailedDrops)) { if (logger.isDebugEnabled()) { logger.debug("Failed to execute SQL script statement at line " + lineNumber + " of resource " + resource + ": " + statement, ex); } } else { throw new ScriptStatementFailedException(statement, lineNumber, resource, ex); } } } } finally { try { stmt.close(); } catch (Throwable ex) { logger.debug("Could not close JDBC Statement", ex); } } long elapsedTime = System.currentTimeMillis() - startTime; if (logger.isInfoEnabled()) { logger.info("Done executing SQL script from " + resource + " in " + elapsedTime + " ms."); } }
From source file:org.springframework.jdbc.datasource.init.ScriptUtils.java
/** * Execute the given SQL script./*from w ww . j a va 2 s.c o m*/ * <p>Statement separators and comments will be removed before executing * individual statements within the supplied script. * <p><strong>Warning</strong>: this method does <em>not</em> release the * provided {@link Connection}. * @param connection the JDBC connection to use to execute the script; already * configured and ready to use * @param resource the resource (potentially associated with a specific encoding) * to load the SQL script from * @param continueOnError whether or not to continue without throwing an exception * in the event of an error * @param ignoreFailedDrops whether or not to continue in the event of specifically * an error on a {@code DROP} statement * @param commentPrefix the prefix that identifies single-line comments in the * SQL script — typically "--" * @param separator the script statement separator; defaults to * {@value #DEFAULT_STATEMENT_SEPARATOR} if not specified and falls back to * {@value #FALLBACK_STATEMENT_SEPARATOR} as a last resort; may be set to * {@value #EOF_STATEMENT_SEPARATOR} to signal that the script contains a * single statement without a separator * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; never * {@code null} or empty * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; never * {@code null} or empty * @throws ScriptException if an error occurred while executing the SQL script * @see #DEFAULT_STATEMENT_SEPARATOR * @see #FALLBACK_STATEMENT_SEPARATOR * @see #EOF_STATEMENT_SEPARATOR * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection */ public static void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, boolean ignoreFailedDrops, String commentPrefix, @Nullable String separator, String blockCommentStartDelimiter, String blockCommentEndDelimiter) throws ScriptException { try { if (logger.isInfoEnabled()) { logger.info("Executing SQL script from " + resource); } long startTime = System.currentTimeMillis(); String script; try { script = readScript(resource, commentPrefix, separator); } catch (IOException ex) { throw new CannotReadScriptException(resource, ex); } if (separator == null) { separator = DEFAULT_STATEMENT_SEPARATOR; } if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) { separator = FALLBACK_STATEMENT_SEPARATOR; } List<String> statements = new LinkedList<>(); splitSqlScript(resource, script, separator, commentPrefix, blockCommentStartDelimiter, blockCommentEndDelimiter, statements); int stmtNumber = 0; Statement stmt = connection.createStatement(); try { for (String statement : statements) { stmtNumber++; try { stmt.execute(statement); int rowsAffected = stmt.getUpdateCount(); if (logger.isDebugEnabled()) { logger.debug(rowsAffected + " returned as update count for SQL: " + statement); SQLWarning warningToLog = stmt.getWarnings(); while (warningToLog != null) { logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" + warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]"); warningToLog = warningToLog.getNextWarning(); } } } catch (SQLException ex) { boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop"); if (continueOnError || (dropStatement && ignoreFailedDrops)) { if (logger.isDebugEnabled()) { logger.debug(ScriptStatementFailedException.buildErrorMessage(statement, stmtNumber, resource), ex); } } else { throw new ScriptStatementFailedException(statement, stmtNumber, resource, ex); } } } } finally { try { stmt.close(); } catch (Throwable ex) { logger.debug("Could not close JDBC Statement", ex); } } long elapsedTime = System.currentTimeMillis() - startTime; if (logger.isInfoEnabled()) { logger.info("Executed SQL script from " + resource + " in " + elapsedTime + " ms."); } } catch (Exception ex) { if (ex instanceof ScriptException) { throw (ScriptException) ex; } throw new UncategorizedScriptException( "Failed to execute database script from resource [" + resource + "]", ex); } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
/** * This method writes the return update row count to the response * * @param stmt SQL Statement//from w w w.j av a 2s . com * @param xmlWriter XMLStreamWriter * @param params input params * @param queryLevel Query Level * @throws DataServiceFault * @throws SQLException */ private void writeOutUpdatedRowCount(Statement stmt, XMLStreamWriter xmlWriter, InternalParamCollection params, int queryLevel) throws DataServiceFault, SQLException { int updateCount = stmt.getUpdateCount(); DataEntry dataEntry = new DataEntry(); ParamValue param = new ParamValue(ParamValue.PARAM_VALUE_SCALAR); param.setScalarValue(Integer.toString(updateCount)); /* Updated Row Count result is mapped to Column Number 1 */ dataEntry.addValue("1", param); this.writeResultEntry(xmlWriter, dataEntry, params, queryLevel); }