Spring Data JPA JPQL list in constructor

Question

In my application, a non-standard situation, I have a layer of entity in mysql, layer of dominain in controllers. My domain model contains a few entities, can this be integrated into one JPQL query?

entity layer:

PersonEntity table

EventEntity table

EventVisitorEntity table

PersonEntity many to many EventEntity
EventVisitorEntity interim table

domain layer:

class PersonInfo {
Person person;
List<PersonEvent> personEvent
...
}

Now I get all Person to take their ids and get the PersonEvent, using this query:

@Query("SELECT new domain.PersonEvent(ev.personId,ev.eventId,e.name,ev.state)" +
        " FROM EventVisitorEntity AS ev ,EventEntity AS e WHERE e.id = ev.eventId AND ev.personId IN (?1)")
List<PersonEvent> findEventsForPerson(List<Integer> ids);

It is possible to write one query to get persons with an personEvents ? in the constructor which is below:

public PersonInfo(Person person, List<PersonEvent> personEvents)

Even if it was possible, it would be mapped to several SQL queries returning plain tuples. What is the purpose? — skapral, Dec 06 '17 at 20:45

skapral · Accepted Answer · 2017-12-07T09:31:15.193

1

Hardly doubt it is possible. JPQL subqueries, which might help you outline personEvents, are allowed only in where and having clauses.

Instead, I'd suggest you to just embrace the query as-is and move the logic of gathering to your DAO tier. This link might be helpful: https://dzone.com/articles/add-custom-functionality-to-a-spring-data-reposito. Declare a method List<PersonEvent> findEventsForPerson(List<Integer> ids), implement custom repository for it, doing all nesessary JPQL queries and combinations there. But beware of N+1 issue.

Also it may be convenient to use entity graphs in such custom implementation.

EDIT: After rereading the spec on fresh mind, I realized that I have mistaken saying that subqueries are allowed only in WHERE/HAVING clauses. It says that it may be used there, which doesn't exclude the opposite. Anyway, even if it is possible, such approach (extracting relation via subqueries) would most probably lead to N+1 issues, unless JPA implementors are smart enough to predict that (I wouldn't count on that anyway).

edited Dec 07 '17 at 09:31

answered Dec 06 '17 at 20:59

skapral

1,128
9
26

Hmmm just yesterday i did subqueries in the SELECT clause and it works. I dont see that subqueries are only allowed in WHERE and HAVING. – Robert Niestroj Dec 07 '17 at 08:31
I just quoted from JPQL spec. And WHERE/HAVING clauses are parts of SELECT query so where is contradiction? Are we talking on one thing? – skapral Dec 07 '17 at 08:36
1

`SELECT new domain.MyDTO(e.id, (HERE_MY_SUBQUERY)) FROM MyEntity e` – Robert Niestroj Dec 07 '17 at 08:52
Weird. And what kind of subquery is there? Can you provide full example? – skapral Dec 07 '17 at 09:00
SELECT new domain.MyDTO(e.id, (SELECT COUNT(ent.id) FROM MyEntity ent WHERE ent.gruop = e.group)) FROM MyEntity e – Robert Niestroj Dec 07 '17 at 09:05
Well the result of subquery is scalar afterall, so it doesn't solve the originator's question. Have you tried it with a select returning a tuple set? – skapral Dec 07 '17 at 09:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/160692/discussion-between-robert-niestroj-and-skapral). – Robert Niestroj Dec 07 '17 at 09:18
There is no need to. It seems I realized my mistake. Corrected the answer. – skapral Dec 07 '17 at 09:31

Spring Data JPA JPQL list in constructor

1 Answers1