title: Java Criteria API query for bridge table with composite primary key
body: <p>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.</p>
  
  <pre><code>SELECT p.* FROM Product p
  RIGHT JOIN store_has_product shp ON  p.id_product=shp.id_product
  WHERE shp.id_store=1;
  </code></pre>
  
  <p>TableA: store</p>
  
  <p>Bridge Table: store_has_product (table has id_store, id_product as composite pk)</p>
  
  <p>TableB: product</p>
  
  <p>Entities (without constructors, getters/setters)</p>
  
  <pre><code>    @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&lt;StoreHasProduct&gt; 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&lt;StoreHasProduct&gt; 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;
  </code></pre>
  
  <p>Metamodel</p>
  
  <pre><code>@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
  @StaticMetamodel(Product.class)
  public abstract class Product_ {
  
      public static volatile SingularAttribute&lt;Product, Double&gt; cost;
      public static volatile SingularAttribute&lt;Product, String&gt; name;
      public static volatile SingularAttribute&lt;Product, Integer&gt; idProduct;
      public static volatile CollectionAttribute&lt;Product, StoreHasProduct&gt; storeHasProductCollection;
  
  }
  
  @Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
  @StaticMetamodel(Store.class)
  public abstract class Store_ {
  
      public static volatile SingularAttribute&lt;Store, String&gt; name;
      public static volatile SingularAttribute&lt;Store, Integer&gt; idStore;
      public static volatile CollectionAttribute&lt;Store, StoreHasProduct&gt; storeHasProductCollection;
  
  }
  
  @Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
  @StaticMetamodel(StoreHasProduct.class)
  public abstract class StoreHasProduct_ {
  
      public static volatile SingularAttribute&lt;StoreHasProduct, Product&gt; product;
      public static volatile SingularAttribute&lt;StoreHasProduct, Short&gt; active;
      public static volatile SingularAttribute&lt;StoreHasProduct, Store&gt; store;
      public static volatile SingularAttribute&lt;StoreHasProduct, StoreHasProductPK&gt; storeHasProductPK;
  
  }
  
  @Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
  @StaticMetamodel(StoreHasProductPK.class)
  public abstract class StoreHasProductPK_ {
  
      public static volatile SingularAttribute&lt;StoreHasProductPK, Integer&gt; productidProduct;
      public static volatile SingularAttribute&lt;StoreHasProductPK, Integer&gt; storeidStore;
  
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Integer resultsPerPage = 20;
      Sort.Direction sortOrder = Sort.Direction.DESC;
      String sortBy = "name";
      PageRequest request
              = new PageRequest(1 - 1, resultsPerPage, sortOrder, sortBy);
      Page&lt;Product&gt; prods = productRepository.findAll(where(productBelongsToStore("Tesco")), request);
  
      for (Product prod : prods.getContent()) {
          System.out.println(""+ prod.getName());
      }
  </code></pre>
  
  <p>Specification:</p>
  
  <pre><code>public static Specification&lt;Product&gt; productBelongsToStore(final String searchTerm) {
      return new Specification&lt;Product&gt;() {
          @Override
          public Predicate toPredicate(Root&lt;Product&gt; root, CriteriaQuery&lt;?&gt; cq, CriteriaBuilder cb) {
              String likePattern = SpecificationUtilityMethods.getLikePattern(searchTerm);
              Root&lt;Product&gt; product = cq.from(Product.class);
              Join&lt;Product, StoreHasProduct&gt; storeHasProduct = product.join(Product_.storeHasProductCollection);
              Path&lt;Store&gt; storeName = storeHasProduct.get(StoreHasProduct_.store);
              return cb.like(cb.lower(storeName.&lt;String&gt;get(Store_.name)), likePattern);
  
          }
      };
  }
  </code></pre>
  
  <p>This outputs:
  Milk
  Milk
  Juice
  Juice
  Cereal
  Cereal
  Butter
  Butter
  Bread
  Bread</p>
  
  <p>Which is wrong</p>
  
  <p>*For anyone attempting the same thing I fixed this by removing "Root product = cq.from(Product.class);" and just using the root parameter.</p>