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.

score 2 · Answer 1 · answered Sep 23 '16 at 15:30

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));
            };

score 0 · Accepted Answer · edited May 23 '17 at 12:33

0

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

edited May 23 '17 at 12:33

Community

1
1

answered Sep 22 '16 at 18:26

Antoniossss

31,590
6
57
99

score 0 · Answer 3 · answered Sep 22 '16 at 18:33

0

Something like:

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

answered Sep 22 '16 at 18:33

Ralph

118,862
56
287
383

Thanks Ralph, but that created a SQL error. like: `and deliveries1_deliveryId=part2_.id and 1=1 and (. not in (47))`. – Steve s. Sep 22 '16 at 22:12

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

3 Answers3