Differences between JPA predicate using Entity and property

Question

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?

score 1 · Accepted Answer · answered Dec 17 '21 at 08:20

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 = ?

Haha, you knew exactly I was finding it hard to setup SQL logging hence asking the question. Thanks for your detailed input! — user1589188, Dec 17 '21 at 08:30
Yeah, I know the logging is a pain, but definitely worth it! Good luck :) — Nikos Paraskevopoulos, Dec 17 '21 at 08:31

Differences between JPA predicate using Entity and property

1 Answers1