Hibernate version: 2.1.6

Name and version of the database you are using: SQL Server 2000 / jtds 0.9 rc 1

Hi,

I hope someone can help me out here with a scenario that I've got.

We've got an address table with quite a lot dependencies on other tables that needs to be queried with all kinds of selects. I've solved this using Criteria building, which work perfectly.

However, sometimes we need to get a partial result from a query. For instance, say that a query will result 1000 rows, but I only need 100 of them, we would like to get the results evenly divided from the result set.
So in the above example, I would like to return row 10, 20, 30, 40 etc.

I've tried this with the following snippet:

//rowNrs will create a list with rownrs to fetch like: [10,20,30,...]
List rowNrs = DatabaseUtil.createRowList(leadMaxResults, count);
for (int i = 0; i < rowNrs.size();i++){
int rowNr = ((Integer)rowNrs.get(i)).intValue();
addressCriteria.setFirstResult(rowNr);
addressCriteria.setMaxResults(1);
results.add(addressCriteria.list().get(0));
}

But this code generates an exception:

java.sql.SQLException: Invalid column name AdressID0_.


But when I even change the last line
to addressCriteria.list(); so I can test the speed of this solution, it is way to slow (the query can be quite heavy sometimes).

So what I would like to have is that my Criteria query only runs once, but I would not like to have all the objects be loaded into memory, only the ones I hand pick myself.

I has also seen that a Query can result Scrollable result. Would this be the solution I'm looking for? If so, is there a way to do some kind of addressCriteria.toString() so I can use the Criteria classes to nicely build my query?

Any suggestions are appreciated.

Regards,
Wouter

probably ScrollableResults is the best way.


But what does the generatd SQL look like??

I've tried the ScrollableResult solution and the performance is acceptable enough.

But now instead of using Criteria, I have to dynamically create a query string which I personally find a lot less elegant than Criteria (see code snippet below).

What I've seen so far lead me to the following conclusions:
1 Criteria results can't be scrolled
2 There is no way to translate a Criteria object to a hql string.

Am I correct? If so, will these be features available in hibernate 3?

Regards,
Wouter

HQL Code
-------------


Criteria code
---------------

(1) Criteria.scroll() is available in Hibernate3.
(2) No, you can't translate Criteria->HQL, and I can't think of any reason why you'd want to...

I can!!!!

I'm writing an ad-hoc reporting tool that uses hibernate to provide the user with a metamodel of the data. The user uses that model to select what should appear in the report.

The report is going to be done in JasperReports, and I'd like to be able to store the SQL in the report template and use a simple SQL-based jasperreports data source with it.

Ideally, I'd get the HQL out of the criteria and store that (to maintain the hibernate layer), but my understanding is that Criteria objects are translated directly to SQL, bypassing HQL entirely.

The point is that I think there is a good case for access to the SQL that is generated by the Criteria API. Anyone who wants to allow the user to dynamically create a search and then save that search off for later use would need it.

Is there any way (listeners, interceptors, ANYTHING) that will allow me to access the SQL?