Spring Data Hibernate native query with Pageable

Spring Data Hibernate native query with Pageable

According to Example 50 at Using @Query from spring-data documentation this is possible specifying the query itself and a countQuery, like this:

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:
 @Query(value="SELECT a.* "
            + "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
            + "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time)"
            + "ORDER BY a.id \n#pageable\n", 
        /*countQuery="SELECT count(a.*) "
            + "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
            + "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time) \n#pageable\n",*/
        nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable pageable);


https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination


SAMPLE:
@Query(value = "SELECT distinct p.id, p.name, " +
                "(SELECT count(*) from children c2 WHERE c2.status in ('NEW') and c2.parent_id = p.id) as n,\n" +
                "(SELECT count(*) from children c3 WHERE c3.status in ('UPDATED') and c3.parent_id = p.id) as u,\n" +
                "(SELECT count(*) from children c4 WHERE c4.status in ('DELETED') and c4.parent_id = p.id) as d \n" 
                "FROM parent p WHERE p.id IN (:ids) ORDER BY p.id \n-- #pageable\n",
            countQuery = "SELECT count(*) FROM parent p WHERE p.id IN (:ids)",
            nativeQuery = true)
Page<Object[]> findParentChildrenStat(@Param("ids") List<Long> parentIds, Pageable pageable);