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

/**
 * 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());
	}
}
 

@Override
public List<String> findDefaultColumnType(String dbInfoId) throws Exception {
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	Connection conn = null;
	ResultSet resultSet = null;
	try {
		conn = DataSourceUtils.getConnection(ds);
		DatabaseMetaData metaData = conn.getMetaData();
		resultSet = metaData.getTypeInfo();
		List<String> list = new ArrayList<String>();
		while (resultSet.next()) {
			String typeName = resultSet.getString("TYPE_NAME").toUpperCase();
			list.add(typeName);
		}
		return list;
	} finally {
		JdbcUtils.closeResultSet(resultSet);
		JdbcUtils.closeConnection(conn);
	}
}
 

/**
 * 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());
	}
}
 

private static List<Column> generateColumns(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException {
    ResultSet columnsResultSet = null;

    try {
        columnsResultSet = metaData.getColumns(tableName, null);

        List<Column> columns = new ArrayList<>();
        Map<String, Object> values;

        for (; columnsResultSet.next(); columns.add(generateOneColumn(metaData, values))) {
            Map<String, Object> tmp = readColumns(columnsResultSet, getDescriptorsForColumn());
            if (tableName.equalsIgnoreCase((String) tmp.get("TABLE_NAME"))) {
                values = tmp;
            } else {
                break;
            }
        }

        return columns;
    } finally {
        JdbcUtils.closeResultSet(columnsResultSet);
    }
}
 

/**
 * 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());
	}
}
 

@Override
public List<String> findTablePrimaryKeys(String dbInfoId, String tableName) throws Exception {
	List<String> primaryKeys = new ArrayList<String>();
	Connection con = null;
	ResultSet rs = null;
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	try {
		con = ds.getConnection();
		DatabaseMetaData metaData = con.getMetaData();
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (rs.next()) {
			primaryKeys.add(rs.getString("COLUMN_NAME").toUpperCase());
		}
		return primaryKeys;
	} finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeConnection(con);
	}
}
 

@Override
@Nullable
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
	Assert.notNull(sql, "SQL must not be null");
	Assert.notNull(rse, "ResultSetExtractor must not be null");
	if (logger.isDebugEnabled()) {
		logger.debug("Executing SQL query [" + sql + "]");
	}

	/**
	 * Callback to execute the query.
	 */
	class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
		@Override
		@Nullable
		public T doInStatement(Statement stmt) throws SQLException {
			ResultSet rs = null;
			try {
				rs = stmt.executeQuery(sql);
				return rse.extractData(rs);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
		}
		@Override
		public String getSql() {
			return sql;
		}
	}

	return execute(new QueryStatementCallback());
}
 

@Override
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
	Assert.notNull(sql, "SQL must not be null");
	Assert.notNull(rse, "ResultSetExtractor must not be null");
	if (logger.isDebugEnabled()) {
		logger.debug("Executing SQL query [" + sql + "]");
	}
	class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
		@Override
		public T doInStatement(Statement stmt) throws SQLException {
			ResultSet rs = null;
			try {
				rs = stmt.executeQuery(sql);
				ResultSet rsToUse = rs;
				if (nativeJdbcExtractor != null) {
					rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
				}
				return rse.extractData(rsToUse);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
		}
		@Override
		public String getSql() {
			return sql;
		}
	}
	return execute(new QueryStatementCallback());
}
 

@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
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
	Assert.notNull(sql, "SQL must not be null");
	Assert.notNull(rse, "ResultSetExtractor must not be null");
	if (logger.isDebugEnabled()) {
		logger.debug("Executing SQL query [" + sql + "]");
	}
	class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
		@Override
		public T doInStatement(Statement stmt) throws SQLException {
			ResultSet rs = null;
			try {
				rs = stmt.executeQuery(sql);
				ResultSet rsToUse = rs;
				if (nativeJdbcExtractor != null) {
					rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
				}
				return rse.extractData(rsToUse);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
		}
		@Override
		public String getSql() {
			return sql;
		}
	}
	return execute(new QueryStatementCallback());
}
 

@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++];
}
 

public void buildDatabaseTables(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
	Connection conn=null;
	ResultSet rs = null;
	try{
		conn=buildConnection(req);
		DatabaseMetaData metaData = conn.getMetaData();
		String url = metaData.getURL();
		String schema = null;
		if (url.toLowerCase().contains("oracle")) {
			schema = metaData.getUserName();
		}
		List<Map<String,String>> tables = new ArrayList<Map<String,String>>();
		rs = metaData.getTables(null, schema, "%", new String[] { "TABLE","VIEW" });
		while (rs.next()) {
			Map<String,String> table = new HashMap<String,String>();
			table.put("name",rs.getString("TABLE_NAME"));
			table.put("type",rs.getString("TABLE_TYPE"));
			tables.add(table);
		}
		writeObjectToJson(resp, tables);
	}catch(Exception ex){
		throw new ServletException(ex);
	}finally{
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeConnection(conn);
	}
}
 

@Override
public List<TableInfo> findTableInfos(String dbInfoId) throws Exception {
	List<TableInfo> tablesList = new ArrayList<TableInfo>();
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	Connection conn = null;
	ResultSet rs = null;
	try {
		conn = DataSourceUtils.getConnection(ds);
		DatabaseMetaData metaData = conn.getMetaData();
		String url = metaData.getURL();
		String schema = null;
		if (url.toLowerCase().contains("oracle")) {
			String value = Configure.getString("bdf2.default.schema");
			if (StringUtils.hasText(value)) {
				schema = value;
			} else {
				schema = metaData.getUserName();
			}
		}
		rs = metaData.getTables(null, schema, "%", new String[] { "TABLE" });
		TableInfo tableInfo = null;
		while (rs.next()) {
			tableInfo = new TableInfo();
			tableInfo.setTableName(rs.getString("TABLE_NAME"));
			tableInfo.setDbInfoId(dbInfoId);
			tablesList.add(tableInfo);
		}
		return tablesList;
	} finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeConnection(conn);
	}
}
 

/**
 * Method supporting the meta-data processing for a table's columns.
 */
private void processTableColumns(DatabaseMetaData databaseMetaData, TableMetaData tmd) {
	ResultSet tableColumns = null;
	String metaDataCatalogName = metaDataCatalogNameToUse(tmd.getCatalogName());
	String metaDataSchemaName = metaDataSchemaNameToUse(tmd.getSchemaName());
	String metaDataTableName = tableNameToUse(tmd.getTableName());
	if (logger.isDebugEnabled()) {
		logger.debug("Retrieving meta-data for " + metaDataCatalogName + '/' +
				metaDataSchemaName + '/' + metaDataTableName);
	}
	try {
		tableColumns = databaseMetaData.getColumns(
				metaDataCatalogName, metaDataSchemaName, metaDataTableName, null);
		while (tableColumns.next()) {
			String columnName = tableColumns.getString("COLUMN_NAME");
			int dataType = tableColumns.getInt("DATA_TYPE");
			if (dataType == Types.DECIMAL) {
				String typeName = tableColumns.getString("TYPE_NAME");
				int decimalDigits = tableColumns.getInt("DECIMAL_DIGITS");
				// Override a DECIMAL data type for no-decimal numerics
				// (this is for better Oracle support where there have been issues
				// using DECIMAL for certain inserts (see SPR-6912))
				if ("NUMBER".equals(typeName) && decimalDigits == 0) {
					dataType = Types.NUMERIC;
					if (logger.isDebugEnabled()) {
						logger.debug("Overriding meta-data: " + columnName + " now NUMERIC instead of DECIMAL");
					}
				}
			}
			boolean nullable = tableColumns.getBoolean("NULLABLE");
			TableParameterMetaData meta = new TableParameterMetaData(columnName, dataType, nullable);
			this.tableParameterMetaData.add(meta);
			if (logger.isDebugEnabled()) {
				logger.debug("Retrieved meta-data: " + meta.getParameterName() + " " +
						meta.getSqlType() + " " + meta.isNullable());
			}
		}
	}
	catch (SQLException ex) {
		if (logger.isWarnEnabled()) {
			logger.warn("Error while retrieving meta-data for table columns: " + ex.getMessage());
		}
	}
	finally {
		JdbcUtils.closeResultSet(tableColumns);
	}
}
 

@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++];
}
 

/**
 * Method supporting the meta-data processing for a table's columns.
 */
private void processTableColumns(DatabaseMetaData databaseMetaData, TableMetaData tmd) {
	ResultSet tableColumns = null;
	String metaDataCatalogName = metaDataCatalogNameToUse(tmd.getCatalogName());
	String metaDataSchemaName = metaDataSchemaNameToUse(tmd.getSchemaName());
	String metaDataTableName = tableNameToUse(tmd.getTableName());
	if (logger.isDebugEnabled()) {
		logger.debug("Retrieving meta-data for " + metaDataCatalogName + '/' +
				metaDataSchemaName + '/' + metaDataTableName);
	}
	try {
		tableColumns = databaseMetaData.getColumns(
				metaDataCatalogName, metaDataSchemaName, metaDataTableName, null);
		while (tableColumns.next()) {
			String columnName = tableColumns.getString("COLUMN_NAME");
			int dataType = tableColumns.getInt("DATA_TYPE");
			if (dataType == Types.DECIMAL) {
				String typeName = tableColumns.getString("TYPE_NAME");
				int decimalDigits = tableColumns.getInt("DECIMAL_DIGITS");
				// Override a DECIMAL data type for no-decimal numerics
				// (this is for better Oracle support where there have been issues
				// using DECIMAL for certain inserts (see SPR-6912))
				if ("NUMBER".equals(typeName) && decimalDigits == 0) {
					dataType = Types.NUMERIC;
					if (logger.isDebugEnabled()) {
						logger.debug("Overriding meta-data: " + columnName + " now NUMERIC instead of DECIMAL");
					}
				}
			}
			boolean nullable = tableColumns.getBoolean("NULLABLE");
			TableParameterMetaData meta = new TableParameterMetaData(columnName, dataType, nullable);
			this.tableParameterMetaData.add(meta);
			if (logger.isDebugEnabled()) {
				logger.debug("Retrieved meta-data: " + meta.getParameterName() + " " +
						meta.getSqlType() + " " + meta.isNullable());
			}
		}
	}
	catch (SQLException ex) {
		if (logger.isWarnEnabled()) {
			logger.warn("Error while retrieving meta-data for table columns: " + ex.getMessage());
		}
	}
	finally {
		JdbcUtils.closeResultSet(tableColumns);
	}
}
 

@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.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() + " (" + getDummyName() + ") values(null)");
				ResultSet rs = stmt.executeQuery("select IDENTITY_VAL_LOCAL() from " + getIncrementerName());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("IDENTITY_VAL_LOCAL() 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 value", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

@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++];
}