Table of contents
1.
Introduction
2.
Commonly used methods of ResultSet interface:
2.1.
1. next()
2.2.
2. getXXX(int columnIndex) or getXXX(String columnLabel)
2.3.
3. beforeFirst()
2.4.
4. afterLast()
2.5.
5. first()
2.6.
6. last()
2.7.
7. absolute(int row)
2.8.
8. relative(int rows)
3.
Example of Scrollable ResultSet:
3.1.
Java
4.
Frequently Asked Questions
4.1.
What is the difference between a forward-only & a scrollable ResultSet?
4.2.
How can I create a scrollable ResultSet in JDBC?
4.3.
What happens if I try to move the cursor beyond the first or last row in a ResultSet?
5.
Conclusion
Last Updated: Jul 28, 2024
Easy

Resultset In Java

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

In Java, a ResultSet is an object that contains the results of executing a database query. It provides a way to access & manipulate the data returned from a database. The ResultSet interface in JDBC (Java Database Connectivity) allows you to move through its data & obtain the data from various columns. 

Resultset In Java

In this article, we will learn about the commonly used methods of the ResultSet interface with an example of a scrollable ResultSet.

Commonly used methods of ResultSet interface:

The ResultSet interface provides several methods to access & manipulate the data returned from a database query. Let’s look at some of the commonly used methods:

1. next()

This method moves the cursor to the next row in the ResultSet object. It returns true if there is a next row & false if there are no more rows.

while (rs.next()) {
    // process the current row
}

2. getXXX(int columnIndex) or getXXX(String columnLabel)

These methods retrieve the value of a column in the current row. The XXX represents the data type of the column, such as getInt(), getString(), getDate(), etc. You can specify either the column index (starting from 1) or the column label.

String name = rs.getString("name");
int age = rs.getInt("age");

3. beforeFirst()

This method moves the cursor to the position before the first row in the ResultSet object.

rs.beforeFirst();

4. afterLast()

This method moves the cursor to the position after the last row in the ResultSet object.

rs.afterLast();

5. first()

This method moves the cursor to the first row in the ResultSet object.

rs.first();

6. last()

This method moves the cursor to the last row in the ResultSet object.

rs.last();

7. absolute(int row)

This method moves the cursor to the specified row number in the ResultSet object. If the row number is positive, it counts from the beginning of the ResultSet. If the row number is negative, it counts from the end of the ResultSet.

rs.absolute(5); // moves to the 5th row
rs.absolute(-2); // moves to the second-to-last row

8. relative(int rows)

This method moves the cursor forward or backward by the specified number of rows relative to the current position.

rs.relative(3); // moves forward by 3 rows
rs.relative(-2); // moves backward by 2 rows


Note: These methods allow you to navigate through the ResultSet & access the data from different columns. It's important to note that the availability of some methods depends on the type of ResultSet & the JDBC driver being used.

Example of Scrollable ResultSet:

By default, a ResultSet is forward-only, which means you can only move forward through the rows using the next() method. However, JDBC also provides scrollable ResultSets that allow you to move forward & backward through the rows. Let’s look at an example of how to create & use a scrollable ResultSet:

  • Java

Java

import java.sql.*;

public class ScrollableResultSetExample {
public static void main(String[] args) {
try {
// Establish a connection to the database
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");

// Create a statement with scrollable & read-only ResultSet
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

// Execute a query
String query = "SELECT * FROM employees";
ResultSet rs = stmt.executeQuery(query);

// Move to the last row
rs.last();
int totalRows = rs.getRow();
System.out.println("Total rows: " + totalRows);

// Move to the first row
rs.first();
System.out.println("First row:");
System.out.println("Name: " + rs.getString("name"));
System.out.println("Age: " + rs.getInt("age"));

// Move to the third row
rs.absolute(3);
System.out.println("Third row:");
System.out.println("Name: " + rs.getString("name"));
System.out.println("Age: " + rs.getInt("age"));

// Move to the previous row
rs.relative(-1);
System.out.println("Previous row:");
System.out.println("Name: " + rs.getString("name"));
System.out.println("Age: " + rs.getInt("age"));

// Close the ResultSet, statement & connection
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
You can also try this code with Online Java Compiler
Run Code


Output

Total rows: 4
First row:
Name: Alice
Age: 30
Third row:
Name: Charlie
Age: 35
Previous row:
Name: Bob
Age: 25


In this example:

1. We establish a connection to the database using the DriverManager.getConnection() method.
 

2. We create a statement with a scrollable & read-only ResultSet using the createStatement() method & specifying the ResultSet type & concurrency.
 

3. We execute a query using the executeQuery() method & obtain the ResultSet.
 

4. We move to the last row using the last() method & get the total number of rows using the getRow() method.
 

5. We move to the first row using the first() method & retrieve the data from the "name" & "age" columns.
 

6. We move to the third row using the absolute(3) method & retrieve the data.
 

7. We move to the previous row relative to the current position using the relative(-1) method & retrieve the data.
 

8. Finally, we close the ResultSet, statement & connection to release the resources.

Frequently Asked Questions

What is the difference between a forward-only & a scrollable ResultSet?

A forward-only ResultSet allows you to move forward through the rows using the next() method, while a scrollable ResultSet allows you to move both forward & backward through the rows using methods like first(), last(), absolute(), & relative().

How can I create a scrollable ResultSet in JDBC?

To create a scrollable ResultSet, you need to use the createStatement() method of the Connection object & specify the ResultSet type & concurrency. 

What happens if I try to move the cursor beyond the first or last row in a ResultSet?

If you try to move the cursor before the first row or after the last row in a ResultSet, the cursor will be positioned before the first row or after the last row, respectively. Attempting to retrieve data in this state will result in a SQLException.

Conclusion

In this article, we learned about the ResultSet interface in JDBC & how it allows us to access & manipulate the data returned from a database query. We discussed some of the commonly used methods of the ResultSet interface, such as next(), getXXX(), beforeFirst(), afterLast(), first(), last(), absolute(), & relative(). We also saw an example of how to create & use a scrollable ResultSet, which allows us to move both forward & backward through the rows.

You can also practice coding questions commonly asked in interviews on Coding Ninjas Code360

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.

Live masterclass