0

I am developing a SpringBoot service which uses a mysql database.

There are three tables in my database: Person, Recipient and Category. Person has a bidirectional many-to-many relationship to Category and so does Recipient. Collections are loaded lazily.

In a controller, I want to give an overview about the entities in these three tables. My code in the service layer looks like this:

List<Person> allPersons = personRepository.findAll();
List<Recipient> allRecipients = recipientRepository.findAll();
List<Category> allCategories = categoryRepository.findAll();

for(Person person : allPersons){
    Set<Category> categories = person.getCategories();
    for(Category category : categories){
        // do something with the person and the categories
    }
}

for(Recipient recipient : allRecipients){
    Set<Category> categories = recipient.getCategories();
    for(Category category : categories){
        // do something with the recipient and the categories
    }
}

for(Category category : allCategories){
    Set<Person> persons = category.getPersons();
    for(Person person : persons){
        // do something with the category and the persons
    }
    Set<Recipient> recipients = category.getRecipients();
    for(Recipient recipient : recipients){
        // do something with the category and the recipients
    }
}

Within the first three lines, all necessary entities are loaded from the database exactly once within three database queries. This is OK from a performance point of view. But:

According to the logs, while calling e.g.

Set<Category> categories = person.getCategories()

in the first outer for-loop, the service makes another database query to fetch the person's categories for each person, although the categories have already been loaded within the first three code lines. The same holds for the other loops.

For example, if there are 5 persons, 6 recipients and 7 categories within the database, the service performs 3 + 5 + 6 + 2*7 = 28 database queries in total. Obviously, this is very inefficient.

My question is:

What do I have to change so that the service fetches each entity only once with as few database queries as possible?

Florian
  • 23
  • 8

1 Answers1

0

The issue you are facing is typical hibernate N+1 problem.You can eagerly fetch your collection and write your own query.

@Query("Select p from Person p join fetch p.categories categories)
public List<Person> fetchAllPerson();

You can read more about it JPA Hibernate n+1 issue (Lazy & Eager Diff)

Ishant Gaurav
  • 1,183
  • 2
  • 13
  • 32
  • Thanks for your answer! I tried your solution and now exactly three database queries are performed to fetch all entities. However, a person who is not linked to any category is not fetched with this query. I changed "join fetch" to "left join fetch" what solved the problem. Please tell me if the usage of "left join fetch" is not correct here. One more question regarding your solution: Is it correct that each entity is NOT fetched only once because of the bidirectional relationship? If so, is it possible to make each entity get fetched only once? – Florian Oct 01 '18 at 15:06
  • To make an application faster you have to make less number query. And you need those Person as well which don't have category linked to it . So left outer join is the correct choice – Ishant Gaurav Oct 01 '18 at 15:33
  • For testing I added four persons and one category to my database and linked them. When I execute the code from my first post with the "left join fetch" solution, then four categories are found in the outer loop for categories. In the database itself only one category is saved. Do you have any idea? – Florian Oct 01 '18 at 16:16
  • Changing type of "allPersons", "allRecipients", "allCategories" as well as the return types of the repository methods to Set instead of List fixed the issue. – Florian Oct 01 '18 at 16:39