CODEWITHSUNDEEP
springhibernatejpapagination
Deroude
Deroude

Reputation: 1112

Spring Data Hibernate: N+1 problem and pagination

TL;DR -- I cannot find a way to solve the N+1 problem while doing pagination at the same time.

My Entities:

@Entity
public class Invoice {

    @Id
    @JsonView(InvoiceView.ShortView.class)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

// other stuff

    @OneToMany(targetEntity = InvoiceItem.class)
    @JoinColumn(name = "invoice")
    private List<InvoiceItem> items;
}

@Entity
public class InvoiceItem {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

// other stuff
}

My repository:

    @EntityGraph(attributePaths = {"vendor","items"})
    @Query(value = "select i from Invoice i where i.status=:status")
    Page<Invoice> getInvoicesWithItemsByStatus(@Param("status") Status status, Pageable pageSpec);

or

        @Query(value = "select i from Invoice i join fetch i.items join fetch i.vendor where i.status=:status",
            countQuery = "select count(i) from Invoice i where i.status=:status")
    Page<Invoice> getInvoicesWithItemsByStatus(@Param("status") Status status, Pageable pageSpec);

Both of these produce this Hibernate warning:

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

and they do just that: fetch everything and give back the page requested. Every time a new page is requested, naturally.

Upvotes: 2

Views: 1739

Answers (1)

areus
areus

Reputation: 2947

Your problem and how to solve it is described in this article by Vlad Mihalcea

https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/

Basically, you would need to write a native query.

Upvotes: 1

Related Questions