What is result set? What are its variations? Explain each variation with suitable example.

 A ResultSet is a Java object that contains the results of executing an SQL query. In other words, it contains the rows that satisfy the conditions of the query. The data stored in a ResultSet object is retrieved through a set of get methods that allows access to the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet, making it the current row.

Java ResultSet interface is a part of the java.sql package. It is one of the core components of the JDBC Framework. ResultSet Object is used to access query results retrieved from the relational databases.

ResultSet maintains cursor/pointer which points to a single row of the query results. Using navigational and getter methods provided by ResultSet, we can iterate and access database records one by one. ResultSet can also be used to update data.

                  OR,

Result set functionality in JDBC 2.0 includes enhancements for scrollability and positioning, sensitivity to changes by others, and updatability. Scrollability, positioning, and sensitivity at determined by the result set type. And, updatability is determined by the concurrency type. We specify the desired result set type and concurrency type when we create the statement object that will produce the result set by using the syntax as below:

Statement createStatement(int resultSetType, int Concurrency Type)

A result set where we can move back and forth is called scrollable Result Set. If we want a scrollable result set, then we must also specify sensitivity. Specify the scroll-sensitive type for the result set to be scrollable and sensitive to underlying changes. Specify the scroll-insensitive type for the result set to be scrollable but not sensitive to underlying changes. If we do not provide any value for resultSetType, forward Only result set is created

.

A result set that allows us to update data in a result set and then copy the changes to the database is called updateable result set. This includes inserting new rows into the result set or deleting existing rows. Updatability in a result set is associated with Concurrency Type in database access. Concurrency Type can be either Read-only or updateable. Updatable result sets allows updates, inserts, and deletes to be performed on the result set and copied to the database.

ResultSet characteristics are as follows:

• It maintains a connection to a database and because of that, it can’t be serialized.
• it can not pass the Result set object from one class to another class across the network.
• ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
• javax.sql.rowset.RowSet is a wrapper around a ResultSet which makes it possible to use the result set as a JavaBeans component in JDBC java.
• ResultSet alone cannot be used as a JavaBeans component.

ResultSet Example

We will be using MySQL for our example purpose. Use below DB script to create a database and table along with some records.

create database empdb;

use empdb;

create table tblemployee (empid integer primary key, firstname varchar(32), lastname varchar(32), dob date);

insert into tblemployee values  (1, 'Mike', 'Davis',' 1998-11-11');

insert into tblemployee values  (2, 'Josh', 'Martin', '1988-10-22');

insert into tblemployee values  (3, 'Ricky', 'Smith', '1999-05-11');

Let’s have look at the below example program to fetch the records from the table and print them on the console. Please make sure you have the MySQL JDBC driver in the project classpath.

package com.journaldev.examples;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Date;

 

/**

 * Java Resultset Example of Retrieving records.

 * 

 * @author pankaj

 *

 */

 

public class ResultSetDemo {

 

    public static void main(String[] args) {

        String query = "select empid, firstname, lastname, dob from tblemployee";

        Connection conn = null;

        Statement stmt = null;

        try {

            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb", "root", "root");

            stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {

                Integer empId = rs.getInt(1);

                String firstName = rs.getString(2);

                String lastName = rs.getString(3);

                Date dob = rs.getDate(4);

                System.out.println("empId:" + empId);

                System.out.println("firstName:" + firstName);

                System.out.println("lastName:" + lastName);

                System.out.println("dob:" + dob);

                System.out.println("");

            }

            rs.close();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            try {

                stmt.close();

                conn.close();

            } catch (Exception e) {}

        }

    }

}

Output:

empId:1

firstName:Mike

lastName:Davis

dob:1998-11-11

 

empId:2

firstName:Josh

lastName:Martin

dob:1988-10-22

 

empId:3

firstName:Ricky

lastName:Smith

dob:1999-05-11

ResultSet Types & Concurrency

We can specify type and concurrency of  ResultSet while creating an instance of Statement, PreparedStatement or CallableStatement.

statement.createStatement(int resultSetType, int resultSetConcurrency)

ResultSet Types

1) Forward Only (ResultSet.TYPE_FORWARD_ONLY)

This type of ResultSet instance can move only in the forward direction from the first row to the last row. ResultSet can be moved forward one row by calling the next() method. We can obtain this type of ResultSet while creating Instance of Statement, PreparedStatement or CallableStatement.

Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery("select * from tbluser");

2) Scroll Insensitive (ResultSet.TYPE_SCROLL_INSENSITIVE)

Scroll Insensitive ResultSet can scroll in both forward and backward directions. It can also be scrolled to an absolute position by calling the absolute() method. But it is not sensitive to data changes. It will only have data when the query was executed and ResultSet was obtained. It will not reflect the changes made to data after it was obtained.

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,          ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery("select * from tbluser");

3) Scroll Sensitive (ResultSet.TYPE_SCROLL_SENSITIVE)

Scroll Sensitive ResultSet can scroll in both forward and backward directions. It can also be scrolled to an absolute position by calling the absolute() method. But it is sensitive to data changes. It will reflect the changes made to data while it is open.

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,        ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery("select * from tbluser");

ResultSet Concurrency

1) Read Only (ResultSet.CONCUR_READ_ONLY)

It is the default concurrency model.  We can only perform Read-Only operations on ResultSet Instance. No update Operations are allowed.

2) Updatable (ResultSet.CONCUR_UPDATABLE)

In this case, we can perform update operations on ResultSet instance.

            OR,

ResultSet Types

The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.

The sensitivity of a ResultSet object is determined by one of three different ResultSet types:

TYPE_FORWARD_ONLY: The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

TYPE_SCROLL_INSENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

TYPE_SCROLL_SENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.

The default ResultSet type is TYPE_FORWARD_ONLY.

Note: Not all databases and JDBC drivers support all ResultSet types. The method DatabaseMetaData.supportsResultSetType returns true if the specified ResultSet type is supported and false otherwise.