How to use JPA Criteria API in JOIN


  • I have five tables: Company, Product/Service, Address, Country and
  • A Company can have n products with category, 1 address with 1 country
    and 1 city inside the address entity.
  • A user has chosen "England – Leeds".
  • I know now that I have to select every companies from db where city
    is Leeds and populate product/service-list with those companies'
    products or services. After that user can select for instance dentist
    from the third list.
  • After that I know Enlgand – Leeds – Dentist and I have to populate
    the last list with compenies (dentists in Leeds)

public class Company implements {

@GeneratedValue(strategy = GenerationType.AUTO)
private Short companyId;
private Address address;
private String companyName;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "company",cascade=CascadeType.PERSIST)
private Set<Product> products = new HashSet<Product>(0);

public class Product implements {

@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "productId", unique = true, nullable = false)
private Integer productId;
private Short branchId;
private String productName;
private String sku;
private String category; ------> I am using this field in company search (dentists, garages etc.)

How can I query only those companies which have products with category dentist?

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

CriteriaQuery<Company> criteria = criteriaBuilder.createQuery( Company.class );
Root<Company> companyRoot = criteria.from( Company.class );

TypedQuery<Company> q = em.createQuery(criteria);
List<Company> results = q.getResultList();

Now I've got every company, how can I select only the companies with the correct category? I think I will need JOIN but how I don't know how to use it.

Best Solution

use join(),

criteriaBuilder.equal(companyRoot.join("products").get("category"), "dentist")