Java & MySQL - Navigating a ResultSet

There are several methods in the ResultSet interface that involve moving the cursor, including −

S.N.	Methods & Description
1	pubpc void beforeFirst() throws SQLException Moves the cursor just before the first row.
2	pubpc void afterLast() throws SQLException Moves the cursor just after the last row.
3	pubpc boolean first() throws SQLException Moves the cursor to the first row.
4	pubpc void last() throws SQLException Moves the cursor to the last row.
5	pubpc boolean absolute(int row) throws SQLException Moves the cursor to the specified row.
6	pubpc boolean relative(int row) throws SQLException Moves the cursor the given number of rows forward or backward, from where it is currently pointing.
7	pubpc boolean previous() throws SQLException Moves the cursor to the previous row. This method returns false if the previous row is off the result set.
8	pubpc boolean next() throws SQLException Moves the cursor to the next row. This method returns false if there are no more rows in the result set.
9	pubpc int getRow() throws SQLException Returns the row number that the cursor is pointing to.
10	pubpc void moveToInsertRow() throws SQLException Moves the cursor to a special row in the result set that can be used to insert a new row into the database. The current cursor location is remembered.
11	pubpc void moveToCurrentRow() throws SQLException Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing

Following is the example which makes use of few navigation methods described.

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

Copy and paste the following example in TestApppcation.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;

pubpc class TestApppcation {
   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";

   pubpc 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();
      } 
   }
}

Now let us compile the above example as follows −


C:>javac TestApppcation.java
C:>

When you run TestApppcation, it produces the following result −


C:>java TestApppcation
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: Ap
Moving cursor to the next row...
Displaying record...
ID: 101, Age: 25, First: Mehnaz, Last: Fatma
C:>

Java & MySQL - Navigating a ResultSet

友情链接