How to select multiple many-to-many associations efficiently using JPQL and Hibernate?

Hibernate ORM
1

I’m using JPA with hibernate as a provider. One of my entities has several many-to-many junctures with other entities.

Sometimes there can be thousands of related entities, so setting up a collection getter for the primary entity in question isn’t doable, since I’d prefer to acquire the related entities in batches. So then my options are:

  1. Write a join query in JPQL. But this joins and grabs entities from both sides of the relationship, a much slower query than simply getting a list of IDs sitting in the middle
  2. Create a separate entity for each individual M2M juncture (but this seems like a lot of needless classes)
  3. Write a native query to get this information… but unfortunately SQL Server (yuck) deems all the IDs that I select to be BigDecimals rather than Integers, which causes trouble in other parts of the code. I need to support multiple databases, so doing a cast in the native query would mean I have to check what type of database is being used… which really defeats the purpose of an ORM

Is there a way to have JPQL select from this table so that I can have the guarantee of retrieving integers, and have the guarantee that the query will be fast?

2

If you have thousands of child entities and you map them as a collection, you are doing it all wrong. Collections make sense when you have just a relative small amount of records, otherwise a JPQL query is much more efficient and flexible as well (e.g. can be paginated).

  1. If you join fetch multiple collections you will get with a Cartesian Product, so if you have 1000 children per collections and you need to fetch 5 collections and the root entity has also 1000 records matching the query, you will get: 1000 x 1000 x 1000 x 1000 x 1000 x 1000 rows in the ResultSet which will take hours or days to return.
  2. That’s not a bad idea at all and it allows you to use Lists efficiently not just Sets like the classic @ManyToMany association.
  3. You can use native SQL and still have portability. You can even use named native queries and expose those via an external DB-specific orm.xml that you provide during build using a profile.

Is there a way to have JPQL select from this table so that I can have the guarantee of retrieving integers, and have the guarantee that the query will be fast?

Join fetch just one collection and use secondary queries for the rest. But then, do you really need to fetch all that data. Maybe you don’t need all of it. Maybe a DTO projection is more efficient for your use case, and you can use the Hibernate ResultTransformer to return the result as a graph, not as a table.

1 Like
3

When you say

retrieving integers

do you mean to say, retrieve just the ids of the elements and query the rest of the data later?

There are multiple ways, even with plain JPQL, to do what you want, but I need to understand better what you are trying to achieve.

4

By “from a many to many table” I mean select a list of related IDs from one side of a table consisting of nothing but two columns for different entites’ IDs, yes.

5

You could do something like this

select e, m.id from SomeEntity e LEFT JOIN e.manyToMany m

Depending on whether you want the many to many assocations from multiple or just a single entity, you could even do pagination with setFirstResult and setMaxResults.

I don’t think it makes much sense to present a user 100+ elements, so I would rather try to use pagination and offer proper filtering.

6

Hello. Can you please elaborate how to create such graph if we need multiple associations in the DTO ? Will that not suffer from the same cartesian product issue as the JPQL joins ?

Regards, Vassil

7

As @vlad already answered in his blog, the way to do this is with one query per association type. Thanks again.