CODEWITHSUNDEEP
javaspringjpqlspring-data-jpa
Thomas Junk
Thomas Junk

Reputation: 5676

Spring-Data-JPA paging

I am using the Datatables jQuery plugin to display a List of orderpositions. While debugging my application I stumbled upon the following inconsistency.

To retrieve the data, I used the @Query-Notation for my custom query:

@Query("select op from OrderPosition  " +
        "op where op.order.created >= ?1" +
        " and op.depot in ?2" +
        " and op.order.deliveryAddress.deliveryMode in ?3" +
        " and op.pickStatus in ?4 and op.order.id like ?5 ")
Page<OrderPosition> findOrderpositionsByFilterStatus(Date date, List<Integer>depots, List<DeliveryMode> deliveryModes, List<ArticleStatusType> pickStatus, String sSearch, Pageable p);

The error occured is the following:

I have a set of 81 Orderpositions test data. In the frontend I am able to filter by several criteria. One of the criteria is the deliveryMode (express|standard). I display 10 Entries at a time. The total number of expressdeliveries is 6. When paging through the Pages only 2 express are displayed. When filtering explicitely on express I get all 6.

When I add some sort of ordering e.g:

@Query("select op from OrderPosition  " +
        "op where op.order.created >= ?1" +
        " and op.depot in ?2" +
        " and op.order.deliveryAddress.deliveryMode in ?3" +
        " and op.pickStatus in ?4 and op.order.id like ?5 " +
        "order by op.order.id desc")
Page<OrderPosition> findOrderpositionsByFilterStatus(Date date, List<Integer>depots, List<DeliveryMode> deliveryModes, List<ArticleStatusType> pickStatus, String sSearch, Pageable p);

I get all 6 of 'em.

What brings me to the question:

When using non annotated queries - either the generic findAll() or queries from method names- does Spring-Data-JPA use an order by-clause internally, when detecting a pageable? Or inverse: Do I have to add an order by-clause in each of my custom queries, when using a pageable?

Upvotes: 5

Views: 1473

Answers (1)

Atmask
Atmask

Reputation: 78

Yes inded. Spring Data uses an OrderBy (with a default desc) if you do not specify further. Look at the Logs:

SELECT t0.oid, t0.jpaversion, t0.amount, [...]
FROM [...]
WHERE [..] AND (t13.jpatype IS NULL OR t13.jpatype IN (?))
ORDER BY t0.oid DESC LIMIT ?, ?

Upvotes: 2

Related Questions