10

I have 3 entities as you can see below. I want to write a query that fetches products. In this query the parameter is a list of optionValues id.

now my question is how to join these entities?

Product:

 public class Product{
   //other col

    @OneToMany(mappedBy = "product")
    private Set<Attribute> attributeSet = new HashSet<>();
 }

Attribute:

public class Attribute{
  @OneToOne
  @JoinColumn(name = "OPTION_VALUE_ID")
  private OptionValue optionValue;

  @ManyToOne
  @JoinColumn(name="PRODUCT_ID",referencedColumnName="id")
  private Product product;
}

optionValue:

 public class OptionValue{
     @Column(name = "id")
     private Long id;

    @Column(name = "value",updatable = true)
    private String value;
 }

I wrote a query but I think my code is not a good solution.

 Criteria aCriteria = null;
    if (!optionValueList.isEmpty()) {
        aCriteria = currentSession().createCriteria(Attribute.class, "attribute");
        aCriteria.createAlias("attribute.optionValue", "optionValue");
        aCriteria.add(Restrictions.in("optionValue.id", optionValueList));
        attributes = aCriteria.list();
    }
    PagingData<Product> pagingData = new PagingData<>();
    Criteria criteria = currentSession().createCriteria(Product.class, "product");
    if (!attributes.isEmpty()) {
        for (Attribute attribute:attributes){
            longList.add(attribute.getId());
        }
        criteria.createAlias("product.attributeSet", "attribute");
        criteria.add(Restrictions.in("attribute.id", longList));
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

    }
Emil
  • 423
  • 1
  • 12
  • 34
  • 1
    can you show us the complete code of those classes? And the definitions of the corresponding tables? You wrote that the input is a list of attribute IDs, but class Attribute has no such member / mapping to a column. – Ossin Java guy May 01 '17 at 07:36
  • @JavaGuynextdoor, sorry :(. `optionValueList` instead `attribute` IDs. – Emil May 01 '17 at 14:36

2 Answers2

1

The general idea is to start with creating criteria of objects you want to return, and travel further by adding criteria which is joined. So I start with Parent class, add qualifiers and end up with most nested element, OptionValue.

Code below is untested, but you should get the idea:

Criteria criteria = currentSession()
  .createCriteria(Product.class)
  .createCriteria("attributeSet", "join_between_product_and_attribute");

if (!attributes.isEmpty()) {
  Set<String> attributeIds = new HashSet<>();
  for (Attribute attribute : attributeList) {
    attributeIds.add(attribute.getId());
  }
  criteria.add(Restrictions.in("id", attributeIds));
}

criteria = criteria.createCriteria("optionValue", "join_between_attribute_optionvalue");
if (!optionValueList.isEmpty()) {
  criteria.add(Restrictions.in("id", optionValueList));
}
mindas
  • 26,463
  • 15
  • 97
  • 154
  • Thanks for your answer. I don't have `attributes` IDs. How get it by youe solution? – Emil May 05 '17 at 14:52
  • When you join `attributeSet`, your criteria starts to refer to attributes. And each attribute has an `id`, hasn't it? Your own example also references attribute ids. I recommend trying this code first, and coming back with errors you get (if any). – mindas May 07 '17 at 15:15
  • I just have `optionValue` IDs. as you see in my answer i fetch it by `criteria` query. – Emil May 07 '17 at 16:35
1

an even easier solution would be to use a CriteriaQuery. i did not test the following code, but i think it should work correctly. it requires hibernate 5, but also works with some modifications in hibernate 4:

CriteriaBuilder cb = sessionFactory.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> r = query.from(Product.class);

In<Object> in = cb.in(r.join("attributeSet ").join("optionValue").get("id"));
for(Object optionValue : optionValueList){
    in.value(optionValue);
}
query.select(r).where(in);

return sessionFactory.getCurrentSession().createQuery(query).getResultList();

i am assuming, that you can access the optionValueList since you posted it in your question.

For the solution with EntityManager i am assuming you already were able to instantiate one.

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

In<Object> in = cb.in(r.join("attributeSet ").join("optionValue").get("id"));
for(Object optionValue : optionValueList){
    in.value(optionValue);
}
query.select(r).where(in);

return entityManager.createQuery(query).getResultList();

if you have an EntityManagerFactory, replace the first entityManager with it and the second one with entityManagerFactory.createEntityManager()

XtremeBaumer
  • 6,275
  • 3
  • 19
  • 65