JDBC - Navigating a Result Set Examples

Following is the example which makes use of few navigation methods described in the Result Set tutorial.

This sample code has been written based on the environment and database setup done in the previous chapters.

Navigating to first, last and next Record in a ResultSet Example

In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY, then using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset.

In first step, we've moved resultset cursor to the last row using ResultSet.last() method and printed the last record. In second step, we've moved cursor to the first row using ResultSet.first() method and printed the first record. As last step, we moved cursor to next record using ResultSet.next() method and printed the record.

Copy and paste the following example in ResultSetExample.java, compile and run as follows −

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

public class ResultSetExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY);
         ResultSet rs = stmt.executeQuery(QUERY);
      ) {		
         // Move cursor to the last row.
         System.out.println("Moving cursor to the last...");
         rs.last();

         // Extract data from result set
         System.out.println("Displaying record...");
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);

         // Move cursor to the first row.
         System.out.println("Moving cursor to the first row...");
         rs.first();

         // Extract data from result set
         System.out.println("Displaying record...");
         // Retrieve by column name
         id  = rs.getInt("id");
         age = rs.getInt("age");
         first = rs.getString("first");
         last = rs.getString("last");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
         // Move cursor to the first row.

         System.out.println("Moving cursor to the next row...");
         rs.next();

         // Extract data from result set
         System.out.println("Displaying record...");
         id  = rs.getInt("id");
         age = rs.getInt("age");
         first = rs.getString("first");
         last = rs.getString("last");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);		

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac ResultSetExample.java
C:\>

When you run ResultSetExample, it produces the following result −

C:\>java ResultSetExample
Moving cursor to the last...
Displaying record...
ID: 103, Age: 30, First: Sumit, Last: Mittal
Moving cursor to the first row...
Displaying record...
ID: 100, Age: 18, First: Zara, Last: Ali
Moving cursor to the next row...
Displaying record...
ID: 101, Age: 25, First: Mehnaz, Last: Fatma
Goodbye!
C:\>

Navigating to a particular Record in a ResultSet Example

In first step, we've moved resultset cursor to a particular row using ResultSet.absolute() method and printed the record. In second step, we've moved cursor to the first row using ResultSet.first() method and printed the first record. As last step, we moved cursor to next record using ResultSet.next() method and printed the record.

Copy and paste the following example in ResultSetExample.java, compile and run as follows −

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

public class ResultSetExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT StudentID, FirstName, LastName FROM Students";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

         Statement stmt = conn.createStatement(
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY);
         ResultSet rs = stmt.executeQuery(QUERY);
      ) {		
         // Move cursor to the third row.
         System.out.println("Moving cursor to the 3rd row...");
         rs.absolute(3);

         // Extract data from result set
         System.out.println("Displaying record of 3rd row...");
         //Retrieve by column name
         int id  = rs.getInt("StudentID");
         String first = rs.getString("FirstName");
         String last = rs.getString("LastName");

         // Display values
         System.out.print("StudentID: " + id);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);

         // Move cursor to the first row.
         System.out.println("Moving cursor to the first row...");
         rs.first();
         // Extract data from result set
         System.out.println("Displaying record...");
         // Retrieve by column name
         id  = rs.getInt("StudentID");
         first = rs.getString("FirstName");
         last = rs.getString("LastName");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
         // Move cursor to the first row.

         System.out.println("Moving cursor to the next row...");
         rs.next();

         // Extract data from result set
         System.out.println("Displaying record...");
         id  = rs.getInt("StudentID");
         first = rs.getString("FirstName");
         last = rs.getString("LastName");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);		

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac ResultSetExample.java
C:\>

When you run ResultSetExample, it produces the following result −

C:\>java ResultSetExample
Moving cursor to the 3rd row...
Displaying record of 3rd...
StudentID: 1002, First: Shefali, Last: Kumar
Moving cursor to the first row...
Displaying record...
ID: 1000, First: Bonny, Last: Agarwal
Moving cursor to the next row...
Displaying record...
ID: 1001, First: Amit, Last: Pandey

C:\>

jdbc-result-sets.htm