JPA Eager Fetch Does Not Join

Eager fetch performs left join in hibernate but fires seperate sql queries in springboot/JPA

The findByCustomerId will actually generate a query based on that method instead of using em.find. It will create something along the lines of SELECT c FROM Customer c WHERE c.customerId=:customerId. afterwards it will notice the fetch strategy and obtain the needed references. This is also explained here. The query will do exactlly what you instruct it to do.

If you want to eagerly load the reference you would need to write the query yourself along the lines of SELECT c FROM Customer c JOIN FETCH c.orders o WHERE c.customerId=:customerId, this will automatically retrieve the orders.

However the customerId is actually the primary key or identitifier for your entity and thus you should actually be using the findById or findOne method (depending on your Spring Data JPA version). This will use the EntityManager.find which should take the mapping information into account and create the appropriate query.

JPA & Hibernate: Eager loading performing subsequent queries to fetch all data, instead of doing it in just one query

I found it described in https://vladmihalcea.com/eager-fetching-is-a-code-smell/
under EAGER fetching inconsistencies:

Both JPQL and Criteria queries default to select fetching, therefore issuing a secondary select for each individual EAGER association. The larger the associations’ number, the more additional individual SELECTS, the more it will affect our application performance.

Also, note that Hibernate similarly ignoreg fetching annotations for HQL queries:
https://developer.jboss.org/wiki/HibernateFAQ-AdvancedProblems#jive_content_id_Hibernate_ignores_my_outerjointrue_or_fetchjoin_setting_and_fetches_an_association_lazily_using_n1_selects

Hibernate ignores my outer-join="true" or fetch="join" setting and fetches an association lazily, using n+1 selects!

HQL queries always ignore the setting for outer-join or fetch="join" defined in mapping metadata. This setting applies only to associations fetched using get() or load(), Criteria queries, and graph navigation. If you need to enable eager fetching for a HQL query, use an explicit LEFT JOIN FETCH.

How to perform an EAGER fetch in JPQL

Yup. You are right. The syntax is [inner|left] join fetch. Example :

select cal from ContactAddressLink cal
inner join fetch cal.contact c
inner join fetch cal.address a
where cal.id = 123456789

If you want to match ContactAddressLink if it has Contact/Address , use inner join fetch.

If you want to match ContactAddressLink even if it does not have Contact/Address , use left join fetch.

Eager fetch does not seem to join

Ebean is the underlying JPA implementation

Ebean is not a JPA Implementation. Ebean has a little in common with the JPA standard. It looks a little similar, because it's annotations look similar to JPA's.

Cause of your problem: Ebean does not support fetch=... attribute on @ManyToMany annotation.

2 solutions:

1. Remove fetch=... from annotation and instead use fetch() method on either the play.db.ebean.Model.Finder<I,T> object (which implements com.avaje.ebean.Query<T>) or the com.avaje.ebean.Ebean object.

   Problem is there are two separate persistent libraries here (play.db.ebean and com.avaje.ebean) and integration between them is a challenge and issue.
   
   E.g. play.db.ebean.Model.Finder.fetch() method returns com.avaje.ebean.Query<T> rather than play.db.ebean.Model.Finder - which I find unhelpful as it takes you away from the play API Model helper. After calling fetch() apparently you can no longer use the Model.Finder methods - not much of a "helper" class at that point.
   
   To be honest, I don't think the fetch functionality is a first class feature within Play API yet - best to defer to the Avaje API.

   Two examples of using fetch() from within the Avaje API:

   // single SQL query with a fetch join
   List<Order> l0 = Ebean.find(Order.class)
   .fetch("customer")
   .findList();
   

   and

   // two separate SQL queries
   List<Order> l0 = Ebean.find(Order.class)
   .fetch("customer", new FetchConfig().query())
   .findList();
   

   Note: the last release/update to Ebean (other that bug fixes) was 6 Nov 2010 - 2 full years ago. The uptake rate of Ebean is very low.

2. Switch to using a JPA 2.0 implementation within Play. The 2.0 release of JPA is very powerful indeed, is easy to use, and is very widely supported. It will probably mean the death of Ebean. Note that Play has added the following configuration feature into 2.0 (wasn't present in 1.1) - suggesting Play is moving towards fuller support for JPA from any provider.
   http://www.playframework.org/documentation/2.0/JavaJPA

   Once you do this, stop using play.db classes and com.avaje classes - I suggest you use 100% javax.persistence classes.

---

Related Topics