JPA and aggregate functions. How do I use the result of the query?

Question

Hey guys,

I'm new to ORM stuff and I need some help understanding something.

Let's assume I have the following standard SQL query:

SELECT *, COUNT(test.testId) AS noTests FROM inspection
LEFT JOIN test ON inspection.inspId = test.inspId
GROUP BY inspection.inspId

which I want to use in JPA.

I have an Inspection entity with a one-to-many relationship to a Test entity. (an inspection has many tests) I tried writing this in JPQL:

Query query = em.createQuery("SELECT insp, COUNT(???what???) " +
      "FROM Inspection insp LEFT JOIN insp.testList " +  
      "GROUP BY insp.inspId");

1) How do I write the COUNT clause? I'd have to apply count to elements from the test table but testList is a collection, so I can't do smth like COUNT(insp.testList.testId)

2) Assuming 1 is resolved, what type of object will be returned. It will definitely not be an Inspection object... How do I use the result?

Answer 1

1. You can give an alias to the joined entity (with AS)
2. You can create either a new object, or a List with the returned values

So:

SELECT new com.yourproject.ResultHolder(insp, COUNT(test.testId)) 
    FROM Inspection insp LEFT JOIN  insp.testList AS test GROUP BY insp.inspId

Or

SELECT new list(insp, COUNT(test.testId)) 
    FROM Inspection insp LEFT JOIN  insp.testList AS test GROUP BY insp.inspId

The result is then accessible either as an instance of ResultHolder, or as a java.util.List, where the insp is list.get(0), and the count is list.get(1)