JPA/JPQL COUNT question

Question

I have the following JPQL query -

SELECT f.md5 
FROM File f, Collection leafCollections, Collection instCollections 
WHERE (f.status = com.foo.bar.FileStatus.Happy OR f.status = com.foo.bar.FileStatus.Sad) 
      AND f.collectionId = leafCollections.collectionId 
      AND leafCollections.instanceCollectionId = instCollections.collectionId 
GROUP BY f.md5, instCollections.collectionId 

It basically returns the md5s for files which are organized in a hierarchy (tree) such that if the same MD5 appears in more then one leaf in a particular branch of the hierarchy it will be only shown once (thanks to the GROUP BY).

This works fine. Let's say I get 100 rows back. Each row containing an md5 as a string.

Now I want to get the COUNT of the rows returned. I thought I could simply do:

SELECT COUNT(f.md5) 
FROM File f, Collection leafCollections, Collection instCollections 
WHERE (f.status = com.foo.bar.FileStatus.Happy OR f.status = com.foo.bar.FileStatus.Sad) 
      AND f.collectionId = leafCollections.collectionId 
      AND leafCollections.instanceCollectionId = instCollections.collectionId 
GROUP BY f.md5, instCollections.collectionId 

However this returns 100 rows, each one containing a long representing the number of times the md5 appeared in a branch. What I wanted was simply to get 1 row back with a long value of 100 being the total count of rows the original query returned. I feel like I am missing something obvious.

Suggestions?

Answer 1

As @doc_180 has commented. Using getSingleResult() will yield the count

Use the following code to get the result.

// Assuming that the Query is strQ
Query q = entityManager.createQuery( strQ );
int count = ( (Integer) q.getSingleResult() ).intValue();