Search code examples
javamysqlspringhibernatejpa

Spring Data JPA JPQL list in constructor

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

  1. PersonEntity many to many EventEntity

  2. 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)
Solution

  • 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).