-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Join and alias issues with JPA Criteria API queries
PostPosted: Mon Dec 11, 2017 5:22 pm 
Newbie

Joined: Mon Dec 11, 2017 12:09 pm
Posts: 3
I'm in the process of converting Hibernate Criteria queries to JPA Criteria ones (in an application that will be using Hibernate 5.2.12) and have encountered some issues with joins and aliasing. I can't tell where I'm going wrong and am hoping that someone else can shed some light on the subject for me.

If I take an entity (Entity 1 with alias A) and join it to a second entity (Entity 2 with alias B), the code will only "see" the fields associated with B, not the A, using the join. Aliasing doesn't seem to work the way that I am used to in the Hibernate API, i.e., I can't reference fields by A.field1, A.field2, B.field3, B.field4 when selecting query items or using those fields to constrain the query.

I've examples below of what works and what does not using two skeleton classes, Person and Phone.

Code:
   @Entity
   public class Person {
      @Id
      private Integer id;
      
      @Column
      private String name;
      
      @ManyToOne
      private Phone Phone;

   }

   @Entity
   public class Person {
      @Id
      private Integer id;
      
      @Column
      private String number;
      
      @OneToMany
      private List<Persons> persons;
      
   }


JPA Criteria examples:

Selecting a list of Person entities (persons) succeeds:

Code:
   CriteriaBuilder builder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Person> criteria = builder.createQuery( Person.class );
   Root<Person> personRoot = criteria.from( Person.class );
   personRoot.alias("a");
   Join<Person,Phone> join = personRoot.join( "phone" );
   personRoot.alias("b");
   criteria.select( personRoot );
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


Attempting to restrict the list by a person's name using the join fails: <==== What I would like to do

Code:
   criteria.select( personRoot );
   criteria.where( builder.equal( join.get( "name" ), "John Doe" ) );
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


Attempting to restrict the list by a phone number on the join succeeds: <==== What I would like to do

Code:
   criteria.select( personRoot );
   criteria.where( builder.equal( join.get( "number" ), "999999999" ) );
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


Attempting to restrict the list by a person's name using getParent() on the join succeeds: <==== What works but I don't want to do

Code:
   criteria.select( personRoot );
   criteria.where( builder.equal( join.getParent().get( "name" ), "John Doe" ) );
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


Attempting to accessing a field using an alias fails: <==== What I would like to do

Code:
   criteria.select( personRoot );
   criteria.where( builder.equal( join.get( "a.name" ), "John Doe" ) );
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


Top
 Profile  
 
 Post subject: Re: Join and alias issues with JPA Criteria API queries
PostPosted: Tue Dec 12, 2017 6:20 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
You don't need the alias when you use Criteria API, just use the type-safe objects (e.g. Root, Join) to reference the properties you are interested:

Code:
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> criteria = builder.createQuery( Person.class );
Root<Person> personRoot = criteria.from( Person.class );
Join<Person,Phone> join = personRoot.join( "phone" );
criteria.select( personRoot );
criteria.where( builder.equal( join.get( "number" ), "999999999" ) );
TypedQuery<Person> tq = entityManager.createQuery(criteria);
List<Person> persons = tq.getResultList();


Check out the Hibernate User Guide for more details.


Top
 Profile  
 
 Post subject: Re: Join and alias issues with JPA Criteria API queries
PostPosted: Tue Dec 12, 2017 10:03 am 
Newbie

Joined: Mon Dec 11, 2017 12:09 pm
Posts: 3
Thanks for your reply.

So, I can't select fields in the root from the join but have to use the root itself? The code below fails when I attempt to filter on a person's name from the join

Code:
   CriteriaBuilder builder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Person> criteria = builder.createQuery( Person.class );
   Root<Person> personRoot = criteria.from( Person.class );
   Join<Person,Phone> join = personRoot.join( "phone" );
   criteria.select( personRoot );
   criteria.where( builder.equal( join.get( "name" ), "John Doe" ) );  // filter on name in join
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


but succeeds when I filter a person's name from the root

Code:
   CriteriaBuilder builder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Person> criteria = builder.createQuery( Person.class );
   Root<Person> personRoot = criteria.from( Person.class );
   Join<Person,Phone> join = personRoot.join( "phone" );
   criteria.select( personRoot );
   criteria.where( builder.equal( personRoot.get( "name" ), "John Doe" ) );  // filter on name in root
   TypedQuery<Person> tq = entityManager.createQuery(criteria);
   List<Person> persons = tq.getResultList();


I didn't want to have to swap between root and join in doing this.


Top
 Profile  
 
 Post subject: Re: Join and alias issues with JPA Criteria API queries
PostPosted: Tue Dec 12, 2017 10:22 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Of course, it works. If you want to prove it, then:

1. go to GitHub
2. fork this repository
3. run the CriteriaAPITest

Code:
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<PostComment> criteria = builder.createQuery(PostComment.class);
Root<PostComment> fromPost = criteria.from(PostComment.class);

Join<PostComment, Post> postJoin = fromPost.join("post");

criteria.where(builder.like(postJoin.get(Post_.title), "high-performance%"));
List<PostComment> comments = entityManager.createQuery(criteria).getResultList();

assertEquals(5, comments.size());


It works like a charm.


Top
 Profile  
 
 Post subject: Re: Join and alias issues with JPA Criteria API queries
PostPosted: Tue Dec 12, 2017 10:30 am 
Newbie

Joined: Mon Dec 11, 2017 12:09 pm
Posts: 3
Thank you - I'll take a look. :-)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.