I am using Spring Boot and Hibernate.

Some complex logic, dictated by business, needs to use various nested fields, which traverse various DB relationships (again, some are NxN, Nx1, 1xN, 1x1).

I encountered the N 1 problem, and I solved it at first with HQL, but some queries need several joins and the result sets become unmanageable.

I started working on a custom utility that collects the ids of things that need to be fetched, fetches them all at once and uses the setters to then populate the fields on the starting objects. This utility works for ManyToOne relationships, but is still inefficient with ManyToMany relationships, because it falls back in the N 1 problem when I collect the ids (as it queries the join table once per object via the getter).

How can I solve this? Has this problem really not been solved yet? Am I missing some obvious settings that solves this automagically?

CodePudding user response：

I had faced the same situation and I had 3 ways to solve it;

1. increase the fetchsize for the dependent attribute so that the queries are executed in batch
2. write a custom query for the purpose
3. define entity graph relations and map accordingly to attributes

I personally preferred the 3rd option as it was convenient to do that and was cleaner with spring data JPA.

you can refer to examples from the comments from the below answers:

• Spring Data JPA And NamedEntityGraphs
• What is the solution for the N 1 issue in JPA and Hibernate?

CodePudding user response：

Write fetch logic on your own. E.g You have author which has book, author_devices You can join fetch author with books. Than you can separatly fetch author_devices using repository "where author_id IN (authorsList.stream().map(author.getId())". Than you should detach author and iterate author_devices and assign it to apropriate author devices list. I think it's only adequate solution for situations where you need to join-fetch more than 1 relation.