spring data jpa @query and pageable
Solution 1
A similar question was asked on the Spring forums, where it was pointed out that to apply pagination, a second subquery must be derived. Because the subquery is referring to the same fields, you need to ensure that your query uses aliases for the entities/tables it refers to. This means that where you wrote:
select * from internal_uddi where urn like
You should instead have:
select * from internal_uddi iu where iu.urn like ...
Solution 2
You can use pagination with a native query. It is documented here: Spring Data JPA - Reference Documentation
"You can however use native queries for pagination by specifying the count query yourself: Example 59. Declare native count queries for pagination at the query method using @Query"
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
Solution 3
Considering that the UrnMapping
class is mapped to the internal_uddi
table, I would suggest this:
@Repository
public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {
@Query(value = "select iu from UrnMapping iu where iu.urn like %:text% or iu.contact like %:text%")
Page<UrnMapping> fullTextSearch(@Param("text") String text, Pageable pageable);
}
Please note that you might have to turn off native queries with dynamic requests.
Solution 4
With @Query , we can use pagination as well where you need to pass object of Pageable class at end of JPA method
For example:
Pageable pageableRequest = new PageRequest(page, size, Sort.Direction.DESC, rollNo);
Where,
page = index of page (index start from zero)
size = No. of records
Sort.Direction = Sorting as per rollNo
rollNo = Field in User class
UserRepository repo
repo.findByFirstname("John", pageableRequest);
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USER WHERE FIRSTNAME = :firstname)
Page<User> findByLastname(@Param("firstname") String firstname, Pageable pageable);
}
Solution 5
Please reference :Spring Data JPA @Query, if you are using Spring Data JPA version 2.0.4 and later. Sample like below:
@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);
Awakening
Updated on February 04, 2022Comments
-
Awakening over 2 years
I'm using Spring Data JPA, and when I use
@Query
to to define a query WITHOUTPageable
, it works:public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> { @Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%", nativeQuery = true) List<UrnMapping> fullTextSearch(String text); }
But if I add the second param
Pageable
, the@Query
will NOT work, and Spring will parse the method's name, then throw the exceptionNo property full found
. Is this a bug?public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> { @Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%", nativeQuery = true) Page<UrnMapping> fullTextSearch(String text, Pageable pageable); }
-
vtor over 9 yearsUnfortunately this does not work with native query. Do you know if it is possible to achieve with native query?
-
Steve over 9 yearsIf you want to write native queries, then you obviously need to write the query to do pagination yourself.
-
darefilz almost 4 years
countQuery
also works for non-native queries, where Spring can not derive the number of elements from the original query. -
Steve over 3 yearsScroll down to Example 64. Declare native count queries for pagination at the query method by using @Query in the Spring Data JPA docs.
-
TheRealChx101 over 2 yearsHow can I do this but instead of an entity, I have a view. I want to query the view but do not want to write the native SQL because the
LIKE
parameter comes in unescaped (with %, ~*, etc0)