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<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 exception No 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);
}

score 96 · Answer 1 · edited Feb 03 '22 at 15:20

96

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

edited Feb 03 '22 at 15:20

Laurel

5,965
14
31
57

answered Feb 08 '17 at 14:35

RemusS

1,395
1
11
9

1

`countQuery` also works for **non-native** queries, where Spring can not derive the number of elements from the original query. – darefilz Jul 23 '20 at 13:39
Scroll down to [Example 64. Declare native count queries for pagination at the query method by using @Query](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query) in the Spring Data JPA docs. – Steve Oct 21 '20 at 17:46
your solution works but i got illegalargumentexception even if the result is correct. Please can you check my question about that https://stackoverflow.com/questions/74494684/how-to-fetch-data-as-pageable-with-custom-query-in-jpa-without-illegalargumentex – abidinberkay Nov 18 '22 at 19:54
If performance is critical, `countQuery` is not for you. It triggers an additional count query to achieve it. To avoid such an overhead cost, you can instead return a `Slice` or a `List`. – jumping_monkey Mar 23 '23 at 22:52

score 45 · Accepted Answer · answered Jul 30 '14 at 10:58

45

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 ...

answered Jul 30 '14 at 10:58

Steve

9,270
5
47
61

Unfortunately this does not work with native query. Do you know if it is possible to achieve with native query? – vtor Feb 03 '15 at 17:53
18

If you want to write native queries, then you obviously need to write the query to do pagination yourself. – Steve Feb 04 '15 at 09:06

Dimitri Hautot · Answer 3 · 2014-12-12T15:52:46.697

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.

score 14 · Answer 4 · answered Nov 10 '19 at 11:34

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

score 6 · Answer 5 · answered May 16 '19 at 06:05

6

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

answered May 16 '19 at 06:05

shenyu1997

602
7
5

score 2 · Answer 6 · answered Mar 12 '14 at 08:15

2

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

answered Mar 12 '14 at 08:15

Martin Baumgartner

3,524
3
20
31

score 2 · Answer 7 · answered Nov 21 '19 at 07:14

Declare native count queries for pagination at the query method by 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);

}

Hope this helps

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods

Kai · Answer 8 · 2019-08-15T02:08:31.170

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 >= ?1 ) " +
        "and (?2 is null or data_hour <= ?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 >= ?1 ) " +
                "and (?2 is null or data_hour <= ?2 ) " +
                "and (?3 is null or ?3 = '' or park_no = ?3 ) " +
                "group by park_name, data_hour",
        nativeQuery = true
)
Page<IntegrityScoreView> queryParkView(Date from, Date to, String parkNo, Pageable pageable);

you DO NOT write order by and limit, it generates the right sql

catch23 · Answer 9 · 2020-05-28T16:23:58.167

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<Employee, Integer> {

    Page<Employee> 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;
}

How 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) — TheRealChx101, Sep 13 '21 at 20:00

score 1 · Answer 10 · answered Jun 04 '21 at 17:54

1

When using nativeQuery that is having (nativeQuery = true), you may do the pagination yourself in the query by adding (LIMIT :sizeValue OFFSET :page)

Note: Your page value passed to this method should be offset * size

Example

 @Query(value = "SELECT * FROM person " +    
                   "LIMIT ?1 OFFSET ?2", nativeQuery = true)
    Optional<List<TDriverJob>> findPersons(int size, int page);

answered Jun 04 '21 at 17:54

Christian Keven

31
5

Avoid using `Optional>`, just use `List`. When it comes to lists (and collections in general), there is already a clear way to represent "no result", and that is an empty collection. – jumping_monkey Mar 23 '23 at 23:06

score 1 · Answer 11 · answered Mar 03 '23 at 06:20

For me return type of Page with Pageable was not working, but on changing the return type to List or Optional<?> was working fine.

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); // this doesnt work, for this you need to use countQuery

 List<UrnMapping> fullTextSearch(String text, Pageable pageable); // this is working
}

score 0 · Answer 12 · answered Apr 14 '20 at 05:54

0

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

answered Apr 14 '20 at 05:54

Peji Da

1

score 0 · Answer 13 · edited Apr 21 '21 at 06:04

the following tutorial helped me -> https://www.baeldung.com/spring-data-jpa-query

At this point 4.3. Spring Data JPA Versions Prior to 2.0.4

VERY IMPORTANT to add \ n-- #pageable \ n Without this I was wrong

Also the pagination setting must be without ordering

PageRequest paginaConf = new PageRequest ((param1 - 1)
                                                 , param2);

Finally to convert the Page <Object []>

  Page <Object []> list = myQueryofRepo ();
         List <XXXModel> lstReturn = myConversor (list.getContent ());
         Page <XXXModel> ret = new PageImpl <XXXModel> (lstReturn, pageConf, param2);

score 0 · Answer 14 · answered Dec 29 '22 at 10:24

This bugged me for a while but I managed with a very smooth solution.

The challenge is JPA did not automatically detect the count query so I resolved to use the countName which according JPA docs Returns the name of the javax.persistence.NamedQuery to be used to execute count queries when pagination is used. Will default to the named query name configured suffixed by .count.

So I created a named query

@NamedNativeQuery(
            name = "[queryName].count",
            query = [your count query],
            resultSetMapping = "[query resultSetMapping name]"
        )
    }

As indicated, the count query should be suffixed with .count

Count query returns Long so add the resultSetMapping as

@SqlResultSetMapping(
    name="[query resultSetMapping name]",
    columns={@ColumnResult(name="count", type = Long.class)})

Then in your repository, use the count query as indicated below

@Query(countName ="[queryName].count" , nativeQuery = true)
Page<Object> [mainQuery](...params)

Hope this helps!

spring data jpa @query and pageable

14 Answers14

Linked

Related