Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use JPA Criteria API in JOIN

Tags:

join

jpa

  • 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 need JOIN but how I don't know how to use it.

like image 471
Sami Avatar asked Jan 24 '12 01:01

Sami


People also ask

What is JPA Criteria API?

Advertisements. The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax.

How do you write join query in Hibernate using criteria?

Criteria in Hibernate can be used for join queries by joining multiple tables, useful methods for Hibernate criteria join are createAlias(), setFetchMode() and setProjection() Criteria in Hibernate API can be used for fetching results with conditions, useful methods are add() where we can add Restrictions.


1 Answers

use join(),

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

See, http://en.wikibooks.org/wiki/Java_Persistence/Querying#Joining.2C_querying_on_a_OneToMany_relationship

like image 197
James Avatar answered Oct 04 '22 02:10

James