Skip to main content

Final Usage

Jimmer's Two Usages

  • Use Jimmer's Spring Boot Starter

  • Directly use the underlying SqlClient

Jimmer has complete sample projects. For how to use Jimmer's Spring Boot Starter, please refer to the examples. This article discusses using the underlying sqlClient directly without Spring.

SqlClient​

SqlClient is the lowest-level API in Jimmer

Javaorg.babyfish.jimmer.sql.JSqlClient
Kotlinorg.babyfish.jimmer.sql.kt.KSqlClient

You need to create a global object of this type, which is the API entry point for all ORM behaviors of Jimmer.

Multiple data sources require creating multiple global objects. However, here we discuss the most common case of a single data source, where one global object is sufficient.

Since we are using the Jimmer Spring Boot Starter, in this case, please specify the following configuration in application.yml (or application.properties)

spring:
    datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/jimmer_demo
        username: root
        password: 123456
jimmer:
    dialect: org.babyfish.jimmer.sql.dialect.MySqlDialect
    show-sql: true
    pretty-sql: true
    database-validation-mode: ERROR

The Jimmer Spring Boot Starter will automatically create this global object

Note that for Kotlin, in order to create KSqlClient instead of JSqlClient, you need to configure jimmer.language = kotlin in application.yml/application.properties.

info

Therefore, you can directly inject an object of type JSqlClient/KSqlClient anywhere.

The subsequent code in this article will assume there is a sqlClient variable and use it, but it will not provide an explanation, as a detailed explanation has already been given here.

Query​

package com.example.model.repository;  

import org.babyfish.jimmer.sql.JSqlClient;
import org.babyfish.jimmer.sql.fetcher.Fetcher;
import org.babyfish.jimmer.Page;  

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

public class BookRepository {

    private static final BookTable T = BookTable.$;

    private final JSqlClient sqlClient;

    public BookRepository(JSqlClient sqlClient) {
        this.sqlClient = sqlClient;
    }

    public Page<Book> findBooks(  

            int pageIndex, // Starts from 0
            int pageSize,
            
            @Nullable Fetcher<Book> fetcher,

            // e.g. "name asc, edition desc"
            @Nullable String sortCode, 
            
            @Nullable String name,
            @Nullable BigDecimal minPrice,
            @Nullable BigDecimal maxPrice, 
            @Nullable String storeName,
            @Nullable String storeWebsite,
            @Nullable String authorName,
            @Nullable Gender authorGender
    ) {
        return sqlClient
                .createQuery(T)
                .where(T.name().ilikeIf(name)) 
                .where(T.price().betweenIf(minPrice, maxPrice))
                .where(T.store().name().ilikeIf(storeName))
                .where(T.store().website().ilikeIf(storeWebsite))
                .where(
                        T.authors(author -> 
                                Predicate.or(
                                        author.firstName().ilikeIf(authorName),  
                                        author.lastName().ilikeIf(authorName)
                                )
                        )
                )
                .where(T.authors(author -> author.gender().eqIf(authorGender)))
                .orderBy(
                        Order.makeOrders(
                                T,
                                sortCode != null ?  
                                        sortCode :  
                                        "name asc, edition desc"
                        )
                )
                .select(
                    T.fetch(fetcher)
                )
                .fetchPage(pageIndex, pageSize);
    }
}

Users can create BookRepository objects and combine the parameters in various ways to test:

  • Dynamic queries (including dynamic joins, implicit subqueries)
  • Dynamic sorting
  • Paged queries

Details omitted here.

Save​

Save Short Associations​

In the src/main/dto directory, create any file with extension dto and edit its code:

export com.example.model.Book
    -> package com.example.model.dto  

input BookInput {
    #allScalars(this) 
    id(store) // as storeId
    id(authors) as authorIds  
}

Compile to generate the Java/Kotlin DTO type com.example.model.dto.BookInput:

Then modify the BookRepository:

public class BookRepository {

    ...other members omitted...

    public long saveBook(BookInput input) {
        return sqlClient
            .save(input) 
            .execute()
            .getModifiedEntity()  
            // Return auto-generated id if `input.id` is null
            .getId(); 
    }  
}

Save Long Associations​

In the src/main/dto directory, create any file with extension dto and edit its code:

export com.example.model.BookStore 
    -> package com.example.model.dto  

input CompositeBookStoreInput {
    #allScalars(this)
    books { 
        #allScalars(this)
        -id
    }  
}

Compile to generate the Java/Kotlin DTO type com.example.model.dto.CompositeBookStoreInput:

Then create the BookStoreRepository:

public class BookStoreRepository {

    public long saveBookStore(CompositeBookStoreInput input) {
        return sqlClient
            .save(input)
            .execute()
            .getModifiedEntity()  
            // Return auto-generated id if `input.id` is null
            .getId();
    }
}

Delete​

Modify the BookRepository:

public class BookRepository {

    ...other members omitted...

    public void deleteBook(long id) {
        sqlClient.deleteById(Book.class, id);
    }
}