How to correctly use ResultSet with h:dataTable

Question

Hey there,

The problem is, that after displaying the ResultSet with h:dataTable, the connection is left open. If I close it, it closes the ResultSet too. I'm thinking about copying the ResultSet data into some HashMap/ArrayList combo. Is there a good way to deal with this problem?

Thanks in advance, Daniel

Answer 1

Indeed, you should always acquire and close the Connection, Statement and ResultSet in the shortest possible scope (preferably already inside the very same method block) and you should never pass any of them outside the DAO class. You need to map the ResultSet to a List<Data> wherein Data represents each row of the table. Here's a basic example how to map a resultset:

List<Data> items = new ArrayList<Data>();
...
while (resultSet.next()) {
    Data item = new Data();
    item.setColumn1(resultSet.getString("column1"));
    item.setColumn2(resultSet.getString("column2"));
    items.add(item);
}
...
return items;

Then you can just use it in the h:dataTable's value attribute.

For more examples and insights you may find one or both of the following articles useful:
http://balusc.blogspot.com/2006/06/using-datatables.html
http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html

Good luck.

Answer 2

Are you using ResultDataSetModel? If yes note that in javadoc stays Note that the specified ResultSet MUST be scrollable.

Statement stmt=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

Here is a complete example of ResultDataSetModel usecase.