I use Spring Data JPA and have two entities:
@Entity @Data @Table(name="vehicle_entity") public class Vehicle { @Id @GeneratedValue 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; }
and
@Entity @Data @Table(name="booked_time") public class BookedTime { @Id @GeneratedValue 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?
Advertisement
Answer
I would go for something like:
@Query(nativeQuery=true, value = "select * from vehicle_entity v join booked_time b on b.vehicle = v.id 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.