Using CachedRowSet

When developing MVC-centric web applications, we often have to create custom classes to contain the results of a database query. This is because we can never pass a JDBC ResultSet from the servlet to the JSP. Once the underlying Statement object of a ResultSet is closed, the ResultSet is closed along with it. So many developers have to create some sort of a "Data Transfer Object" in order to contain the data within the ResultSet.

CachedRowSet, in a nutshell, is just a disconnected ResultSet. Unlike a ResultSet, you can access data within a CachedRowSet even if the underlying Connection or Statement object has already been closed.

To use CachedRowSet, be sure to import the right package.

import com.sun.rowset.CachedRowSetImpl;

Populating a CachedRowSet is simpler that it might seem. Assuming you have a ResultSet variable resultSet, here's how to create one.

CachedRowSetImpl crs = new CachedRowSetImpl();
crs.populate(resultSet);

That's all there is to it. Now, we can pass the database query results from the servlet to the JSP without having to use an intermediary object.

request.setAttribute("results", crs);
request.getRequestDispatcher("/results.jsp").forward();

Once you have populated the CachedRowSet, you can access its data as if it was an ordinary ResultSet! In the JSP, you can access the results using scriptlets:

<%
CachedRowSetImpl crs = (CachedRowSetImpl) request.getAttribute("results");
while (crs.next()) {
  out.println(crs.getInt("productId"));
  out.println(crs.getString(2));
}
%>

What? Scriptlets!?! Can you use the JSTL <c:foreach> tag to iterate through a CachedRowSet instead? The answer is no, but I have discovered a way to do this using a workaround, which will be the subject of another post. Til then! :)