Hibernate version:2.1.8

I had posted a question about a way to translate HQL to SQL. Here's why I'd like to get the underlying SQL.

Basically I need to display a paged table along with the total number of pages. Most of the pagination solutions for getting number of records from a Query object rely calling


however as pointed out here http://www.hibernate.org/243.html, Oracle 9 does not have server side scrollable cursors and as a result the entire dataset is cached on the client. Our database has lots of records and our JVM would run out of memory with a driver that did client side caching when scrollableResults.last() is called.

The same URL also proposes a DoubleQueryPage class which executes a secondary count(*) query to get the number of results. The problem is that the implementation does a count(*) on the table corresponding to the primary entity class and this will return a wrong row count if the Query has filters or inner joins.

So I essentially want to follow the same approach except that I want to get the underlying SQL query from the Query object and the replace the substring till the first " from" string and replace it with "select count(*) ".

This would be much easier if the Query interface had a count() method.

If there is an easier way to accomplish this, please advise.

Thanks,
Sanjiv

IMHO your count(*) will work only for extremely simple cases, hence its not the solution. You have to consider that there may be joins, projections, you should find some way to recover aliases and so on, so this would imply non trivial manipulations of the hql. In fact this shows that it is the wrong path.

I will update the code in the wiki as soon as I can.

Thanks for the reply.



If I replace the first occurence of "selelct .... from [rest of query]" in the translated SQL of the provided HQL with "select count(*) from [rest of query]" it would already incorporate the joins, projections, filters etc., regardless of aliases present in the [rest of query] portion, wouldn't it?

Can you provide an example where it wouldn't work?




Looking forward to checking this out.

Thanks,
Sanjiv

I tried with several from clauses in the same query, and it seems to work anyway. So maybe this is the correct solution: if you can test it in detail, I will integrate it in the pagination code. Thanks.

Thinking about it some more, I think I can do the string replacement on the HQL itself and avoid the HQL->SQL translation.

HQL queries can be of the form "select ... from Entity e [rest of hql]" or "from Entity e [rest of hql]"

So all I would need to do is change to HQL to the form

"select count(*) from Entity e [rest of hql]"

Sanjiv

I have built a HQL query builder to deal with this sort of issue for HIbernate 2.x. Don't need to now that Hibernate 3.x QBE supports count() etc etc etc.

Could you give a pointer to this ?



I will check it; but in pagination we need to support the entire spectrum of hql queries.

Thanks