How to count a group by result with JPA and CriteriaBuilder?

jpa-2.0

Solution

This example assumes that you're using Metamodel generation.

CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Subquery<SomeColumnType> subcq = cq.subquery(SomeColumnType.class);
Root<MyTable> from = subcq.from(MyTable.class);
subcq.select(from.get(MyTable_.someColumn));
subcq.where(** complex where statements **);
subcq.groupBy(from.get(MyTable_.someColumn));
cq.select(cb.count(subcq));

Problem

I think this is nearly impossible or very tricky. I'm using CriteriaBuilder, JPA 2.0, Hibernate and MariaDB and want to build the following query with CriteriaBuilder: ``` SELECT COUNT(*) FROM (SELECT DISTINCT(SomeColumn) // I think this is not possible? FROM MyTable WHERE ... COMPLEX CLAUSE ... GROUP BY SomeColumn) MyTable ``` My Question: Possible? And if, how? Thanks for wrapping your mind around this! Mark

Original source