Hibernate HQL: Get count of results without actually returning them

hibernate, hql

Solution

This should do the trick:

select count(*) FROM Company c JOIN ...

There is no sub-select involved, just instead of returning `Company`, you return the count.

Edit: The `FETCH` is out of place now. You're not returning the entities from the query, but only the count, thus, Hibernate complains. Removing it should help:

select count(product) from org.myCompany.applicant.entity.Applicant applicant 
    LEFT OUTER JOIN applicant.products product

Problem

I want to get the count of the results of a dynamically-generated HQL query, without actually getting the list of results. Say that the query I have is something like: ``` select Company company LEFT OUTER JOIN FETCH products product ``` I read in the Hibernate documentation that: You can count the number of query results without returning them: ``` ( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue() ``` I suspected that I should be replacing the .... with my query, but that does not work, as HQL does not support sub-selects in FROM. So, how should I count the results of a dynamically generated HQL query? I think that by executing it and getting the .size() of the results list may be unnecessary overhead. Cheers! **UPDATE: ** I used this regex to convert my query: ``` Number num = (Number) em.createQuery(dynamicQuery.replaceAll("select \\w+ from ", "select count(*) from ")).getSingleResult(); ``` And I get this: Blockquote EJB Exception: ; nested exception is: java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=product,role=org.myCompany.applicant.entity.Applicant.products,tableName=PRS_DEV.PRODUCT,tableAlias=products1_,origin=PRS_DEV.APPLICANT applicant0_,colums={applicant0_.APPLICANT_ID ,className=org.myCompany.product.entity.Product}}] [select count() from org.myCompany.applicant.entity.Applicant applicant LEFT OUTER JOIN FETCH applicant.products product ]; nested exception is: java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=product,role=org.myCompany.applicant.entity.Applicant.products,tableName=PRS_DEV.PRODUCT,tableAlias=products1_,origin=PRS_DEV.APPLICANT applicant0_,colums={applicant0_.APPLICANT_ID ,className=org.myCompany.product.entity.Product}}] [select count() from org.myCompany.applicant.entity.Applicant applicant LEFT OUTER JOIN FETCH applicant.products product ]

Original source