ebjQl, ho do I get COUNT while using LIMIT/OFFSET

[silviomatthes]

Hi all,

We're using LIMIT/OFFSET with DynamicQL in our JBoss-application for a complex query. That works fine.

Now I want to know how many rows are in the full result.
I know that JBoss does not send the LIMIT/OFFSET-clause to the database but handles this itself. So, JBoss should know how many rows are in this resultset.

Is there ANY way to get this rowcount after executing the query?

I know I could use COUNT with the query to get the desired value, but that would mean more load on the database, which is already the bottleneck.

Versions used: JBoss 3.2.5
PostgresQL 7.3.4
The ejbQL-Query used: "SELECT OBJECT(d) FROM Document d ORDER BY d.archivierungAm DESC LIMIT ?1 OFFSET ?2"


any help would be highly appreciated,

Kind Regards,

Silvio

[triathlon98]

I think there is no alternative but using count(*).
If Jboss needs to count the number of records in the DB, then the resultset will need to be traversed, causing an even bigger bottleneck.

Joachim

[silviomatthes]

Hello again,

If Jboss needs to count the number of records in the DB, then the resultset will need to be traversed

I don't think so. JBoss calls the database without any LIMIT/OFFSET-clauses and gets back a collection/resultset of primary-keys. It should only do a size() or something similar (I don't know exactly what's the resulttype) to the resultset.

Maybe that's kind of a feature-request, but I hoped to get that count without changes to JBoss.

So, again some questions:

Is the information about the resultsetsize known in jboss after the database-call?
Is it possible to get this information from JBoss?
If no, what changes should be made to get this information?


I think that I'm not the only one who could benefit from this feature, because everytime a big resultset should be displayed in pages (best made with LIMIT/OFFSET) the overall-count of the resultset is useful.

Kind Regards,

Silvio

[triathlon98]

"SilvioMatthes" wrote:
Hello again,

If Jboss needs to count the number of records in the DB, then the resultset will need to be traversed

I don't think so. JBoss calls the database without any LIMIT/OFFSET-clauses and gets back a collection/resultset of primary-keys. It should only do a size() or something similar (I don't know exactly what's the resulttype) to the resultset.

Thing is that whether this returns a "real" result is IIRC database-dependent.

Joachim

[aloubyansky]

There is no size() method on ResultSet. And no way to know the number of elements thrown away with limit/offset.