Using CachedRowSet in Java

Number of times we do not want to maintain a persistent connection while extracting query data from a ResultSet object. The production environment where we want to quickly connect to database extract the result and release the connection. As a developer I always desired if it could be possible to return the ResultSet back to user after closing the connection, but resultset requires a db connection to remain open until processing completes. Wouldn't it be cool to have ability to return a ResultSet object to a different class or thin client without losing the results of a query. Using CachedRowSet we have the one to accomplish same task without complex coding involved.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

/**
 * @author Shailesh Chandra
 *  
 */
public class CachedRowSetExample {

    public static void main(String[] args) throws Exception {
        CachedRowSetExample example = new CachedRowSetExample();
        CachedRowSet cachedRowSet = example.executeQuery();
        while (cachedRowSet.next()) {
            System.out.println(cachedRowSet.getString(1) + "\t" + cachedRowSet.getString(2) + "\t" + cachedRowSet.getString(3));
        }
        cachedRowSet.release();

    }

    public CachedRowSet executeQuery() throws Exception {
        CachedRowSet cachedRowSet = null;
        String query = "select * from TEST1";

        Connection connection = null;
        ResultSet resultSet = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@server:1521:service_name";
            connection = DriverManager.getConnection(url, "shailesh", "shailesh");
            System.out.println("Connection is " + connection);

            PreparedStatement preparedStatement = connection.prepareStatement(query);
            resultSet = preparedStatement.executeQuery();
            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(resultSet);

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {

            if (resultSet != null) {
                System.out.println("Closing resultSet");
                resultSet.close();
            }
            if (connection != null) {
                System.out.println("Closing connection");
                connection.close();
            }

        }
        return cachedRowSet;

    }

}