Spring Data - Why it's not possible to have paging with native query

Question

Let's say we have an entity called MyEntity. It is possible to query pageable results using @Query and with named queries, e.g.

 @Query(value = "select e from MyEntity e where e.enabled = true")
 Page<MyEntity> findAllEnabled(Pageable pageable);

However, it is not possible to achieve the same with native query, so this

 @Query(value = "select * from my_entity where enabled = true", nativeQuery = true)
 Page<MyEntity> findAllEnabled(Pageable pageable);

won't work.

What are the reasons behind this? Is it possible to make Pageable working with native queries?

Answer 1

I don't know if this is still relevant to you: At least in Spring Data JPA 1.9.4 you can specify two queries.

Given a repository:

interface FoobarEntityRepository extends JpaRepository<FoobarEntity, Integer> {
    Page findFoobarsSpecialQuery(String someParameter, final Pageable pageable);
}

You can add 2 native queries to your entity, one for the query itself and one for the count statement:

@Entity
@SqlResultSetMappings({
    @SqlResultSetMapping(name = "SqlResultSetMapping.count", columns = @ColumnResult(name = "cnt"))
})
@NamedNativeQueries({
    @NamedNativeQuery(
            name = "FoobarEntity.findFoobarsSpecialQuery",
            resultClass = DailyPictureEntity.class,
            query = "Select * from foobars f where someValue = :someParameter "
    ),
    @NamedNativeQuery(
            name = "FoobarEntity.findFoobarsSpecialQuery.count",
            resultSetMapping = "SqlResultSetMapping.count",
            query = "Select count(*) as cnt from foobars f where someValue = :someParameter "
    )
})
FoobarEntity {
}

The trick is to specify the count query with the suffix .count. This works also with the Spring Data @Query annotation.

Notice that you need a SQL result set mapping for the count query, though.

This works actually pretty nice.

Answer 2

This is description, given in spring data jpa documentation (http://docs.spring.io/spring-data/jpa/docs/1.8.0.M1/reference/html/)

Native queriesThe @Query annotation allows to execute native queries by setting the nativeQuery flag to true. Note, that we currently don’t support execution of pagination or dynamic sorting for native queries as we’d have to manipulate the actual query declared and we cannot do this reliably for native SQL.

JPQL abstracts SQL implementation and it's providers specifics, and makes it responsibility of ORM framework to generate correct SQL.

1. So by using Pagination in JPQL form, Spring just needs to generate correct JPQL, and it will be interpreted on ORM level to correct SQL.

2. While doing so with SQL, would imply that Spring knows how to generated correct SQL for the vast majorities of RDBMS, duplicating ORM functionality, which is too much overhead.

Answer 3

There is a way to use Pageable with native queries with the SpEL capacity of Spring data, it is mention here.

You can find an example in this repository.

/**
     * @see DATAJPA-564
     */
    @Query(
            value = "select * from (select rownum() as RN, u.* from SD_User u) where RN between ?#{ #pageable.offset -1} and ?#{#pageable.offset + #pageable.pageSize}",
            countQuery = "select count(u.id) from SD_User u", nativeQuery = true)
    Page<User> findUsersInNativeQueryWithPagination(Pageable pageable);

The sort functionnality will not work properly if there is a subquery in the fromclause or your native query and you wish to apply a dynamic sort on it. The way it can be done is to move the subquery in the where clause.
Spring data will append to the end of your request " order by " if there is a Sort object in the Pageable. (with Spring data 1.10.3)

A better way is to convert the native query in jpql if possible.