Scrollable ResultSet in JDBC

by

Introduction

Java Database Connectivity (JDBC) is a Java-based API that allows Java programs to connect to databases and manipulate data. One important aspect of JDBC is its ability to handle large amounts of data. One way JDBC does this is through the use of Scrollable Result Sets. In this blog post, we’ll discuss what Scrollable Result Sets are and how they can be used in JDBC applications.

What is a Scrollable Result Set?

A Scrollable Result Set is a type of result set in JDBC that allows us to navigate through the results in any order. This is in contrast to a Forward-Only Result Set, which only allows us to move forward through the results one row at a time. With a Scrollable Result Set, we can move both forwards and backwards through the result set, and jump to specific rows in the result set.

Why Use a Scrollable Result Set?

There are several reasons why we might want to use a Scrollable Result Set in our JDBC application:

  1. Flexibility: A Scrollable Result Set gives us more flexibility in how we access and process data. We can move through the data in any order, jump to specific rows, and even repeat rows if necessary.
  2. Efficiency: With a Scrollable Result Set, we can retrieve and process large amounts of data in a more efficient manner. We can fetch only the data we need, rather than retrieving the entire result set at once.
  3. User Experience: A Scrollable Result Set can improve the user experience of our application by allowing users to quickly and easily navigate through large sets of data.

How to Create a Scrollable Result Set in JDBC To create a Scrollable Result Set in JDBC, we need to set the appropriate ResultSet type when we create the statement. Here’s an example:

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");

In this example, we’re creating a Scrollable Result Set that is insensitive to changes made by other users (TYPE_SCROLL_INSENSITIVE), and read-only (CONCUR_READ_ONLY). We’re then executing a simple SELECT statement to retrieve all the rows from the “mytable” table.

Using a Scrollable Result Set Once we have a Scrollable Result Set, we can use the various methods provided by the ResultSet interface to navigate through the data. Here are a few examples:

  1. Moving to the First Row:
rs.first();

This moves the cursor to the first row in the result set.

  1. Moving to the Last Row:
rs.last();

This moves the cursor to the last row in the result set.

  1. Moving to a Specific Row:
rs.absolute(5);

This moves the cursor to the fifth row in the result set.

  1. Moving Forward and Backward:
rs.next(); // moves the cursor to the next row
rs.previous(); // moves the cursor to the previous row

Scrollable ResultSet Example

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

public class ScrollableResultSetDemo {
	private static Connection conn;
	private static Statement stat;

	public static void main(String[] args) throws SQLException {
		try {
			initConnection();
			initStatement();
			createDatabase();
			createTable();
			insertData(10);

			printAllData();

			scrollableResultSet();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println("Clearing data and resources");
			dropTable();
			dropDatabase();
			stat.close();
			conn.close();
		}
	}

	private static void dropTable() throws SQLException {
		stat.execute("drop table user");
		
	}

	private static void scrollableResultSet() throws SQLException {
		Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = stmt.executeQuery("SELECT * FROM user");

		System.out.println("===== Printing last row data =====");
		// Move cursor to the last row
		rs.last();
		// Print the data in the last row
		System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("email"));

		System.out.println("===== Printing first row data =====");
		// Move cursor to the first row
		rs.first();
		// Print the data in the first row
		System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("email"));

		System.out.println("===== Printing second row data =====");
		// Move cursor to the second row
		rs.absolute(2);
		// Print the data in the second row
		System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("email"));

		System.out.println("===== Printing fourth row data =====");
		// Move cursor to the fourth row
		rs.absolute(4);
		// Print the data in the fourth row
		System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("email"));

		System.out.println("===== Printing third row data from the last i.e. 8th =====");
		// Move cursor to the third row from the end
		rs.absolute(-3);
		// Print the data in the third row from the end
		System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("email"));

		rs.close();
		stmt.close();

	}

	private static void printAllData() throws SQLException {
		System.out.println("===== Printing all data =====");
		ResultSet rs = stat.executeQuery("select * from user");
		while (rs.next()) {
			System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("email"));
		}
	}

	private static void dropDatabase() throws SQLException {
		stat.execute("drop database scrollabledemo ");
	}

	private static void initConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
	}

	private static void insertData(int noOfData) throws SQLException {
		for (int i = 1; i <= noOfData; i++) {
			stat.executeUpdate(
					"insert into user (id, name, email) values (" + i + ", 'name" + i + "','email" + i + "' )");
		}
	}

	private static void initStatement() throws SQLException {
		stat = conn.createStatement();
	}

	private static void createDatabase() throws SQLException {
		stat.execute("create database scrollabledemo");
	}

	private static void createTable() throws SQLException {
		stat.execute("create table user (id int, name varchar(25), email varchar(25))");
	}
}

The output of the above code is given below:

===== Printing all data =====
1, name1, email1
2, name2, email2
3, name3, email3
4, name4, email4
5, name5, email5
6, name6, email6
7, name7, email7
8, name8, email8
9, name9, email9
10, name10, email10
===== Printing last row data =====
10, name10, email10
===== Printing first row data =====
1, name1, email1
===== Printing second row data =====
2, name2, email2
===== Printing fourth row data =====
4, name4, email4
===== Printing third row data from the last i.e. 8th =====
8, name8, email8
Clearing data and resources

Key points to understand from the code above:

  • ResultSet.TYPE_SCROLL_INSENSITIVE: This specifies that the result set is scrollable and insensitive to changes made by other transactions. This means that if another transaction modifies the data in the table while we’re iterating through the result set, we won’t see those changes.
  • ResultSet.CONCUR_READ_ONLY: This specifies that the result set is read-only. We won’t be able to update or delete any rows using this result set.
  • Statement.executeQuery(): This method is used to execute a SQL query and retrieve a result set. The result set can be iterated through using methods like next(), previous(), first(), last(), absolute(), and relative().
  • ResultSet.last(): This method moves the cursor to the last row in the result set.
  • ResultSet.first(): This method moves the cursor to the first row in the result set.
  • ResultSet.absolute(): This method moves the cursor to an absolute position in the result set, specified by an integer parameter. If the parameter is positive, it represents an absolute row number. If the parameter is negative, it represents a row number from the end of the result set.
  • ResultSet.getInt(), ResultSet.getString(): These methods are used to retrieve data from the columns of the current row in the result set. The column can be specified either by its index (starting from 1) or by its name.

Similarly, we can use the Updatable ResultSet in JDBC.

Conclusion

Scrollable Result Sets in JDBC provide a powerful and flexible way to navigate through large sets of data. By using a Scrollable Result Set, we can improve the efficiency and user experience of our JDBC application. With the examples provided in this post, we should be able to create and use Scrollable Result Sets in our own JDBC applications.

Related Posts:

Sharing Is Caring: