Dynamic Ordering

Static Ordering

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

Java

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();
}

Kotlin

fun findBooks(): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            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:

Java

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(); 
}

Kotlin

fun findBooks(orderMode: OrderMode): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            when (orderMode) {
                OrderMode.NAME -> orderBy(table.name)
                OrderMode.PRICE -> orderBy(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:

Java

public class Order {

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

    ...other code omitted...
}

Kotlin

fun KProps<*>.makeOrders(vararg codes: String): List<Order> =
    ...implementation 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:

  Java

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

  Kotlin

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

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

  Java

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

  Kotlin

  table.makeOrders("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):

  Java

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

  Kotlin

  table.makeOrders("store.city.name; store.name; name")

info

All dynamic join features are valid for these implicit joins.

Order.makeOrders can be used like:

Java

public List<Book> findBooks(String sort) {

    BookTable table = Tables.BOOK_TABLE;

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

Kotlin

fun findBooks(sort: String): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            orderBy(table.makeOrders(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:

Java

public List<Book> findBooks(Sort sort) {

    BookTable table = Tables.BOOK_TABLE;

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

Kotlin

fun findBooks(sort: Sort): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            orderBy(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:

Java

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

Kotlin

var 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 |
+------------------------+