Geek Answers Handbook

JPA and cumulative features. How to use the query result?

I am new to ORM and I need some help understanding something.

Suppose 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 object with a one-to-many relationship to the test object. (validation has many tests) I tried to write this in JPQL:

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

1) How to write a COUNT sentence? I would have to use count for items from the test table, but testList is a collection, so I cannot do something like COUNT(insp.testList.testId)

2) Assuming 1 is allowed, what type of object will be returned. It will definitely not be subject to inspection ... How to use the result?

+7
java sql jpa
source share
1 answer
  • You can specify the alias of the merged object (with AS )
  • You can create either a new object or a List with 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 available either as an instance of ResultHolder , or as java.util.List , where insp is list.get(0) and the counter is list.get(1)

+16
source share

More articles:


All Articles