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.
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.