Skip to main content

JDBC ResultSet

Introduction to JDBC ResultSet​

ResultSet is an interface in the JDBC API (Java Database Connectivity Application Programming Interface). It represents a table of data generated by executing a statement that queries the database. A ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row.

In this tutorial, we will explore the ResultSet interface in detail, and learn how to use its methods to retrieve and manipulate data.

Creating a ResultSet​

A ResultSet object is typically created when you execute a query against the database. Here is a simple example:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Employees");

In the above example, conn is an active database connection, and stmt.executeQuery runs the SQL query against the database and returns a ResultSet.

Once we have a ResultSet, we can navigate through it using various methods provided by the ResultSet interface.

  • next(): This method moves the cursor to the next row in the ResultSet object and returns true if the new current row is valid. Otherwise, it returns false.
while(rs.next()){
  // access the data in the current row
}
  • previous(), first(), last(), absolute(int row): These methods move the cursor to the previous row, the first row, the last row, or to the specified row respectively. However, these methods are available only for ResultSet objects of type TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE.

Retrieving Data from a ResultSet​

ResultSet provides different get methods to retrieve data from the current row based on the type of data:

  • getString(columnIndex): Retrieves the value of the specified column as a String.

  • getInt(columnIndex): Retrieves the value of the specified column as an int.

  • getBoolean(columnIndex): Retrieves the value of the specified column as a boolean.

And so on. The columnIndex here is the index of the column in the SQL query, starting from 1.

Here's how you can retrieve data:

while(rs.next()){
  String name = rs.getString(1);
  int age = rs.getInt(2);
  // and so on...
}

Updating Data in a ResultSet​

JDBC allows you to update data in a ResultSet and reflect these changes in the database. However, this is possible only if the ResultSet object is of type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE, and its concurrency level is CONCUR_UPDATABLE.

Here's how you can update data:

rs.updateString("name", "John Doe");
rs.updateRow();

Closing a ResultSet​

It's important to close ResultSet objects when you're done with them to free up resources. You can do this by calling the close() method:

rs.close();

Remember to always close your ResultSet within a finally block to ensure that it gets closed even if an error occurs.

Conclusion​

The JDBC ResultSet interface is a powerful tool for interacting with data retrieved from a database. It provides methods for navigating through the data, retrieving it, and even updating it. Always remember to close your ResultSet objects when you're done using them to prevent resource leaks.