[Spring Boot] Exploring Spring Boot 3 with QueryDSL — Part 1

Introduction

In my previous project, the data access layer was implemented using Spring Data JPA because of these well-known reasons:

• Provides CRUD operations out of box
• Easy to create a custom query
• Supports pagination and sorting
• Easy to write unit tests for data access layer

The client has told me that they are using a relational database, I wanted to implement the concept of Bounded Context so that the application domain is only accessed through the aggregate root. In order to do this, JPA associations such as @OneToMany, @ManyToOne, etc were used. Everything was fine until the client had asked for a feature which had to show pagination of all parent table rows with each associated child table row counts (N+1 query problem).

Here is the process that I went through:

Join Fetch and Entity Graph

Join Fetch can be used to eagerly load associated entities in a single query.

public interface TeamRepository extends JpaRepository<Team, Long> {

    @Query("""
            select distinct t
                from Team t
                left join fetch t.members
                left join fetch t.milestones
            """)
    List<Team> findAllUsingJoinFetch();

Similarly, the JPA Entity Graph can improve the runtime performance by dynamically changing the the fetch type strategies of associated entities during runtime.

public interface TeamRepository extends JpaRepository<Team, Long> {

    @EntityGraph(attributePaths = {
            "members", "milestones"
    })
    List<Team> findAll();
}

Problems

These two approaches should provide a lot better performance than before. However, there are some challenges with these.

First of all, let’s have a look at the Team entity. The Team entity has two child entities mapped with List.

@Entity
@Data
@NoArgsConstructor
@Table(name = "teams")
public class Team {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "team", cascade = {CascadeType.PERSIST, CascadeType.MERGE}, orphanRemoval = true)
    private List<Member> members;

    @OneToMany(mappedBy = "team", cascade = {CascadeType.PERSIST, CascadeType.MERGE}, orphanRemoval = true)
    private List<Milestone> milestones;

    // constructors, helper functions, etc ..
}

Using join fetch or entity graph on multiple children simultaneously will give an error like below:

org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [io.jay.service.repository.Team.members, io.jay.service.repository.Team.milestones]

This can be solved if you use Set instead of List. If you really want to stick with List, then you would have to divide into two queries eagerly loading each child and merge in the application code. Therefore, it may require you to change some code if you need to load multiple child entities and the decision will depend on the feature acceptance criteria.

The bigger problem is that both solutions struggle with pagination. To demonstrate the problem I will change both child entities to Set instead of List.

public interface TeamRepository extends JpaRepository<Team, Long> {

    @EntityGraph(attributePaths = {
            "members", "milestones"
    })
    Page<Team> findAll(Pageable pageable);
}

If you were to call this method, it will return pagination response successfully.

{
  "content": [
    {
      "id": 1,
      "name": "First Team",
      "memberCount": 4,
      "milestoneCount": 0
    },
    {
      "id": 2,
      "name": "Second Team",
      "memberCount": 3,
      "milestoneCount": 0
    }
  ],
  "pageable": {
    "pageNumber": 0,
    "pageSize": 20,
    "sort": {
      "empty": true,
      "unsorted": true,
      "sorted": false
    },
    "offset": 0,
    "paged": true,
    "unpaged": false
  },
  "totalElements": 2,
  "totalPages": 1,
  "last": true,
  "size": 20,
  "number": 0,
  "sort": {
    "empty": true,
    "unsorted": true,
    "sorted": false
  },
  "numberOfElements": 2,
  "first": true,
  "empty": false
}

When lookin at the response, it might lead you to believe that pagination is automatically handled when using the entity graph by passing in a Pageable to your query. However, it will give you a warning indicating that pagination is being performed in memory.

WARN 88609 --- [nio-8080-exec-3] org.hibernate.orm.query : HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

While both approaches effectively address the N+1 query problems, it becomes a challenge when used for pagination since they load all the data and then pagination is managed within the application rather than the database.

Batch Processing

Hibernate provides fetch_size and batch_size attributes for batch operations, which can be configured globally for all entities or for specific entities. The fetch size attribute allows you to adjust the number of rows fetched in select statements (if supported by the database driver).

Batch processing can reduce the total number of trips to the database. This might seem like it is the easiest solution because it does not require any code change. However, it was difficult for me to determine the optimal batch size without a production-ready environment.

QueryDSL

The team made a decision to improve the performance as much as possible in application code before tweaking any configurations or any infrastructure in the production environment. QueryDSL offered several advantages which garnered the support from my team:

• Simplified Complex Queries: Easier to write more complex queries with sub-queries and dynamically constructed queries.
• Type Safety.
• Pagination Support.
• Custom Projects: Provides custom projections to map query result to domain models or DTOs easily which should be faster than mapping all unnecessary fields.

In the next article, we will dive into applying QueryDSL in a Spring Boot 3 application.

References

Hibernate throws MultipleBagFetchException - cannot simultaneously fetch multiple bags