How to Use Updatable Result Sets with JDBC

In this JDBC tutorial, you will learn how to use updatable result sets in JDBC. By default, result sets are not updatable if you create a Statement object with no-argument constructor like this:

Statement statement = connection.createStatement();

or if you create a PreparedStatement object like this:

PreparedStatement statement = connection.prepareStatement(sql);

So to use updatable result sets, you must specify the appropriate result set type and result set concurrency values when create a Statement object:

Statement statement = connection.createStatement(int resultSetType, int resultSetConcurrency);

and for a prepared statement:

PreparedStatement statement = connection.prepareStatement(
			String sql, int resultSetType, int resultSetConcurrency);

Remember the possible values for result set type are defined by the following constants in the ResultSet interface:

- TYPE_FORWARD_ONLY: the result set is not scrollable (default).

- TYPE_SCROLL_INSENSITIVE: the result set is scrollable but not sensitive to database changes.

- TYPE_SCROLL_SENSITIVE: the result set is scrollable and sensitive to database changes.

and the possible values for the result set concurrency:

- CONCUR_READ_ONLY: the result set cannot be used to update the database (default).

- CONCUR_UPDATABLE: the result set can be used to update the database.

For example, the following code creates a Statement object that will produce updatable result sets which are scrollable and insensitive to database changes:

Statement statement = connection.createStatement(
			ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

and for a PreparedStatement object:

PreparedStatement statement = connection.prepareStatement(sql,
			ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

Then the result set returned is updatable:

ResultSet result = statement.executeQuery(sql);

It’s also recommended to check if the underlying database supports updatable result sets or not, for example:

DatabaseMetaData metadata = connection.getMetaData();

boolean isUpdatable = metadata.supportsResultSetConcurrency(
		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

if (!isUpdatable) {
	// exit
}

// continue

1. Updating the Current Row in the Result Set

You can update data of the current row in the result set by calling updateXXX() methods:

updateString(int columnIndex, String x)
updateString(String columnLabel, String x)
updateInt(int columnIndex, int x)
updateInt(String columnLabel, int x)
....

As you can see, you can specify the column in the current row by using either column index or column label. Note that columnIndex is the order of the column in the result set, which may be different than the order in the actual database.

It’s recommended to specify columns by their names (labels) instead of indexes for code readability and correctness.

After calling updateXXX() methods, call the updateRow() method to commit the changes to the database.

For example, the following code snippet executes a SQL SELECT query to get all rows from the student table, and then update the 3^rd row:

String sql = "SELECT * FROM student";

Statement statement = conn.createStatement(
		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);


ResultSet result = statement.executeQuery(sql);

result.absolute(3);

result.updateString("name", "New Name");
result.updateString("email", "newemail@gmail.com");
result.updateString("major", "New Major");

result.updateRow();

2. Inserting a New Row in the Result Set

The following code snippet shows you how to insert a new row in the result set:

result.moveToInsertRow();

result.updateString("name", "New Name");
result.updateString("email", "newemail@gmail.com");
result.updateString("major", "New Major");

result.insertRow();

result.moveToCurrentRow();

You see, first you need to move the cursor to the appropriate position by calling moveToInsertRow(). Use updateXXX() methods to specify values for columns in the row, and then call insertRow() to save changes to the database.

The method moveToCurrentRow() moves the cursor back to the remembered position before the new row was inserted.

3. Deleting the Current Row in the Result Set

To remove the current row in the result set, simply call deleteRow(). For example:

result.deleteRow();

NOTE: The updateRow(), insertRow() and deleteRow() methods throw SQLException if a database access error occurs, or the result set is in read-only mode, or they are called on a closed result set.

That’s how to perform changes on an updatable result set. Let’s see a complete example program below.

4. Updatable ResultSet Example Program

The following program retrieves all rows from the student table in a MySQL database schema named college. It allows the user to enter a row number to see the details of that row. Then it asks if the user wishes to update, delete or insert row (type ‘y’ to confirm).

Here’s the complete code of the program:

import java.sql.*;
import java.io.*;

/**
 * This program demonstrates how to use updatable result sets with JDBC.
 * @author www.codejava.net
 */
public class UpdatableResultSetExample {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/college";
		String username = "root";
		String password = "password";

		Console console = System.console();

		try (Connection conn = DriverManager.getConnection(url, username, password)) {

			DatabaseMetaData metadata = conn.getMetaData();

			boolean isUpdatable = metadata.supportsResultSetConcurrency(
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

			if (!isUpdatable) {
				System.out.println("The database does not support updatable result sets.");
				return;
			}

			String sql = "SELECT * FROM student";

			Statement statement = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);


			ResultSet result = statement.executeQuery(sql);

			int row = -1;

			while (row != 0) {
				row = Integer.parseInt(console.readLine("Enter row number: "));

				if (result.absolute(row)) {
					readStudentInfo("Student at row " + row + ": ", result);

					String answer = console.readLine("Do you want to update this row (Y/N)?: ");

					if (answer.equalsIgnoreCase("Y")) {
						String name = console.readLine("\tUpdate name: ");
						String email = console.readLine("\tUpdate email: ");
						String major = console.readLine("\tUpdate major: ");

						result.updateString("name", name);
						result.updateString("email", email);
						result.updateString("major", major);
						result.updateRow();

						System.out.println("The student at row " + row + " has been updated.");
					}

					answer = console.readLine("Do you want to delete this row (Y/N)?: ");

					if (answer.equalsIgnoreCase("Y")) {
						result.deleteRow();
						System.out.println("The student at row " + row + " has been deleted.");
					}

					answer = console.readLine("Do you want to insert new row (Y/N)?: ");

					if (answer.equalsIgnoreCase("Y")) {
						result.moveToInsertRow();

						String name = console.readLine("\tUpdate name: ");
						String email = console.readLine("\tUpdate email: ");
						String major = console.readLine("\tUpdate major: ");

						result.updateString("name", name);
						result.updateString("email", email);
						result.updateString("major", major);
						result.insertRow();

						result.moveToCurrentRow();

						System.out.println("The new student has been inserted.");
					}

				} else {
					System.out.println("There's no student at row " + row);
				}
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		}

	}

	private static void readStudentInfo(String position, ResultSet result)
			throws SQLException {
		String name = result.getString("name");
		String email = result.getString("email");
		String major = result.getString("major");

		String studentInfo = "%s: %s - %s - %s\n";
		System.out.format(studentInfo, position, name, email, major);
	}
}

This program runs in an infinite loop until the user enters row number 0 to quit.

So far you have understood how to use updatable result sets in JDBC and the benefits: You can perform CRUD operations on the result set without writing any SQL statements - it’s a very convenient and time-saving features, isn’t it?

API References:

• ResultSet Javadoc
• Statement Javadoc
• DatabaseMetaData Javadoc 

Related JDBC Tutorials:

• How to use Scrollable Result Sets with JDBC
• How to Use CachedRowSet in JDBC

Other JDBC Tutorials:

• How to connect to a database with JDBC
• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC
• How to insert binary data into database with JDBC
• How to read binary data from database with JDBC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.