• Post Reply Bookmark Topic Watch Topic
  • New Topic

Closing a ResultSetDataModel connection?  RSS feed

 
Izak van Langevelde
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I spent some time using a <h:datatable> getting its data from a ResultSetDataModel, instead of the more usual list, array, whatever.
It works like a charm, although I don't like keeping a database connection open while the datatable is in use. So far, so good.

However, what puzzles me is when I am supposed to close the connection, result set and prepared statement, associated with the ResultSetDataModel.
Conceptually speaking, I would like to cleanup when the datatable is no longer shown, but I have no idea how to catch this moment code-wise.

Any clue is appreciated...
 
Tim Holloway
Bartender
Posts: 18781
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the JavaRanch, Izak!

I've never used a ResultSetDataModel myself. I figure if a project is heavy enough to use JSF, I might as well use an ORM with it, so I use a collection datamodel on the ORM query results.

Actually, the whole idea of a ResultSetDataModel sounds horrible. To make it work between request/response cycles, it seems like you'd have to hold the ResultSet - and therefore its underlying Statement and Connection objects for the duration of the conversation. That's a really inefficient use of critical resources.

In fact, I don't even like holding database resources past the action stage of JSF. I use detached objects in my business logic and a database service tier in my app architecture to marshall and unmarshall my working data sets.

However, what you'd have to do on a ResultSetDataModel is use its getWrappedData() method to get the ResultSet, use its getStatement() method to get the underlying Statement, and use the statement's getConnection method to get the Connection to be closed. Closing the Statement closes the ResultSet, I didn't check to see if closing a Connection closes its Statements.

You could, of course, keep your own local references to Connection, Statement and/or ResultSet and use them, but the net result is the same.
 
Izak van Langevelde
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:
I've never used a ResultSetDataModel myself. I figure if a project is heavy enough to use JSF, I might as well use an ORM with it, so I use a collection datamodel on the ORM query results.

Actually, the whole idea of a ResultSetDataModel sounds horrible. To make it work between request/response cycles, it seems like you'd have to hold the ResultSet - and therefore its underlying Statement and Connection objects for the duration of the conversation. That's a really inefficient use of critical resources.


Agreed. It might work for a huge table which plays a central role, but in most cases I do not expect the costs to outweigh the benefits.


However, what you'd have to do on a ResultSetDataModel is use its getWrappedData() method to get the ResultSet, use its getStatement() method to get the underlying Statement, and use the statement's getConnection method to get the Connection to be closed. Closing the Statement closes the ResultSet, I didn't check to see if closing a Connection closes its Statements.

You could, of course, keep your own local references to Connection, Statement and/or ResultSet and use them, but the net result is the same.


My main problem is when to close these resources. The PreDestroy of a viewscoped bean is called too late, if it is called at all, and for a sessionscoped bean it is called, but I rather not keep my connection open for the entire session. Any ideas on this?
 
Tim Holloway
Bartender
Posts: 18781
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's not the size of the result set that matters. Each machine has a limited number of network connection sockets. When they're all tied up, no task in the machine can get a connection until one of those sockets is freed up. If you're using a Connection Pool for the appserver, that number is reduced even further, since the pool is defined implicitly or explicitly for a given maximum number of Connections. Unless your bean is Application scope, that cascades into limiting the number of user sessions able to obtain a Connection.

A far more efficient approach is to obtain a Connection from a pool, do a query that returns the slice of the ResultSet that you are actually going to display, stuff the results into a Collection, then release the Connection all within the same response cycle. Then do it again if you need to scroll to a different part of the ResultSet. Note that a materialized database view may be appropriate here, depending on how volatile the data is and how your DBMS works. Or, for smaller result sets, just stash the whole thing once and scroll it. A cache may be useful as well if people are doing queries that return common results.

However, the proper place to close the Connection if you simply must hold one open is in the SessionListener's remove session object listener method.

And finally, don't forget that a Connection and therefore a ResultSet is not Serializable, so technically you shouldn't store it in a Session object at all, since session objects MUST be Serializable and you cannot expect a Serialized object with non-Serializable contents to not spontaneously get corrupted if the server decided to serialize it and de-serialize it between requests. That can happen if load balancers or other resource managers decide to "page out" sessions.
 
Izak van Langevelde
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I feel sorry to conclude JSF includes a construct that cannot possibly be used in a sensible way...
 
Tim Holloway
Bartender
Posts: 18781
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Izak van Langevelde wrote:I feel sorry to conclude JSF includes a construct that cannot possibly be used in a sensible way...


I suspect that there is a sensible way, but I have no idea what it is. When I first read about the ResultSetDataModel, I wondered about it and I still do.

It's possible that it's part of a stunted path in JSF design. JSF was originally intended as an abstract architecture to operate on a variety of platforms - perhaps even as a desktop MVC environment like Swing. However, all people ever really saw in it was its use as a web framework, and JSF2 in particular reflects that, as it's much more involved specifically with HTTP and HTML and less abstract than JSF1 was.

Anyway, the collection-based DataModels have always worked fine for me.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!