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
Code:
scrollableResults.last();
scrollableResults.getRowNumber();
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