CODEWITHSUNDEEP
javahibernate
John Nash
John Nash

Reputation: 58

Find Hibernate objects without many to many relationship

I need to find all the products that do not contain a specific allergen using Hibernate.

Here is the SQL used to create the database tables:

CREATE TABLE ALLERGEN (id integer IDENTITY PRIMARY KEY, name varchar(20), UNIQUE (id), UNIQUE(name));
CREATE TABLE PRODUCT (id integer IDENTITY PRIMARY KEY, name varchar(20), UNIQUE (id), UNIQUE(name));
CREATE TABLE PRODUCT_ALLERGEN (product_id integer, allergen_id integer, UNIQUE (product_id, allergen_id), FOREIGN KEY (product_id) REFERENCES PRODUCT (id), FOREIGN KEY (allergen_id) REFERENCES ALLERGEN (id));

Here are the Hibernate annotated Java classes:

@Entity
@Table(name = "ALLERGEN")
class Allergen {

    @Id
    @Column(unique = true, nullable = false)
    @GeneratedValue
    private Integer id;
    private String name;

    // ...
}

@Entity
@Table(name = "PRODUCT")
public class Product {

    @Id
    @Column(unique = true, nullable = false)
    @GeneratedValue
    private Integer id;
    private String name;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(inverseJoinColumns = {@JoinColumn(name = "allergen_id")})
    private final Set<Allergen> allergens = new HashSet<>();

    // ...
}

This SQL appears to give me the result I want, but I don't see how to represent it using Hibernate criteria.

SELECT * FROM PRODUCT WHERE (SELECT COUNT(*) FROM PRODUCT_ALLERGEN WHERE product_id = PRODUCT.id AND allergen_id = 0) = 0;

Upvotes: 1

Views: 384

Answers (2)

John Nash
John Nash

Reputation: 58

This code uses JPQL to get the products without a specific allergen.

List<Product> results = manager.createQuery(
        "SELECT p from Product AS p WHERE (SELECT COUNT(a) FROM p.allergens a WHERE a.name = :an) = 0",
        Product.class)
    .setParameter("an", "nuts")
    .getResultList();

Upvotes: 0

user6073886
user6073886

Reputation:

With the Criteria API you should be able to get all Product without Allergens by creating a left join from Product to Allergen and checking if it is null:

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Product> c = builder.createQuery(Product.class);
final Root<Product> root = c.from(Product.class);
Join<Product, Allergen> allergenJoin = root.join("allergens", JoinType.LEFT);
c.where(builder.isNull(allergenJoin));
c.select(root);
List<Product> = entityManager.createQuery(c).getResultList();

Note: I didn't include where you get the EntityManager from. Usually I use injection for that, but there are other methods like using a factory.

Upvotes: 1

Related Questions