Java Criteria API query for bridge table with composite primary key

Question

What is the Java criteria api query equivalent of the following SQL? I am new to this and have tried a few things but none worked.

SELECT p.* FROM Product p
RIGHT JOIN store_has_product shp ON  p.id_product=shp.id_product
WHERE shp.id_store=1;

TableA: store

Bridge Table: store_has_product (table has id_store, id_product as composite pk)

TableB: product

Entities (without constructors, getters/setters)

    @Entity
@Table(name = "product")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Product.findAll", query = "SELECT p FROM Product p"),
    @NamedQuery(name = "Product.findByIdProduct", query = "SELECT p FROM Product p WHERE p.idProduct = :idProduct"),
    @NamedQuery(name = "Product.findByName", query = "SELECT p FROM Product p WHERE p.name = :name"),
    @NamedQuery(name = "Product.findByCost", query = "SELECT p FROM Product p WHERE p.cost = :cost")})
public class Product implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "idProduct")
    private Integer idProduct;
    @Size(max = 45)
    @Column(name = "name")
    private String name;
    // @Max(value=?)  @Min(value=?)//if you know range of your decimal fields consider using these annotations to enforce field validation
    @Column(name = "cost")
    private Double cost;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product")
    private Collection<StoreHasProduct> storeHasProductCollection;

@Entity
@Table(name = "store")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Store.findAll", query = "SELECT s FROM Store s"),
    @NamedQuery(name = "Store.findByIdStore", query = "SELECT s FROM Store s WHERE s.idStore = :idStore"),
    @NamedQuery(name = "Store.findByName", query = "SELECT s FROM Store s WHERE s.name = :name")})
public class Store implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "idStore")
    private Integer idStore;
    @Size(max = 45)
    @Column(name = "name")
    private String name;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "store")
    private Collection<StoreHasProduct> storeHasProductCollection;

@Entity
@Table(name = "store_has_product")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "StoreHasProduct.findAll", query = "SELECT s FROM StoreHasProduct s"),
    @NamedQuery(name = "StoreHasProduct.findByStoreidStore", query = "SELECT s FROM StoreHasProduct s WHERE s.storeHasProductPK.storeidStore = :storeidStore"),
    @NamedQuery(name = "StoreHasProduct.findByProductidProduct", query = "SELECT s FROM StoreHasProduct s WHERE s.storeHasProductPK.productidProduct = :productidProduct"),
    @NamedQuery(name = "StoreHasProduct.findByActive", query = "SELECT s FROM StoreHasProduct s WHERE s.active = :active")})
public class StoreHasProduct implements Serializable {
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    protected StoreHasProductPK storeHasProductPK;
    @Column(name = "active")
    private Short active;
    @JoinColumn(name = "Product_idProduct", referencedColumnName = "idProduct", insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private Product product;
    @JoinColumn(name = "Store_idStore", referencedColumnName = "idStore", insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private Store store;

@Embeddable
public class StoreHasProductPK implements Serializable {
    @Basic(optional = false)
    @NotNull
    @Column(name = "Store_idStore")
    private int storeidStore;
    @Basic(optional = false)
    @NotNull
    @Column(name = "Product_idProduct")
    private int productidProduct;

Metamodel

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(Product.class)
public abstract class Product_ {

    public static volatile SingularAttribute<Product, Double> cost;
    public static volatile SingularAttribute<Product, String> name;
    public static volatile SingularAttribute<Product, Integer> idProduct;
    public static volatile CollectionAttribute<Product, StoreHasProduct> storeHasProductCollection;

}

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(Store.class)
public abstract class Store_ {

    public static volatile SingularAttribute<Store, String> name;
    public static volatile SingularAttribute<Store, Integer> idStore;
    public static volatile CollectionAttribute<Store, StoreHasProduct> storeHasProductCollection;

}

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(StoreHasProduct.class)
public abstract class StoreHasProduct_ {

    public static volatile SingularAttribute<StoreHasProduct, Product> product;
    public static volatile SingularAttribute<StoreHasProduct, Short> active;
    public static volatile SingularAttribute<StoreHasProduct, Store> store;
    public static volatile SingularAttribute<StoreHasProduct, StoreHasProductPK> storeHasProductPK;

}

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(StoreHasProductPK.class)
public abstract class StoreHasProductPK_ {

    public static volatile SingularAttribute<StoreHasProductPK, Integer> productidProduct;
    public static volatile SingularAttribute<StoreHasProductPK, Integer> storeidStore;

}

Edit* Question: Is it possible to use it as a specification? I don't even know if I am doing the right thing regarding the predicate but here is what I am attempting:

Integer resultsPerPage = 20;
    Sort.Direction sortOrder = Sort.Direction.DESC;
    String sortBy = "name";
    PageRequest request
            = new PageRequest(1 - 1, resultsPerPage, sortOrder, sortBy);
    Page<Product> prods = productRepository.findAll(where(productBelongsToStore("Tesco")), request);

    for (Product prod : prods.getContent()) {
        System.out.println(""+ prod.getName());
    }

Specification:

public static Specification<Product> productBelongsToStore(final String searchTerm) {
    return new Specification<Product>() {
        @Override
        public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
            String likePattern = SpecificationUtilityMethods.getLikePattern(searchTerm);
            Root<Product> product = cq.from(Product.class);
            Join<Product, StoreHasProduct> storeHasProduct = product.join(Product_.storeHasProductCollection);
            Path<Store> storeName = storeHasProduct.get(StoreHasProduct_.store);
            return cb.like(cb.lower(storeName.<String>get(Store_.name)), likePattern);

        }
    };
}

This outputs: Milk Milk Juice Juice Cereal Cereal Butter Butter Bread Bread

Which is wrong

*For anyone attempting the same thing I fixed this by removing "Root product = cq.from(Product.class);" and just using the root parameter.

Please add the code for what you have tried already. show the effort (-: — royB, May 18 '15 at 15:58
Do you have any Metamodel classes generated? It's usually easier then. And what does the entity objects for `Store` and `Product`look like? You can skip getters/setters when listing the entity classes. — Roger Gustavsson, May 18 '15 at 16:08

Roger Gustavsson · Accepted Answer · 2015-05-19T11:04:15.607

1

Try the following:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);

// "FROM Product p"
Root<Product> product = query.from(Product.class);
// "RIGHT JOIN store_has_product shp", "ON p.id_product=shp.id_product" is handled inside Product.class
Join<Product,StoreHasProduct> storeHasProduct = product.join(Product_.storeHasProductCollection);
// Create an alias to the shp.id_store column to use in the where condition
Path<Integer> id = storeHasProduct.get(StoreHasProduct_.storeHasProductPK).get(StoreHasProductPK_.storeidStore);
// "WHERE shp.id_store = 1"
query.where(cb.equal(id, 1));
// "SELECT p.*"
query.select(product);

TypedQuery<Product> tq = entityManager.createQuery(query);
for (Product p : tq.getResultList()) {
    // do stuff
}

This is from top of my head and the code is untested, so I hope I haven't made any major errors.

This can be solved without meta model classes as well, but you risk making a mistake when entering column names as strings. Something that the compiler can't catch. With meta models, the compiler can catch more errors that may be missed otherwise.

edited May 19 '15 at 11:04

answered May 18 '15 at 16:26

Roger Gustavsson

1,689
10
20

Thank you, I will have to look into this in more detail once I get home. I don't have metamodel classes as I am not sure how to generate them. Is it possible to include this into a repository so that I can utilise the specification and pageable features? – kk7 May 18 '15 at 16:51
See [this](http://stackoverflow.com/questions/3037593/how-to-generate-jpa-2-0-metamodel) question about how to generate metamodel classes. – Roger Gustavsson May 18 '15 at 17:30
Hey I added more details about the entities and metamodel, I couldn't get the join to work. Any ideas? – kk7 May 19 '15 at 10:00
Thank you so much! That did work and helped me learn. I have edited my post with an additional question if you have some time :) – kk7 May 19 '15 at 13:25
I think it's better that you ask a new question about that part. That will get more attention from potential helpers. I have not used `Specification`. – Roger Gustavsson May 19 '15 at 13:32
Thats okay, it kind of works now when I removed the product root and just used the one from the parameter. Thanks again. – kk7 May 19 '15 at 13:41

Java Criteria API query for bridge table with composite primary key

1 Answers1