Saturday, March 24, 2018

ResultSet Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API. In this post we’ll see Java ResultSet interface in detail.

ResultSet interface in JDBC

ResultSet interface resides in java.sql package and it represents the storage for the data you get by executing a SQL statement that queries the database.

A ResultSet object maintains a cursor pointing at the result data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, it returns false when there are no more rows in the ResultSet object.

Creating and iterating a ResultSet Example

ResultSet rs = stmt.executeQuery("Select * from Employee");
   
// Processing Resultset
while(rs.next()){
 System.out.println("id : " + rs.getInt("id") + " Name : " +  rs.getString("name") + " Age : " + rs.getInt("age")); 
}

By default, ResultSet object is not updatable and has a forward moving cursor only. Thus, you can iterate through it only once and only from the first row to the last row. But ResultSet interface provides parameters that can produce ResultSet objects that are scrollable and/or updatable.

Fields for scrollable ResultSet

ResultSet interface in Java has fields that determine whether ResultSet object will be scrollable or not and will it be sensitive to the changes to the data that is represented by ResultSet or not.

  • TYPE_FORWARD_ONLY- The constant indicating the type for a ResultSet object whose cursor may move only forward.
  • TYPE_SCROLL_INSENSITIVE- The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. Which means you can move the cursor to an absolute position or relative to the current cursor position. If the data in the DB is changed by another thread/process that change won’t be reflected in the data stored in the ResultSet.
  • TYPE_SCROLL_SENSITIVE- The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet. Which means you can move the cursor to an absolute position or relative to the current cursor position. If the data in the DB is changed by another thread/process that change is reflected in the data stored in the ResultSet.

Fields for updatable ResultSet

  • CONCUR_READ_ONLY- The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
  • CONCUR_UPDATABLE- The constant indicating the concurrency mode for a ResultSet object that may be updated.

Java ResultSet interface example

Let’s see an example with scrollable resultset, DB used here is MySql, schema is netjs and table is Employee.

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

public class JDBCResultSet {

 public static void main(String[] args) {
  try(Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/netjs", "root", "admin")){
   // creating Statement
   Statement stmt = connection.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);  
   
   // Executing Query
   ResultSet rs = stmt.executeQuery("Select * from Employee");
   System.out.println("Displaying all rows");
   // Processing Resultset
   while(rs.next()){
       System.out.println("id : " + rs.getInt("id") + " Name : " 
        + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
   // moving to 3rd row
   rs.absolute(3);
   System.out.println("Displaying 3rd row");
   System.out.println("id : " + rs.getInt("id") + " Name : " 
                          + rs.getString("name") + " Age : " + rs.getInt("age")); 
  }catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

Output

Displaying all rows
id : 5 Name : Tom Age : 35
id : 6 Name : Tim Age : 20
id : 7 Name : John Age : 25
id : 8 Name : Johnny Age : 35
id : 17 Name : Johnny Age : 65
Displaying 3rd row
id : 7 Name : John Age : 25

Getter Methods in ResultSet

You would have noticed in the examples how appropriate data type getter method is used (i.e. getInt, getString) for retrieving column values from the current row. You can retrieve value using either the index number of the column or the name of the column.

In general, using the column index will be more efficient. Columns are numbered from 1. Drawback is, any alteration in the table structure will mean change in the indexes in the Java code.

Updater methods in ResultSet

There are also updater methods corresponding to the data types which are used when your ResultSet is updatable. Using updater methods you can update the column values then update the row in the DB. Updater methods are used in conjunction with updateRow and insertRow methods.

Let’s see an example to update a row and insert a row using ResultSet methods.

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

public class JDBCResultSetUpdate {

 public static void main(String[] args) {
  try(Connection connection = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/netjs", "root", "admin")){
   // creating Statement
   Statement stmt = connection.createStatement(
                          ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);  
   
   // Executing Query
   ResultSet rs = stmt.executeQuery("Select * from Employee");
   System.out.println("Displaying all rows");
   // Processing Resultset
   while(rs.next()){
       System.out.println("id : " + rs.getInt("id") + " Name : " 
                               + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
   // moving to 3rd row
   rs.absolute(3);
   // updating age column for 3rd row
   rs.updateInt("age", 28);
   rs.updateRow();
   System.out.println("Displaying 3rd row");
   System.out.println("id : " + rs.getInt("id") + " Name : " 
                            + rs.getString("name") + " Age : " + rs.getInt("age"));
   
   /*** Inserting row  ***/
   // moves cursor to the insert row
   rs.moveToInsertRow(); 
     
   //rs.updateInt("id",18); //updates the first column using column name
   rs.updateString(2, "Bob"); //updates the second column using column index
   rs.updateInt("age",45);
   rs.insertRow();
   rs.moveToCurrentRow();
   
  }catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

 }

}

Other fields of ResultSet interface

Some of the fields are already mentioned with examples, ResultSet in Java has some other fields which are as follows-

  • CLOSE_CURSORS_AT_COMMIT- This constant indicates that open ResultSet will be closed when the current transaction is commited.
  • HOLD_CURSORS_OVER_COMMIT- This constant indicates that open ResultSet will remain open when the current transaction is commited.
  • FETCH_FORWARD- The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.
  • FETCH_REVERSE- The constant indicating that the rows in a result set will be processed in a reverse direction; last-to-first.
  • FETCH_UNKNOWN- The constant indicating that the order in which rows in a result set will be processed is unknown.

Methods of the ResultSet

Most of the often used methods of the ResultSet are already covered with the examples. Some of the other methods which are used for moving the cursor are as follows-

  • afterLast()- Moves the cursor to the end of this ResultSet object, just after the last row.
  • beforeFirst()- Moves the cursor to the front of this ResultSet object, just before the first row.
  • first()- Moves the cursor to the first row in this ResultSet object.
  • last()- Moves the cursor to the last row in this ResultSet object.
  • moveToCurrentRow()- Moves the cursor to the remembered cursor position, usually the current row.
  • moveToInsertRow()- Moves the cursor to the insert row.
  • next()- Moves the cursor froward one row from its current position.
  • previous()- Moves the cursor to the previous row in this ResultSet object.
  • relative(int rows)- Moves the cursor a relative number of rows, either positive or negative.

Reference: https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/ResultSet.html

That's all for this topic ResultSet Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page


Related Topics

  1. Connection Interface in Java-JDBC
  2. Statement Interface in Java-JDBC
  3. DatabaseMetaData Interface in Java-JDBC
  4. Types of JDBC Drivers
  5. Configuring DataSource in Spring Framework

You may also like-

  1. Difference between abstract class and interface
  2. super in Java
  3. try-with-resources in Java 7
  4. How HashMap internally works in Java
  5. Difference between CountDownLatch and CyclicBarrier
  6. Java Stream API Examples
  7. Object Cloning in Java
  8. Select Query Using NamedParameterJDBCTemplate in Spring Framework