Cannot find entities not having an entity in a many to many relationship using a JPA Specification

Question

I'm looking to implement this query as a Specification for a Spring Data repository:

select * from Parts where id not in (select partId from PartsToDeliveries where deliveryId = 31)

(Basically, find all the parts that were not part of a certain delivery)

Here are the classes:

@Entity public class Part { @Id private Long id; @ManyToMany @JoinTable(name = "PartsToDeliveries", joinColumns = {@JoinColumn(name = "partId")}, inverseJoinColumns = @JoinColumn(name = "deliveryId")) private Set<Delivery> deliveries = new HashSet<>(); }

and

@Entity public class Delivery { @Id private Long id; @ManyToMany(mappedBy = "deliveries") private List<Part> parts; }

Fill in the blank:

Specification<Part> specification = (root, criteriaQuery, criteriaBuilder) -> { ? _______ ? }

I simply don't know where to start on this.

Steve s. · Accepted Answer · 2016-09-23 15:30:00Z

Antoniossss had the right lead to the solution (the doc not being particularly helpful). For completeness here is the final solution:

specification = (root, criteriaQuery, criteriaBuilder) -> { Subquery<Long> subquery = criteriaQuery.subquery(Long.class); Root<Part> subFrom = subquery.from(Part.class); Path<Long> deliveryId = subFrom.join("deliveries").get("id"); subquery.select(subFrom.get("id")); subquery.where(criteriaBuilder.equal(deliveryId, 31l)); Path<Long> id = root.get("id"); return criteriaBuilder.not(criteriaBuilder.in(id).value(subquery)); };

Community · Accepted Answer · 2017-05-23 12:33:14Z

You can mock the similar semantics using CriteriaAPI - CriteriaBuilder#not() and subquery()

Here u have SO example of building similar query JPA 2.0, Criteria API, Subqueries, In Expressions

and here are JPA docs http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaBuilder.html

Ralph · Accepted Answer · 2016-09-22 18:33:33Z

0

Something like:

criteriaBuilder.not(root.get("deliveries").in(notWantedDelivery))

answered Sep 22, 2016 at 18:33

Ralph

121k57 gold badges300 silver badges391 bronze badges

1 Comment

Steve s. Over a year ago

Thanks Ralph, but that created a SQL error. like: and deliveries1_deliveryId=part2_.id and 1=1 and (. not in (47)).

Collectives™ on Stack Overflow

Cannot find entities not having an entity in a many to many relationship using a JPA Specification

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related