CODEWITHSUNDEEP
javaspringhibernatejpajpa-criteria
justus
justus

Reputation: 584

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.

Upvotes: 2

Views: 3347

Answers (3)

Sagar Panchasara
Sagar Panchasara

Reputation: 199

Try this,

Query: select label, count(distinct file) from tableName group by label;

Criteria: criteria.setProjection(Projections.projectionList().add(Projections.groupProperty("label")).add(Projections.countDistinct("file")));

Upvotes: 1

Abdelghani Roussi
Abdelghani Roussi

Reputation: 2817

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

Upvotes: 0

dimitrisli
dimitrisli

Reputation: 21381

Yes it is possible by using JPA javax.persistence.criteria API.

Take a look at this example in the official Documentation.

Upvotes: 0

Related Questions