JPA CriteriaApi: Not exist with Entity as relationship

Question

I would like to use not exist using criteria api. The checking is that return all Rental when Car not exists within from and to date. The problem is that there is no effect even though I remove the subQueryPredicates, the connection between table using Car field in the Rental table is not working. Please help me.

CriteriaBuilder cb = em.getCriteriaBuilder();  
CriteriaQuery<Car> query = cb.createQuery(Car.class); 
Root<Car> tbCar =  query.from(Car.class); 
query.select(tbCar);

Subquery<Rental> subquery = query.subquery(Rental.class); 
Root<Rental> tbRental = subquery.from(Rental.class);  
subquery.select(tbRental);

List<Predicate> subQueryPredicates = new ArrayList<Predicate>(); 
subQueryPredicates.add(cb.equal(tbRental.get("car"), tbCar));
subQueryPredicates.add(cb.greaterThan(tbRental.<LocalDate>get("from"), counterDate));
subQueryPredicates.add(cb.lessThan(tbRental.<LocalDate>get("to"), counterDate));
subQueryPredicates.add(cb.equal(tbRental.get("status"), "booked"));
subquery.where(subQueryPredicates.toArray(new Predicate[]{}));

List<Predicate> mainQueryPredicates = new ArrayList<Predicate>(); 
mainQueryPredicates.add(cb.not(cb.exists(subquery)));

This is my Rental Entity. The connection between Rental and Car is the field 'car'.

@Entity
public class Rental {
public static final String STATUS_BOOKED = "booked";
public static final String STATUS_CANCELED = "canceled";

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;

@NotBlank
private String status;

@NotNull
@ManyToOne
private Car car;

@NotNull
@ManyToOne
private Client client;

@NotNull
@Column(name="\"from\"")
private LocalDate from;

@NotNull
@Column(name="\"to\"")
    private LocalDate to;

This is my Car Entity

@Entity
public class Car {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;

@ManyToOne
private Provider provider;

@NotBlank
@Column(unique = true)
private String licensePlate;

This is the HQL I would to mimic:

String sql = "from Car c where not exists (" 
+ "from Rental r" 
+ "where " 
+ "r.car = c "    
+ "and "  
+ counterDate.toString()
+ "between r.from and r.to " 
+ "and r.status ='booked' )";

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

Take a look at this Question on SubQueries in JPA 2.0. Looks to me like you're missing the Path, which maps the main query to the sub query?

edited May 23 '17 at 12:11

Community

1
1

answered Jun 07 '14 at 09:58

ConMan

1,642
1
14
22

score 0 · Answer 2 · answered Jun 09 '14 at 14:30

I guess that where clause is missing at the end of your criteria:

...

List<Predicate> mainQueryPredicates = new ArrayList<Predicate>(); 
mainQueryPredicates.add(cb.not(cb.exists(subquery))); 
query.where(mainQueryPredicates.toArray(new Predicate[]{}));

therefore subquery is not taken into consideration.

JPA CriteriaApi: Not exist with Entity as relationship

2 Answers2