Edit Question - KnowledgeHub

Title Body

I have five tables: Company, Product/Service, Address, Country and City. 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 java.io.Serializable { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Short companyId; @OneToOne(cascade=CascadeType.PERSIST) 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 java.io.Serializable { @Id @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 ); //criteria.select(companyRoot); 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

Tags (comma-separated) Cancel