How to find cortege with OneToMany relation in Spring Data JPA

Tags: , , ,

I use Spring Data JPA and have two entities:

public class Vehicle {
    private Integer id;
    private String type;
    private String vehicleRegNumber;
    @OneToMany(targetEntity = com.transport.model.BookedTime.class, fetch=FetchType.EAGER)
    @JoinColumn(name="bookingTime", referencedColumnName="id")
    Set<BookingTime> bookingTime;


public class BookedTime {
    Integer id;
    Long startPeriod;
    Long finishPeriod;


And repository:

public interface VehicleRepository extends JpaRepository<Vehicle, Integer> {

   @Query("correct query")
    List<Vehicle> findAllByPeriod(@Param("startPeriod") int startPeriod, @Param("endPeriod") int endPeriod);

I need to find available vehicles, which not booked by time. How can I do this?


I would go for something like:

 @Query(nativeQuery=true, value = "select * from vehicle_entity v join booked_time b on b.vehicle = where not (b.startPeriod > :startPeriod and b.endPeriod < :endPeriod)"

BTW I think that you might wanna try to change FetchType from EAGER to LAZY for bookingTime and then use join fetch in select query.

Source: stackoverflow