Query with JPA filtering by Entity List

Asked

Viewed 387 times

1

I’m performing a system in Java using Springboot and JPA, being event management and have two entities: Inscricao and HorarioAtividade.

They have a relationship Many To Many, with my class Inscricao having a list of HorarioAtividades with the activities of each enrollment and the Class HorarioAtividade possessing a list of Entries with the entries of that activity.

The class Inscricao also has an attribute situacao who’s kind enum, ex. 

SituacaoInscricao.RESERVADA
SituacaoInscricao.CONFIRMADA
SituacaoInscricao.AG_PAGAMENTO

When I make a findAll in the repository of HorarioAtividade, it brings the schedules Activities with the list of registrations all straight automatically through their relationship.

The problem I can’t solve is:

I need to get all the HorarioAtividade from my base, only it is bringing only the registrations that are confirmed and awaiting payment.

I’ve tried with join fetch, but he ends up mixing everything and the query is not correct.

I searched about the Criteria api, but I didn’t understand how to solve my problem with her.

java hibernate jpa criteria
  • 1

    It would be interesting to put the code in the question

1 answer

0


I need to get all the HorarioAtividade from my base, only it is bringing only the registrations that are confirmed and awaiting payment.

I’ve tried with join fetch, but he ends up mixing everything and the query is not correct.

You probably fell for a JPA "prank": you should not use WHERE in a JOIN FETCH.

In your example, when this occurs, it incorrectly returns all the HorarioAtividade who has confirmed registrations (or awaiting payment) but will remove all registrations that do not obey the same filter. So, if you have a HorarioAtividade with 3 entries in the bank, 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 registrations of an activity schedule and apply a WHERE in them, bringing the other inscriptions that do not fit in the same filter but belong to the same activity time, you need two Joins: one for the WHERE and another to the JOIN FETCH.

I need to pick up all the schedules from my base, ONLY bringing only the registrations that are Confirmed and awaiting payment.

The JPQL for this case would be 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'

Browser other questions tagged java hibernate jpa criteria

You are not signed in. Login or sign up in order to post.