I'm currently trying to figure out an efficient way to fetch @OneToMany mapped entities from a list of other entities.
Let's have a look into the details:
I have the two classes E and L (Note: both have the annotations, etc. necessary for JPA/Hibernate. I'm just omitting these information since they're not important for my case)
public class L {
public String name;
}
public class E {
@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
public List<L> ls = new LinkedList<>();
public String name;
[...]
}
for an export I want to get all E objects and their relevant information including the respective Lists of L. Usually I'd do it with a normal Query like the following:
@Query("select e from E e where [...]")
The result will be List<E>
when I go on and create my export the system fetches for each E the list of L, which sums up if we're talking about 1000s of entries.
In my current test system I'm retrieving 3982 rows which takes 5323ms in total. compared to 338ms if i just get the E entities without fetching the L entities.
Is there any efficient way to load all information in one go? One attempt I tried is the following query:
@Query("select e, l from E e LEFT JOIN e.ls l where [...]")
which returns List<Object[]>
Using this query the amount of rows will increase, since some E might be duplicate when they contain more than 1 L.
But maybe there is a better way that I'm not aware of?