2

I'm using Spring Data and faced up the issue HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! warning message.

The entity relationship that cause that issue is pretty simple, I use @NamedEntityGraph in order to fetch all data:

@NamedEntityGraph(
    name = "customer-graph",
    attributeNodes = @NamedAttributeNode(
        value = "order", 
        subgraph = "sub-graph"
    ),
    subgraphs = @NamedSubgraph(
        name = "sub-graph", 
        attributeNodes = @NamedAttributeNode("products")
    )
)
@Entity
public class Customer {
    @Id
    private Long id;
    @OneToOne(fetch = FetchType.LAZY)
    private Order order;
}


@Entity
public class Order {
    @Id
    private Long orderId;
    @ManyToMany(fetch = FetchType.LAZY)
    private Set<Product> products = new HashSet<>();
}


@Entity
public class Product {
    @Id
    private Long id;
    private String name;
}

And my repository looks like:

public interface CustomerRepository extends CrudRepository<Customer, Long> {
    @EntityGraph(value = "customer-graph")
    Page<Customer> findAll(Pageable pageable);
}

Using say findAll(PageRequest.of(1, 1)) gives me warning message:

o.h.h.internal.ast.QueryTranslatorImpl : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

It's pretty clear for me and I understand why it's happened.

I've seen quite good explanation from @Vlad Mihalcea how to fix it when using Hibernate: https://stackoverflow.com/a/46195656/5108737

But is there a way to fix it using not Hibernate, but Spring Data only?

Ruslan
  • 6,090
  • 1
  • 21
  • 36
  • 1
    The error is still coming from Hibernate and the fix is still the same: don't use firstResult/maxResult with join fetch. – Jens Schauder Apr 15 '20 at 12:45
  • 1
    Pity that this question was closed as a Spring Data specific answer would surely be beneficial. I did it by creating 2 query methods in my Repository interface and call them one after the other from the Service that uses the Repository. – Wim Deblauwe May 25 '20 at 09:20
  • As for fixing HHH000104 using Spring Data JPA, Specification, Criteria queries, and EntityGraph I ended up implementing a generic / reusable solution using the two queries approach. One query to select the entity IDs and a second query with an IN predicate including the IDs from the first query. You can read more at https://tech.asimio.net/2021/05/19/Fixing-Hibernate-HHH000104-firstResult-maxResults-warning-using-Spring-Data-JPA.html – ootero May 20 '21 at 02:27

0 Answers0