If I am in the situation where I have a n+1 problem I normally use the JPA "join fetch" keyword if it is only one indirection (i. e. select p from Person p join fetch p.address) or if it is more than one indirection I use a JPA provider-proprietary query hint, which is in my case eclipselink (i. e. select p from person with the query hint eclipselink.join-fetch=p.address.city). Thats well explained in the article Java Persistence Performance.
Anyway, lately I stumbled over a datamodel where two entities where subclassed by another one.
I have an Account which owns a Contact. A Contact itself is a abstract superclass which is extended by a Person or a Company. And a Person does have a relationship to a list of Hobbyies.
@Entity
public class Account
{
@OneToOne
private Contact contact;
}
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Contact {}
@Entity
public class Person extends Contact
{
@OneToMany
private List<Hobby> hobbies;
}
@Entity
public class Company extends Contact {}
I need to load a list of Accounts, select a from Account a. I surely ran into the n+1 problem, because of the hobbies. No problem I thought, with the two above mentioned and well probed solutions I can cope with the n+1 problem. But soon I realized that does not work.
select a from Account a join fetch a.contacts does not fetch the hobbies. Nor does select a from Account a with the query hint eclipselink.join-fetch=a.contacts. And select a from Account a with the query hint eclipselink.join-fetch=a.contacts.hobbies throws a
... navigated to a non-existent relationship-Exception.
I also tried to use the JPA treat-function i. e. select a from Account a join fetch treat(a.contact as Person) p join fetch p.hobbies, but that does not fetch the hobbies and only fetches the Persons and not the Companies along.
Does anyone have an idea how to use join fetch or eclipselink query hints to accomplish such a query optimization?
EDIT
In order to answer the comment by Chris.
The @BatchFetch annotation at the hobbies does not have any impact on the query. There is no difference to the queries without it.
Here are the sqls generated by each query with the @BatchFetch annotation
select a from Account a
1 SELECT id, contact_id FROM account
2 SELECT DISTINCT DTYPE FROM contact WHERE (ID = ?)
3 SELECT t0.ID, t0.DTYPE, t1.ID, t1.FOUNDED FROM contact t0, company t1 WHERE ((t0.ID = ?) AND ((t1.ID = t0.ID) AND t0.DTYPE = ?))
4 SELECT DISTINCT DTYPE FROM contact WHERE (ID = ?)
5 SELECT t0.ID, t0.DTYPE, t1.ID, t1.NAME FROM contact t0, person t1 WHERE ((t0.ID = ?) AND ((t1.ID = t0.ID) AND (t0.DTYPE = ?)))
6 SELECT ID, OUTDOOR, PERSON_ID FROM hobby WHERE (PERSON_ID = ?)
... (repetition of lines 2 to 6)
select a from Account a join fetch a.contacts
SELECT t3.ID, t3.contact_id, t0.ID, t0.DTYPE, t1.NAME, t2.ID, t2.FOUNDED FROM contacts t0 LEFT OUTER JOIN person t1 ON (t1.ID = t0.ID) LEFT OUTER JOIN company t2 ON (t2.ID = t0.ID), account t3 WHERE (t0.ID = t3.contact_id)
SELECT ID, OUTDOOR, PERSON_ID FROM hobby WHERE (PERSON_ID = ?)
SELECT ID, OUTDOOR, PERSON_ID FROM hobby WHERE (PERSON_ID = ?)
SELECT ID, OUTDOOR, PERSON_ID FROM hobby WHERE (PERSON_ID = ?)
SELECT ID, OUTDOOR, PERSON_ID FROM hobby WHERE (PERSON_ID = ?)
SELECT ID, OUTDOOR, PERSON_ID FROM hobby WHERE (PERSON_ID = ?)
...
