Is it possible to use COUNT with a DISTINCT JPA projection?

Question

I am using a JPA distinct projection to get some data:

select distinct o.f1, o.f2, o.f3 from SomeEntity o where ...

This works fine with setFirstResult and setMaxResults to page data.

However I need to count the total number of rows without fetching all of them. I have tried:

select count(distinct o.f1, o.f2, o.f3) from SomeEntity o where ...

This does not work (with EclipseLink anyway) and it doesn't seem to be allowed by the JPA spec. Is there another way? I don't want to have to write an SQL query to do this.

Answer 1

You say you don't want to write an SQL query to do this, but what is the difference between JPA QL and SQL? If you don't want to use the getResultList().size() method to determine the number of total rows, then the only way is to use a native sql query.

entityManager.createNativeQuery("select count(distinct o.f1, o.f2, o.f3) from SomeEntity o where ...").getSingleResult();

or

entityManager.createQuery("select distinct o.f1, o.f2, o.f3 from SomeEntity o where ...").getResultList().size();