Complex Queries

In the previous document, we introduced adding abstract methods to custom Repository interfaces following certain conventions, which are implemented automatically by Jimmer.

However, this approach has some issues:

Results in very long method names
Cannot implement complex query logic
If developers add methods violating conventions, it leads to startup failures rather than compile time errors

So Jimmer provides another approach that allows developers to write queries themselves and control all details.

To maximize the safety of static languages, Jimmer provides a strongly typed SQL DSL, rather than weak, string-based solutions like Spring Data JPA's @Query.

In Jimmer, query logic can be directly implemented in default methods of the Repository interface.

Dynamic WHERE

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;
import com.example.model.BookTable;

import org.babyfish.jimmer.spring.repository.JRepository;
import org.jetbrains.annotations.Nullable;
import org.springframework.util.StringUtils;

public interface BookRepository extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE; ❶

    default List<Book> find(@Nullable String name) {
        return sql() ❷
            .createQuery(table)
            .whereIf( ❸
                StringUtils.hasText(name),
                table.name().ilike(name)
            )
            .orderBy(table.name())
            .orderBy(table.edition().desc())
            .select(table) ❹
            .execute(); ❺
    }
}

BookRepository.kt
package com.example.repository

import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.sql.kt.ast.expression.*

interface BookRepository : KRepository<BookStore, Long> {

    fun find(name: String? = null): List<Book> =
        sql ❷
            .createQuery(Book::class) { ❶
                name?.takeIf { it.isNotEmpty() }?.let { ❸
                    where(table.name ilike it)
                }
                orderBy(table.name)
                orderBy(table.edition.desc())
                select(table) ❹
            }
            .execute() ❺
}

❶
- Java: The parent query is based on the table object Tables.BOOK, renamed to table for convenience.
  
  This rename applies to all default methods of the current interface, so declaring it as a static interface variable is appropriate.
- Kotlin: createQuery accepts a lambda expression which switches the meaning of this, so code inside the lambda can directly use the table variable.
❷
- Java: The sql() method inherited from JRepository returns org.babyfish.jimmer.sql.JSqlClient, which is the total entry point for Java API of the SQL DSL.
- Kotlin: The sql property inherited from KRepository returns org.babyfish.jimmer.sql.kt.KSqlClient, which is the total entry point for Kotlin API of the SQL DSL.
❸ Only add the where condition when the query parameter is non-null and string length is non-zero.

Obviously, this query is dynamic.
❹ The select statement is always at the end. Before select, the query has no return type; only after select can the return type of the query be determined. This is why select appears at the end.

note
The practice of putting select at the end is not originally invented by Jimmer, it was first introduced in C# linq.

❺ Between select and execute:

In Java, the expression type is TypedRootQuery<Book>
In Kotlin, the expression type is KTypedRootQuery<Book>

But neither is List<Book>.

This represents a query that is created but not yet executed. It needs to call execute to actually execute it to get the final result List<Book>.

note

The query is not executed immediately after creation, execute must be called to execute it. This is not a design flaw, but an important capability that the pagination query section will illustrate.

execute is not the only execution method, in addition to execute, there are other execution methods:

Method	Return Type	Note
execute (as shown in this example)	List<T>	Returns a list
fetchOne	T (cannot be null)	Returns one record, throws exception if no data or multiple data
fetchOneOrNull	T (can be null)	Returns one record, throws exception if multiple data
fetchOptional (Java only)	Optional wrapping of T or null	Returns one record, throws exception if multiple data
forEach	No return	Use when data volume is large, don't want to build collection, just iterate

All these execution methods have two overloaded versions:

Version without parameters (current example): Execute based on JDBC connection managed by Spring transaction. Should be called in most cases.
Version with java.sql.Connection parameter: Execute based on the JDBC connection specified by the user.

Usage:

Do not specify parameter

Java
Kotlin

List<Book> books = bookRepository
    .find(null);

val books = bookRepository.find()

The generated SQL is (formatted for readability):

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK as tb_1_
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc 

Specify parameter

Java
Kotlin

List<Book> books = bookRepository.find("G"); 

val books = bookRepository.find("G")

The generated SQL is (formatted for readability):

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK as tb_1_
where
    /* highlight-next-line */
    lower(tb_1_.NAME) like ? /* %g% */
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc

Dynamic JOIN

Next, add a storeName parameter to this query method to filter on the name property of BookStore associated via the Book.store property of the current Book.

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;
import com.example.model.BookTable;

import org.babyfish.jimmer.spring.repository.JRepository;
import org.jetbrains.annotations.Nullable;
import org.springframework.util.StringUtils;

public interface BookRepository extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default List<Book> find(
        @Nullable String name,
        @Nullable String storeName
    ) {
        return sql()
            .createQuery(table)
            .whereIf(
                StringUtils.hasText(name),
                table.name().ilike(name)
            )
            .whereIf(
                StringUtils.hasText(storeName),
                table.store().name().ilike(storeName) 
            )
            .orderBy(table.name())
            .orderBy(table.edition().desc())
            .select(table) 
            .execute();
    }
}

BookRepository.kt
package com.example.repository

import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.sql.kt.ast.expression.*

interface BookRepository : KRepository<BookStore, Long> {

    fun find(
        name: String? = null,
        storeName: String? = null
    ): List<Book> =
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name ilike it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    table.store.name ilike it
                }
                orderBy(table.name)
                orderBy(table.edition.desc())
                select(table)
            }
            .execute()
}

Where Java's table.store().name() or Kotlin's table.store.name means:

Starting from the current queried object Book, join to the BookStore object via the association property Book.store, and finally apply filter condition on the BookStore.name property.

Obviously, if the storeName parameter is not specified, the final SQL will not contain JOIN, no need to demonstrate.

So let's demonstrate the case where the storeName parameter is specified.

Java
Kotlin

List<Book> books = bookRepository.find(null, "M");

val books = bookRepository.find(storeName = "M") 

The generated SQL is (formatted for readability):

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK as tb_1_
/* highlight-next-line */ 
inner join BOOK_STORE as tb_2_
    on tb_1_.STORE_ID = tb_2_.ID
where
    lower(tb_2_.NAME) like ?  
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc 

Subqueries

Next, add an authorName parameter to this query method to filter on the firstName or lastName properties of Author associated via Book.authors property of the current Book.

note

Unfortunately, if you try to emulate the previous example's table.store.name by writing table.authors.firstName, you'll find there is no table.authors option in the IDE auto-complete of table.

This is because Book.authors is a collection association (collective term for one-to-many and many-to-many associations). Blindly joining other objects via collection associations will lead to duplicate data in the query results. More importantly, this duplication is fatal for pagination queries to be shown later.

For more details, please refer to Pagination Safety.

If you read Pagination Safety, you'll know you can force connecting via collection associations using table.asTableEx().authors. However, this capability is not suitable for use here.

In short, Jimmer's API will subtly hint that this should be implemented using subqueries rather than JOIN.

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.AuthorTableEx;
import com.example.model.Book;
import com.example.model.BookTable;

import org.babyfish.jimmer.spring.repository.JRepository;
import org.jetbrains.annotations.Nullable;
import org.springframework.util.StringUtils;

public interface BookRepository extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default List<Book> find(
        @Nullable String name,
        @Nullable String storeName,
        @Nullable String authorName
    ) {
        AuthorTableEx author = TableExes.AUTHOR_TABLE_EX; ❶

        return sql()
            .createQuery(table)
            .whereIf(
                StringUtils.hasText(name),
                table.name().ilike(name)
            )
            .whereIf(
                StringUtils.hasText(storeName),
                table.store().name().ilike(storeName) 
            )
            .whereIf(
                StringUtils.hasText(authorName),
                table.id().in(
                    sql()
                        .createSubQuery(author) ❷
                        .where(
                            Predicate.or(
                                author.firstName().ilike(authorName),
                                author.lastName().ilike(authorName)
                            )
                        )
                        .select(
                            author.books().id()
                        )
                )
            )
            .orderBy(table.name())
            .orderBy(table.edition().desc())
            .select(table) 
            .execute();
    }
}

BookRepository.kt
package com.example.repository

import com.example.model.Author
import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.sql.kt.ast.expression.*

interface BookRepository : KRepository<BookStore, Long> {

    fun find(
        name: String? = null,
        storeName: String? = null,
        authorName: String? = null
    ): List<Book> =
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name ilike it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    table.store.name ilike it
                }
                authorName?.takeIf { it.isNotEmpty() }?.let {
                    where(
                        table.id valueIn subQuery(Author::class) { ❶❷
                            where(
                                or(
                                    table.firstName ilike it,
                                    table.lastName ilike it
                                )
                            )
                            select(table.books.id)
                        }
                    )
                }
                orderBy(table.name)
                orderBy(table.edition.desc())
                select(table)
            }
            .execute()
}

❶
- Java: The subquery is based on the global variable TableExes.AUTHOR_TABLE_EX, alias it to author for subsequent convenience.
  
  Here, the subquery is based on TableExes.AUTHOR_TABLE_EX rather than Tables.AUTHOR_TABLE so that select(table.books().id()) can compile.
  
  Please read query/Pagination Safety for more details.
- Kotlin: subQuery accepts a lambda expression where this is redefined, so table variable can be used directly inside, but note:
  - Inside subquery lambda: table var represents KNonNullTableEx<Author>
  - Outside subquery lambda, inside parent query lambda: table var represents KNonNullTable<Book>
  - If need to reference parent query table inside subquery (not shown in this example, usually used for correlated subqueries): please use parentTable
❷ Create subquery

Obviously, if the authorName parameter is not specified, the final SQL will not contain sub query, no need to demonstrate.

So let's demonstrate the case where authorName is specified.

Java
Kotlin

List<Book> books = bookRepository.find(null, null, "A"); 

val books = bookRepository.find(authorName = "M")

The generated SQL is (formatted for readability):

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK as tb_1_
where
    tb_1_.ID in (
        /* highlight-next-line */
        select tb_3_.BOOK_ID
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
            lower(tb_2_.FIRST_NAME) like ? /* %a% */ 
        or
            lower(tb_2_.LAST_NAME) like ? /* %a% */ 
    )
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc

Dynamic ORDER BY

So far, the sorting in our queries has been static. We expect to accept external parameters to achieve dynamic sorting.

Add a parameter of type org.springframework.data.domain.Sort to the query method and use it to sort.

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.AuthorTableEx;
import com.example.model.Book;
import com.example.model.BookTable;

import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.spring.repository.SpringOrders;
import org.jetbrains.annotations.Nullable;
import org.springframework.data.domain.Sort;
import org.springframework.util.StringUtils;

public interface BookRepository extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default List<Book> find(
        @Nullable String name,
        @Nullable String storeName,
        @Nullable String authorName,
        @Nullable Sort sort
    ) {
        AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

        return sql()
            .createQuery(table)
            .whereIf(
                StringUtils.hasText(name),
                table.name().ilike(name)
            )
            .whereIf(
                StringUtils.hasText(storeName),
                table.store().name().ilike(storeName)
            )
            .whereIf(
                StringUtils.hasText(authorName),
                table.id().in(
                    sql()
                        .createSubQuery(author)
                        .where(
                            Predicate.or(
                                author.firstName().ilike(authorName),
                                author.lastName().ilike(authorName)
                            )
                        )
                        .select(
                            author.books().id()
                        )
                )
            )
            .orderBy(SpringOrders.toOrders(table, sort))
            .select(table)
            .execute();
    }
}

BookRepository.kt
package com.example.repository

import com.example.model.Author
import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.spring.repository.orderBy
import org.babyfish.jimmer.sql.kt.ast.expression.*
import org.springframework.data.domain.Sort

interface BookRepository : KRepository<BookStore, Long> {

    fun find(
        name: String? = null,
        storeName: String? = null,
        authorName: String? = null,
        sort: Sort? = null
    ): List<Book> =
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name ilike it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    table.store.name ilike it
                }
                authorName?.takeIf { it.isNotEmpty() }?.let {
                    where(
                        table.id valueIn subQuery(Author::class) {
                            where(
                                or(
                                    table.firstName ilike it,
                                    table.lastName ilike it
                                )
                            )
                            select(table.books.id)
                        }
                    )
                }
                orderBy(sort)
                select(table)
            }
            .execute()
}

For Java, org.babyfish.jimmer.spring.repository.SpringOrders.toOrders is needed to convert the Spring Data Sort object to the sorting accepted by Jimmer.
For Kotlin, org.babyfish.jimmer.spring.repository.orderBy extends the capability of Jimmer's underlying SQL DSL, so the query object can sort based on the Spring Data Sort object.

To facilitate the top-level code to accept sorting strings from the client, Jimmer provides the utility class org.babyfish.jimmer.spring.model.SortUtils to convert the string passed from the frontend to org.springframework.data.domain.Sort.

It can be used like:

Java
Kotlin

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

val sort = SortUtils.toSort(
    "store.name asc", "name asc", "edition desc"
)

Java
Kotlin

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

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

Now let's demonstrate the usage of dynamic sorting by specifying all other query parameters as null to focus on the sorting logic.

Java
Kotlin

List<Book> books = bookRepository.find(
    null,
    null,
    null, 
    SortUtils.toSort(
        "store.name", // This sort implies JOIN
        "price desc" 
    )
);

val books = bookRepository.find(
    sort = SortUtils.toSort(
        "store.name", // This sort implies JOIN
        "price desc"
    )
)

The generated SQL is (formatted for readability):

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK as tb_1_  
/* Dynamic sort causes table join */
/* highlight-next-line */ 
left join BOOK_STORE as tb_2_
    on tb_1_.STORE_ID = tb_2_.ID 
/* Dynamic sort */
/* highlight-next-line */
order by
    tb_2_.NAME asc,
    tb_1_.PRICE desc

Pagination

Let's continue improving the example by adding pagination capability.

To implement pagination, need:

Replace the org.springframework.data.domain.Sort type parameter with org.springframework.data.domain.Pageable (Pageable contains Sort)
Change return value from java.util.List<Book> to org.springframework.data.domain.Page<Book>
Internally use specific APIs to achieve pagination in one statement

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.AuthorTableEx;
import com.example.model.Book;
import com.example.model.BookTable;

import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.spring.repository.SpringOrders;
import org.jetbrains.annotations.Nullable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.util.StringUtils;

public interface BookRepository extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default Page<Book> find( ❶
        @Nullable String name,
        @Nullable String storeName,
        @Nullable String authorName,
        Pageable pageable ❷
    ) {
        AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

        return pager(pageable).execute( ❸
            sql()
                .createQuery(table)
                .whereIf(
                    StringUtils.hasText(name),
                    table.name().ilike(name)
                )
                .whereIf(
                    StringUtils.hasText(storeName),
                    table.store().name().ilike(storeName)
                )
                .whereIf(
                    StringUtils.hasText(authorName),
                    table.id().in(
                        sql()
                            .createSubQuery(author)
                            .where(
                                Predicate.or(
                                    author.firstName().ilike(authorName),
                                    author.lastName().ilike(authorName)
                                )
                            )
                            .select(
                                author.books().id()
                            )
                    )
                )
                .orderBy(SpringOrders.toOrders(table, pageable.getSort())) ❹
                .select(table)
        );
    }
}

BookRepository.kt
package com.example.repository

import com.example.model.Author
import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.spring.repository.orderBy
import org.babyfish.jimmer.sql.kt.ast.expression.*
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable

interface BookRepository : KRepository<BookStore, Long> {

    fun find(
        name: String? = null,
        storeName: String? = null,
        authorName: String? = null,
        pageable: Pageable ❷
    ): Page<Book> = ❶
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name ilike it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    table.store.name ilike it
                }
                authorName?.takeIf { it.isNotEmpty() }?.let {
                    where(
                        table.id valueIn subQuery(Author::class) {
                            where(
                                or(
                                    table.firstName ilike it,
                                    table.lastName ilike it
                                )
                            )
                            select(table.books.id)
                        }
                    )
                }
                orderBy(pageable.sort) ❹
                select(table)
            }
            .fetchPage( ❸
                pageable.pageNumber,
                pageable.pageSize
            )
}

❶ Return org.springframework.data.domain.Page rather than java.util.List
❷ Parameter becomes org.springframework.data.domain.Pageable
❸ Execute paged query
- Java
  
  Create a Pager object to paginate the original unexecuted query
  - pager is a method inherited from base interface JRepository/KRepository
  - The pager method has multiple overloaded versions, here the one called accepts a pagable object
  - The pager method creates a Pager object, which has an execute method that accepts the unexecuted original query
- Kotlin
  
  Kotlin API provides the extension method fetchPage for KConfigurableRootQuery<*, E> types to implement paged queries
❹ Get sort object from pageable to perform dynamic sorting

Now let's demonstrate the usage of paging query by specifying all other query parameters as null to focus on the pagination logic.

Java
Kotlin

List<Book> books = bookRepository.find(
    null,
    null,
    null,
    PageRequest.of(
        1, // zero based, 1 means second page
        5,
        SortUtils.toSort("name, edition desc")
    )
);

val books = bookRepository.find(
    pageable = PageRequest.of(
        1, // zero based, 1 means second page
        5,
        SortUtils.toSort("name, edition desc")
    )
)

The returned Page object is:

{
    "content":[
        {
            "id":10,
            "name":"GraphQL in Action",
            "edition":1,
            "price":80,
            "store":{
                "id":2
            }
        },
        {
            "id":3,
            "name":"Learning GraphQL",
            "edition":3,
            "price":51,
            "store":{
                "id":1
            }
        },
        {
            "id":2,
            "name":"Learning GraphQL",
            "edition":2,
            "price":55,
            "store":{
                "id":1
            }
        },
        {
            "id":1,
            "name":"Learning GraphQL",
            "edition":1,
            "price":45,
            "store":{
                "id":1
            }
        },
        {
            "id":9,
            "name":"Programming TypeScript",
            "edition":3,
            "price":48,
            "store":{
                "id":1
            }
        }
    ],
    "pageable":{
        "sort":{
            "unsorted":false,
            "sorted":true,
            "empty":false
        },
        "pageNumber":1,
        "pageSize":5,
        "offset":5,
        "paged":true,
        "unpaged":false
    },
    "totalPages":3,
    "totalElements":12, 
    "last":false,
    "numberOfElements":5,
    "first":false,
    "sort":{
        "unsorted":false,
        "sorted":true,
        "empty":false
    },
    "number":1,
    "size":5,
    "empty":false
}

The generated SQL is (formatted for readability):

/* Step 1: Query total rows before pagination */
select count(tb_1_.ID) from BOOK as tb_1_  

/* Step 2: Query data within one page */
select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK as tb_1_
order by 
    tb_1_.NAME asc,
    tb_1_.EDITION desc
/* MySQL pagination */  
/* highlight-next-line */
limit ?, /* 5(offset) */ ? /* 5(limit) */

Object Fetcher

Object fetchers are one of Jimmer's signature features, allowing querying of arbitrary complex data structures instead of just simple entity objects.

To use it, need:

Add a parameter of type org.babyfish.jimmer.sql.fetcher.Fetcher<Current Entity>
Change the select call of the top-level query from select(table) to select(table.fetch(fetcher))

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.AuthorTableEx;
import com.example.model.Book;
import com.example.model.BookTable;

import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.spring.repository.SpringOrders;
import org.babyfish.jimmer.sql.fetcher.Fetcher;
import org.jetbrains.annotations.Nullable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.util.StringUtils;

public interface BookRepository extends JRepository<Book, Long> {

    BookTable table = Tables.BOOK_TABLE;

    default Page<Book> find(
        @Nullable String name,
        @Nullable String storeName,
        @Nullable String authorName,
        Pageable pageable,
        @Nullable Fetcher<Book> fetcher
    ) {
        AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

        return pager(pageable).execute(
            sql()
                .createQuery(table)
                .whereIf(
                    StringUtils.hasText(name),
                    table.name().ilike(name)
                )
                .whereIf(
                    StringUtils.hasText(storeName),
                    table.store().name().ilike(storeName)
                )
                .whereIf(
                    StringUtils.hasText(authorName),
                    table.id().in(
                        sql()
                            .createSubQuery(author)
                            .where(
                                Predicate.or(
                                    author.firstName().ilike(authorName), 
                                    author.lastName().ilike(authorName)
                                )
                            )
                            .select(
                                author.books().id()
                            )
                    )
                )
                .orderBy(SpringOrders.toOrders(table, pageable.getSort()))
                .select(
                    table.fetch(fetcher) 
                )
        );
    }
}

BookRepository.kt
package com.example.repository

import com.example.model.Author
import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.spring.repository.orderBy
import org.babyfish.jimmer.sql.fetcher.Fetcher;
import org.babyfish.jimmer.sql.kt.ast.expression.*
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable

interface BookRepository : KRepository<BookStore, Long> {

    fun find(
        name: String? = null,
        storeName: String? = null,
        authorName: String? = null,
        pageable: Pageable,
        fetcher: Fetcher<Book>? = null
    ): Page<Book> =
        sql
            .createQuery(Book::class) {
                name?.takeIf { it.isNotEmpty() }?.let {
                    where(table.name ilike it)
                }
                storeName?.takeIf { it.isNotEmpty() }?.let {
                    table.store.name ilike it
                }
                authorName?.takeIf { it.isNotEmpty() }?.let {
                    where(
                        table.id valueIn subQuery(Author::class) {
                            where(
                                or(
                                    table.firstName ilike it,
                                    table.lastName ilike it
                                )
                            )
                            select(table.books.id)
                        }
                    )
                }
                orderBy(pageable.sort)
                select(
                    table.fetch(fetcher)
                )
            }
            .fetchPage(
                pageable.pageNumber,
                pageable.pageSize
            )
}

If no fetcher is passed or simple object shape is passed, the result will necessarily be similar to previous examples, no need to repeat.

So let's just demonstrate querying a complex data structure directly. To focus on the object fetcher, all other query parameters are specified as null except the fetcher.

Java
Kotlin

Page<Book> page = bookRepository
    .find(
        null,
        null,
        null,
        PageRequest.of(
            1, // zero based, 1 means second page
            5,
            SortUtils.toSort("name, edition desc")
        ),
        Fetchers.BOOK_FETCHER
            .allScalarFields()
            .store(
                Fetchers.BOOK_FETCHER
                    .name() // associated object only queries id (implicit+mandatory) and name
            )
            .authors(
                Fetchers.AUTHOR_FETCHER
                    // associated object only queries id (implicit+mandatory), firstName and lastName
                    .firstName().lastName()
            )
    );

val page = bookRepository
    .find(
        pageable = PageRequest.of(
            1, // zero based, 1 means second page
            5,
            SortUtils.toSort("name, edition desc")
        ),
        fetcher = newFetcher(Book::class).by {
            allScalarFields()
            store {
                // associated object only queries 
                // id (implicit+mandatory) and name
                name()
            }
            authors {
                // associated object only queries 
                // id (implicit+mandatory), firstName and lastName
                firstName()
                lastName()
            }
        }
    )

The returned Page object is:

{
    "content":[
        {
            "id":10,
            "name":"GraphQL in Action",
            "edition":1,
            "price":80,
            "store":{
                "id":2,
                "name":"MANNING"
            },
            "authors":[
                {
                    "id":5,
                    "firstName":"Samer",
                    "lastName":"Buna"
                }
            ]
        },
        {
            "id":3,
            "name":"Learning GraphQL",
            "edition":3,
            "price":51,
            "store":{
                "id":1,
                "name":"O'REILLY"
            },
            "authors":[
                {
                    "id":2,
                    "firstName":"Alex",
                    "lastName":"Banks"
                },
                {
                    "id":1,
                    "firstName":"Eve",
                    "lastName":"Procello"
                }
            ]
        },
        {
            "id":2,
            "name":"Learning GraphQL",
            "edition":2,
            "price":55,
            "store":{
                "id":1,
                "name":"O'REILLY"
            },
            "authors":[
                {
                    "id":2,
                    "firstName":"Alex",
                    "lastName":"Banks"
                },
                {
                    "id":1,
                    "firstName":"Eve",
                    "lastName":"Procello"
                }
            ]
        },
        {
            "id":1,
            "name":"Learning GraphQL",
            "edition":1,
            "price":45,
            "store":{
                "id":1,
                "name":"O'REILLY"
            },
            "authors":[
                {
                    "id":2,
                    "firstName":"Alex",
                    "lastName":"Banks"
                },
                {
                    "id":1,
                    "firstName":"Eve",
                    "lastName":"Procello"
                }
            ]
        },
        {
            "id":9,
            "name":"Programming TypeScript",
            "edition":3,
            "price":48,
            "store":{
                "id":1,
                "name":"O'REILLY"
            },
            "authors":[
                {
                    "id":4,
                    "firstName":"Boris",
                    "lastName":"Cherny"
                }
            ]
        }
    ],
    "pageable":{
        "sort":{
            "unsorted":false,
            "sorted":true,
            "empty":false
        },
        "pageNumber":1,
        "pageSize":5,
        "offset":5,
        "paged":true,
        "unpaged":false
    },
    "totalPages":3,
    "totalElements":12,
    "last":false,
    "sort":{
        "unsorted":false,
        "sorted":true,
        "empty":false
    },
    "numberOfElements":5,
    "number":1,  
    "first":false,
    "size":5,
    "empty":false
}

The generated SQL is (formatted for readability):

/* Step 1: Query total rows before pagination */
select count(tb_1_.ID) from BOOK as tb_1_

/* Step 2: Query aggregate root objects within one page */
select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID  
from BOOK as tb_1_
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc
/* MySQL pagination */
limit ?, /* 5(offset) */ ? /*  5(limit) */  

/* 
 * Step 3: For the 5 paged data (not 12 rows before paging),  
 * query the associated objects of property `Book.store`
 * 
 * Note:
 * Here the foreign key `STORE_ID` of the 5 records will be queried,
 * so finding parent objects directly via foreign key. 
 * Although there are 5 rows of data, the foreign keys only  
 * have two distinct values, so only two SQL params.
 */
select tb_1_.ID, tb_1_.NAME
from BOOK_STORE as tb_1_
where tb_1_.ID in (
    ?/* 2 */, ?/* 1 */
)

/* 
 * Step 4: For the 5 paged data (not 12 rows before paging),
 * query the associated objects of property `Book.authors` 
 */  
select tb_2_.BOOK_ID, tb_1_.ID, tb_1_.FIRST_NAME, tb_1_.LAST_NAME
from AUTHOR as tb_1_
inner join BOOK_AUTHOR_MAPPING as tb_2_
    on tb_1_.ID = tb_2_.AUTHOR_ID
where tb_2_.BOOK_ID in (
    ?/* 10 */, ?/* 3 */, ?/* 2 */, ?/* 1 */, ?/* 9 */
)

tip

Whether it is complex queries discussed in this article, or simple queries discussed in previous article, as long as the query returns entity objects or their collections rather than simple column tuples, it is highly recommended to add a Fetcher parameter to make all object queries as powerful in data structure shaping as GraphQL.

This brings great convenience to higher level business code.

Dynamic WHERE​

Dynamic JOIN​

Subqueries​

Dynamic ORDER BY​

Pagination​

Object Fetcher​

Dynamic WHERE

Dynamic JOIN

Subqueries

Dynamic ORDER BY

Pagination

Object Fetcher