How do you manage connections?

[Jeff Albertson]

This is a continuation of a discussion from the JDBC forum in this thread about when to acquire connections from a connection pool.

Here's my situation, which is pretty typical: I'm using JSF to wire backing beans to my JSP. Suppose I have a page with several separate tables on it:

<h:dataTable value="#{pc_backingBean.customerList}" ...>

<h:dataTable value="#{pc_backingBean.supplierList}" ...>

Method getCustomerList and the others each select a list of beans from the database. Should *each* of these methods be getting and releasing a connection from the pool every time they are called, or is this micro-management? If I handled my connections in this way, some pages would be acquiring and releasing connections a dozen times just to render themselves.

Or should I try to arrange it so that one connection is used per client request cycle? Currently, I was a ServletFilter to acquire and release the connection, but this has been critized as a misuse of filters.
[ February 16, 2006: Message edited by: Jeff Albertson ]

[Tim Holloway]

Well, getting a connection from a pool is usually pretty lightweight. Getting a connection from scratch usually isn't. I tend to like to minimize trips to the well just on general principles. But the real test is measured performance. Anything else is just vanity.

You could of course create a request object that gets the connection once and makes it available to everyone. But I suspect that it probably won't speed things up enough to justify the extra complexity.

[Jeff Albertson]

Since this is so common, I was wondering if there was a JSF best practice advice for this?

[Gerardo Tasistro]

All my data gets and sets from the dbase are action triggered not render triggered. That is an action be it load a page or hit a button or link activate it. I also use dataModel and encapsulate the info in it.

Basically the JSF has a controller bean (the managed bean) which knows what to do with all the buttons and has all the "report" or data containers. The data containers have all the dbase logic and actual lists. So in your case I'd

Create JSF that has a controller bean
Create a controller class which has a customerListReport and customerListReportListModel.
Create a CustomerListReport that does all the data I/O to dbase and has a customerList.

Have the controller init customerListReport and customerListReportListModel and link through customerListReportListModel.setWrappedData(customerListReport.getReportList). Now the reportList and its model al linked. Even if the customerList is empty now.

You pass customerListReportListModel to the JSF.

<h ataTable value="#{pc_backingBean.customerListReportListModel}" ...>

This does nothing as the list is empty (or not if you querried on load).

Now suppose you have a select options/ text input area for some query parameters. The user inputs data there and hits a button. I'd create a class called QueryCriteria that catches that input. So the controller exposes queryCriteriaData to the JSF and a method called doQuery. The button is bound to doQuery. You hit the button and the controller gets the action, digests the queryCriteriaData and passes it to the customerListReport throuh a method customerListReport.queryByCriteria(Criteria queryCriteria);. The method queryByCriteria now goes to the database and fetches all info. Loads it into the reportList and since this list is bound to the model it is transparently displayed in the JSF.

Notice how I now have a subset from the database. Say 20 records in the reportList. These records are read repeatedly during the JSF lifecycle, but they are in RAM not dbase. As long as I don't hit a button nothing takes me to the database. I can edit them. Get a master/detail view. Edit the detail view and all reads and writes are done to the snapshot. When I'm done I hit the Apply Changes button on the view and this calls a controller method that calls the report's persist method.

A move one page ahead is a simple report method that increases the current cursor by the page size (all values set in the report class not the controller) and refreshes the report.

Hope that helps.