Hibernate Data Access for reporting (session.iterate())

[Aaron]

I've seen a lot of discussion about OpenSessionInView and the recommended strategy seems to be one that obtains a full object graph in the service layer. My problem: What if that object graph is huge?

I need to build (arggg) MS Excel report files on a database table that has over 100 columns. Most reports will vary from 500 to 1,000s of rows. According to "recommended" approaches, I should grab the object graph in a .find() call, which will give me a List of 1,000s of domain objects. For me, that's a whole lot of memory to use up.

As I understand Hibernate, the .iterate() method returns an Iterator implementation that maintains an open connection to the database, and only populates a domain object when I call .next(). This means only one domain object is required to be in memory at a time.... much more efficient for my purposes.

Unfortunately, this requires a Hibernate session to be open until the view is completely rendered (an Excel document in my case). My uneducated test to return the Iterator from the service layer failed. I get a SQLException prohibiting movement on the ResultSet after the connection is closed (I presume my service layer closed the session somewhere in HibernateTemplate... I got the iterator with HibernateTemplate.iterate()).

Is my only option to use OpenSessionInView filter? I've had some problems configuring this, but I won't go into that now. For now, I'd like to find an alternative.

[Dan Washusen]

Not really offering a alternative solution but, I think the OpenSessionInView is the perfect solution to your problem... You could possibly only open the session in view for the reports section of your app...

[Dan Washusen]

Hmm, now that I think about it, are you sure you want to use session.iterate()? It can result in a lot of queries against the DB...

The first query you execute results in only the primary keys being fetched by Hibernate, then each iterator.next() results in another query to fetch the data. Not really what I would have expected, but there you have it.

Hibernate in Action has a section of writing reports using hibernate, might be worth checking out. The general idea is to use the DB to aggregate and group the data and only return the columns relevant to your report. The example they give goes something like this:

select new ItemRow(item.id, item.description, bid.amount)
from Item item join item.bids
where bid.amount > 100