I'm trying to use a Criteria query to retrieve an entity, and using pagination while I'm at it. I can't seem to get it right.
I have two entities, Flight and Passenger. I want the first 2 flights which have a passenger called Joe. And I want the pagination to be done by the database.
In theory, the code is simple
Code:
Criteria flightCriteria = session.createCriteria(Flight.class);
Criteria passengerCriteria = flightCriteria.createCriteria("passengers");
passengerCriteria.add(Restrictions.eq("firstName", "Joe"));
flightCriteria.setFirstResult(0);
flightCriteria.setMaxResults(2);
However, if I have flight 1 with two passengers named Joe, and flight 2 with one passenger named Joe, I simply get flight 1 twice. (This is an old issue
https://forum.hibernate.org/viewtopic.php?f=1&t=955186)
A distinct root entity ResultTransformer won't help in this case. And I know I could use a subquery to achieve this. But what would really be nice is if the query that came out of there looked like...
Code:
select distinct flight.*
from flight inner join passenger on flight.id = passenger.flight_id
where passenger.first_name like '%Joe%'
offset 0
limit 2
This would require the projection to only contain the flight entity, and the distinct keyword to be added to the projection.
Is that even possible?
Thanks,
Emerson
Login
FAQ
Search