0

How can i add complete Entity with Nested Entities in Projection.

The Problem is I want to convert the given HQL to Criteria API.

SELECT p FROM Product p LEFT JOIN p.reviews r GROUP BY p ORDER BY r.rating ASC

Consider 1 Product has many Reviews. (One To Many Relationship)

Also I am reluctant to using Projection as Product class has other entities which are fetched eagerly. eg Product class has Brand Entity. (Many To One).

If possible I want minimum amount of Projection code.

Product class

@Entity
public class Product {

    @Id
    @GeneratedValue
    private long id;

    private String name;

    @OneToMany(mappedBy = "product")
    private Review review;

    @ManyToOne
    private Brand brand;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Review getReview() {
        return review;
    }

    public void setReview(Review review) {
        this.review = review;
    }

    public Brand getBrand() {
        return brand;
    }

    public void setBrand(Brand brand) {
        this.brand = brand;
    }

}

Review

@Entity
public class Review {

    @Id
    @GeneratedValue
    private long id;

    private String text;

    private int rating;

    @ManyToOne
    private Product product;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public int getRating() {
        return rating;
    }

    public void setRating(int rating) {
        this.rating = rating;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

}
Piyush
  • 449
  • 1
  • 5
  • 15
  • @NeilStockton As per my knowlege I can use Projections.groupProperty("product.id") along Projections.avg("review.rating") and then I can sort by average of review. But I will have to add each field in projection. My requirement is simple I want list of Products and want to order them by average of their rating. 1 Product may have many Rating – Piyush Oct 17 '16 at 08:58
  • in which case this is NOT the JPA Criteria API, hence removing JPA tag. – Neil Stockton Oct 17 '16 at 09:00
  • @NeilStockton Ok. Can u help me with Hibernate Criteria for the above HQL. Or If it is possible with JPA criteria then It would be helpful too. – Piyush Oct 17 '16 at 09:02

1 Answers1

0

If your problem is on inserts, you have to configure the cascade (CascadeType) on the parent class. http://docs.oracle.com/javaee/6/tutorial/doc/bnbqa.html

If querying is what you need, this could work as an example of a join+order query: 

CriteriaQuery<Product> cq = cb.createQuery(Product.class);
Root<Product> product= cq.from(Product.class);
Join<Product, Review> review = product.join("review");
cq.select(product);
cq.groupBy(review.get("rating"));
cq.orderBy(cb.asc(cb.avg(review.get("rating"))));

Source: http://docs.oracle.com/javaee/6/tutorial/doc/gjivm.html

Jorge C
  • 429
  • 4
  • 13
  • I want to order them by average of rating – Piyush Oct 17 '16 at 08:52
  • I've edited the answer so it includes the group by. Anyway I think the link about Using the Criteria API worths a visit. – Jorge C Oct 17 '16 at 09:03
  • Thanks, I am using Hibernate. And I have Hibernate SessionFactory instance. Can I get EntityManager instance or Criteria builder Instance from it. If not Can you modify the code for Hibernate Criteria. – Piyush Oct 17 '16 at 09:16
  • Here are a couple of examples using the Hibernate specific API: http://stackoverflow.com/questions/1780129/order-by-using-criteria-api, http://stackoverflow.com/questions/8491796/hibernate-group-by-criteria-object – Jorge C Oct 17 '16 at 09:31