Spring-Data FETCH JOIN with Paging is not working

Related searches

I am trying to use HQL fetching my entity along with sub-entities using JOIN FETCH, this is working fine if I want all the results but it is not the case if I want a Page

My entity is

@Entity
@Data
public class VisitEntity {

    @Id
    @Audited
    private long id;

    .
    .
    .   

    @OneToMany(cascade = CascadeType.ALL,)
    private List<VisitCommentEntity> comments;
}

and because I have millions of visits I need to use Pageable and I want to Fetch the comments in a single database query like :

@Query("SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and ..." )
public Page<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...,
        Pageable pageable);

That HQL call throws the following exception:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=com.ro.lib.visit.entity.VisitEntity.comments,tableName=visitdb.visit_comment,tableAlias=comments1_,origin=visitdb.visit visitentit0_,columns={visitentit0_.visit_id ,className=com.ro.lib.visit.entity.VisitCommentEntity}}] [select count(v) FROM com.ro.lib.visit.entity.VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and (v.actualArrival > :date or v.arrival > :date)]
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:309)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

and once I remove the paging everything works fine

@Query("SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and  ..." )
public List<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...);

Obviously the problem is the count query from Spring-Data, but how can we fix it?

The easiest way is to use the countQuery attribute of the the @Query annotation to provide a custom query to be used.

@Query(value = "SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments …",
       countQuery = "select count(v) from VisitEntity v where …")
List<VisitEntity> getVenueVisits(@Param("venueId") long venueId, …);

java - Spring-Data FETCH JOIN with Paging is not working, I am trying to use HQL fetching my entity along with sub-entities using JOIN FETCH, this is working fine if I want all the results but it is not the case if I want a� The problem turned out to be caused by the pagination functionality provided by Spring Data JPA by extending the PagingAndSortingRepository interface (extended by the JpaRepository interface). Whenever one uses this pagination functionality, Spring Data JPA executes two queries; one to get the number of rows matching the query, and one that fetches the data for a given page.

Alternatively in newest versions of Spring (supporting JPA 2.1 specification) you can use entity graph like this:

@EntityGraph(attributePaths = "roles")
@Query("FROM User user")
Page<User> findAllWithRoles(Pageable pageable);

Of course named entity graphs work as well.

Fetch Query not working in Spring Data JPA with Pageable, Using fetch joins together with pagination in Spring Data JPA may cause this exception to be thrown: query specified join fetching, but the� But with the above mapping LIMIT is missing in the query and it takes a long time to fetch all records and then do in memory mapping to return the paginated result. It's been more than 3 weeks I am trying to find a work around but not able to figure out how.

You have to specify countQuery param for @Query and now you can use Page or List as return value.

@Query(value = "SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and ...",
       countQuery = "SELECT count(v) FROM VisitEntity v LEFT JOIN v.comments WHERE v.venue.id = :venueId and ..." )
public Page<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...,
        Pageable pageable);

[#DATAJPA-105] Paged findAll(…) with Specification does not work , with Specification does not work with join fetch The method Page<T> findAll( Specification<T>, Pageable) in the JpaSpecificationExecutor� There is nothing wrong with the query because it works for first pageSize number of records and fails when try to fetch the next set of pageSize as mentioned. The query that fetches the first set of records and the query that fetches subsequent sets are different, so it might look like there is nothing wrong with the query but this is only true

Try countProjection

@Query(value="SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and ..." ,
countProjection = "v.id")
public Page<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...,
    Pageable pageable);

Query pagination with JPA and Hibernate, Learn how to use pagination with JPA and Hibernate to restrict the Hibernate � SQL � Spring when using query pagination because SQL does not guarantee any When running the previous JPQL query on PostgreSQL, Hibernate However, if we try to use the JOIN FETCH clause in the entity query� The fetch="" attribute applies when you're directly getting/loading an object containing the colleciton. If you're using Criteria or HQL, it doesn't apply (or at least, it doesn't seem to apply.. maybe it does under certain circumstances). For HQL, use the "join fetch" syntax to force a join fetch.

Generally, paging and sorting parameters are optional and thus part of request URL as query parameters. If any API supports paging and sorting, ALWAYS provide default values to its parameters – to be used when client does not choose specify any paging or sorting preferences.

In order to avoid the n+1 problem, the Specification object I created does a fetch join between User and Merchant. The problem is that Spring Data will call the toPredicate() function twice: 1- once for the count query 2- once for the main query with limit and offset

The problem is that when I do a call to findAll(pageable) the paging information in the page result is removed. For example the page.getSize() wil always return 0. Why is the pageable not added to the super, but is there a Unpaged pageable added to it?

Comments
  • ehh great but second line , countQuery = "select count(v) from VisitEntity v where …")
  • Thanks, that's fixed! :)
  • temporary: Oliver could u plz confirm this?? stackoverflow.com/questions/21203875/…
  • If you copied actual query and posted as countQuery, do not forget to remove FETCH. there should be no FETCH in the countQuery.
  • May be useful to highlight that amount of rows of countQuery corresponds to amount of pages.
  • I read more about >> baeldung.com/spring-data-jpa-named-entity-graphs. Definitely best solution for 2.1+
  • Do you have to fetch the children (e.g. v.comments) for the countQuery? Or would the following suffice "SELECT count(v) FROM VisitEntity v WHERE v.venue.id = :venueId and ..."?
  • Right, for countQuery, no need to specify fetch.