In this article, we will learn how to create a ResultSet interface and how to use ResultSet interface methods with examples.
JDBC ResultSet Interface Overview
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 a type, concurrency, and cursor holdability.
ResultSet Types
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.
ResultSet Concurrency
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.
Concurrency level - CONCUR_UPDATABLE Example
The below example demonstrates how to use a ResultSet object whose concurrency level is CONCUR_UPDATABLE.
public class ResultSetExamples {
public static void main(String[] args) {
modifyUserName();
// batchUpdate();
//parameterizedBatchUpdate();
//insertRowInResultSetObject();
}
private static void modifyUserName() {
String QUERY = "select id,name,email,country,password from Users where id = 1";
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// Step 3: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY)) {
// Step 4: Process the ResultSet object.
while (rs.next()) {
String name = rs.getString("name");
System.out.println(" User name before update : " + name);
rs.updateString("name", "Ramesh");
rs.updateRow();
System.out.println("User name after update : " + rs.getString("name"));
}
} catch (SQLException e) {
printSQLException(e);
}
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Output:
User name before update : Ras
User name after update : RameshCursor Holdability
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 a 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.
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));
}
Retrieving Column Values from Rows
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, a 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, main(), retrieves column values by number:
package com.javaguides.jdbc.statement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Select Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class SelectStatementExample {
private static final String QUERY = "select id,name,email,country,password from Users";
public static void main(String[] args) {
// using try-with-resources to avoid closing resources (boiler plate code)
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement stmt = connection.createStatement();
// Step 3: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY)) {
// Step 4: Process the ResultSet object.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
String country = rs.getString("country");
String password = rs.getString("password");
System.out.println(id + "," + name + "," + email + "," + country + "," + password);
}
} catch (SQLException e) {
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Output:
1,Ramesh,tony@gmail.com,US,secretUpdating Rows in ResultSet Objects
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 backward or move to an absolute position) and updated.
The following method, modifyUserName(), updates the existing name with a new name:
private static void modifyUserName() {
String QUERY = "select id,name,email,country,password from Users where id = 1";
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// Step 3: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY)) {
// Step 4: Process the ResultSet object.
while (rs.next()) {
String name = rs.getString("name");
System.out.println(" User name before update : " + name);
rs.updateString("name", "Ramesh");
rs.updateRow();
System.out.println("User name after update : " + rs.getString("name"));
}
} catch (SQLException e) {
printSQLException(e);
}
}
Output:
User name before update : Ramesh
User name after update : RamUsing Statement Objects for Batch Updates
A Statement, PreparedStatement, and CallableStatement objects have a list of commands that are associated with them. 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 batchUpdate() adds four rows to the Users table with a batch update:
package com.javaguides.jdbc.resultset;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
public class ResultSetExamples {
public static void main(String[] args) {
batchUpdate();
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
private static void batchUpdate() {
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement statement = connection.createStatement()) {
connection.setAutoCommit(false);
statement.addBatch("INSERT INTO Users VALUES (13, 'Pramod', 'pramod@gmail.com', 'India', '123');");
statement.addBatch("INSERT INTO Users VALUES (14, 'A', 'a@gmail.com', 'India', '123');");
statement.addBatch("INSERT INTO Users VALUES (15, 'B', 'b@gmail.com', 'India', '123');");
statement.addBatch("INSERT INTO Users VALUES (16, 'C', 'c@gmail.com', 'India', '123');");
statement.addBatch("INSERT INTO Users VALUES (17, 'D', 'd@gmail.com', 'India', '123');");
int[] updateCounts = statement.executeBatch();
System.out.println(Arrays.toString(updateCounts));
connection.commit();
} catch (BatchUpdateException batchUpdateException) {
printBatchUpdateException(batchUpdateException);
} catch (SQLException e) {
printSQLException(e);
}
}
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] + " ");
}
}
}
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.
connection.setAutoCommit(false);
To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.
Performing Parameterized Batch Update
It is also possible to have a parameterized batch update, as shown in the following code fragment, where the con is a Connection object:
private static void parameterizedBatchUpdate() {
String INSERT_USERS_SQL = "INSERT INTO users" + " (id, name, email, country, password) VALUES " +
" (?, ?, ?, ?, ?);";
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
connection.setAutoCommit(false);
preparedStatement.setInt(1, 20);
preparedStatement.setString(2, "a");
preparedStatement.setString(3, "a@gmail.com");
preparedStatement.setString(4, "India");
preparedStatement.setString(5, "secret");
preparedStatement.addBatch();
preparedStatement.setInt(1, 21);
preparedStatement.setString(2, "b");
preparedStatement.setString(3, "b@gmail.com");
preparedStatement.setString(4, "India");
preparedStatement.setString(5, "secret");
preparedStatement.addBatch();
preparedStatement.setInt(1, 22);
preparedStatement.setString(2, "c");
preparedStatement.setString(3, "c@gmail.com");
preparedStatement.setString(4, "India");
preparedStatement.setString(5, "secret");
preparedStatement.addBatch();
preparedStatement.setInt(1, 23);
preparedStatement.setString(2, "d");
preparedStatement.setString(3, "d@gmail.com");
preparedStatement.setString(4, "India");
preparedStatement.setString(5, "secret");
preparedStatement.addBatch();
int[] updateCounts = preparedStatement.executeBatch();
System.out.println(Arrays.toString(updateCounts));
connection.commit();
connection.setAutoCommit(true);
} catch (BatchUpdateException batchUpdateException) {
printBatchUpdateException(batchUpdateException);
} catch (SQLException e) {
printSQLException(e);
}
}
Inserting Rows in ResultSet Objects
The following method, insertRowInResultSetObject(), inserts a row into the Users table through a ResultSet object:
private static void insertRowInResultSetObject() {
String QUERY = "select id,name,email,country,password from Users where id = 1";
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// Step 3: Execute the query or update query
ResultSet uprs = stmt.executeQuery(QUERY)) {
uprs.moveToInsertRow();
uprs.updateInt(1, 25);
uprs.updateString(2, "b");
uprs.updateString(3, "b@gmail.com");
uprs.updateString(4, "India");
uprs.updateString(5, "secret");
uprs.insertRow();
uprs.beforeFirst();
} catch (SQLException e) {
printSQLException(e);
}
}
Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours
Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course