+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: FrameWork (https://0day.red/Forum-FrameWork)
+---- Forum: Spring (https://0day.red/Forum-Spring)
+---- Thread: Spring-Data FETCH JOIN with Paging is not working (/Thread-Spring-Data-FETCH-JOIN-with-Paging-is-not-working)
Spring-Data FETCH JOIN with Paging is not working - sansi498058 - 08-02-2023
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?
RE: Spring-Data FETCH JOIN with Paging is not working - suspectible951529 - 08-02-2023
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, …);
RE: Spring-Data FETCH JOIN with Paging is not working - bagwig884 - 08-02-2023
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);
RE: Spring-Data FETCH JOIN with Paging is not working - amebas546249 - 08-02-2023
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.
RE: Spring-Data FETCH JOIN with Paging is not working - plowboy640476 - 08-02-2023
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);
RE: Spring-Data FETCH JOIN with Paging is not working - Mrpetiteness909 - 08-02-2023
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.