How to query three related tables efficiently (JPA-QL)

Question

Let's say I have entities A, B, C and each A has many B and C entities. I want to query a load of A entities based on some criterea, and I know I will be accessing all B and C entities for each A I return.

Something like select a from A as a join fetch a.b join fetch a.c would seem to make sense at first, but this creates a huge product if the numbers of B and C entities are large. Extending this to another associated entities makes the query totally unreasonable.

If I leave JPA to its own devices, I end up with n+1 selects when it wants to access the B and C entities.

What I thought I'd do was query A join fetch B, then A join fetch C, but this doesn't work as it gives me two List<A> results each with only half the information.

This is a pretty simple query in SQL terms, and I'm disappointed there isn't an obvious way to handle this. Am I missing something?

Provider is toplink essentials

Answer 1

JPA should at least mention objects. The fact that you don't suggests to me that you're not going to be leveraging JPA to its fullest extent.

If you've got a legacy schema, and an object model doesn't make sense, perhaps you shouldn't be using JPA.

JPA isn't intended to be a substitute for SQL. It addresses that object-relational mismatch. If you don't have objects, just drop down to JDBC and SQL.

I don't know what your tables represent, but if you're thinking about objects you should be talking about 1:m and m:n relationships. Once you have those you can use caching, lazy and eager fetching to optimize populating the objects.

UPDATE: Write the query so each product has its options and prices lists as 1:m relationships and do eager fetching. That will avoid the (n+1) problem.

How can you say that relationships and eager fetching don't help here?

Try expressing the relationships in objects and have JPA show you the SQL it generates and compare it to what you'd write. If it's satisfactory, go for it. If not, drop down to JDBC and see if you can do better.

Answer 2

I wonder why you say this is pretty simple in SQL terms. Wouldn't you also have the cartesian product?

---

Using the Hibernate provider for JPA, an option you mention works:

query A join fetch B, then A join fetch C

You have two list of the same values, you use only one and it is fine (you just need to LEFT join).

---

In Hibernate, you can also ask to fetch the missing data in a second query.

Use fetch="subselect".

See https://www.hibernate.org/315.html

---

UPDATED after comment of the Original Poster:

In java, you could also do this by hand.

1. Fetch the As with their collections of Bs, in a list called entityAs.
2. Fetch the As with their collections of Cs (reusing part of the query, or using ids).
3. Create a datastructure Map> for the second query (for performance, to avoid inner loop).
4. Loop on list entityAs, using the Map to set the set Cs for each instance A.

This would have a good performance also.

If you run several times into this need, you could write a parameterized method to do this for you, so you only code it once.

As commented by the Original Poster, you need to detach all A entities from entityAs before modifying them, to be sure there will be no update send to the database...