Simple Queries

Just like other Spring Data implementations, users can define abstract methods in the Repository interface. As long as the names, parameters and return values of these methods follow conventions, Jimmer implements them automatically. For example:

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;

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

public interface BookRepository extends JRepository<Book, Long> {

    List<Book> findByNameOrderByEditionDesc(
        @Nullable String name
    );

    List<Book> findByPriceBetweenOrderByName(
        @Nullable BigDecimal minPrice,
        @Nullable BigDecimal maxPrice
    );

    long countByName(String name);
}

BookRepository.kt
package com.example.repository

import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository

interface BookRepository : KRepository<Book, Long> {

    fun findByNameOrderByEditionDesc(
        name: String?
    ): List<Book>

    fun findByPriceBetweenOrderByName(
        minPrice: BigDecimal?,
        maxPrice: BigDecimal?
    ): List<Book>

    fun countByName(String name): Long
}

note

There are various conventions for method names, but basic usage is similar to Spring Data JPA.

So please refer to https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repository-query-keywords, this article will not repeat it.

Dynamic WHERE

You may have noticed that in the above examples, many parameters can be null.

In Jimmer, the automatically implemented abstract methods are inherently dynamic queries, that is, any query parameter can be null.

Let's look at another more representative example, where the abstract method is defined as:

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;

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

public interface BookRepository extends JRepository<Book, Long> {

    List<Book> findByNameLikeIgnoreCaseAndPriceBetween(
        @Nullable String name,
        @Nullable BigDecimal minPrice,
        @Nullable BigDecimal maxPrice
    );
}

BookRepository.kt
package com.example.repository

import com.example.model.Book  

import org.babyfish.jimmer.spring.repository.KRepository

interface BookRepository : KRepository<Book, Long> {

    fun findByNameLikeIgnoreCaseAndPriceBetween(
        name: String? = null,
        minPrice: BigDecimal? = null,
        maxPrice: BigDecimal? = null
    ): List<Book>
}

Each parameter of this method can be null, look at the 6 possible invocations:

Not specify any parameters

Java
Kotlin

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

val books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween()

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_

Specify name

Java
Kotlin

List<Book> books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        "G",
        null,
        null
    );

val books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(name = "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 lower(tb_1_.NAME) like ? /* %g% */ 

Specify minPrice

Java
Kotlin

List<Book> books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        null,
        new BigDecimal(40), 
        null
    );

val books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        minPrice = BigDecimal(40)
    )

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_.PRICE >= ? /* 40 */

Specify maxPrice

Java
Kotlin

List<Book> books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        null,
        null,
        new BigDecimal(60)
    );

val books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        maxPrice = BigDecimal(60)
    )

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_.PRICE <= ? /* 60 */

Specify both minPrice and maxPrice

Java
Kotlin

List<Book> books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        null,
        new BigDecimal(40),
        new BigDecimal(60)
    );

val books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        null,
        BigDecimal(40), 
        BigDecimal(60)
    )

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_.PRICE between ? /* 40 */ and ? /* 60 */)

Specify all parameters

Java
Kotlin

List<Book> books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        "G",
        new BigDecimal(40),
        new BigDecimal(60) 
    );

val books = bookRepository
    .findByNameLikeIgnoreCaseAndPriceBetween(
        "G",
        BigDecimal(40),
        BigDecimal(60)
    )

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
    lower(tb_1_.NAME) like ? /* %g% */
and 
    (tb_1_.PRICE between ? /* 40 */ and ? /* 60 */)

Dynamic JOIN

Users can not only apply filters to properties of the currently queried object, but also to properties of associated objects. For example:

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;

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

public interface BookRepository extends JRepository<Book, Long> {

    // name -> `Book.name`
    // storeName -> `Book.store.name`
    List<Book> findByNameStartsWithAndStoreName(
        @Nullable String name,
        @Nullable String storeName
    );
}

BookRepository.kt
package com.example.repository

import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository

interface BookRepository : KRepository<Book, Long> {

    // name -> `Book.name`
    // storeName -> `Book.store.name`
    fun findByNameStartsWithAndStoreName(
        name: String? = null,
        storeName: String? = null
    ): List<Book> 
}

Here, storeName in findByNameStartWithAndStoreName actually refers to store.name.

It means a join is performed through Book.store to BookStore first, then a condition is applied on BookStore.name.

note

JOIN will only be generated in the SQL if the storeName parameter is specified
The association that can be used by the convention method must be a non-collection association (one-to-one, many-to-one)

Let's see how to use it:

Specify name parameter, no JOIN

Java
Kotlin

List<Book> books = bookRepository
    .findByNameStartsWithAndStoreName("G", null);

val books = bookRepository
    .findByNameStartsWithAndStoreName("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 tb_1_.NAME like ? /* G% */

Specify storeName parameter, JOIN generated

Java
Kotlin

List<Book> books = bookRepository
    .findByNameStartsWithAndStoreName(null, "MANNING");

val books = bookRepository
    .findByNameStartsWithAndStoreName(null, "MANNING") 

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 tb_2_.NAME = ? /* MANNING */

Dynamic ORDER BY

As long as the abstract method has a parameter of type org.springframework.data.domain.Sort, dynamic ordering can be achieved. For example:

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;

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

public interface BookRepository extends JRepository<Book, Long> {

    List<Book> findByNameLikeIgnoreCase(

        // This parameter is not used in subsequent examples, always null.
        // Reason:
        // If a query does not need any parameters, the method from base 
        // interface is enough,  no need to define this method. The value 
        // of this parameter in this example is solely to make the current 
        // custom abstract method look reasonable. 
        @Nullable String name,

        @Nullable Sort sort
    );
}

BookRepository.kt
package com.example.repository

import com.example.model.Book  

import org.babyfish.jimmer.spring.repository.KRepository
import org.springframework.data.domain.Sort

interface BookRepository : KRepository<Book, Long> {

    fun findByNameLikeIgnoreCase(

        // This parameter is not used in subsequent examples, always null.
        // Reason:
        // If a query does not need any parameters, the method from base 
        // interface is enough, no need to define this method. The value 
        // of this parameter in this example is solely to make the current 
        // custom abstract method look reasonable.
        name: String? = null,

        sort: Sort? = null
    ): List<Book>
}

To facilitate the top-level code to accept sorting strings from the frontend, Jimmer provides the utility class org.babyfish.jimmer.spring.model.SortUtils to convert the sorting string passed from the client 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"
);

ORDER BY without JOIN

Java
Kotlin

List<Book> books = bookRepository
    .findByName(
        null,
        SortUtils.toSort("name, edition desc") 
    );

val books = bookRepository
    .findByName(
        null,
        SortUtils.toSort("name, edition 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_
/* highlight-next-line */
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc 

ORDER BY requiring JOIN

Java
Kotlin

List<Book> books = bookRepository
    .findByName(
        null,
        SortUtils.toSort("store.name, name, edition desc")
    );

val books = bookRepository
    .findByName(
        null,
        SortUtils.toSort("store.name, name, edition 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_
/* highlight-next-line */
left join BOOK_STORE as tb_2_
    on tb_1_.STORE_ID = tb_2_.ID
/* highlight-next-line */  
order by
    tb_2_.NAME asc,
    tb_1_.NAME asc,
    tb_1_.EDITION desc

Paged Queries

To perform paged queries, the method needs:

A parameter of type org.springframework.data.domain.Pageable
Return org.springframework.data.domain.Page<Current Entity>

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;

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

public interface BookRepository extends JRepository<Book, Long> {

    Page<Book> findByName(

        // This parameter is not used in subsequent examples, always null.
        // Reason:
        // If a query does not need any parameters, the method from base 
        // interface is enough, no need to define this method. The value 
        // of this parameter in this example is solely to make the current 
        // custom abstract method look reasonable.
        @Nullable String name,

        Pageable pageable
    );
}

BookRepository.kt
package com.example.repository

import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.springframework.data.domain.Pageable
import org.springframework.data.domain.Page

interface BookRepository : KRepository<Book, Long> {

    fun findByName(

        // This parameter is not used in subsequent examples, always null.
        // Reason:
        // If a query does not need any parameters, the method from base 
        // interface is enough, no need to define this method. The value 
        // of this parameter in this example is solely to make the current 
        // custom abstract method look reasonable.
        name: String? = null,

        pageable: Pageable

    ): Page<Book>
}

It can be used like:

Java
Kotlin

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

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

The returned Page object is like:

{
    "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 paging */
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 paging */
/* highlight-next-line */  
limit ?, /* 5 (offset) */ ? /* 5 (limit) */ 

Object Fetchers

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

Adding a parameter of type org.babyfish.jimmer.sql.fetcher.Fetcher<Current Entity> makes the abstract method capable of this.

Java
Kotlin

BookRepository.java
package com.example.repository;

import com.example.model.Book;

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

public interface BookRepository extends JRepository<Book, Long> {

    Page<Book> findByName(

        // This parameter is not used in subsequent examples, always null.
        // Reason:
        // If a query does not need any parameters, the method from base 
        // interface is enough, no need to define this method. The value 
        // of this parameter in this example is solely to make the current 
        // custom abstract method look reasonable.
        @Nullable String name,

        Pageable pageable,

        @Nullable Fetcher<Book> fetcher
    );
}

BookRepository.kt
package com.example.repository

import com.example.model.Book

import org.babyfish.jimmer.spring.repository.KRepository
import org.babyfish.jimmer.sql.fetcher.Fetcher
import org.springframework.data.domain.Pageable
import org.springframework.data.domain.Page

interface BookRepository : KRepository<Book, Long> {

    fun findByName(

        // This parameter is not used in subsequent examples, always null.
        // Reason:
        // If a query does not need any parameters, the method from base 
        // interface is enough, no need to define this method. The value 
        // of this parameter in this example is solely to make the current 
        // custom abstract method look reasonable.
        name: String? = null,

        pageable: Pageable,

        fetcher: Fetcher<Book>? = null
    ): Page<Book>
}

If no Fetcher is passed or a shape of simple objects is passed, the result will be necessarily similar to previous examples, no need to repeat.

So let's just demonstrate querying a complex data structure:

Java
Kotlin

Page<Book> page = bookRepository
    .findByName(
        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
    .findByName(
        null,
        PageRequest.of(
            1, // zero based, 1 means second page 
            5,
            SortUtils.toSort("name, edition desc")
        ),
        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 paging */ 
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 paging */
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:
 * In current case, the foreign key `STORE_ID` 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 (  
    ?, ? 
    /* actual param list: 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 (
    ?, ?, ?, ?, ? 
    /* actual param list: 10, 3, 2, 1, 9 */
)

tip

Whether it is simple queries discussed in this article, or complex queries to be discussed in next article, as long as the query returns entity objects or their collections instead of 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​

Dynamic ORDER BY​

Paged Queries​

Object Fetchers​

Dynamic WHERE

Dynamic JOIN

Dynamic ORDER BY

Paged Queries

Object Fetchers