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;

    }

}


Displaying an Image from Servlet

package com.shailesh;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import javax.servlet.Servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ImageServlet extends HttpServlet implements Servlet {
    /**
     * 
     * @see javax.servlet.http.HttpServlet#HttpServlet()
     */
    public ImageServlet() {
        super();
    }

    /**
     * 
     * 
     * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest arg0,
     *      HttpServletResponse arg1)
     */
    protected void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }

    /**
     * 
     * 
     * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest arg0,
     *      HttpServletResponse arg1)
     */
    protected void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("image/jpeg");
        File file = new File("c:\\myImage.jpg");
        FileInputStream fileInputStream = new FileInputStream(file);
        int i = 0;
        byte[] buffer = new byte[1024];
        while ((i = fileInputStream.read(buffer)) >= 0) {
            response.getOutputStream().write(buffer, 0, i);
        }
    }

}