Home Filter by JPA Criteria Query with overlapping paths expressions
Reply: 0

Filter by JPA Criteria Query with overlapping paths expressions

user3841
1#
user3841 Published in April 25, 2018, 6:09 pm

I am using Jpa/Hibernate to access MariaDB in a Spring Boot application

I have this Model of a customer and it's company. Where one company can be owned by another.

import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

class customer {

  @Id
  private Long id;
  private String name;
  private Company company;
}

class Company {

  @Id
  private Long id;
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "ownedBy")
  private Company ownedBy;
}

I want to filter for all customers of the company and the customers of the companies the company owns.

e.g.

  • Customer1 belong to Company (id: 1)

  • Customer2 belong to Company2 (id: 2

  • Company2 is owned by Company1

  • querying for id = 1 -> [Customer1, Customer2]

I build the following 2 predidates to match the customer by company id or owned Company id.

Join join = root.join("company");
Path companyPath = join.get("id");
// creating this path lead to the other one not working any more
Path companyOwnedPath = join.join("ownedBy").get("id");
Predicate company = criteriaBuilder.equal(companyPath, id);
Predicate companyOwned = criteriaBuilder.equal(companyOwnedPath, id);

They both work alone so I tried to combine them with an or.

criteriaBuilder.or(company, companyOwned);

But then I only get the results matching companyOwned. But I found that always when I declare the companyOwnedPath (even without the predicate), the company predicate doesn't work anymore.

Is there any side effect when creating such a path?

This is the code for the whole query building.

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.transaction.annotation.Transactional;


public interface CustomerRepositoryCustom {

  List<Customer> getByCompany(Long id, int page, int size);
}

@Transactional(readOnly = true)
public class CustomerRepositoryImpl implements CustomerRepositoryCustom {

  @PersistenceContext
  private EntityManager entityManager;

  public List<Customer> getByCompany(Long id, int page, int size) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery<Customer> query = criteriaBuilder.createQuery(Customer.class);
    Root<Customer> root = query.from(Customer.class);
    CriteriaQuery<Customer> select = query.select(root);

    query.where(buildCompanyPredicate(criteriaBuilder, root, id);

    TypedQuery<Customer> typedQuery = entityManager.createQuery(select);

    typedQuery.setFirstResult(page * size);
    typedQuery.setMaxResults(page);

    return typedQuery.getResultList();
  }

  private Predicate buildCompanyPredicate(CriteriaBuilder criteriaBuilder, Root root, Long id) {

    // company join
    Join join = root.join("company");   
    Path companyPath = join.get("id");    
    // creating this path lead to the other one not working any more
    Path companyOwnedPath = join.join("ownedBy").get("id");  
    Predicate company = criteriaBuilder.equal(companyPath, id);   
    Predicate companyOwned = criteriaBuilder.equal(companyOwnedPath, id); 
    return criteriaBuilder.or(company, companyOwned);
  }
}

Thanks

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.306646 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO