Search code examples
javaspringhibernatejpajpa-criteria

JPA Criteria Query GROUP and COUNT over subquery

Given the following SQL structure of MY_TABLE:

GROUP_LABEL | FILE  | TOPIC
-----------------------------
group A     | 1.pdf | topic A
group A     | 1.pdf | topic B
group A     | 2.pdf | topic A
group B     | 2.pdf | topic B

My task is to get this stuff grouped by GROUP_LABEL, while forgetting about the different TOPICs of a file. So my expected result is

GROUP_LABEL | COUNT(*) 
----------------------
group A     | 2       -- two different files 1.pdf and 2.pdf here
group B     | 1       -- only one file here

In pure SQL I would do it like 

SELECT GROUP_LABEL, COUNT(*) FROM (
    SELECT DISTINCT GROUP_LABEL, FILE FROM MY_TABLE
);

Is it possible to transform it into a JPA Criteria API query? I don't have any idea to get my inner query into the from construct of the Criteria query, in 9.3.1 of https://docs.jboss.org/hibernate/entitymanager/3.5/reference/en/html/querycriteria.html it seems like this is not possible.

But I just can't believe it ;-) Has anyone done this before? The inner query would be enriched with various, well-tested, filter Predicates which I would want to reuse.

I'm using spring-boot-starter-data : 1.5.6.RELEASE with mainly standard configuration.

Solution

  • Firstly your sql query can be resumed to this : 

    Select distinct GLOBAL_LABEL ,count (distinct FILE) from MY_TABLE group by GLOBAL_LABEL

    Secondly it's always good to not name your columns with primary names to avoid problems.

    Finaly you can use this as your HQL query (with no magic) : 

    Select distinct ge.globalLabel,count (distinct ge.file) from GlobalEntity ge group by ge.globalLabel