Java Code Examples for org.springframework.jdbc.support.JdbcUtils#closeStatement()

/**
 * Executes the SQL as specified by {@link #getSequenceQuery()}.
 */
@Override
protected long getNextKey() throws DataAccessException {
	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	ResultSet rs = null;
	try {
		stmt = con.createStatement();
		DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
		rs = stmt.executeQuery(getSequenceQuery());
		if (rs.next()) {
			return rs.getLong(1);
		}
		else {
			throw new DataAccessResourceFailureException("Sequence query did not return a result");
		}
	}
	catch (SQLException ex) {
		throw new DataAccessResourceFailureException("Could not obtain sequence value", ex);
	}
	finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

/**
 * Execute the given schema script on the given JDBC Connection.
 * <p>Note that the default implementation will log unsuccessful statements
 * and continue to execute. Override the {@code executeSchemaStatement}
 * method to treat failures differently.
 * @param con the JDBC Connection to execute the script on
 * @param sql the SQL statements to execute
 * @throws SQLException if thrown by JDBC methods
 * @see #executeSchemaStatement
 */
protected void executeSchemaScript(Connection con, String[] sql) throws SQLException {
	if (sql != null && sql.length > 0) {
		boolean oldAutoCommit = con.getAutoCommit();
		if (!oldAutoCommit) {
			con.setAutoCommit(true);
		}
		try {
			Statement stmt = con.createStatement();
			try {
				for (String sqlStmt : sql) {
					executeSchemaStatement(stmt, sqlStmt);
				}
			}
			finally {
				JdbcUtils.closeStatement(stmt);
			}
		}
		finally {
			if (!oldAutoCommit) {
				con.setAutoCommit(false);
			}
		}
	}
}
 

/**
 * Executes the SQL as specified by {@link #getSequenceQuery()}.
 */
@Override
protected long getNextKey() throws DataAccessException {
	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	ResultSet rs = null;
	try {
		stmt = con.createStatement();
		DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
		rs = stmt.executeQuery(getSequenceQuery());
		if (rs.next()) {
			return rs.getLong(1);
		}
		else {
			throw new DataAccessResourceFailureException("Sequence query did not return a result");
		}
	}
	catch (SQLException ex) {
		throw new DataAccessResourceFailureException("Could not obtain sequence value", ex);
	}
	finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

/**
 * Execute the given schema script on the given JDBC Connection.
 * <p>Note that the default implementation will log unsuccessful statements
 * and continue to execute. Override the {@code executeSchemaStatement}
 * method to treat failures differently.
 * @param con the JDBC Connection to execute the script on
 * @param sql the SQL statements to execute
 * @throws SQLException if thrown by JDBC methods
 * @see #executeSchemaStatement
 */
protected void executeSchemaScript(Connection con, String[] sql) throws SQLException {
	if (sql != null && sql.length > 0) {
		boolean oldAutoCommit = con.getAutoCommit();
		if (!oldAutoCommit) {
			con.setAutoCommit(true);
		}
		try {
			Statement stmt = con.createStatement();
			try {
				for (String sqlStmt : sql) {
					executeSchemaStatement(stmt, sqlStmt);
				}
			}
			finally {
				JdbcUtils.closeStatement(stmt);
			}
		}
		finally {
			if (!oldAutoCommit) {
				con.setAutoCommit(false);
			}
		}
	}
}
 

@Override
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	try {
		Connection conToUse = con;
		if (this.nativeJdbcExtractor != null &&
				this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
			conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
		}
		stmt = conToUse.createStatement();
		applyStatementSettings(stmt);
		Statement stmtToUse = stmt;
		if (this.nativeJdbcExtractor != null) {
			stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
		}
		T result = action.doInStatement(stmtToUse);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

private void closeStatement(Statement stmt) {
	try {
		JdbcUtils.closeStatement(stmt);
		stmt = null;
	} catch (Exception ex) {
		//throw new DistributedTransactionException(ex);
	}
}
 

@Override
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	try {
		Connection conToUse = con;
		if (this.nativeJdbcExtractor != null &&
				this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
			conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
		}
		stmt = conToUse.createStatement();
		applyStatementSettings(stmt);
		Statement stmtToUse = stmt;
		if (this.nativeJdbcExtractor != null) {
			stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
		}
		T result = action.doInStatement(stmtToUse);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returns the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate(getIncrementStatement());
				ResultSet rs = stmt.executeQuery(getIdentityStatement());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("Identity statement failed after inserting");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			stmt.executeUpdate(getDeleteStatement(this.valueCache));
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

private void closeStatement(Statement stmt) {
	try {
		JdbcUtils.closeStatement(stmt);
		stmt = null;
	} catch (Exception ex) {
		//throw new DistributedTransactionException(ex);
	}
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returns the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate(getIncrementStatement());
				ResultSet rs = stmt.executeQuery(getIdentityStatement());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("Identity statement failed after inserting");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			stmt.executeUpdate(getDeleteStatement(this.valueCache));
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

@Override
@Nullable
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(obtainDataSource());
	Statement stmt = null;
	try {
		stmt = con.createStatement();
		applyStatementSettings(stmt);
		T result = action.doInStatement(stmt);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		String sql = getSql(action);
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw translateException("StatementCallback", sql, ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returns the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate(getIncrementStatement());
				ResultSet rs = stmt.executeQuery(getIdentityStatement());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("Identity statement failed after inserting");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			stmt.executeUpdate(getDeleteStatement(this.valueCache));
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

@Override
@Nullable
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(obtainDataSource());
	Statement stmt = null;
	try {
		stmt = con.createStatement();
		applyStatementSettings(stmt);
		T result = action.doInStatement(stmt);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		String sql = getSql(action);
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw translateException("StatementCallback", sql, ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returns the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate(getIncrementStatement());
				ResultSet rs = stmt.executeQuery(getIdentityStatement());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("Identity statement failed after inserting");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			stmt.executeUpdate(getDeleteStatement(this.valueCache));
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returnes the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate("insert into " + getIncrementerName() + " default values");
				ResultSet rs = stmt.executeQuery("select @@identity");
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("@@identity failed after executing an update");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			long maxValue = this.valueCache[(this.valueCache.length - 1)];
			stmt.executeUpdate("delete from " + getIncrementerName() + " where " + getColumnName() + " < " + maxValue);
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same Connection. Otherwise we can't be sure that last_insert_id()
		* returned the correct value.
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate("insert into " + getIncrementerName() + " values(null)");
				ResultSet rs = stmt.executeQuery("select max(identity()) from " + getIncrementerName());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("identity() failed after executing an update");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			long maxValue = this.valueCache[(this.valueCache.length - 1)];
			stmt.executeUpdate("delete from " + getIncrementerName() + " where " + getColumnName() + " < " + maxValue);
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not obtain identity()", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.maxId == this.nextId) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that last_insert_id()
		* returned the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			// Increment the sequence column...
			String columnName = getColumnName();
			stmt.executeUpdate("update "+ getIncrementerName() + " set " + columnName +
					" = last_insert_id(" + columnName + " + " + getCacheSize() + ")");
			// Retrieve the new max of the sequence column...
			ResultSet rs = stmt.executeQuery(VALUE_SQL);
			try {
				if (!rs.next()) {
					throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
				}
				this.maxId = rs.getLong(1);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
			this.nextId = this.maxId - getCacheSize() + 1;
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	else {
		this.nextId++;
	}
	return this.nextId;
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.maxId == this.nextId) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that last_insert_id()
		* returned the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			// Increment the sequence column...
			String columnName = getColumnName();
			stmt.executeUpdate("update "+ getIncrementerName() + " set " + columnName +
					" = last_insert_id(" + columnName + " + " + getCacheSize() + ")");
			// Retrieve the new max of the sequence column...
			ResultSet rs = stmt.executeQuery(VALUE_SQL);
			try {
				if (!rs.next()) {
					throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
				}
				this.maxId = rs.getLong(1);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
			this.nextId = this.maxId - getCacheSize() + 1;
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	else {
		this.nextId++;
	}
	return this.nextId;
}
 

@Override
public void close() {
    JdbcUtils.closeResultSet(rs);
    JdbcUtils.closeStatement(st);
    JdbcUtils.closeConnection(con);
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returnes the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate(getIncrementStatement());
				ResultSet rs = stmt.executeQuery("select @@identity");
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("@@identity failed after executing an update");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			long maxValue = this.valueCache[(this.valueCache.length - 1)];
			stmt.executeUpdate("delete from " + getIncrementerName() + " where " + getColumnName() + " < " + maxValue);
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}