Spring Data pagination

Question

Just face strange behavior of Spring Data JPA 2.2.0.

Product and Category are two very simple entities with one-to-many relations. Notice that in my case some products could have no category.

I make this query

@Query("select p, c" +
        "from Product p " +
        "left join fetch Category c on p.category.id = c.id " +
        "where (:categoryId = -1L or c.id = :categoryId) and " +
        "(:priceFrom is null or p.price >= :priceFrom) and " +
        "(:priceTo is null or p.price <= :priceTo)")
Page<Product> filterProducts(@Param("categoryId") Long categoryId,
                             @Param("priceFrom") BigDecimal priceFrom,
                             @Param("priceTo") BigDecimal priceTo,
                             Pageable pageable);

But method call returns Page<Object[]> instead of Page<Product>. If I change Page to List in return type all going to be fine. Why it works this way? Is it possible to change this behavior?

I use select p, c to fill resulting products with all data from product and category by one query. Without c Hibernate doing some additional queries to get Categories.

can you check if this also happens if you use an existing Spring Data repo method, e.g. `Page findAll();`? — Tommy Brettschneider, Nov 07 '19 at 20:35
possibly a duplicate... plz have a look: https://stackoverflow.com/questions/6877857/jpa-query-that-returns-multiple-entities — Tommy Brettschneider, Nov 07 '19 at 20:47
As far as I know to use Lazy load of a field it has to have `optional=false` parameter. But `Category` is optional. You can try to add `EmptyCategory` value instead of null. And then try to use `join fetch` instead of `left join fetch` — Oleksii Valuiskyi, Nov 08 '19 at 07:53

score 0 · Answer 1 · answered Nov 07 '19 at 20:20

does your repository interface extend JpaRepository or PagingAndSortingRepository, e.g. like this (where Long is the type of your entities @Id field:

@Repository
interface ProductRepository extends JpaRepository<Product, Long> {

    // your custom repo methods...
}

Oleksii Valuiskyi · Answer 2 · 2019-11-08T07:47:45.037

0

Try this

@Query("from Product p left join fetch p.category c " +
       "where (:categoryId = -1L or c.id = :categoryId) and " +
       "(:priceFrom is null or p.price >= :priceFrom) and " +
       "(:priceTo is null or p.price <= :priceTo)")
Page<Product> filterProducts(@Param("categoryId") Long categoryId,
                                     @Param("priceFrom") BigDecimal priceFrom,
                                     @Param("priceTo") BigDecimal priceTo,
                                     Pageable pageable);

edited Nov 08 '19 at 07:47

answered Nov 08 '19 at 07:09

Oleksii Valuiskyi

2,691
1
8
22

This query fetches every Category by separate select. I'm looking for a way how to force Hibernate to do that by one query with a join. – Alexey Usharovski Nov 08 '19 at 09:23

score 0 · Answer 3 · answered Nov 09 '19 at 09:03

In order to calculate the proper page info, Spring JPA also need the maxinum number of rows. (for calculating the page numbers). In order to get this you need to provide a countQuery in addition to the original query definition:

@Query(value = "select p, c" +
        "from Product p " +
        "left join fetch Category c on p.category.id = c.id " +
        "where (:categoryId = -1L or c.id = :categoryId) and " +
        "(:priceFrom is null or p.price >= :priceFrom) and " +
        "(:priceTo is null or p.price <= :priceTo)",
       countQuery = "select count(p.id)" +
        "from Product p " +
        "left join fetch Category c on p.category.id = c.id " +
        "where (:categoryId = -1L or c.id = :categoryId) and " +
        "(:priceFrom is null or p.price >= :priceFrom) and " +
        "(:priceTo is null or p.price <= :priceTo)",

) Page filterProducts(@Param("categoryId") Long categoryId, @Param("priceFrom") BigDecimal priceFrom, @Param("priceTo") BigDecimal priceTo, Pageable pageable);

Spring Data pagination

3 Answers3