Skip to content
Advertisement

JPQL query for parameter in list

I have two JPA-entities:

public class BusinessTripRequest extends StandardEntity {
    @OneToMany(mappedBy = "businessTripRequest", fetch = FetchType.LAZY)
    @OnDelete(DeletePolicy.CASCADE)
    @Composition
    protected List<HotelBooking> hotelBookings;
}

public class HotelBooking extends StandardEntity {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "BUSINESS_TRIP_REQUEST_ID")
    protected BusinessTripRequest businessTripRequest;

    @Column(name = "JOINT_CHECK_IN")
    protected Boolean jointCheckIn;
}

and I tried to write a JPQL query to extract requests that:

  • If the parameter is false then extract all requests with empty hotelBookings and all requests where every booking have parameter jointCheckIn is set to false
  • If a parameter is true then extract all requests that have one or more bookings with jointCheckIn is set to true

I wrote something like this

select e from nk$BusinessTripRequest e join e.hotelBookings hb
where (true = ? and e.hotelBookings is not empty and hb.jointCheckIn = true)
or (false = ? and e.hotelBookings is empty)

It works well when parameter is true because of the first condition. But I can’t write a working condition for false parameter

Advertisement

Answer

solution suggested from comments

select e
from nk$BusinessTripRequest e
where (true = ? and e.id in (select hb1.businessTripRequest.id
                         from HotelBooking hb1
                         where hb1.jointCheckIn = true))
   or (false = ? and {E}.id not in (select hb1.businessTripRequest.id
                                    from nokia$HotelBooking hb1
                                    where hb1.jointCheckIn = true))
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement