JPA Query Filtering by Entity List

Question

JPA Query Filtering by Entity List

Navigation

#1 by (0 votes)

1

I'm running a Java system using SpringBoot and JPA , being event management and I have two entities: Inscricao and HorarioAtividade .

They have a Many To Many relationship, with my class Inscricao having a list of HorarioAtividades with the activities of each entry and Class HorarioAtividade having a list of Inscriptions with the inscriptions of that activity.

The Inscricao class also has a situacao attribute that is of type enum , eg.

SituacaoInscricao.RESERVADA
SituacaoInscricao.CONFIRMADA
SituacaoInscricao.AG_PAGAMENTO

When I make a findAll in the repository of HorarioAtividade , it automatically schedules activities with the list of subscriptions automatically through their relationship.

The problem I can not solve is:

I need to search all HorarioAtividade of my base, only that is bringing only those subscriptions that are confirmed and waiting for payment.

I have tried with join fetch , but it ends up mixing everything and the query is not correct.

I looked for Criteria api , but I did not understand how to solve my problem with it.

java hibernate jpa criteria

asked by anonymous 04.07.2018 / 17:07

1 answer

Access the previous item in a dir-paginate [closed] Send a jquery variable via ajax to PHP

score 0 · Answer 1

I need to search all HorarioAtividade of my base, only that is bringing only those subscriptions that are confirmed and waiting for payment.

I have tried with join fetch , but it ends up mixing everything and the query is not correct.

You've probably fallen for a "catch" of JPA: you should not use WHERE in a JOIN FETCH .

In your example, when this occurs, it incorrectly returns all HorarioAtividade that has confirmed subscriptions (or waiting for payment) but will remove all subscriptions that do not conform to the same filter. So if you have HorarioAtividade with 3 entries in the database, one of them being CONFIRMADA , the other two will not be returned (and you would expect it to be).

If you want to return all the entries of an activity schedule and apply WHERE to them, bringing the other entries that do not fit the same filter but belong to the same activity schedule, you need two JOINs: one for the WHERE and another to JOIN FETCH .

I need to search all myAbout Activity Schedule, ONLY bringing only those subscriptions that are Confirmed and waiting for payment.

The JPQL for this case would look something like this:

SELECT ha FROM HorarioAtividade ha 
JOIN ha.inscricoes insc
FETCH JOIN ha.inscricoes
WHERE insc.situacao = 'CONFIRMADA' OR insc.situacao = 'AG_PAGAMENTO'