1

I am trying to write Query using JPA Criteria API. I have the following classes:

Class Booking {

  @ForeignKey(name = "BVisit_ID_FK")
  private List<BVisit> BVisits = new LinkedList<>();

    //other properties
    ...
}

Class Visit{

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

}

Class VisitSpecial extends Visit{

  @Column(name = "ARRIVAL_TIME", nullable = false)
  private Date arrivalTime;

  @Column(name = "DEPARTURE_TIME", nullable = false)
  private Date departureTime;

  //other properties...
}`

How can I write query using JPA Criteria Api (and metamodels) that will find all bookings that have visits with date value(parameter) that is between min arrival time, an max departure time for its booking visits. I use org.springframework.data.jpa.domain.Specification

The query should look like something like this:

SELECT Booking 
from Booking B, Visit V, VisitSpecial VS
where Visit.bookingId = Booking.id and Visit.id = VisitSpecial.id
      and VisitSpecial.arrivalTime = (SELECT MIN(VisitSpecial.arrivalTime) from VisitSpecial VS1 WHERE V.id = VS1.id)
      and VisitSpecial.arrivalTime <= :date
      and VisitSpecial.departureTime = (SELECT MAX(VisitSpecial.departureTime) from VisitSpecial VS1 Where V.id = VS1.id)
      and VisitSpecial.departureTime >= :date
mejas
  • 297
  • 1
  • 5
  • 17

1 Answers1

2

See my answer here for dealing with InheritanceType.JOINED and other inheritance strategies through JPA Criteria Subquery.

Downcasting with treat may work, but I do not recommend it for complex queries as it may produce inefficient queries (redundant joins) or not work at all based on your JPA provider (Hibernate, Eclipselink, etc.).

lradomirov
  • 54
  • 3