Monday, April 28, 2014

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 e.g. 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 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 connection with database, while a disconnected RowSet connects to database, get the data and then close the connection. This doesn't prevent from working with data though, you can still change data in disconnected state, but to commit, 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, rest of them are disconnected row sets. It's very important to understand these new concepts form JDBC API, there are very popular JDBC Interview questions based upon these concepts e.g. difference between RowSet and ResultSet and difference between Connected vs Disconnected RowSet. Once you are familiar with key concept of RowSet class, you should be able to answer these question by your own.


Difference between Connected and Disconnected RowSet

As I mentioned, main difference between connected and disconnected RowSet is that former always keeps a database connection, while later doesn't. It connects to database, get the data and then close the connection. Here are 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 connection alive all time.

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

3) Disconnected RowSet are 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 record using RowSet instead of 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 database and retrieving result. In this JDBC example, we will use 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 e.g. setURL(), setUsername() and setPassword(). After configuration, its time to specify our SQL SELECT Query, we will use method called setCommand() for providing 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() method from ResultSet. You just need to iterate through RowSet by checking hasNext() method in 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 inherit from java.sql.ResultSet interface, you can actually pass RowSet implementation to any method, which expect ResultSet. This diagram will give you more idea about ResultSet and RowSet class hierarchy.  It also useful to find out which RowSet implementation are Connected and which one are Disconnected. You can see that there are two major implementation of RowSet interface, JdbcRowSet which is connected and CachedRowSet which is disconnected.

Connected vs Disconnected RowSet in Java, Class hierarchy JDBC

CachedRowSet further has couple of more implementation in terms of WebRowSet and its subclasses. This table provides a nice comparison of features supported by different RowSet implementation in 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 its disconnected. WebRowSet further adds feature of generating and consuming XML.


Things to remember about RowSet, ResultSet and RowSetFactory

It's time to revise 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 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 data source, read data from it and close the connection. A disconnected RowSet can still make changes to its data while it is not connected with database, but it must re-establish 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, 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 database, and difference between Connected and Disconnected RowSet in Java. We have also see example 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.

2 comments :

Anonymous said...

Hi, great post, very useful :)

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

Anonymous said...

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

Post a Comment