Java coding best-practices for reusing part of a query to count

Question

The implementing-result-paging-in-hibernate-getting-total-number-of-rows question trigger another question for me, about some implementation concern:

Now you know you have to reuse part of the HQL query to do the count, how to reuse efficiently?

The differences between the two HQL queries are:

1. the selection is count(?), instead of the pojo or property (or list of)
2. the fetches should not happen, so some tables should not be joined
3. the order by should disappear

Is there other differences?

Do you have coding best-practices to achieve this reuse efficiently (concerns: effort, clarity, performance)?

Example for a simple HQL query:

    select       a     from A a join fetch a.b b where a.id=66 order by a.name
    select count(a.id) from A a                  where a.id=66

---

UPDATED

I received answers on:

• using Criteria (but we use HQL mostly)
• manipulating the String query (but everybody agrees it seems complicated and not very safe)
• wrapping the query, relying on database optimization (but there is a feeling that this is not safe)

I was hoping someone would give options along another path, more related to String concatenation.
Could we build both HQL queries using common parts?

Answer 1

Have you tried making your intentions clear to Hibernate by setting a projection on your (SQL?)Criteria? I've mostly been using Criteria, so I'm not sure how applicable this is to your case, but I've been using

getSession().createCriteria(persistentClass).
setProjection(Projections.rowCount()).uniqueResult()

and letting Hibernate figure out the caching / reusing / smart stuff by itself.. Not really sure how much smart stuff it actually does though.. Anyone care to comment on this?

Answer 2

Well, I'm not sure this is a best-practice, but is my-practice :)

If I have as query something like:

select A.f1,A.f2,A.f3 from A, B where A.f2=B.f2 order by A.f1, B.f3

And I just want to know how many results will get, I execute:

select count(*) from ( select A.f1, ... order by A.f1, B.f3 )

And then get the result as an Integer, without mapping results in a POJO.

Parse your query for remove some parts, like 'order by' is very complicated. A good RDBMS will optimize your query for you.

Good question.