CODEWITHSUNDEEP
jpajoincriteria
Amine MEHARZI
Amine MEHARZI

Reputation: 33

Get all columns of two table joined with criteria Query in JPA

I have join two tables with criteria Jpa Api. But I need get columns of two tables joined : 

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
        CriteriaQuery<FacturePoste> criteria = criteriaBuilder.createQuery(FacturePoste.class);
        Root<FacturePoste> root = criteria.from(FacturePoste.class);
        Join<FacturePoste, FactureEntete> contactInfoJoin = root.join("numeroFacture");
          TypedQuery<FacturePoste> query = em.createQuery(criteria.multiselect(contactInfoJoin));list = query.getResultList();

Please help me.

Upvotes: 3

Views: 2919

Answers (2)

Vince D
Vince D

Reputation: 137

It is really easy if we use multiselect. I will try to explain in simple words.

You have already created a CriteriaBuilder but you need to give the generic type of CriteriaQuery an Object[] type because the data that will come from both the tables are of different data types so we put them into a List of Objects:

CriteriaQuery<Object[]> criteria = criteriaBuilder.createQuery(Object[].class);

That means the query will fetch the data from both the tables as objects.

Moving further you created Root for the class FacturePoste but you also need to make the Root for FactureEntete as well:

Root<FacturePoste> posteRoot = cq.from(FacturePoste.class);
Root<FactureEntete> enteteRoot = cq.from(FactureEntete.class);

Now we will perform the multiselect operation on the query:

criteria.multiselect(posteRoot,enteteRoot);

and then you can perform TypedQuery but here also with Object[] as Generic type:

TypedQuery<Object[]> query = em.createQuery(criteria);

Hope this helps!

PS: This method of mine don't need Join and you can customize the query according to your need. Example:

criteria.multiselect(posteRoot,enteteRoot).where(cb.equal(posteRoot.get("id"), enteteRoot.get("posteId")));

If FacturePoste and FactureEntete are mapped as FactureEntete is ManyToOne to FacturePoste with foreign key posteId then only the data will be fetched that ar related.

Upvotes: 1

Vaneet Kataria
Vaneet Kataria

Reputation: 605

You will have to pass an entity graph as described below as a hint to query and pass only root in criteria.select(root) .

CriteriaBuilder criteriaBuilder = 
em.getCriteriaBuilder();
CriteriaQuery<FacturePoste> criteria =    
criteriaBuilder.
createQuery(FacturePoste.class);
Root<FacturePoste> root = 
criteria.from(FacturePoste.class);
Join<FacturePoste, FactureEntete> 
contactInfoJoin = 
root.join("numeroFacture");
TypedQuery<FacturePoste> query = 
em.createQuery(criteria.select(root));

 //   Create an entity graph with a subgraph 
 EntityGraph<FacturePoste> graph = 
 em.createEntityGraph(FacturePoste.class);
 graph.addSubgraph("numeroFacture");

 query.setHint("javax.persistence.fetchgraph", 
 graph);

 list = query.getResultList();

 Note:It will be better if you use static 
 meta model instead of passing entity feild.     
 names directly.

Upvotes: 2

Related Questions