Differences between JPA predicate using Entity and property

Say I have the following Entity classes:

@Entity public class MyEntity {
    @Id private String id;
    @ManyToOne private MyOtherEntity myOtherEntity;
}

@Entity public class MyOtherEntity {
    @Id private String id;
    @Column private String name;
}

Now I want to do a query to get all the MyEntitys linked to a certain MyOtherEntity, I wonder the difference between the following 3 predicates:

cb.equal(root.get(MyEntity_.myOtherEntity), myOtherEntity);
cb.equal(root.get(MyEntity_.myOtherEntity).get(MyOtherEntity_.id), myOtherEntity.getId());
cb.equal(root.get(MyEntity_.myOtherEntity).get(MyOtherEntity_.name), myOtherEntity.getName());

How would the generated SQLs look like in each case? And which one is most efficient?

>Solution :

For a start I suggest to take the trouble and enable SQL logging in Hibernate while developing – see here. Knowing the exact statements Hibernate creates for your JPA queries is invaluable, e.g. you have a chance to spot N+1 query problems, excessive joins etc.

Having said that, in your case the statements should look like as follows:

• cb.equal(root.get(MyEntity_.myOtherEntity), myOtherEntity) → SELECT ... FROM MyEntity WHERE MyEntity.myOtherEntity_id = ?. In cases like this, Hibernate usually knows to optimize and avoid the unnecessary join.

• cb.equal(root.get(MyEntity_.myOtherEntity).get(MyOtherEntity_.id), myOtherEntity.getId()) → Should be like above; again Hibernate should know that the .get(MyOtherEntity_.id) is already in the table and avoid the unnecessay join.

  I have seen Hibernate working the way I describe for the cases above. Definitely enable SQL logging to verify, there may be details for your own use case that make it behave in a different way!

• cb.equal(root.get(MyEntity_.myOtherEntity).get(MyOtherEntity_.name), myOtherEntity.getName()) → Will definitely create a join because it cannot find myOtherEntity.name in the MyEntity table: SELECT ... FROM MyEntity e JOIN MyOtherEntity oe ON ... WHERE oe.name = ?