dcsimg
November 19, 2019
Hot Topics:

How to Create a Scrollable, Updatable ResultSet Object in JDBC

  • December 17, 2018
  • By Manoj Debnath
  • Send Email »
  • More Articles »

When fetching a list of records through queries, we often need to store them in an object that allows back and forth traversing, updating as needed. This article illustrates this commonly needed technique in database programming with explicit codes and example scenarios.

About ResultSet

The ResultSet is an interface defined in the java.sql package. It represents a table of data returned by a Statement object. A Statement object is used to execute SQL queries to the database. The ResultSet object maintains a cursor pointing to the current record in the database table. As a result, it can be effectively used to position at different rows, back and forth using first(), previous(), next(), and last() methods as per requirements. Initially, the ResultSet object is positioned at a location before the first row. This is the reason a ResultSet traversal always begins as follows:

while(resultSet.next()) {

   // ...

}

Note that the ResultSet object is positioned at the first row by executing the next() method when entering the loop, because, as already mentioned, the ResultSet object is initially located at a position just before the first row. So, it must be put to at least the first row, for example, to get a valid record. It may be deemed as a value -1 in an array position a pointer/index is pointing to. It must be first relocated to at least the 0th location to get any kind of valid value from the array.

Now, as we have mentioned, we can scroll through records with the help of the ResultSet object. But, this ability does not come by default. The default behavior of the ResultSet object is that it is not updatable and the cursor it owns actually moves in one direction, forward only. This means that we can iterate through the records only once and in a forward direction only. However, there are ways to make it flexible so that the ResultSet is not only updatable but also scrollable.

We'll see them in a minute in two separate programs.

Scrollable ResultSet

Let's first make the ResultSet object scrollable. Scrollable means that once the ResultSet object has been created, we can traverse through fetched records in any direction, forward and backward, as we like. This provides the ability to read the last record, first record, next record, and the previous record.

package org.mano.example;
import java.sql.*;
public class App
{
   static final String JDBC_DRIVER =
      "com.mysql.cj.jdbc.Driver";
   static final String DB_URL =
      "jdbc:mysql://localhost:3306/employees";
   static final String USER = "root";
   static final String PASS = "secret";
   static final String SQL =
      "SELECT * FROM employees ORDER BY first_name";

   public static void main( String[] args )
   {
      Connection connection = null;
      ResultSet rs = null;
      try {
         Class.forName(JDBC_DRIVER);
         connection = DriverManager.getConnection
            (DB_URL, USER, PASS);
         System.out.println("\n1. Connection established");
      }catch(Exception ex) {
         ex.printStackTrace();
      }

      try (PreparedStatement pstmt =
            connection.prepareStatement(SQL,
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY);){
            System.out.println("\n2.
               Executing SQL query...");
            rs = pstmt.executeQuery();
            System.out.println("\n3.
               ResultSet object created successfully.");
            System.out.println("\n4.
               Now some RecordSet scrolling starts...");

            rs.first();
            show(rs);
            rs.last();
            show(rs);
            rs.previous();
            rs.previous();
            show(rs);
            rs.next();
            show(rs);

            System.out.println("\n\n5. That's all.
               RecordSet scrolling ends.");
      }catch(SQLException ex){
         ex.printStackTrace();
      }finally{
         try {
            connection.close();
         }catch(SQLException ex){
         }
      }
   }
   public static void show(ResultSet rs) throws
         SQLException{
      System.out.printf
         ("\n--------------------------------"+
            "-------------------------------------");
      System.out.printf("\n%7d | %10s | %10s | %s
         | %s | %s ",rs.getLong("emp_no"),
         rs.getString("first_name"),
         rs.getString("last_name"),
         rs.getDate("birth_date").toString(),
         rs.getDate("hire_date"),
         rs.getString("gender"));
      System.out.printf
         ("\n---------------------------------"+
         "------------------------------------");
   }
}

Output

  1. Connection established.
  2. Executing SQL query...
  3. ResultSet object created successfully.
  4. Now, some RecordSet scrolling starts...
    -------------------------------------------------------------
     497615 |  Aamer  |  McDermid   | 1954-11-18 | 1985-04-24 | M
    -------------------------------------------------------------
    -------------------------------------------------------------
     484995 |  Zvonko |  Lakshmanan | 1964-11-04 | 1992-12-04 | M
    -------------------------------------------------------------
    -------------------------------------------------------------
     482000 |  Zvonko |  Cannata    | 1960-11-23 | 1986-08-13 | M
    -------------------------------------------------------------
    -------------------------------------------------------------
     483497 |  Zvonko |  Pollacia   | 1961-12-26 | 1985-08-01 | M
    -------------------------------------------------------------
    
  5. That's all. RecordSet scrolling ends.

Note that the scrollable ResultSet object is the result of the execution of the executeQuery() method obtained through the instance of Statement or PreparedStatement. The type of ResultSet object we like to create must be explicitly declared to the Statement object through defined scroll type constants.

  • ResultSet.TYPE_FORWARD_ONLY: This is the default type.
  • ResultSet.TYPE_SCROLL_INSENSITIVE: Enables back and forth movement, but is insensitive to ResultSet updates.
  • ResultSet.TYPE_SCROLL_SENSITIVE: Enables back and forth movement, but is sensitive to ResultSet updates.

There are other constants used, such as CONCUR_READ_ONLY, which means that the ResultSet is not updatable. There is another constant, CONCUR_UPDATABLE, which signifies the opposite, meaning the ResultSet is updatable.

Updatable ResultSet

Creating an updatable ResultSet means that the record it points to is not only be traversable but also be updatable. The changes will immediately be persisted in the database and reflected by the ResultSet object in real time.

package org.mano.example;
import java.sql.*;
public class App
{
   static final String JDBC_DRIVER =
      "com.mysql.cj.jdbc.Driver";
   static final String DB_URL =
      "jdbc:mysql://localhost:3306/employees";
   static final String USER = "root";
   static final String PASS = "secret";
   static final String SQL =
      "SELECT * FROM employees WHERE emp_no = ?";
   public static void main( String[] args )
   {
      Connection connection = null;
      ResultSet rs = null;
      long emp_no = 484995;
      try {
         Class.forName(JDBC_DRIVER);
         connection = DriverManager.getConnection
            (DB_URL, USER, PASS);
         System.out.println("\n1.
            Connection established");
      }catch(Exception ex) {
         ex.printStackTrace();
      }
      try(PreparedStatement pstmt =
            connection.prepareStatement(SQL,
         ResultSet.TYPE_SCROLL_SENSITIVE,
         ResultSet.CONCUR_UPDATABLE);){
            pstmt.setLong(1,emp_no);
            System.out.println("\n2.
               Executing SQL query...");
            rs = pstmt.executeQuery();
            System.out.println("\n3.
               ResultSet object created successfully.");
            while(rs.next()){
               show(rs);
               String fname = rs.getString("first_name");
               System.out.println("\n4.
                  Updating name "+fname+" to Subham");
               rs.updateString("first_name", "Subham");
               rs.updateRow();
            }
            System.out.println("\n\n5.
               Record updated. See below.");
            rs.previous();
            show(rs);
      }catch(SQLException ex){
         ex.printStackTrace();
      }finally{
      try {
         rs.close();
         connection.close();
      }catch(SQLException ex){
      }
      }
   }
   public static void show(ResultSet rs)
         throwsSQLException{
      System.out.printf
         ("\n--------------------------------"+
            "-------------------------------------");
      System.out.printf("\n%7d | %10s | %10s | %s
            | %s | %s ",rs.getLong("emp_no"),
         rs.getString("first_name"),
         rs.getString("last_name"),
         rs.getDate("birth_date").toString(),
         rs.getDate("hire_date"),
         rs.getString("gender"));
         System.out.printf
            ("\n---------------------------------"+
               "------------------------------------");
   }
}

The updatable ResultSet is particularly useful when we want to update certain values after doing some comparison by traversing back and forth through the fetched records. The process of creation is similar to the preceding program, but the ResultSet constants used here are TYPE_SCROLL_SENSITIVE and CONCUR_UPDATABLE.

Conclusion

Contrary to the default behavior of the ResultSet, it empowers the object to have greater flexibility. This functionality can be leveraged by the application to not only traverse through records but also make them updatable so that they can provide a better service. Although standard behavior of a result set seems quite inefficient in comparison with the scrollable ResultSet, it has its own use and therefore is irreplaceable.







Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.


Thanks for your registration, follow us on our social networks to keep up-to-date