Tuesday, May 9, 2023

Difference between Connected vs Disconnected RowSet in Java JDBC | RowSetProvider, RowSetFactory Example

RowSet interface is another key interface from JDBC API, which extends the ResultSet interface to provide support for the JavaBean component model. Along with convenient methods to set connection parameters like the setUrl(), setUsername(), setPassword() and setCommand(), it also defines getters and setters for different data types e.g. getLong() and setLong(). Another feature of RowSet is that it supports event listeners. You can attach listeners to RowSet object to listen for events, much like Observer design pattern. RowSet acts as Subject and notifies Observer on events like change in cursor location, change in a row, or change in the entire RowSet. There are two types of RowSet available in JDBC 4.1 API, connected and disconnected.

A connected RowSet always keeps a connection with the database, while a disconnected RowSet connects to the database, gets the data, and then closes the connection.

This doesn't prevent working with data though, you can still change data in a disconnected state, but to commit, the connection needs to be re-established by RowSet.

Java provides five different types of RowSet, JdbcRowSet, JoinRowSet, CachedRowSet, FilteredRowSet and WebRowSet. Out of these five only JdbcRowSet is connected RowSet, the rest of them are disconnected row sets.

It's very important to understand these new concepts from JDBC API, there are very popular JDBC Interview questions based upon these concepts like the difference between RowSet and ResultSet and the difference between Connected vs Disconnected RowSet.




Difference between Connected and Disconnected RowSet

As I mentioned, the main difference between connected and disconnected RowSet is that the former always keeps a database connection, while later doesn't. It connects to the database, get the data and then closes the connection. Here are a couple of more differences between them :

1) Disconnected database connected to the database only when they want to read or write, all the times they are disconnected from the database, on the other hand, Connected database keep JDBC connections alive all time.

2) Only JdbcRowSet from JDBC 4.1 API is connected, rest like CachedRowSet, WebRowSet are disconnected RowSet implementation.

3) Disconnected RowSet is Serializable and that's why suitable for sending over the network


RowSet Example in Java

Let's see a simple example of RowSet in Java. In this example, we will retrieve employees' records using RowSet instead of the ResultSet interface. So far, you might have only seen a ResultSet object returning query result, but from JDBC 4.1 API, you can use any implementation of RowSet implementation for querying the database and retrieving results.

In this JDBC example, we will use the JdbcRowSet class. In order to create object of JdbcRowSet, you need a RowSetFactory, which can be created by using newFactory() method of RowSetProvider.

Once you have an object of RowSet, you can pass configuration details like database URL, username and password by calling their respective setProperties() method like setURL(), setUsername() and setPassword().

After configuration, its time to specify our SQL SELECT Query, we will use a method called setCommand() for providing the query. Now you can execute the query by calling execute() method. Did you notice a much cleaner and simple API?

After query execution its time to retrieve data, which is very similar to what you are used by calling various getXXX() methods from ResultSet. You just need to iterate through RowSet by checking hasNext() method in the while loop and getting data row by row.

import javax.sql.rowset.*;
import java.sql.*;

/**
 * Java Program to illustrate how to use RowSet, RowSetProvider, 
 * and RowSetFactory in JDBC
 * In this example, RowSet is used to retrieve data from database 
 * instead of ResultSet.
 * @author Javin Paul
 */
public class RowSetDemo {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/addressBook";
        String userName = "root";
        String password = "mysql123";
       
        try {
            // first, create a factory object for rowset
            RowSetFactory rowSetFactory = RowSetProvider.newFactory();

            // create a JDBC rowset from the factory
            JdbcRowSet rowSet = rowSetFactory.createJdbcRowSet();

            // Set connection properties
            rowSet.setUrl(url);
            rowSet.setUsername(userName);
            rowSet.setPassword(password);

            // Set SQL Query to execute
            rowSet.setCommand("SELECT * FROM contact");
            rowSet.execute();
            System.out.println("id \tName \tDepartment \tEmail \t\Salary");

            // Iterating over RowSet
            while (rowSet.next()) {
                System.out.println(rowSet.getInt("id") + "\t"
                        + rowSet.getString("name") + "\t"
                        + rowSet.getString("department") + "\t"
                        + rowSet.getString("email") + "\t"
                        + rowSet.getString("salary"));
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }
}


Output
Id      Name    Department      Email   Salary
1       Jack    Sales           jack@bluechip.com       9000
2       Jill    Marketing       jill@bluechip.com       8000
3       Tom     Accounts        tom@bluechip.com        7000

You can see how easy is to use RowSet in DAO  of your Java program, or you can also use them along with DAO Design Pattern. Since RowSet inherits from java.sql.ResultSet interface, you can actually pass RowSet implementation to any method, which expects ResultSet.

This diagram will give you more idea about ResultSet and RowSet class hierarchy.  It is also useful to find out which RowSet implementation is Connected and which one is Disconnected. You can see that there are two major implementations of the RowSet interface, JdbcRowSet which is connected, and CachedRowSet which is disconnected.

Connected vs Disconnected RowSet in Java, Class hierarchy JDBC

CachedRowSet further has a couple of more implementations in terms of WebRowSet and its subclasses. This table provides a nice comparison of features supported by different RowSet implementations in the JDBC API.

Features comparison of JdbcRowSet and CachedRowSet vs WebRowSet JDBC

You can see that JdbcRowSet is Scrollable, Updateable, and Connected, while CachedRowSet is also Serializable because it's disconnected. WebRowSet further adds the feature of generating and consuming XML.


Things to remember about RowSet, ResultSet, and RowSetFactory

It's time to revise a few key points about these classes from JDBC 4.1 API.

1) RowSet extends ResultSet interface to add support of Java Bean model e.g. it adds methods like setUrl() which makes it easy to connect to the database.

2) There are two types of RowSet, connected and disconnected. connected RowSet object make a connection with the database and maintain that connection throughout its life cycle. On the other hand, a disconnected RowSet makes a connection with the data source, reads data from it, and close the connection. A disconnected RowSet can still make changes to its data while it is not connected with the database, but it must re-establish a connection to commit those changes.

3) There are five implementations of RowSet available in JDBC 4.1 API, JdbcRowSet, CachedRowSet, JoinRowSet, WebRowSet and FilteredRowSet. Out of these five, only JdbcRowSet is a connected RowSet, the rest of them are disconnected RowSets.

4) RowSetProvider provides methods to get a RowSetFactory implementation, which can be used to instantiate a relevant RowSet implementation. Both of these are added from Java 1.7 only.

5) Disconnected RowSet classes are also Serializable in Java.

That's all about What is RowSet in JDBC, How to use RowSet to connect to the database, and the difference between Connected and Disconnected RowSet in Java. We have also see examples of using RowSetProvider and RowSetFactory which can be used to create different types of RowSet in Java. Remember, only JdbcRowSet is the connected RowSet, rest of them are disconnected one.

Once you are familiar with the key concept of RowSet class, you should be able to answer these questions on your own. Btw, if you are new to JDBC and looking for a comprehensive online course to learn JDBC in-depth then I also suggest you check out these JDBC online courses on Udemy. It's a great course of direct classroom lectures and covers JDBC in depth


3 comments:

  1. Hi, great post, very useful :)

    There is one little mistake in features table - CacheRowSet and WebRowSet are checked as Connected.

    ReplyDelete
  2. This post is great and gives nice explanation. You gave an example of Connected rowset but not disconnected row set to differentiate, how our code will change, do we just need to change "JdbcRowSet " to some disconnect RowSet. Example will be a great explanation.

    Thanks

    ReplyDelete
  3. I have a doubt, If ResultSet is not Serializable then it is possible to send back chunk of rows from DB Server to Client to work on. ?

    ReplyDelete