How to retrieve the contents of a ResultSet from last to first in JDBC?

ResultSet object

Certain SQL queries (especially SELECT) returns tabular data, In JDBC the object of the java.sql.ResultSet interface holds the tabular data returned by the methods that execute the statements which quires the database (executeQuery() method of the Statement interface in general).

ResultSet Cursor/pointer

The ResultSet object has a cursor/pointer which points to the current row. Initially this cursor is positioned before first row.

There are two types of result sets namely, forward only and, bidirectional. By default the ResultSet we get by the executeQuery() method is of the type forward only. Using this you can traverse/move the cursor only forward direction.

Bidirectional ResultSet

A bi-directional ResultSet object is the one whose cursor moves in both forward and backward directions. The createStatement() method of the Connection interface has a variant which accepts two integer values representing the result set type and the concurrency type.

Statement createStatement(int resultSetType, int resultSetConcurrency)

To create a bi-directional result set you need to pass the type as ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE, along with the concurrency, to this method as −

//Creating a Statement object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

Then, if you invoke executeQuery() method using this statement it returns a ResultSet object which is bi-directional.

Printing the contents from last

The previous() method of the ResultSet interface moves the pointer of this ResultSet object to the previous row, from the current position.

This method returns a boolean value specifying whether the ResultSet object contains more rows.

If there are no rows previous to its current position this method returns false, else it returns true.

The Getter methods of the ResultSet interface (getInt(), getString() etc..) accepts the column index and returns the value of the specified column in the current row.

Using these methods, you can retrieve the contents of a ResultSet object from first to last.

while(rs.previous()) {
   System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
   System.out.print("Sale: "+rs.getString("Unit_Sale"));
   System.out.println("");
}

Let us create a table with name mobile_sales in MySQL database using CREATE statement as shown below −

CREATE TABLE mobile_sales (
   mobile_brand VARCHAR(255),
   unit_sale INT
);

Now, we will insert 11 records in mobile_sales table using INSERT statements −

insert into mobile_sales values('Iphone', 3000);
insert into mobile_sales values('Samsung', 4000);
insert into mobile_sales values('Nokia', 5000);
insert into mobile_sales values('Vivo', 1500);
insert into mobile_sales values('Oppo', 900);
insert into mobile_sales values('MI', 6400);
insert into mobile_sales values('MotoG', 4360);
insert into mobile_sales values('Lenovo', 4100);
insert into mobile_sales values('RedMI', 4000);
insert into mobile_sales values('MotoG', 4360);
insert into mobile_sales values('OnePlus', 6334);

Following example establishes connection with the database and retrieves the contents of the table mobile_sales from last to first.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BidirectionalResultSet {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/TestDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from mobile_sales");
      //Moving the cursor to the end of the ResultSet
      rs.afterLast();
      System.out.println("Contents of the table");
      while(rs.previous()) {
         System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
         System.out.print("Sale: "+rs.getString("Unit_Sale"));
         System.out.println("");
      }
   }
}

Output

Connection established......
Contents of the table
Brand: Vivo, Sale: 1500
Brand: Nokia, Sale: 5000
Brand: Samsung, Sale: 4000
Brand: IPhone, Sale: 3000