Usage

Features of Jimmer Pagination

Pagination query is a very characteristic function of Jimmer, which can greatly improve development efficiency.

Pagination requires executing two SQL queries:

Query the total number of rows that meet the criteria, the result of which can calculate how many pages there are in total, and whether the user's page number is out of bounds.

info
For discussion purposes, Jimmer refers to this SQL as count-query.
Query all data within the current page, the number of returned data rows does not exceed the page size, and skip all data from previous pages.

info
For discussion purposes, Jimmer refers to this SQL as data-query.

tip

Jimmer's feature: The developer only needs to write the data-query (actual data-query without limit and offset), and the framework automatically generates the count-query.

Not only can Jimmer automatically generate count-query, it can also optimize count-query. This optimization will be discussed in next article.

When Used with Spring Data

When used with Spring Data, developers derive custom Repository interfaces from JRepository/KRepository, and there are two options for adding query methods to the custom interfaces:

Declare abstract methods according to certain conventions, and let Jimmer automatically implement them.

caution
This usage is too simple, hiding all details, and is not suitable for discussing pagination here.
You can check Spring Section/Spring Data Style/Abstract Methods to learn how to implement pagination queries in this way.
Directly define default methods in the custom interface and implement the query logic yourself.

Java
Kotlin

BookRepository.java

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
...other imports omitted...  

public interface BookRepository<Book, Long> extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default Page<Book> findBooks(
        Pageable pageable,
        @Nullable String name,
        @Nullable String storeName,
    ) {
        return sql()
                .createQuery(table)
                .whereIf(
                    name != null && !name.isEmpty(),
                    table.name().eq(name)
                )
                .whereIf(
                    storeName != null && !storeName.isEmpty(),
                    table.store().name().eq(storeName)
                )
                .orderBy(SpringOrders.toOrders(table, pageable.getSort())) ❶
                .select(table)
                .fetchPage( ❷
                    pageable.getPageNumber(), 
                    pageable.getPageSize()
                    SpringPageFactory.getInstance()
                );
    }
}

BookRepository.kt
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable  
...other imports omitted...

interface BookRepository<Book, Long> : KRepository<Book, Long> {

    fun findBooks(
        pageable: Pageable,
        name: String? = null,
        storeName: String? = null
    ): Page<Book> =
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name eq it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    where(table.store.name eq it)
                }
                orderBy(pageable.sort) ❶
                select(table)
            }
            .fetchSpringPage(pageable) ❷
}

❶ Since Spring Data's Pageable contains dynamic sorting, dynamic sorting needs to be applied.
❷ Pagination query, returns org.springframework.data.domain.Page<Book> type of object

Jimmer pagination can use any Page object, whether it's Spring Data's Page, Jimmer's own Page, or even Page defined by third parties.

Here, the Java code uses SpringPageFactory.getInstance() to request the current pagination operation to return Spring Data's Page.

In fact, the Kotlin code can also use SpringPageFactory.getInstance() for the same purpose, but in Kotlin there is a more convenient extension method fetchSpringPage().

If we execute:

Page<Book> page = bookRepository.findBooks(
    PageRequest.of(
        1,
        5,
        SortUtils.toSort("name asc, edition desc") 
    ),
    null,
    null
)

caution

In Spring Data, the page number of Pageable starts from 0 instead of 1, so this queries the second page.

It will generate two SQL statements:

count-query

select
    count(tb_1_.ID) 
from BOOK tb_1_

data-query (assuming the database is H2)

select
    tb_1_.ID,
    tb_1_.CREATED_TIME, 
    tb_1_.MODIFIED_TIME,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_ 
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc
limit ? /* 5 */, ? /* 5 */ 

This example allows us to understand Jimmer's pagination functionality, but Jimmer's Spring API hides some details. Therefore, next we bypass Spring Data and explain more clearly from a lower level perspective.

When Not Using Spring Data

Jimmer's Page object

Since Spring Data is not used, naturally org.springframework.data.domain.Page<T> cannot be used.

For this, Jimmer defines org.babyfish.jimmer.Page<T> with the following definition:

Page.java
package org.babyfish.jimmer;

public class Page<T> {

    private final List<T> rows;
    
    private final int totalRowCount;
    
    private final int totalPageCount;
    
    ...Omit other fields...
    
}

It can be seen that Jimmer's own Page<T> is much simpler than Spring Data's Page<T>, the differences are:

info

org.springframework.data.domain.Page<T> is designed for server-side paging so that the page must still maintain its previous state after being refreshed. A lot of information (such as tedious sort information) needs to be returned verbatim to the client, so it is very complex.
org.babyfish.jimmer.Page<T> is designed for rich client pages. Such client pages are stateful applications themselves, the server only needs to provide pure data services, so returning just the bare necessities is enough, hence very simple.

Implement Business Logic

Java
Kotlin

public Page<Book> findBooks(
    int pageIndex,
    int pageSize,
    @Nullable String name,
    @Nullable String storeName  
) {
  return
    sqlClient
        .createQuery(table)
        .whereIf(
            name != null && !name.isEmpty(),
            table.name().eq(name)  
        )
        .whereIf(
            storeName != null && !storeName.isEmpty(),  
            table.store().name().eq(storeName)
        )
        .orderBy(table.name().asc(), table.edition().desc())
        .select(table)
        .fetchPage(pageIndex, pageSize);
}

fun findBooks(
    pageIndex: Int,
    pageSize: Int,
    name: String? = null,
    storeName: String? = null  
): Page<Book> =
  sql
    .createQuery(Book::class) {
        name?.takeIf { it.isNotEmpty() }?.let {
            where(table.name eq it)
        }
        storeName?.takeIf { it.isNotEmpty() }?.let {
            where(table.store.name eq it)  
        }
        orderBy(table.name.asc(), table.edition.desc())
        select(table)
    }
    .fetchPage(pageIndex, pageSize)

The finally generated SQL is the same as the example discussed earlier with Spring Data, so I won't repeat it here.

Internal Mechanism

In the above examples, we discussed language differences between Java and Kotlin, as well as using or not using Spring Data.

The underlying logic of these behaviors is the same, taking Java as an example:

BookTable table = Tables.BOOK_TABLE;

ConfigurableRootQuery<Book> query = ❶
  sqlClient
    .createQuery(table)
    .whereIf(
        name != null && !name.isEmpty(),
        table.name().eq(name)
    )  
    .whereIf(
        storeName != null && !storeName.isEmpty(),
        table.store().name().eq(storeName)
    )
    .orderBy(table.name().asc(), table.edition().desc())

int totalCount = query.fetchUnlimitedCount(); ❷
int totalPage = (totalCount + pageSize - 1) / pageSize;
if (pageIndex >= totalPage) {
    return new Page<Book>(totalCount, totalPage, Collections.emptyList()); 
}
List<Book> entities = query
    .limit(pageSize, pageIndex * pageSize) ❸ 
    .execute(); ❹
return new Page<>( ❺
    totalCount,
    totalPage,
    entities
)

caution

To simplify the discussion, this pseudocode does not consider reverse sorting optimization.

❶ Create the query, but do not execute it yet. I can call it the template query.
❷ Based on the original template query without modification, generate the count-query, then execute the count-query to get the total number of rows before pagination.

Here the fetchUnlimitedCount method is a shortcut API, and its underlying logic is:
```
public interface ConfigurableRootQuery<T extends Table<?>, R> extends ... {

    default int fetchUnlimitedCount() {
        return count(null);
    }
    
    default int fetchUnlimitedCount(Connection con) {
        return reselect((q, t) -> q.select(t.count()))
            .withoutSortingAndPaging()
            .execute(con)
            .get(0)
            .intValue();
    }
}
```
- reselect((q, t) -> q.select(t.count())): The count-query does not query data, but queries COUNT
- withoutSortingAndPaging(): The count-query does not need the sorting clause order by nor the paging clause (such as H2's limit ? offset ?)
tip
Not only can Jimmer automatically generate count-query, it can also automatically optimize count-query, please refer to Join Optimization.
❸ limit(limit, offset): Based on the original template query without modification, generate the real data-query with pagination limits.
❹ Execute the data-query generated in ❸ to get the data within one page.
❺ Combine the data obtained in ❷ and ❹ into the page object and return it.

Dialects

This section discusses the SQL implementation of data-query with pagination limits under different databases.

Consider the following single page data query:

Java
Kotlin

List<Book> books = query
  .limit(/*limit*/ 10, /*offset*/ 90)
  .execute();

val books = query
  .limit(limit = 10, offset = 90)
  .execute()  

Here limit(limit, offset) sets the pagination range.

Different databases have vastly different support for pagination queries. So when creating SqlClient, the dialect needs to be specified.

Spring Data configuration method:

Add a configuration in application.properties or application.yml called jimmer.dialect with value as the class name of the dialect class provided by Jimmer:
```
jimmer:
    dialect: org.babyfish.jimmer.sql.dialect.H2Dialect
```

Non-Spring Data configuration method:

Java
Kotlin

JSqlClient sqlClient = JSqlClient
    .newBuilder()
    .setDialect(new H2Dialect())
    ...other code omitted...
    .build();

val sqlClient = newKSqlClient {
    setDialect(H2Dialect())
    ...other code omitted...
}

Different dialects will use different SQL to implement the limit query:

Default Behavior

info

Default behavior includes DefaultDialect, H2Dialect and PostgresDialect.

select
  tb_1_.ID,
  tb_1_.NAME,
  tb_1_.EDITION,
  tb_1_.PRICE,
  tb_1_.STORE_ID
from BOOK as tb_1_
left join BOOK_STORE as tb_2_
  on tb_1_.STORE_ID = tb_2_.ID
where tb_1_.PRICE between ? and ?
order by tb_2_.NAME asc, tb_1_.NAME asc
/* highlight-next-line */  
limit ? offset ?

MySqlDialect

select
  tb_1_.ID,
  tb_1_.NAME,
  tb_1_.EDITION,
  tb_1_.PRICE,
  tb_1_.STORE_ID 
from BOOK as tb_1_
left join BOOK_STORE as tb_2_
  on tb_1_.STORE_ID = tb_2_.ID
where tb_1_.PRICE between ? and ?
order by tb_2_.NAME asc, tb_1_.NAME asc
/* highlight-next-line */
limit ?, ?  

OracleDialect

When offset is not 0:

select * from (
  select core__.*, rownum rn__
  from (
    select
      tb_1_.ID,
      tb_1_.NAME,
      tb_1_.EDITION,
      tb_1_.PRICE,
      tb_1_.STORE_ID
    from BOOK as tb_1_
    left join BOOK_STORE as tb_2_
      on tb_1_.STORE_ID = tb_2_.ID
    where tb_1_.PRICE between ? and ?
    order by tb_2_.NAME asc, tb_1_.NAME asc  
  ) core__ where rownum <= ? ❶
) limited__ where rn__ > ? ❷

Where the variable at ❶ is limit + offset, and at ❷ is offset.

When offset is 0:

select core__.* from (
  select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID  
  from BOOK as tb_1_
  left join BOOK_STORE as tb_2_
    on tb_1_.STORE_ID = tb_2_.ID
  core__ where rownum <= ? ❶

Where ❶ is the variable limit.

Used with Object Fetcher

The object fetcher defines the shape of the queried object, allowing the queried object to carry more associated objects. This feature can be used together with pagination.

info

After the paged query is completed, Jimmer launches queries for other association objects, only for objects within a single page.

Take the Spring Data mode as an example. Now, let's modify the BookRepository we discussed earlier to support object fetchers:

Java
Kotlin

BookRepository.java

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
...other imports omitted...

public interface BookRepository<Book, Long> extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default Page<Book> findBooks(
        Pageable pageable,
        @Nullable Fetch<Book> fetcher,
        @Nullable String name,
        @Nullable String storeName
    ) {
        return sql()
            .createQuery(table)
            .whereIf(
                name != null && !name.isEmpty(),
                table.name().eq(name)
            )
            .whereIf(
                storeName != null && !storeName.isEmpty(),
                table.store().name().eq(storeName)
            )
            .orderBy(SpringOrders.toOrders(table, pageable.getSort()))
            .select(
                table.fetch(fetcher)
            )
            .fetchPage(
                pageable.getPageNumber(), 
                pageable.getPageSize(), 
                SpringPageFactory.getInstance()
            );
    }
}

BookRepository.kt
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable  
...other imports omitted...

interface BookRepository<Book, Long> : KRepository<Book, Long> {

    fun findBooks(
        pageable: Pageable,
        fetcher: Fetcher<Book>? = null,
        name: String? = null,
        storeName: String? = null
    ): Page<Book> =
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name eq it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    where(table.store.name eq it)
                }
                orderBy(pageable.sort)
                select(
                    table.fetch(fetcher)
                )
            }
            .fetchSpringPage(pageable)
}

If called as follows:

Java
Kotlin

Page.java
Page<Book> page = bookRepository.findBooks(
    PageRequest.of(
        1,
        5,
        SortUtils.toSort("name asc, edition desc")
    ),
    Fetchers.BOOK_FETCHER
        .allScalarFields()
        .store(
            Fetchers.BOOK_STORE_FETCHER
                .allScalarFields()
        )
        .authors(
            Fetchers.AUTHOR_FETCHER
                .allScalarFields()
        ),
    null, 
    null
);

Page.kt
val page = bookRepository.findBooks(
    PageRequest.of(
        1,
        5,
        SortUtils.toSort("name asc, edition desc")
    ),
    newFetcher(Book::class).by {
        allScalarFields()
        store {
            allScalarFields()
        }
        authors {
            allScalarFields()
        }
    }
)

It will generate the following 4 SQL statements:

count-query

select
    count(tb_1_.ID)
from BOOK tb_1_ 

data-query (assuming the database is H2)

select
    tb_1_.ID,
    tb_1_.CREATED_TIME,
    tb_1_.MODIFIED_TIME,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_
order by 
    tb_1_.NAME asc,
    tb_1_.EDITION desc
limit ? /* 5 */, ? /* 5 */

Query the many-to-one association Book.store for the 5 paged objects
```
select
    tb_1_.ID,
    tb_1_.NAME
from BOOK_STORE tb_1_
where
    tb_1_.ID in (
        ? /* 2 */, ? /* 1 */  
    )
```
info
Although there are 5 paged objects, their foreign key STORE_ID only has two values.

Query the many-to-many association Book.authors for the 5 paged objects

select
    tb_2_.BOOK_ID,
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME
from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_
    on tb_1_.ID = tb_2_.AUTHOR_ID
where
    tb_2_.BOOK_ID in (
        ? /* 10 */, ? /* 3 */, ? /* 2 */,  
        ? /* 1 */, ? /* 9 */
    )

Finally, in the obtained pagination, each object conforms to the data structure of the object fetcher.

{
    "content":[ // Current page
        {
            "id":12,
            "name":"GraphQL in Action",
            "edition":3,
            "price":80,
            "store":{
                "id":2,
                "name":"MANNING",
                "website":null
            },
            "authors":[
                {
                    "id":5,
                    "firstName":"Samer",
                    "lastName":"Buna",
                    "gender":"MALE"
                }
            ]
        },
        {
            "id":11,
            "name":"GraphQL in Action",
            "edition":2,
            "price":81,
            "store":{
                "id":2,
                "name":"MANNING",
                "website":null
            },
            "authors":[
                {
                    "id":5,
                    "firstName":"Samer",
                    "lastName":"Buna",
                    "gender":"MALE"
                }
            ]
        },
        {
            "id":10,
            "name":"GraphQL in Action",
            "edition":1,
            "price":82,
            "store":{
                "id":2,
                "name":"MANNING",
                "website":null
            },
            "authors":[
                {
                    "id":5,
                    "firstName":"Samer",
                    "lastName":"Buna",
                    "gender":"MALE"
                }
            ]
        },
        {
            "id":3,
            "name":"Learning GraphQL",
            "edition":3,
            "price":51,
            "store":{
                "id":1,
                "name":"O'REILLY",
                "website":null
            },
            "authors":[
                {
                    "id":2,
                    "firstName":"Alex",
                    "lastName":"Banks",
                    "gender":"MALE"
                },
                {
                    "id":1,
                    "firstName":"Eve",
                    "lastName":"Procello",
                    "gender":"FEMALE"
                }
            ]
        },
        {
            "id":2,
            "name":"Learning GraphQL",
            "edition":2,
            "price":55,
            "store":{
                "id":1,
                "name":"O'REILLY",
                "website":null
            },
            "authors":[
                {
                    "id":2,
                    "firstName":"Alex",
                    "lastName":"Banks",
                    "gender":"MALE"
                },
                {
                    "id":1,
                    "firstName":"Eve",
                    "lastName":"Procello",
                    "gender":"FEMALE"
                }
            ]
        }
    ],
    "totalPages":3, // Total page count is 3
    "totalElements":12, // Total row count before pagination is 12

    ...Spring Data's Page object has too many properties, omitted...
}

Features of Jimmer Pagination​

When Used with Spring Data​

When Not Using Spring Data​

Jimmer's Page object​

Implement Business Logic​

Internal Mechanism​

Dialects​

Default Behavior​

MySqlDialect​

OracleDialect​

Used with Object Fetcher​

Features of Jimmer Pagination

When Used with Spring Data

When Not Using Spring Data

Jimmer's Page object

Implement Business Logic

Internal Mechanism

Dialects

Default Behavior

MySqlDialect

OracleDialect

Used with Object Fetcher