1. Overview
The Java Database Connectivity (JDBC) API provides access to the database from a Java application. We can use JDBC to connect to any database as long as the supported JDBC driver is available.
The ResultSet is a table of data generated by executing database queries. In this tutorial, we’re going to take a deeper look at the ResultSet API.
2. Generating a ResultSet
First, we retrieve a ResultSet by calling executeQuery() on any object implementing the Statement interface. Both the PreparedStatement and the CallableStatement are subinterfaces of Statement:
PreparedStatement pstmt = dbConnection.prepareStatement("select * from employees");
ResultSet rs = pstmt.executeQuery();
The ResultSet object maintains a cursor that points to the current row of the result set. We’ll use next() on our ResultSet to iterate through the records.
Next, we’ll use the getX() methods while iterating through the results to fetch the values from the database columns, where X is the datatype of the column. In fact, we’ll provide database column names to the getX() methods:
while(rs.next()) {
String name = rs.getString("name");
Integer empId = rs.getInt("emp_id");
Double salary = rs.getDouble("salary");
String position = rs.getString("position");
}
Likewise, the index number of the column can be used with the getX() methods instead of the column name. The index number is the sequence of the columns in the SQL select statement.
If the select statement doesn’t list column names, the index number is the sequence of columns in the table. The column index numbering starts from one:
Integer empId = rs.getInt(1);
String name = rs.getString(2);
String position = rs.getString(3);
Double salary = rs.getDouble(4);
In this section, we’ll see how to retrieve information about the column properties and types in a ResultSet.
First, let’s use the getMetaData() method on our ResultSet to obtain the ResultSetMetaData:
ResultSetMetaData metaData = rs.getMetaData();
Next, let’s get the number of columns that are in our ResultSet:
Integer columnCount = metaData.getColumnCount();
Furthermore, we can use any of the below methods on our metadata object to retrieve properties of each column:
- getColumnName(int columnNumber) – to get the name of the column
- getColumnLabel(int columnNumber) – to access the label of the column, which is specified after AS in the SQL query
- getTableName(int columnNumber) – to get the table name this column belongs to
- getColumnClassName(int columnNumber) – to acquire the Java data type of the column
- getColumnTypeName(int columnNumber) – to get the data type of the column in the database
- getColumnType(int columnNumber) – to get the SQL data type of the column
- isAutoIncrement(int columnNumber) – indicates whether the column is auto increment
- isCaseSensitive(int columnNumber) – specifies whether the column case matters
- isSearchable(int columnNumber) – suggests if we can use the column in the where clause of the SQL query
- isCurrency(int columnNumber) – signals if the column contains a cash value
- isNullable(int columnNumber) – returns zero if the column cannot be null, one if the column can contain a null value, and two if nullability of the column is unknown
- isSigned(int columnNumber) – returns true if values in the column are signed, otherwise returns false
Let’s iterate through the columns to get their properties:
for (int columnNumber = 1; columnNumber <= columnCount; columnNumber++) {
String catalogName = metaData.getCatalogName(columnNumber);
String className = metaData.getColumnClassName(columnNumber);
String label = metaData.getColumnLabel(columnNumber);
String name = metaData.getColumnName(columnNumber);
String typeName = metaData.getColumnTypeName(columnNumber);
int type = metaData.getColumnType(columnNumber);
String tableName = metaData.getTableName(columnNumber);
String schemaName = metaData.getSchemaName(columnNumber);
boolean isAutoIncrement = metaData.isAutoIncrement(columnNumber);
boolean isCaseSensitive = metaData.isCaseSensitive(columnNumber);
boolean isCurrency = metaData.isCurrency(columnNumber);
boolean isDefiniteWritable = metaData.isDefinitelyWritable(columnNumber);
boolean isReadOnly = metaData.isReadOnly(columnNumber);
boolean isSearchable = metaData.isSearchable(columnNumber);
boolean isReadable = metaData.isReadOnly(columnNumber);
boolean isSigned = metaData.isSigned(columnNumber);
boolean isWritable = metaData.isWritable(columnNumber);
int nullable = metaData.isNullable(columnNumber);
}
4. Navigating the ResultSet
When we obtain a ResultSet, the position of the cursor is before the first row. Moreover, by default, the ResultSet moves only in the forward direction. But, we can use a scrollable ResultSet for other navigation options.
In this section, we’ll discuss the various navigation options.
4.1. ResultSet Types
ResultSet type indicates how we’ll steer through the dataset:
- TYPE_FORWARD_ONLY – the default option, in which the cursor moves from start to end
- TYPE_SCROLL_INSENSITIVE – our cursor can move through the dataset in both forward and backward directions; if there are changes to the underlying data while moving through the dataset, they are ignored; the dataset contains the data from the time the database query returns the result
- TYPE_SCROLL_SENSITIVE – similar to the scroll insensitive type, however for this type, the dataset immediately reflects any changes to the underlying data
Not all databases support all the ResultSet types. So, let’s check if the type is supported by using the supportsResultSetType on our DatabaseMetaData object:
DatabaseMetaData dbmd = dbConnection.getMetaData();
boolean isSupported = dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
To get a scrollable ResultSet, we need to pass some additional parameters while preparing the Statement.
For example, we would obtain a scrollable ResultSet by using either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE as a ResultSet type:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
4.3. Navigation Options
We can use any of the below options on a scrollable ResultSet:
- next() – proceeds to the next row from the current position
- previous() – traverses to the previous row
- first() – navigates to the first row of the ResultSet
- last() – jumps to the last row
- beforeFirst() – moves to the start; calling next() on our ResultSet after calling this method returns the first row from our ResultSet
- afterLast() – leaps to the end; calling previous() on our ResultSet after executing this method returns the last row from our ResultSet
- relative(int numOfRows) – go forward or backward from the current position by the numOfRows
- absolute(int rowNumber) – jumps to the rowNumber specified
Let’s see some examples:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
// iterate through the results from first to last
}
rs.beforeFirst(); // jumps back to the starting point, before the first row
rs.afterLast(); // jumps to the end of resultset
rs.first(); // navigates to the first row
rs.last(); // goes to the last row
rs.absolute(2); //jumps to 2nd row
rs.relative(-1); // jumps to the previous row
rs.relative(2); // jumps forward two rows
while (rs.previous()) {
// iterates from current row to the first row in backward direction
}
4.4. ResultSet Row Count
Let’s use getRow() to get the current row number of our ResultSet.
First, we’ll navigate to the last row of the ResultSet and then use getRow() to get the number of records:
rs.last();
int rowCount = rs.getRow();
5. Updating Data in a ResultSet
By default, the ResultSet is read-only. However, we can use an updatable ResultSet to insert, update, and delete the rows.
5.1. ResultSet Concurrency
The concurrency mode indicates if our ResultSet can update the data.
The CONCUR_READ_ONLY option is the default and should be used if we don’t need to update the data using our ResultSet.
However, if we need to update the data in our ResultSet, then the CONCUR_UPDATABLE option should be used.
Not all databases support all the concurrency modes for all ResultSet types. Therefore, we need to check if our desired type and concurrency mode are supported using the supportsResultSetConcurrency() method:
DatabaseMetaData dbmd = dbConnection.getMetaData();
boolean isSupported = dbmd.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
5.2. Obtaining an Updatable ResultSet
To obtain an updatable ResultSet, we need to pass an additional parameter when we prepare the Statement. For that, let’s use CONCUR_UPDATABLE as the third parameter while creating a statement:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
5.3. Updating a Row
In this section, we’ll update a row using the updatable ResultSet created in the previous section.
We can update data in a row by calling updateX() methods, passing the column names and values to update. We can use any supported data type in place of X in the updateX() method.
Let’s update the “salary” column, which is of type double:
rs.updateDouble("salary", 1100.0);
Note that this just updates the data in the ResultSet, but the modifications are not yet saved back to the database.
Finally, let’s call updateRow() to save the updates to the database:
rs.updateRow();
Instead of the column names, we can pass the column index to the updateX() methods. This is similar to using the column index for getting the values using getX() methods. Passing either the column name or index to the updateX() methods yields the same result:
rs.updateDouble(4, 1100.0);
rs.updateRow();
5.4. Inserting a Row
Now, let’s insert a new row using our updatable ResultSet.
First, we’ll use moveToInsertRow() to move the cursor to insert a new row:
rs.moveToInsertRow();
Next, we must call updateX() methods to add the information to the row. We need to provide data to all the columns in the database table. If we don’t provide data to every column, then the default column value is used:
rs.updateString("name", "Venkat");
rs.updateString("position", "DBA");
rs.updateDouble("salary", 925.0);
Then, let’s call insertRow() to insert a new row into the database:
rs.insertRow();
Finally, let’s use moveToCurrentRow(). This will take the cursor position back to the row we were at before we started inserting a new row using the moveToInsertRow() method:
rs.moveToCurrentRow();
5.5. Deleting a Row
In this section, we’ll delete a row using our updatable ResultSet.
First, we’ll navigate to the row we want to delete. Then, we’ll call the deleteRow() method to delete the current row:
rs.absolute(2);
rs.deleteRow();
6. Holdability
The holdability determines if our ResultSet will be open or closed at the end of a database transaction.
6.1. Holdability Types
Use CLOSE_CURSORS_AT_COMMIT if the ResultSet is not required after the transaction is committed.
Use HOLD_CURSORS_OVER_COMMIT to create a holdable ResultSet. A holdable ResultSet is not closed even after the database transaction is committed.
Not all databases support all the holdability types.
So, let’s check if the holdability type is supported using supportsResultSetHoldability() on our DatabaseMetaData object. Then, we’ll get the default holdability of the database using getResultSetHoldability():
boolean isCloseCursorSupported
= dbmd.supportsResultSetHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
boolean isOpenCursorSupported
= dbmd.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
boolean defaultHoldability
= dbmd.getResultSetHoldability();
6.2. Holdable ResultSet
To create a holdable ResultSet, we need to specify the holdability type as the last parameter while creating a Statement. This parameter is specified after the concurrency mode.
Note that if we’re using Microsoft SQL Server (MSSQL), we have to set holdability on the database connection, rather than on the ResultSet:
dbConnection.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
Let’s see this in action. First, let’s create a Statement, setting the holdability to HOLD_CURSORS_OVER_COMMIT:
Statement pstmt = dbConnection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE,
ResultSet.HOLD_CURSORS_OVER_COMMIT)
Now, let’s update a row while retrieving the data. This is similar to the update example we discussed earlier, except that we’ll continue to iterate through the ResultSet after committing the update transaction to the database. This works fine on both MySQL and MSSQL databases:
dbConnection.setAutoCommit(false);
ResultSet rs = pstmt.executeQuery("select * from employees");
while (rs.next()) {
if(rs.getString("name").equalsIgnoreCase("john")) {
rs.updateString("name", "John Doe");
rs.updateRow();
dbConnection.commit();
}
}
rs.last();
It’s worth noting that MySQL supports only HOLD_CURSORS_OVER_COMMIT. So, even if we use CLOSE_CURSORS_AT_COMMIT, it will be ignored.
The MSSQL database supports CLOSE_CURSORS_AT_COMMIT. This means that the ResultSet will be closed when we commit the transaction. As a result, an attempt to access the ResultSet after committing the transaction results in a ‘Cursor is not open error’. Therefore, we can’t retrieve further records from the ResultSet.
7. Fetch Size
Typically, when loading data into a ResultSet, the database drivers decide on the number of rows to fetch from the database. On a MySQL database, for example, the ResultSet normally loads all the records into memory at once.
Sometimes, however, we may need to deal with a large number of records that won’t fit into our JVM memory. In this case, we can use the fetch size property either on our Statement or ResultSet objects to limit the number of records initially returned.
Whenever additional results are required, ResultSet fetches another batch of records from the database. Using the fetch size property, we can provide a suggestion to the database driver on the number of rows to fetch per database trip. The fetch size we specify will be applied to the subsequent database trips.
If we don’t specify the fetch size for our ResultSet, then the fetch size of the Statement is used. If we don’t specify fetch size for either the Statement or the ResultSet, then the database default is used.
7.1. Using Fetch Size on Statement
Now, let’s see the fetch size on Statement in action. We’ll set the fetch size of the Statement to 10 records. If our query returns 100 records, then there will be 10 database round trips, loading 10 records each time:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(10);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
// iterate through the resultset
}
7.2. Using Fetch Size on ResultSet
Now, let’s change the fetch size in our previous example using the ResultSet.
First, we’ll use the fetch size on our Statement. This allows our ResultSet to initially load 10 records after executing the query.
Then, we’ll modify the fetch size on the ResultSet. This will override the fetch size we earlier specified on our Statement. So, all the subsequent trips will load 20 records until all the records are loaded.
As a result, there will be only 6 database trips to load all the records:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(10);
ResultSet rs = pstmt.executeQuery();
rs.setFetchSize(20);
while (rs.next()) {
// iterate through the resultset
}
Finally, we’ll see how to modify the fetch size of the ResultSet while iterating the results.
Similar to the previous example, we’ll first set the fetch size to 10 on our Statement. So, our first 3 database trips will load 10 records per each trip.
And then, we’ll modify the fetch size on our ResultSet to 20 while reading the 30th record. So, the next 4 trips will load 20 records per each trip.
Therefore, we’ll need 7 database trips to load all 100 records:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(10);
ResultSet rs = pstmt.executeQuery();
int rowCount = 0;
while (rs.next()) {
// iterate through the resultset
if (rowCount == 30) {
rs.setFetchSize(20);
}
rowCount++;
}
8. Conclusion
In this article, we saw how to use the ResultSet API for retrieving and updating data from a database. Several of the advanced features we discussed are dependent on the database we’re using. Thus, we need to check the support for those features before using them.
As always, the code is available over on GitHub.
