1

I am currently using Hibernate and Spring Jpa Data (mostly for pagination) and I have been having trouble getting this query to work with pagination.

@Query("SELECT NEW com.foo.dto.FooDto (y,(SELECT CASE a.id WHEN NULL THEN 0 ELSE 1 END FROM Banco b LEFT JOIN b.blackList a WHERE b.id = ?1)) FROM Oferta y WHERE y.status = 1")
Page<FooDto> randomSearch(Long idBanco, Pageable pageable);

Error message:

Count query validation failed for method public abstract org.springframework.data.domain.Page

The exactly same query using a List<> as return works fine:

@Query("SELECT NEW com.foo.dto.FooDto(y,(SELECT CASE a.id WHEN NULL THEN 0 ELSE 1 END FROM Banco b LEFT JOIN b.blackList a WHERE b.id = ?1)) FROM Oferta y WHERE y.status = 1")
List<FooDto> randomSearch(Long idBanco, Pageable pageable);

FooDto is simply a class to handle the return.

Obviously my goal here is to paginate Oferta (y alias) resultset. Can someone please enlighten me how to make this work?

The only other way I can think of is splitting the query into 2 different ones and handling the logic on Java itself, but I think it is way more elegant doing it on the database.

Thanks.

dambros
  • 4,252
  • 1
  • 23
  • 39
  • [This question](http://stackoverflow.com/questions/21549480/spring-data-fetch-join-with-paging-is-not-working) seems to be about a similar issue using pagination with join, though with a different error message. Perhaps the answer to that question can point you in the right direction. – Raniz May 22 '15 at 00:54
  • 1
    @Raniz you were spot on! Providing a custom countQuery with the same WHERE clause did the trick. Thank you very much. – dambros May 22 '15 at 01:14
  • I'm flagging this as a duplicate then so that it's easier for other people to find the answer if they stumble upon your question – Raniz May 22 '15 at 01:20

0 Answers0