spring data jpa @query and pageable

Solution 1:

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 2:

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