Skip to main content

Dynamic Ordering

Static Ordering​

First, let's look at how to use static ordering to understand the ordering concepts in Jimmer:

public List<Book> findBooks() {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .orderBy(table.name())
        .orderBy(table.edition().desc())
        .orderBy(table.score().desc().nullsLast())
        .select(table)
        .execute();
}
caution

nullsFirst/nullsLast requires database support, such as Oracle.

For more databases that do not support this, use case expressions in common expressions.

Dynamic Ordering​

There are two ways to use dynamic ordering:

  • orderByIf
  • Ordering specified by client

orderByIf​

The usage of orderByIf is similar to whereIf.

info

  • Unlike where, the order of orderBy is very sensitive, so orderByIf is not as useful as whereIf.

    Still, Jimmer supports orderByIf since it's the simplest and most basic usage.

  • orderByIf is actually the Java DSL API for dynamic ordering. Java DSL uses fluent style, orderByIf is provided to avoid breaking the fluent chaining.

    Kotlin DSL uses lambdas so arbitrary logic can be mixed in, hence Kotlin does not need orderByIf.

Assume OrderMode is an enum with values NAME and PRICE, orderByIf can be used like:

public List<Book> findBooks(OrderMode orderMode) {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .orderByIf(mode == OrderMode.NAME, table.name()) 
        .orderByIf(mode == OrderMode.PRICE, table.price())
        .select(table)
        .execute(); 
}

Client-specified Ordering​

Often, the UI allows users to dynamically sort by interacting with table components. That is, the client decides the ordering and the server passively accepts parameters to query according to the sorting requirements from client.

The client can specify dynamic ordering by passing string parameters, which can be converted to List<Order> needed by Jimmer using the method makeOrders.

makeOrders is defined as:

public class Order {

    public static List<Order> makeOrders(Props table, String ... codes) {
        ...implementation omitted... 
    }

    ...other code omitted...
}

Where the first parameter table is the root table in the SQL DSL.

makeOrders is very flexible to use, for example:

  • Sort by multiple columns:

    Order.makeOrders(table, "name", "edition desc")

  • Merge multiple parameters into one separated by , or ;:

    Order.makeOrders(table, "name, edition desc")
    info

    In real projects, most cases would use the single parameter form since it's the simplest.

  • Even supports sorting by reference associations (one-to-one, many-to-one):

    Order.makeOrders(table, "store.city.name; store.name; name")
info

All dynamic join features are valid for these implicit joins.

Order.makeOrders can be used like:

public List<Book> findBooks(String sort) {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .orderBy(Order.makeOrders(table, sort)) 
        .select(table)
        .execute();
}

If called as findBooks("store.name asc, name asc"), the generated SQL would be:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_
/* highlight-next-line */  
inner join BOOK_STORE tb_2_
    on tb_1_.STORE_ID = tb_2_.ID
order by
    tb_2_.NAME asc,
    tb_1_.NAME desc

As can be seen, implicit joins are built for sorting criteria involving associations.

Handling Spring Data Sort​

In the above examples, we directly convert the sorting string from client into Jimmer SQL AST orders.

However, when working with Spring Data, we may need to handle Sort from Spring Data.

We can write the query like this, with Sort as parameter instead of string:

public List<Book> findBooks(Sort sort) {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .orderBy(table, SpringOrders.toOrders(sort))
        .select(table)
        .execute();
}

This code works as:

  • Java:

    Jimmer Java API provides a utility class org.babyfish.jimmer.spring.repository.SpringOrders. Its static method toOrders converts the Spring Data Sort object into the Order array in Jimmer SQL DSL.

    SpringOrders.toOrders takes two parameters:

    • table: The root table in SQL DSL

    • sort: The Spring Data Sort object

    After SpringOrders.toOrders converts the Sort into Order array, orderBy can be used to sort.

  • Kotlin:

    Jimmer Kotlin API extends the query object to directly support sorting by the Spring Data Sort.

This shows how to convert a Spring Data Sort into ordering in Jimmer.

To further simplify user code, Jimmer provides the utility class org.babyfish.jimmer.spring.model.SortUtils. Its static method toSort can convert the sorting string from client into a Spring Data Sort object. For example:

Sort sort = SortUtils.toSort("name asc, edition desc");

That is:

+------------------------+ 
| Client sorting string  |  
+-----------+------------+
            |
    SortUtils.toSort
            |
           \|/
+------------------------+
|    Spring Data Sort    |
+-----------+------------+
            |
Java: SpringOrders.toOrders, then orderBy
Kotlin: Directly orderBy with Sort object
            |
           \|/
+------------------------+
| Ordering in Jimmer AST |
+------------------------+