Spring Data and Native Query with pagination

by

My apologies in advance, this is pretty much summing up the original question and the comment from Janar, however…

I run into the same problem: I found the Example 50 of Spring Data as the solution for my need of having a native query with pagination but Spring was complaining on startup that I could not use pagination with native queries.

I just wanted to report that I managed to run successfully the native query I needed, using pagination, with the following code:

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

The countQuery (that is commented out in the code block) is needed to use Page<Author>
as the return type of the query, the newlines around the “#pageable” comment are needed to avoid the runtime error on the number of expected parameters (workaround of the workaround). I hope this bug will be fixed soon…

More Related Contents:

  1. Disable all Database related auto configuration in Spring Boot
  2. Spring data JPA query with parameter properties
  3. How to test Spring’s declarative caching support on Spring Data repositories?
  4. Spring Data optional parameter in query method
  5. Spring Data: “delete by” is supported?
  6. Can Spring Data REST’s QueryDSL integration be used to perform more complex queries?
  7. How to use OrderBy with findAll in Spring Data
  8. Can’t Autowire @Repository annotated interface in Spring Boot
  9. Spring Boot Data JPA – Modifying update query – Refresh persistence context
  10. Spring Boot – Loading Initial Data
  11. Using generics in Spring Data JPA repositories
  12. What is difference between CrudRepository and JpaRepository interfaces in Spring Data JPA?
  13. Joining two table entities in Spring Data JPA
  14. Filtering database rows with spring-data-jpa and spring-mvc
  15. Spring boot – Not a managed type
  16. Spring CrudRepository findByInventoryIds(List inventoryIdList) – equivalent to IN clause
  17. Spring Data JPA – Multiple EnableJpaRepositories
  18. How to correctly use PagedResourcesAssembler from Spring Data?
  19. Can I make a custom controller mirror the formatting of Spring-Data-Rest / Spring-Hateoas generated classes?
  20. Spring Data JPA: query ManyToMany
  21. Spring Data JPA: Query by Example?
  22. Set default page size for JPA Pageable Object
  23. Spring Data – Multi-column searches
  24. How to use Spring AbstractRoutingDataSource with dynamic datasources?
  25. Configure Multiple MongoDB repositories with Spring Data Mongo
  26. Missing CrudRepository#findOne method
  27. Pagination in Spring Data JPA (limit and offset)
  28. Best way of handling entities inheritance in Spring Data JPA
  29. Spring Data JPA – Is it possible to sort on a calculated property?
  30. Why is an excerpt projection not applied automatically for a Spring Data REST item resource?

Leave a Comment