The Java Tutorials have been written for JDK 8. Examples and practices described in this page don't take advantage of improvements introduced in later releases and might use technology no longer available.
See Java Language Changes for a summary of updated language features in Java SE 9 and subsequent releases.
See JDK Release Notes for information about new features, enhancements, and removed or deprecated options for all JDK releases.
The following method,
CoffeesTable.viewTable
, outputs the contents of the COFFEES
tables, and demonstrates the use of ResultSet
objects and cursors:
public static void viewTable(Connection con) throws SQLException { String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) { ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } }
A ResultSet
object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. For example, the CoffeeTables.viewTable
method creates a ResultSet
, rs
, when it executes the query through the Statement
object, stmt
. Note that a ResultSet
object can be created through any object that implements the Statement
interface, including PreparedStatement
, CallableStatement
, and RowSet
.
You access the data in a ResultSet
object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet
. Initially, the cursor is positioned before the first row. The method ResultSet.next
moves the cursor to the next row. This method returns false
if the cursor is positioned after the last row. This method repeatedly calls the ResultSet.next
method with a while
loop to iterate through all the data in the ResultSet
.
This page covers the following topics:
The ResultSet
interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet
objects can have different functionality and characteristics. These characteristics are type, concurrency, and cursor holdability.
The type of a ResultSet
object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet
object.
The sensitivity of a ResultSet
object is determined by one of three different ResultSet
types:
TYPE_FORWARD_ONLY
: The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.TYPE_SCROLL_INSENSITIVE
: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.TYPE_SCROLL_SENSITIVE
: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.The default ResultSet
type is TYPE_FORWARD_ONLY
.
Note: Not all databases and JDBC drivers support all ResultSet
types. The method DatabaseMetaData.supportsResultSetType
returns true
if the specified ResultSet
type is supported and false
otherwise.
The concurrency of a ResultSet
object determines what level of update functionality is supported.
There are two concurrency levels:
CONCUR_READ_ONLY
: The ResultSet
object cannot be updated using the ResultSet
interface.CONCUR_UPDATABLE
: The ResultSet
object can be updated using the ResultSet
interface.The default ResultSet
concurrency is CONCUR_READ_ONLY
.
Note: Not all JDBC drivers and databases support concurrency. The method DatabaseMetaData.supportsResultSetConcurrency
returns true
if the specified concurrency level is supported by the driver and false
otherwise.
The method
CoffeesTable.modifyPrices
demonstrates how to use a ResultSet
object whose concurrency level is CONCUR_UPDATABLE
.
Calling the method Connection.commit
can close the ResultSet
objects that have been created during the current transaction. In some cases, however, this may not be the desired behavior. The ResultSet
property holdability gives the application control over whether ResultSet
objects (cursors) are closed when commit is called.
The following ResultSet
constants may be supplied to the Connection
methods createStatement
, prepareStatement
, and prepareCall
:
HOLD_CURSORS_OVER_COMMIT
: ResultSet
cursors are not closed; they are holdable: they are held open when the method commit
is called. Holdable cursors might be ideal if your application uses mostly read-only ResultSet
objects.CLOSE_CURSORS_AT_COMMIT
: ResultSet
objects (cursors) are closed when the commit
method is called. Closing cursors when this method is called can result in better performance for some applications.The default cursor holdability varies depending on your DBMS.
Note: Not all JDBC drivers and databases support holdable and non-holdable cursors. The following method, JDBCTutorialUtilities.cursorHoldabilitySupport
, outputs the default cursor holdability of ResultSet
objects and whether HOLD_CURSORS_OVER_COMMIT
and CLOSE_CURSORS_AT_COMMIT
are supported:
public static void cursorHoldabilitySupport(Connection conn) throws SQLException { DatabaseMetaData dbMetaData = conn.getMetaData(); System.out.println("ResultSet.HOLD_CURSORS_OVER_COMMIT = " + ResultSet.HOLD_CURSORS_OVER_COMMIT); System.out.println("ResultSet.CLOSE_CURSORS_AT_COMMIT = " + ResultSet.CLOSE_CURSORS_AT_COMMIT); System.out.println("Default cursor holdability: " + dbMetaData.getResultSetHoldability()); System.out.println("Supports HOLD_CURSORS_OVER_COMMIT? " + dbMetaData.supportsResultSetHoldability( ResultSet.HOLD_CURSORS_OVER_COMMIT)); System.out.println("Supports CLOSE_CURSORS_AT_COMMIT? " + dbMetaData.supportsResultSetHoldability( ResultSet.CLOSE_CURSORS_AT_COMMIT)); }
The ResultSet
interface declares getter methods (for example, getBoolean
and getLong
) for retrieving column values from the current row. You can retrieve values using either the index number of the column or the alias or name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.
For example, the following method,
CoffeesTable.alternateViewTable
, retrieves column values by number:
public static void alternateViewTable(Connection con) throws SQLException { String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) { ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String coffeeName = rs.getString(1); int supplierID = rs.getInt(2); float price = rs.getFloat(3); int sales = rs.getInt(4); int total = rs.getInt(5); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } }
Strings used as input to getter methods are case-insensitive. When a getter method is called with a string and more than one column has the same alias or name as the string, the value of the first matching column is returned. The option to use a string as opposed to an integer is designed to be used when column aliases and names are used in the SQL query that generated the result set. For columns that are not explicitly named in the query (for example, select * from COFFEES
) it is best to use column numbers. If column names are used, the developer should guarantee that they uniquely refer to the intended columns by using column aliases. A column alias effectively renames the column of a result set. To specify a column alias, use the SQL AS
clause in the SELECT
statement.
The getter method of the appropriate type retrieves the value in each column. For example, in the method CoffeeTables.viewTable
, the first column in each row of the ResultSet
rs
is COF_NAME
, which stores a value of SQL type VARCHAR
. The method for retrieving a value of SQL type VARCHAR
is getString
. The second column in each row stores a value of SQL type INTEGER
, and the method for retrieving values of that type is getInt
.
Note that although the method getString
is recommended for retrieving the SQL types CHAR
and VARCHAR
, it is possible to retrieve any of the basic SQL types with it. Getting all values with getString
can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString
converts the numeric value to a Java String
object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Furthermore, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString
method.
As mentioned previously, you access the data in a ResultSet
object through a cursor, which points to one row in the ResultSet
object. However, when a ResultSet
object is first created, the cursor is positioned before the first row. The method CoffeeTables.viewTable
moves the cursor by calling the ResultSet.next
method. There are other methods available to move the cursor:
next
: Moves the cursor forward one row. Returns true
if the cursor is now positioned on a row and false
if the cursor is positioned after the last row.previous
: Moves the cursor backward one row. Returns true
if the cursor is now positioned on a row and false
if the cursor is positioned before the first row.first
: Moves the cursor to the first row in the ResultSet
object. Returns true
if the cursor is now positioned on the first row and false
if the ResultSet
object does not contain any rows.last:
: Moves the cursor to the last row in the ResultSet
object. Returns true
if the cursor is now positioned on the last row and false
if the ResultSet
object does not contain any rows.beforeFirst
: Positions the cursor at the start of the ResultSet
object, before the first row. If the ResultSet
object does not contain any rows, this method has no effect.afterLast
: Positions the cursor at the end of the ResultSet
object, after the last row. If the ResultSet
object does not contain any rows, this method has no effect.relative(int rows)
: Moves the cursor relative to its current position.absolute(int row)
: Positions the cursor on the row specified by the parameter row
.Note that the default sensitivity of a ResultSet
is TYPE_FORWARD_ONLY
, which means that it cannot be scrolled; you cannot call any of these methods that move the cursor, except next
, if your ResultSet
cannot be scrolled. The method CoffeesTable.modifyPrices
, described in the following section, demonstrates how you can move the cursor of a ResultSet
.
You cannot update a default ResultSet
object, and you can only move its cursor forward. However, you can create ResultSet
objects that can be scrolled (the cursor can move backwards or move to an absolute position) and updated.
The following method,
CoffeesTable.modifyPrices
, multiplies the PRICE
column of each row by the argument percentage
:
public void modifyPrices(float percentage) throws SQLException { try (Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) { ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES"); while (uprs.next()) { float f = uprs.getFloat("PRICE"); uprs.updateFloat("PRICE", f * percentage); uprs.updateRow(); } } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } }
The field ResultSet.TYPE_SCROLL_SENSITIVE
creates a ResultSet
object whose cursor can move both forward and backward relative to the current position and to an absolute position. The field ResultSet.CONCUR_UPDATABLE
creates a ResultSet
object that can be updated. See the ResultSet
Javadoc for other fields you can specify to modify the behavior of ResultSet
objects.
The method ResultSet.updateFloat
updates the specified column (in this example, PRICE
with the specified float
value in the row where the cursor is positioned. ResultSet
contains various updater methods that enable you to update column values of various data types. However, none of these updater methods modifies the database; you must call the method ResultSet.updateRow
to update the database.
Statement
, PreparedStatement
and CallableStatement
objects have a list of commands that is associated with them. This list may contain statements for updating, inserting, or deleting a row; and it may also contain DDL statements such as CREATE TABLE
and DROP TABLE
. It cannot, however, contain a statement that would produce a ResultSet
object, such as a SELECT
statement. In other words, the list can contain only statements that produce an update count.
The list, which is associated with a Statement
object at its creation, is initially empty. You can add SQL commands to this list with the method addBatch
and empty it with the method clearBatch
. When you have finished adding statements to the list, call the method executeBatch
to send them all to the database to be executed as a unit, or batch.
For example, the following method,
CoffeesTable.batchUpdate
, adds four rows to the COFFEES
table with a batch update:
public void batchUpdate() throws SQLException { con.setAutoCommit(false); try (Statement stmt = con.createStatement()) { stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)"); int[] updateCounts = stmt.executeBatch(); con.commit(); } catch (BatchUpdateException b) { JDBCTutorialUtilities.printBatchUpdateException(b); } catch (SQLException ex) { JDBCTutorialUtilities.printSQLException(ex); } finally { con.setAutoCommit(true); } }
The following line disables auto-commit mode for the Connection
object con so that the transaction will not be automatically committed or rolled back when the method executeBatch
is called.
con.setAutoCommit(false);
To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.
The method Statement.addBatch
adds a command to the list of commands associated with the Statement
object stmt
. In this example, these commands are all INSERT INTO
statements, each one adding a row consisting of five column values. The values for the columns COF_NAME
and PRICE
are the name of the coffee and its price, respectively. The second value in each row is 49 because that is the identification number for the supplier, Superior Coffee. The last two values, the entries for the columns SALES
and TOTAL
, all start out being zero because there have been no sales yet. (SALES
is the number of pounds of this row's coffee sold in the current week; TOTAL
is the total of all the cumulative sales of this coffee.)
The following line sends the four SQL commands that were added to its list of commands to the database to be executed as a batch:
int[] updateCounts = stmt.executeBatch();
Note that stmt
uses the method executeBatch
to send the batch of insertions, not the method executeUpdate
, which sends only one command and returns a single update count. The DBMS executes the commands in the order in which they were added to the list of commands, so it will first add the row of values for Amaretto, then add the row for Hazelnut, then Amaretto decaf, and finally Hazelnut decaf. If all four commands execute successfully, the DBMS will return an update count for each command in the order in which it was executed. The update counts that indicate how many rows were affected by each command are stored in the array updateCounts
.
If all four of the commands in the batch are executed successfully, updateCounts
will contain four values, all of which are 1 because an insertion affects one row. The list of commands associated with stmt
will now be empty because the four commands added previously were sent to the database when stmt
called the method executeBatch
. You can at any time explicitly empty this list of commands with the method clearBatch
.
The Connection.commit
method makes the batch of updates to the COFFEES
table permanent. This method needs to be called explicitly because the auto-commit mode for this connection was disabled previously.
The following line enables auto-commit mode for the current Connection
object.
con.setAutoCommit(true);
Now each statement in the example will automatically be committed after it is executed, and it no longer needs to invoke the method commit
.
It is also possible to have a parameterized batch update, as shown in the following code fragment, where con
is a Connection
object:
con.setAutoCommit(false); PreparedStatement pstmt = con.prepareStatement( "INSERT INTO COFFEES VALUES( " + "?, ?, ?, ?, ?)"); pstmt.setString(1, "Amaretto"); pstmt.setInt(2, 49); pstmt.setFloat(3, 9.99); pstmt.setInt(4, 0); pstmt.setInt(5, 0); pstmt.addBatch(); pstmt.setString(1, "Hazelnut"); pstmt.setInt(2, 49); pstmt.setFloat(3, 9.99); pstmt.setInt(4, 0); pstmt.setInt(5, 0); pstmt.addBatch(); // ... and so on for each new // type of coffee int[] updateCounts = pstmt.executeBatch(); con.commit(); con.setAutoCommit(true);
You will get a BatchUpdateException
when you call the method executeBatch
if (1) one of the SQL statements you added to the batch produces a result set (usually a query) or (2) one of the SQL statements in the batch does not execute successfully for some other reason.
You should not add a query (a SELECT
statement) to a batch of SQL commands because the method executeBatch
, which returns an array of update counts, expects an update count from each SQL statement that executes successfully. This means that only commands that return an update count (commands such as INSERT INTO
, UPDATE
, DELETE
) or that return 0 (such as CREATE TABLE
, DROP TABLE
, ALTER TABLE
) can be successfully executed as a batch with the executeBatch
method.
A BatchUpdateException
contains an array of update counts that is similar to the array returned by the method executeBatch
. In both cases, the update counts are in the same order as the commands that produced them. This tells you how many commands in the batch executed successfully and which ones they are. For example, if five commands executed successfully, the array will contain five numbers: the first one being the update count for the first command, the second one being the update count for the second command, and so on.
BatchUpdateException
is derived from SQLException
. This means that you can use all of the methods available to an SQLException
object with it. The following method,
JDBCTutorialUtilities.printBatchUpdateException
, prints all of the SQLException
information plus the update counts contained in a BatchUpdateException
object. Because BatchUpdateException.getUpdateCounts
returns an array of int
, the code uses a for
loop to print each of the update counts:
public static void printBatchUpdateException(BatchUpdateException b) { System.err.println("----BatchUpdateException----"); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Message: " + b.getMessage()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts: "); int[] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.err.print(updateCounts[i] + " "); } }
Note: Not all JDBC drivers support inserting new rows with the ResultSet
interface. If you attempt to insert a new row and your JDBC driver database does not support this feature, a SQLFeatureNotSupportedException
exception is thrown.
The following method,
CoffeesTable.insertRow
, inserts a row into the COFFEES
through a ResultSet
object:
public void insertRow(String coffeeName, int supplierID, float price, int sales, int total) throws SQLException { try (Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) { ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES"); uprs.moveToInsertRow(); uprs.updateString("COF_NAME", coffeeName); uprs.updateInt("SUP_ID", supplierID); uprs.updateFloat("PRICE", price); uprs.updateInt("SALES", sales); uprs.updateInt("TOTAL", total); uprs.insertRow(); uprs.beforeFirst(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } }
This example calls the Connection.createStatement
method with two arguments, ResultSet.TYPE_SCROLL_SENSITIVE
and ResultSet.CONCUR_UPDATABLE
. The first value enables the cursor of the ResultSet
object to be moved both forward and backward. The second value, ResultSet.CONCUR_UPDATABLE
, is required if you want to insert rows into a ResultSet
object; it specifies that it can be updatable.
The same stipulations for using strings in getter methods also apply to updater methods.
The method ResultSet.moveToInsertRow
moves the cursor to the insert row. The insert row is a special row associated with an updatable result set. It is essentially a buffer where a new row can be constructed by calling the updater methods prior to inserting the row into the result set. For example, this method calls the method ResultSet.updateString
to update the insert row's COF_NAME
column to Kona
.
The method ResultSet.insertRow
inserts the contents of the insert row into the ResultSet
object and into the database.
Note: After inserting a row with the ResultSet.insertRow
, you should move the cursor to a row other than the insert row. For example, this example moves it to before the first row in the result set with the method ResultSet.beforeFirst
. Unexpected results can occur if another part of your application uses the same result set and the cursor is still pointing to the insert row.