spring data jpa query and pageable

Question

I m using Spring Data JPA  and when I use  Query to to define a query WITHOUT Pageable  it works   public interface UrnMappingRepository extends JpaRepository lt UrnMapping  Long gt         Query value    select   from internal uddi where urn like   1  or contact like   1                nativeQuery   true      List lt UrnMapping gt  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 exception No property full found  Is this a bug   public interface UrnMappingRepository extends JpaRepository lt UrnMapping  Long gt         Query value    select   from internal uddi where urn like   1  or contact like   1               nativeQuery   true      Page lt UrnMapping gt  fullTextSearch String text  Pageable pageable

User · Answer

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 lt User  Long gt        Query value    SELECT   FROM USER WHERE FIRSTNAME    firstname    Page lt User gt  findByLastname  Param  firstname   String firstname  Pageable pageable

User · Answer

Considering that the UrnMapping class is mapped to the internal uddi table  I would suggest this    Repository public interface UrnMappingRepository extends JpaRepository lt UrnMapping  Long gt          Query value    select iu from UrnMapping iu where iu urn like   text  or iu contact like   text        Page lt UrnMapping gt  fullTextSearch  Param  text   String text  Pageable pageable       Please note that you might have to turn off native queries with dynamic requests

User · Answer

I found it works different among different jpa versions  for debug  you d better add this configurations to show generated sql  it will save your time a lot    spring jpa show-sql true spring jpa properties hibernate format sql true   for spring boot 2 1 6 RELEASE  it works good   Sort sort   new Sort Sort Direction DESC   column name    int pageNumber   3  pageSize   5  Pageable pageable   PageRequest of pageNumber - 1  pageSize  sort      Query value    select   from integrity score view              where   1 is null or data hour  gt    1                and   2 is null or data hour  lt    2                and   3 is null or  3      or park no    3                group by park name  data hour            countQuery    select count    from integrity score view                      where   1 is null or data hour  gt    1                        and   2 is null or data hour  lt    2                        and   3 is null or  3      or park no    3                        group by park name  data hour           nativeQuery   true   Page lt IntegrityScoreView gt  queryParkView Date from  Date to  String parkNo  Pageable pageable     you DO NOT write order by and limit  it generates the right sql

User · Answer

Rewrite your query to    select iu from internal uddi iu where iu urn        description  http   forum spring io forum spring-projects data 126415-is-it-possible-to-use-query-and-pageable p 611398 post611398

User · Answer

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 lt User gt  findAllUsersWithPagination Pageable pageable

User · Answer

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

User · Answer

Declare native count queries for pagination at the query method by using  Query  public interface UserRepository extends JpaRepository lt User  Long gt        Query value    SELECT   FROM USERS WHERE LASTNAME    1     countQuery    SELECT count    FROM USERS WHERE LASTNAME    1     nativeQuery   true    Page lt User gt  findByLastname String lastname  Pageable pageable        Hope this helps  https   docs spring io spring-data jpa docs current reference html  jpa query-methods

User · Answer

I tried all above solution and non worked   finally I removed the Sorting from Pagination and it worked

User · Answer

You can use pagination with a native query  It is documented here  https   docs spring io spring-data jpa docs current reference html   native queries   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 lt User  Long gt        Query value    SELECT   FROM USERS WHERE LASTNAME    1       countQuery    SELECT count    FROM USERS WHERE LASTNAME    1       nativeQuery   true    Page lt User gt  findByLastname String lastname  Pageable pageable

User · Answer

I had the same issue - without Pageable method works fine  When added as method parameter - doesn t work   After playing with DB console and native query support came up to decision that method works like it should  However  only for upper case letters  Logic of my application was that all names of entity starts from upper case letters   Playing a little bit with it  And discover that IgnoreCase at method name do the  magic  and here is working solution   public interface EmployeeRepository                              extends PagingAndSortingRepository lt Employee  Integer gt         Page lt Employee gt  findAllByNameIgnoreCaseStartsWith String name  Pageable pageable        Where entity looks like    Data  Entity  Table name    tblEmployees   public class Employee         Id      Column name    empID        GeneratedValue strategy   GenerationType IDENTITY      private Integer id        NotEmpty      Size min   2  max   20       Column name    empName   length   25      private String name        Column name    empActive       private Boolean active        ManyToOne      JoinColumn name    emp dpID       private Department department

[java] spring data jpa @query and pageable

Examples related to java

Examples related to spring

Examples related to hibernate

Examples related to jpa

Examples related to spring-data-jpa