JDBC ResultSet

Back to: Java Tutorials For Beginners and Professionals

JDBC ResultSet in Java Application

In this article, I am going to discuss JDBC ResultSet in Java Application with Examples. Please read our previous article where we discussed CRUD Operations in Java using JDBC and Oracle Database with Examples. At the end of this article, you will understand the following pointers in detail.

1. ResultSet Interface in Java
2. How JDBC ResultSet is Classified?
3. What are the methods of ResultSet that deal with the Cursor?
4. How to Create Different Kinds of Result Sets in Java?
5. What is the Difference between Sensitive and Insensitive Result set?

JDBC ResultSet Interface in Java

In JDBC applications if we use Selection group SQL Query as a parameter to executeQuery() method then JVM will send that selection group SQL Query to the database engine, where Database Engine will execute that SQL Query, fetch the data from Database and send back to Java application.

At Java application, the fetched data will be stored in the form of an object at the heap memory called ResultSet. As per the predefined implementation of the executeQuery method, JVM will return the generated ResultSet object reference as the return value.

ResultSet rs = st.executeQuery(“select * from account”);

When ResultSet object is created automatically a cursor will be created positioned before the first record.

If we want to read the records data from ResultSet object, for each and every record we have to check whether the next record is available or not from ResultSet cursor position, if it is available then we have to move ResultSet cursor to the next record position. To perform the above work we have to use the following method from ResultSet.

public boolean next()

After getting ResultSet cursor to a particular Record position we have to retrieve the data from respective columns, for this, we have to use the following overloaded method

public xxx getxxx(int field_No)
public xxx getxxx(String field_Name)
Where, xxx may be a byte, short, int, etc.

Example :

while(rs.next())
{
      System.out.println(rs.getInt(1));
      System.out.println(rs.getString(2));
      System.out.println(rs.getFloat(3));
}

How JDBC ResultSet is Classified?

ResultSet is of 4 types.

1. Scrollable, updatable
2. Scrollable, non-updatable
3. Non-scrollable, updatable
4. Non-scrollable, non-updatable

SCROLLABILITY AND NON-SCROLLABILITY OF RESULTSET

If cursor can be moved in the forward direction, backward direction and to any record directly, then that is known as scrollability features of a ResultSet. A ResultSet is said to be non-scrollable if cursor can be moved only in the forward direction and that too only one record at a time. In a non-scrollable ResultSet, the cursor cannot be moved in the backward direction.

UPDATABILITY AND NON-UPDATABILITY OF RESULT SET

A ResultSet is said to be updatable if updataion, deletion, and insertion of data of the ResultSet is possible in addition to the reading of data. Read-only ResultSet is a non-updatable ResultSet.

What are the methods of ResultSet that deal with the Cursor?

1. next()
2. previous()
3. first()
4. last()
5. afterLast()
6. beforeFirst()
7. absolute()
8. relative()
9. moveToInsertRow()
10. moveToCurrentRow()

Note: these 10 methods move the cursor.

1. int getRow()
2. boolean isFirst()
3. boolean isLast()
4. boolean isAfterLast()
5. Boolean isBeforeFirst()

Note: these five methods doesn’t move the cursor, they inform the cursor position.

next():

The next() method does two things.

1. Moves the cursor by one record (area) in the forward direction.
2. Returns boolean value after moving the cursor. It returns true if the record exists there, otherwise, it returns false.

previous():

This method does two things

1. Moving the cursor in the backward direction by one record area.
2. Returning true or false.

absolute():

it moves the cursor directly to a specified record in the result set. For example

rs.absolute(4);
rs.absolute(-3);

If the number is negative, counting starts from the last record in the backward direction, and if the number is positive counting starts from the first record and in the forward direction.

relative():

It moves the cursor by the specified number of records from the current position. If a negative number is supplied movement of the cursor is backward otherwise forward. For example

rs.relative(2);
rs.relative(-1);

moveToInsertRow():  This method moves the cursor to a specified row called buffered row or insert row.

moveToCurrentRow(): Before moving the cursor to the special row where the cursor was, the cursor is moving to that row again if this method is called on updatable ResultSet.

getRow(): This method returns the position of the cursor in the ResultSet.

How to Create Different Kinds of Result Sets in Java?

The java.sql.ResultSet has two types of constants

1. Scrollability specifying constants.
2. Updatability specifying constants.

Scrollability constants are 3

1. TYPE_FORWARD_ONLY
2. TYPE_SCROLL_SENSITIVE
3. TYPE_SCROLL_INSENSITIVE

Updatability constants are 2

1. CONCUR_UPDATABLE
2. CONCUR_READ_ONLY

The first scrollability constant (TYPE_FORWARD_ONLY) contributes to making the ResultSet non-scrollable and the other two (TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE) are used for making the Resultset scrollable.

The first updatability constant (CONCUR_UPDATABLE) contributes to making the Resultset updatable and the other constant (CONCUR_READ_ONLY) is used for making the ResultSet non-updatable.

In ResultSet object creation time, we don’t do anything special. Instead, create the statement object in a special manner so that it produces different types of ResultSet.

The createStatement() method is overloaded in Connection object. createStatement() method also take two arguments. The first argument is scrollability specifying constant and the second argument is updatability specifying constant.

Note: By default, a ResultSet is non-scrollable and non-updatable.

What is the Difference between Sensitive and Insensitive Result set?

Whether the ResultSet is sensitive to external change or not is the only difference.

How to Update a record of a database table without sending Update SQL Statement to the database?

It can only be possible by using updatable ResultSet.

Step1. Make the ResultSetupdatabale (scrollable as well)
Statement st = con.createStatement(Resultset.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
RessultSetrs = st.executeQuery(“SELECT ACCNO, NAME, BALANCE FROM ACCOUNT”);

Step2. Move the cursor to the specified record.
For example: rs.absolute(1);

Step3. Update the required column(s).
For example: rs.updateFloat(3, 5600);
Note: Only ResultSet rows column is updated not the tables.

Step4. Update the record in the table as well.
For example: rs.updateRow();

How to Delete a record of a database table without sending Delete SQL Statement to the database?

Using updateable ResultSet.

Step1. Make the ResultSet updatable (Scrollable as well).
Step2. Move the cursor to the specified Record.
For example: rs.first();
Step3. Delete the record.
For example: rs.deleteRow(); //record deleted from both the places.

How to Insert a record of a database table without sending Insert SQL Statement to the database?

When updatable ResultSet is created, a special kind of row known as buffered row (or insert row) is attached to it. The following steps are involved in inserting the record programmatically into the table without using the INSERT SQL statement.

Step1. Make the ResultSet updatable and scrollable as well.
Step2. Move the cursor to the buffered row in order to compose a new row.
rs.moveToInsertRow();

Step3. Compose the new row.
For example:
rs.updateInt(1, 1001);
rs.updateString(2, “rahim”);
rs.updateFloat(3, 5600);

Step4. Insert the record
rs.insertRow(); // record inserted into both the places

Example:

import java.sql.*;
class AccountStoringApplication2 
{
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {
        Class.forName ("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");  
        Statement st = con.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,	   
        ResultSet.CONCUR_UPDATABLE);
    
        ResultSetrs = st.executeQuery ("SELECT accno, name, balance, address FROM ACCOUNT");  
        rs.moveToInsertRow (); 
        rs.updateInt (1, 1008);   
        rs.updateString (2, "SOMMANATH");
        rs.updateFloat (3, 2345);
        rs.updateString (4, "SAMBALPUR");
    
        rs.insertRow ();
        System.out.println ("account stored successfully");
    
        rs.close ();
        st.close ();    
        con.close ();
    } 
}

Note: updatable Resultset are almost not used in real time java projects as no proper support is them.

Types of ResultSet

In JDBC applications ResultSets can be divided into two types: As per ResultSet concurrency there are two types of ResultSets:

Read-only ResultSet :

It is a ResultSet object, it will allow the users to read the data only. To represent this ResultSet object interface has provided the following constant:

public static final int CONCUR_READ_ONLY

Updatable ResultSet :

It is a ResultSet object, it will allow the users to perform updations on its content. To represent this resultset object ResultSetInterface has provided the following constant :

public static final int CONCUR_UPDATABLE

As per the ResultSet cursor movement there are two types of ResultSets:

Forward only ResultSet :

It is a ResultSet object, it allows the users to iterate the data in the forward direction only. To represent this ResultSet, the ResultSet interface has provided the following constant:

public static final int TYPE_FORWARD_ONLY

Scrollable ResultSet:

These are the ResultSet object which will allow the users to iterate the data in both forward and backward directions. To retrieve the data in the forward direction for each and every record we have to check whether the next record is available or not, if it is available we have to move resultset cursor to the next record position. When we refer a particular record then we have to retrieve the data from the respective columns. To retrieve data in backward directions for each and every record we have to check whether the previous record is available or not from the resultset cursor position, if is available then we have to move resultset cursor to the previous record. After moving the resultset cursor to the particular record then we have to retrieve the data from the corresponding columns.

There are two types of Scrollable ResultSets:

Scroll Sensitive ResultSet :

It is a Scrollable resultset object, which will allow the later Database updations. To refer this ResultSet, ResultSet Interface has provided the following constant :

public static final int TYPE_SCROLL_SENSITIVE

Scroll Insensitive ResultSet :

Scroll Insensitive ResultSet is a scrollable ResultSet object, which will not allow the later database updations after creation. To represent this ResultSet, ResultSet Interface has provided the following constant:

public static final int TYPE_SCROLL_INSENSITIVE

In the next article, I am going to discuss the Prepared Statement in JDBC. Here, in this article, I try to explain JDBC ResultSet in Java Application with Examples. I hope you enjoy this JDBC ResultSet in Java Application with Examples article.