How do you specify multi-column OrderSpecifier for use in SpringData and QueryDsl? Is this possible

Question

So I have the following query below:

public Iterable<Dealer> findAll(Dealer dealer) {
    QDealer qdealer = QDealer.dealer;

    BooleanExpression where = null;
    if(dealer.getId() != null && dealer.getId() != 0) {
        buildPredicate(qdealer.id.goe(dealer.getId()));
    }

    OrderSpecifier<String> sortOrder = QDealer.dealer.dealerCode.desc();
    Iterable<Dealer> results = dlrRpstry.findAll(where, sortOrder); 
    return results;
}

The query above works fine. However, I would like to sort the results by dealerType first, then by dealerCode somewhat like "order by dealerType asc, dealerCode desc". How do I instantiate the OrderSpecifier so that the results will be sorted by dealerType then by dealer code.

The DealerRepository dlrRpstry extends JpaRepository, QueryDslPredicateExecutor

I am using spring-data-jpa-1.1.0, spring-data-commons-dist-1.3.2 and querydsl-jpa-2.9.0.

If OrderSpecifier can not be configured to a multi-column sort order what would be the alternative solution that will satisfy my requirement of sorting the results "by dealerType asc, dealerCode desc".

Any help would be greatly appreciated. Thanks in advance.

Nick

score 19 · Accepted Answer · edited May 23 '17 at 12:30

19

I do not have JPA install setup, but I believe reading the QueryDslJpaRepository documentation, you can simply do this:

OrderSpecifier<String> sortOrder1 = QDealer.dealer.dealerType.asc();
OrderSpecifier<String> sortOrder2 = QDealer.dealer.dealerCode.desc();
Iterable<Dealer> results = dlrRpstry.findAll(where, sortOrder1, sortOrder2);

Let us know if it works. Here is a link to a stackoverflow question/answer that explains the ... parameter syntax on the findAll() method:

https://stackoverflow.com/a/12994104/2879838

This means you can have as many OrderSpecifiers as you want as optional parameters to the function.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 20 '13 at 19:35

David Fleeman

2,588
14
17

1

Just tested the offered solution above. It works! Thanks David for your quick response. We appreciate it. – Dominique Morales Nov 20 '13 at 22:17
1

So that's what the ... means on the param list. That's another point I just learned from your answer. For the benefit those who might stumble on this post, here's the method definition of findAll: Iterable findAll(Predicate predicate, OrderSpecifier>... orders). Many thanks again David – Dominique Morales Nov 20 '13 at 22:40

score 4 · Answer 2 · answered Aug 27 '21 at 12:35

If you don't want to create variable for each specifier, it can be done this way:

OrderSpecifier<?>[] sortOrder = new OrderSpecifier[] {
    QDealer.dealer.dealerType.asc(),
    QDealer.dealer.dealerCode.desc()
};
Iterable<Dealer> results = dlrRpstry.findAll(where, sortOrder);

How do you specify multi-column OrderSpecifier for use in SpringData and QueryDsl? Is this possible

2 Answers2

Linked