Spring-Data FETCH JOIN with Paging is not working

Question

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?

Oliver Drotbohm · Accepted Answer · 2014-06-30T11:47:03.133

69

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, …);

edited Jun 30 '14 at 11:47

answered Feb 05 '14 at 08:17

Oliver Drotbohm

80,157
18
225
211

3

ehh great but second line , `countQuery = "select count(v) from VisitEntity v where …")` – azerafati Jun 30 '14 at 10:46
1

Thanks, that's fixed! :) – Oliver Drotbohm Jun 30 '14 at 11:47
temporary: Oliver could u plz confirm this?? http://stackoverflow.com/questions/21203875/difference-between-save-and-saveandflush-in-spring-data-jpa/21204100?noredirect=1#comment38102356_21204100 – azerafati Jul 06 '14 at 07:42
4

If you copied actual query and posted as `countQuery`, do not forget to remove `FETCH`. there should be no `FETCH` in the `countQuery`. – sedooe Jan 03 '18 at 15:51
May be useful to highlight that amount of rows of countQuery corresponds to amount of pages. – Yurii Bratchuk Mar 11 '19 at 15:14
3

But that leads to warning: `HHH000104: firstResult/maxResults specified with collection fetch; applying in memory` when using pagination.. – Ruslan Jan 28 '20 at 08:07
@Ruslan did you find any solution for this problem wiht spring data because hibernate with fetch join make pagination in memory not in database . – hicham abdedaime Jan 21 '21 at 23:22
@hichamabdedaime there is one of the best explanation for such problem https://stackoverflow.com/a/46195656/5108737 – Ruslan Jan 22 '21 at 09:17
@Ruslan thanks for your comment , I already saw this reponse but my problem related to spring data with specificatoin to make dynamic search . – hicham abdedaime Jan 22 '21 at 11:08
@hichamabdedaime looks like there is no solution in spring data. Take a look to my related question https://stackoverflow.com/questions/59935631/avoiding-hhh000104-firstresult-maxresults-specified-with-collection-fetch-app – Ruslan Jan 22 '21 at 16:43
Does exist any way to do the countQuery be mans of JpaSpecificationExecutor without Pagable? – J. Abel Mar 16 '22 at 04:17

Piotr Tempes · Answer 2 · 2018-03-05T13:35:30.733

14

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.

edited Mar 05 '18 at 13:35

answered Feb 16 '18 at 14:55

Piotr Tempes

1,091
1
12
26

I read more about >> https://www.baeldung.com/spring-data-jpa-named-entity-graphs. Definitely best solution for 2.1+ – davidwillianx Dec 04 '19 at 18:41

Max · Answer 3 · 2018-02-23T06:59:31.880

6

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);

edited Feb 23 '18 at 06:59

answered Nov 22 '17 at 07:07

Max

766
9
19

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 ..."`? – Francisco C. Feb 01 '18 at 04:22
1

Right, for countQuery, no need to specify fetch. – Max Feb 23 '18 at 06:57

score 4 · Answer 4 · answered Nov 13 '20 at 15:09

If you want completely control your query build by Specification with join fetch you can check CriteriaQuery return type and change join fetch logic according to query type like this:

public class ContactSpecification implements Specification<Contact> {
    @Override
    public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        if(query.getResultType() == Long.class) {
            root.join(Contact_.company);
        } else {
            root.fetch(Contact_.company);
        }
        return cb.equal(root.get(Contact_.company).get(Company_.name), "Company 123");
    }
}

I was not able to find this info in documentation, but from SimpleJpaRepository.getCountQuery() method you can see query for count request first build for Long return type, and later fetch for expected class is running.

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> query = builder.createQuery(Long.class);

Root<S> root = applySpecificationToCriteria(spec, domainClass, query);

It can be not reliable since it is an implementation details which can be changed, but it works.

score 0 · Answer 5 · answered Nov 06 '19 at 02:50

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);

Spring-Data FETCH JOIN with Paging is not working

5 Answers5

Linked

Related