Java ResultSet previous() method with example

JDBCJava 8Object Oriented ProgrammingProgramming

When we execute certain SQL queries (SELECT query in general) they return tabular data.

The java.sql.ResultSet interface represents such tabular data returned by the SQL statements.

i.e. the ResultSet object holds the tabular data returned by the methods that execute the statements which quires the database (executeQuery() method of the Statement interface in general).

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

The previous() method of the ResultSet interface moves the pointer of the current (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.

Note: Using this method in the while loop you can iterate the contents of the ResultSet object from last to first.

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from MyPlayers");
rs.afterLast();
rs.previous();

i.e., If the cursor is at the next position to the last row, and if the table contains 8 rows, on calling the previous() method for the first time the result set pointer/cursor will be moved to the 8th row (from next to last position).

And on calling the previous() method for the second time the result set cursor will be moved to the 7th row.

rs.afterLast();
while(rs.previous()) {
   System.out.println(rs.getString(1));
   System.out.println(rs.getString(2));
}

Example

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

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program establishes connection with the database, retrieves the contents of the table MyPlayers into a ResultSet object, moves the cursor to the previous row thrice using the previous() method, starting from the next to last position of the ResultSet object and, prints the contents of the row at each position.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSet_previous {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement(
      ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
      //Query to retrieve records
      String query = "Select * from MyPlayers";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("Contents of the last record: ");
      //Moving the cursor position next to the last row
      rs.afterLast();
      //Moving the cursor from next to last position to the last (7th) row
      rs.previous();
      //Current record details.
      System.out.print("ID: "+rs.getInt("ID")+", ");
      System.out.print("Name: "+rs.getString("First_Name")+", ");
      System.out.print("Age: "+rs.getString("Last_Name")+", ");
      System.out.print("Salary: "+rs.getDate("Date_Of_Birth")+", ");
      System.out.print("Country: "+rs.getString("Place_Of_Birth")+", ");
      System.out.print("Address: "+rs.getString("Country"));
      System.out.println();
      //Moving the cursor from 7th row to 6th row.
      rs.previous();
      //Current record details.
      System.out.print("ID: "+rs.getInt("ID")+", ");
      System.out.print("Name: "+rs.getString("First_Name")+", ");
      System.out.print("Age: "+rs.getString("Last_Name")+", ");
      System.out.print("Salary: "+rs.getDate("Date_Of_Birth")+", ");
      System.out.print("Country: "+rs.getString("Place_Of_Birth")+", ");
      System.out.print("Address: "+rs.getString("Country"));
      System.out.println();
      //Moving the cursor from 6th row to 5th row.
      rs.previous();
      //Current record details.
      System.out.print("ID: "+rs.getInt("ID")+", ");
      System.out.print("Name: "+rs.getString("First_Name")+", ");
      System.out.print("Age: "+rs.getString("Last_Name")+", ");
      System.out.print("Salary: "+rs.getDate("Date_Of_Birth")+", ");
      System.out.print("Country: "+rs.getString("Place_Of_Birth")+", ");
      System.out.print("Address: "+rs.getString("Country"));
      System.out.println();
      //Moving the cursor from 5th row to 4th row.
      rs.previous();
      //Current record details.
      System.out.print("ID: "+rs.getInt("ID")+", ");
      System.out.print("Name: "+rs.getString("First_Name")+", ");
      System.out.print("Age: "+rs.getString("Last_Name")+", ");
      System.out.print("Salary: "+rs.getDate("Date_Of_Birth")+", ");
      System.out.print("Country: "+rs.getString("Place_Of_Birth")+", ");
      System.out.print("Address: "+rs.getString("Country"));
      System.out.println();
   }
}

Output

Connection established......
Contents of the last record:
ID: 8, Name: Ryan, Age: McLaren, Salary: 1983-02-09, Country: Kumberly, Address: null
ID: 7, Name: James, Age: Anderson, Salary: 1982-06-30, Country: Burnley , Address: England
ID: 6, Name: Ravindra, Age: Jadeja, Salary: 1988-12-06, Country: Nagpur, Address: India
ID: 5, Name: Rohit, Age: Sharma, Salary: 1987-04-30, Country: Nagpur, Address: India
raja
Published on 09-May-2019 12:15:38
Advertisements