Scrollable ResultSet in JDBC example

A normal ResultSet object can only move in forward direction. But using the ResultSet.TYPE_SCROLL_SENSITIVE we can create Scrollable Resultset in JDBC.

What is Scrollable Resultset in JDBC

Scrollable Resultset is the resultset which can move forward or backward or move to any position when the ResultSet is open.

Types of Scrollable Resultset in JDBC

1. Sensitive ResultSet – Created using ResultSet.TYPE_SCROLL_SENSITIVE
A sensitive result set can see changes made to the database while the result set is open. This can be helpful to get the changes done to the table (columns or data) using the same resultSet.

2. Insensitive ResultSet – Created using ResultSet.TYPE_SCROLL_INSENSITIVE
An insensitive result set is not sensitive to changes made to the database while the result set is open. For any changes done to the table (columns or data) we will need a new resultSet.

Lets see an example of how Scrollable Resulset is used.

Example of Scrollable Resultset in JDBC

package com.kscodes.sampleproject.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ScrollableResultSetExample {

	public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
	public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";
	public static String CONNECTION_USER = "kscodes";
	public static String CONNECTION_PASSWORD = "kscodes";

	public static void main(String[] args) {

		String selectSql = "SELECT * FROM employee_details";
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			statement = connection
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			resultSet = statement.executeQuery(selectSql);
			
			System.out.println("***Lets First Iterate the Resulset in only forward direction");
			while(resultSet.next()){
				System.out.println("Employee Id - " + resultSet.getInt(1) + " Name is ::" + resultSet.getString(2));
			}
			System.out.println("***Now again go back to the first row and print the first Row");
			resultSet.beforeFirst();
			resultSet.next();
			System.out.println("Employee Id - " + resultSet.getInt(1) + " Name is ::" + resultSet.getString(2));
			
			System.out.println("***Now again go to the last row and print the data going backwards.");
			resultSet.afterLast();
			while(resultSet.previous()){
				System.out.println("Employee Id - " + resultSet.getInt(1) + " Name is ::" + resultSet.getString(2));
			}		
			

		} catch (SQLException e) {
			System.out.println("An exception occured.Exception is :: " + e);
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
			}
		}

	}

	public static Connection getConnection() {

		try {
			Class.forName(DRIVER_CLASS_NAME);
		} catch (ClassNotFoundException e) {
			System.out.println("Error while registering JDBC driver");
			return null;
		}

		Connection connection = null;
		try {
			connection = DriverManager.getConnection(CONNECTION_URL,
					CONNECTION_USER, CONNECTION_PASSWORD);

		} catch (SQLException e) {
			System.out.println("Failed to create Connection");
			return null;
		}
		System.out.println("Connection created Successfully.");

		return connection;
	}

}

package com.kscodes.sampleproject.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class ScrollableResultSetExample {

public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";

public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";

public static String CONNECTION_USER = "kscodes";

public static String CONNECTION_PASSWORD = "kscodes";

public static void main(String[] args) {

String selectSql = "SELECT * FROM employee_details";

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

try {

connection = getConnection();

statement = connection

.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

resultSet = statement.executeQuery(selectSql);

System.out.println("***Lets First Iterate the Resulset in only forward direction");

while(resultSet.next()){

System.out.println("Employee Id - " + resultSet.getInt(1) + " Name is ::" + resultSet.getString(2));

}

System.out.println("***Now again go back to the first row and print the first Row");

resultSet.beforeFirst();

resultSet.next();

System.out.println("Employee Id - " + resultSet.getInt(1) + " Name is ::" + resultSet.getString(2));

System.out.println("***Now again go to the last row and print the data going backwards.");

resultSet.afterLast();

while(resultSet.previous()){

System.out.println("Employee Id - " + resultSet.getInt(1) + " Name is ::" + resultSet.getString(2));

}

} catch (SQLException e) {

System.out.println("An exception occured.Exception is :: " + e);

} finally {

try {

if (resultSet != null) {

resultSet.close();

}

if (statement != null) {

statement.close();

}

if (connection != null) {

connection.close();

}

} catch (Exception e) {

}

public static Connection getConnection() {

try {

Class.forName(DRIVER_CLASS_NAME);

} catch (ClassNotFoundException e) {

System.out.println("Error while registering JDBC driver");

return null;

}

Connection connection = null;

try {

connection = DriverManager.getConnection(CONNECTION_URL,

CONNECTION_USER, CONNECTION_PASSWORD);

} catch (SQLException e) {

System.out.println("Failed to create Connection");

return null;

}

System.out.println("Connection created Successfully.");

return connection;

}

Output

Connection created Successfully.
***Lets First Iterate the Resulset in only forward direction
Employee Id - 1 Name is ::Steve
Employee Id - 2 Name is ::Mark
Employee Id - 3 Name is ::Robin
***Now again go back to the first row and print the first Row
Employee Id - 1 Name is ::Steve
***Now again go to the last row and print the data going backwards.
Employee Id - 3 Name is ::Robin
Employee Id - 2 Name is ::Mark
Employee Id - 1 Name is ::Steve

Connection created Successfully.

***Lets First Iterate the Resulset in only forward direction

Employee Id - 1 Name is ::Steve

Employee Id - 2 Name is ::Mark

Employee Id - 3 Name is ::Robin

***Now again go back to the first row and print the first Row

Employee Id - 1 Name is ::Steve

***Now again go to the last row and print the data going backwards.

Employee Id - 3 Name is ::Robin

Employee Id - 2 Name is ::Mark

Employee Id - 1 Name is ::Steve

Useful methods of ResultSet that can be used when Scrollable

ResultSet.afterLast()
ResultSet.beforeFirst()
ResultSet.first()
ResultSet.last()
ResultSet.next()
ResultSet.previous()

ResultSet.afterLast()

ResultSet.beforeFirst()

ResultSet.first()

ResultSet.last()

ResultSet.next()

ResultSet.previous()

Reference

1. ResultSet API