List a Joined table with criteria

Question

I have a Product entity with a list of warehouses with this partially code:

Product.java

@ManyToMany
@JoinTable(name = "product_has_warehouse", joinColumns = @JoinColumn(name = ID), inverseJoinColumns = @JoinColumn(
        name = "warehouse_id"), foreignKey = @ForeignKey(name = FK + "has_warehouse"),
           inverseForeignKey = @ForeignKey(name = FK + "warehouse"))
private List<Warehouse> warehouses;

So i need to make a criteria query to get the list of warehouses by product id.

I tried:

    final CriteriaQuery<Warehouse> query = getCriteriaBuilder().createQuery(Warehouse.class);
    final Root<Product> root = query.from(Product.class);
    final Join<Product, Warehouse> warehouseJoin = root.join("warehouses");


    query.where(getCriteriaBuilder().in(warehouseJoin));
    final TypedQuery<Warehouse> typedQuery = getEm().createQuery(query);

    return typedQuery.getResultList();

But i get:

java.lang.IllegalArgumentException: Error occurred validating the Criteria Caused by: java.lang.IllegalStateException: No explicit selection and an implicit one could not be determined

Answer 1

This should work:

     @Test
     public void getWarehousesByProduct() {

        Product drinks = new Product("drinks");

        Warehouse wh1 = new Warehouse("house1");
        Warehouse wh2 = new Warehouse("house2");
        Warehouse wh3 = new Warehouse("house3");
        Warehouse wh4 = new Warehouse("house4");
        Warehouse wh5 = new Warehouse("house5");
        Warehouse wh6 = new Warehouse("house6");

        drinks.getWarehouses().add(wh1);
        drinks.getWarehouses().add(wh2);
        drinks.getWarehouses().add(wh3);
        drinks.getWarehouses().add(wh4);

        saveAll(Arrays.asList(new Warehouse[]{wh1,wh2,wh3,wh4,wh5,wh6}));
        em.persist(drinks);

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Warehouse> cq = cb.createQuery(Warehouse.class);
        Root<Product> product = cq.from(Product.class);
        Join<Product, Warehouse> warehouses = product.join("warehouses");
        cq.select(warehouses).where(cb.equal(product.get("id"), drinks.getId()));

        TypedQuery<Warehouse> tq = em.createQuery(cq);
        List<Warehouse> result = tq.getResultList();

        Assert.assertNotNull(result);
        Assert.assertEquals(drinks.getWarehouses().size(), result.size());
     }

Notice that i did only "one directional" way. Doing bidirectional way, you need to add product to it's feasible warehouses (to satisfy bidirectional condition). By the way, your mistake was missing cq.select(warehouses) that's why you got an illegal state because your query don't know what to select.