This JDBC tutorial helps you understand scrollable result set and how to use it with some code examples.

You know, in JDBC, a statement that executes a SQL SELECT query returns a result set represented by the java.sql.ResultSet object. By default, you can iterate over rows in the result set in forward direction only, from the first row to the last row, and you can’t update data in the result set.

What if you need to move forth and back in the result set, jumping on any row when needed? Also, wouldn’t it be more naturally to retrieve a result set and update its data against the database?

The JDBC API allows you to use such flexible result sets by making them scrollable and updatable.

Let’s start with scrollable result sets.

 

1. Understanding Scrollable Result Sets

By default, result sets are not scrollable or updatable. If you use the following code:

String sql = "SELECT * FROM student";

Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(sql);

The ResultSet object returned is not scrollableor updatable. To obtain a scrollable result set, you must create a different Statement object with the following method:

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

For a prepared statement, us the following method:

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

The possible values for resultSetType and resultSetConcurrency are defined by some constants in the ResultSet interface, which are described below.

 

2. ResultSet Type Values

You can create a Statement that returns result sets in one of the following types:

- 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.

 

3. ResultSet Concurrency Values

A Statement can return result sets which are read-only or updatable, specified by one of the following constants defined in the ResultSet interface:

- 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, if you want to scroll through the result set but don’t want to update its data, create Statement a like this:

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

Then the result set returned is now scrollable (but it doesn’t reflect to database changes once it is loaded with the data):

ResultSet result = statement.executeQuery(sql);

 

You can use the following methods to scroll through the result set:

- first(): moves the cursor to the first row.

- next(): moves the cursor forward one row from its current position.

- previous(): moves the cursor to the previous row.

- relative(int rows): moves the cursor a relative number of rows from its current position. The value of rows can be positive (move forward) or negative (move backward).

- absolute(int row): moves the cursor to the given row number. The value of row can be positive or negative. A positive number indicates the row number counting from the beginning of the result set. A negative number indicates the row number counting from the end of the result set.

 

4. Scrollable, Insensitive and Read-Only ResultSet Example

Let’s see a complete example program.

The following program executes a SQL SELECT statement to retrieve all rows from the table student in a MySQL database schema named college:

import java.sql.*;

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

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


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

			String sql = "SELECT * FROM student";

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

			ResultSet result = statement.executeQuery(sql);

			result.first();

			readStudentInfo("first", result);

			result.relative(3);

			readStudentInfo("relative(3)", result);

			result.previous();

			readStudentInfo("previous", result);

			result.absolute(4);

			readStudentInfo("absolute(4)", result);

			result.last();

			readStudentInfo("last", result);

			result.relative(-2);

			readStudentInfo("relative(-2)", result);


		} 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);
	}
}

As you can see, this program creates a Statement that returns scrollable, insensitive and read-only result sets. It demonstrates how to scroll through the result set using the navigation methods described above.

 

5. Checking ResultSet Types and ResultSet Concurrency Support

Note that not all databases support scrollable, sensitive and updatable result sets. So you should check whether the database supports these behaviors or not before processing the result sets.

The DatabaseMetaData interface provides methods for checking if the underlying database supports a certain result set type and concurrency. You can obtain a DatabaseMetaData object from the connection using the following statement:

DatabaseMetaData metadata = conn.getMetaData();

And use the following method to check if the database supports a certain result set type:

metadata.supportsResultSetType(int resultSetType)

Use the following method to check if the database supports a certain result set concurrency:

metadata.supportsResultSetConcurrency(int resultSetType, int resultSetConcurrency)

These methods return true if the specified type is supported, or false otherwise.

For example, the following code checks if the database supports scrollable and sensitive result sets:

boolean isScrollSensitive = metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println("Support Scroll Sensitive: " + isScrollSensitive);

 

To check if the database supports scrollable, changes-sensitive and updatable result sets, use the following code:

boolean isUpdatable = metadata.supportsResultSetConcurrency(
			ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
System.out.println("Support Updatable: " + isUpdatable);

 

6. Scrollable, Sensitive and Read-Only ResultSet Example

Let’s see another example. The following program uses a scrollable result set that is sensitive to database changes. It allows the user to enter a row number to jump on:

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

/**
 * This program demonstrates how to use scrollable result sets
 * that are sensitive to database changes with JDBC.
 * @author www.codejava.net
 */
public class ScrollableResultSetSensitiveExample {

	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 isScrollSensitive = metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);

			if (!isScrollSensitive) {
				System.out.println("The database doesn't support scrollable and sensitive result sets.");
				return;
			}

			String sql = "SELECT * FROM student";

			Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

			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);
				} 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);
	}
}

 

As you can see, this program allows the user to print data of any row in the result set. It repeats until the user types 0 to quit.

You can test the sensitive to database changes feature by running this program to query data at a given row number. Then use MySQL Command Line Client program to updates that row, and then come back to the program to query data from that row again, you will see the changes are reflected automatically.

 

Scrollable result sets that are sensitive to database changes is a useful feature that helps programmers write code easily with minimum effort (reducing the code that executes SQL statements).

Imagine you are developing database application which is used concurrently by multiple users. Besides the ability to scroll through the result set in any direction, changes to the database made by one user can be reflected to other users automatically. But make sure the database supports scrollable and sensitive result sets first.

 

API References:

• ResultSet Javadoc
• Statement Javadoc
• DatabaseMetaData Javadoc

 

Related JDBC Tutorials:

• How to Use Updatable 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