Super QBE

Can It Be Simpler

From the previous article, we know Jimmer SQL DSL is inherently designed for arbitrary complex dynamic queries, completely different from other frameworks' SQL DSLs that only provide strong typing experience.

But there are two issues:

1. With more and more parameters introduced in the previous article, the method signatures become less Java-friendly. We urgently need to encapsulate all the query parameters into one object.

2. I'm really lazy. I want to achieve all the capabilities introduced in the previous article, but I don't want to write those code. I just want to write one line of code.

Jimmer's built-in DTO language can quickly solve the above two problems.

Define Specification DTO

Since in the article Query Arbitrary Shape/Exposing Features/Return Output DTO, we have already had some understanding about the DTO language when introducing Output DTO, this article does not repeat it.

1. Install the DTO language Intellij plugin: https://github.com/ClearPlume/jimmer-dto (This step is not required but highly recommended)

2. Create a new directory src/main/dto

3. Create a file Book.dto under src/main/dto and write the code below:

   Book.dto

   export com.yourcompany.yourproject.model.Book  
       -> package com.yourcompany.yourproject.model.dto
   
   specification BookSpecification {
       like/i(name)
       ge(price) // Default alias: minPrice
       le(price) // Default alias: maxPrice 
       flat(store) {
           as(^ -> store) { 
               like/i(name)
               like/i(website)
           }
       }
       flat(authors) {
           like/i(firstName, lastName) as authorName
           gender as authorGender
       }
   }
   ...Omit other DTO type definitions...
   info

   Different from the Output/Input DTO we discussed before, here the query Specification DTO uses the specification modifier.

   The QBE functions used extensively inside this Specification DTO are self-explanatory. As this article belongs to the quick tour section, we do not explain them in detail.

Generated Code

After compiling the project, Jimmer will automatically generate the following code:

Java

BookSpecification.java

@GeneratedBy( ❶
        file = "<yourproject>/src/main/dto/Book.dto"  
) 
public class BookSpecification  
implements JSpecification<Book, BookTable> {  ❷

    @Nullable 
    private String name;

    @Nullable
    private BigDecimal minPrice;   

    @Nullable
    private BigDecimal maxPrice;

    @Nullable
    private String storeName;

    @Nullable
    private String storeWebsite;

    @Nullable
    private String authorName;

    @Nullable
    private Gender authorGender;

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) { ❸
        ...Omit complex dynamic query logic...
    }

    ...Omit getters, setters, hashCode, equals, toString...
}

Kotlin

BookSpecification.kt

@GeneratedBy( ❶
        file = "<yourproject>/src/main/dto/Book.dto"
)  
data class BookSpecification(   
    val name: String? = null,
    val minPrice: BigDecimal? = null, 
    val maxPrice: BigDecimal? = null,
    val storeName: String? = null,
    val storeWebsite: String? = null,
    val authorName: String? = null,
    val authorGender: Gender? = null   
) : KSpecification<Book> { ❷

    override applyTo(args: KSpecificationArgs<Book>) { ❸
        ...Omit complex dynamic query logic...
    }
}

• ❶ Reminds developers that this class is auto-generated by Jimmer at compile time

• ❷ Interface implemented by the Specification DTO

• ❸ This class knows how to generate SQL predicates

Usage

Java

BookRepository.java

@Repository
public class BookRepository {

    private final JSqlClient sqlClient;

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

    List<Book> findBooks(   
        BookSpecification specification,  
        @Nullable Fetcher<Book> fetcher
    ) {
        BookTable table = Tables.BOOK_TABLE;   

        return sqlClient
            .createQuery(table)
            .where(specification)
            .select(table.fetch(fetcher)) 
            .execute();
    }  
}

Kotlin

BookRepository.kt

@Repository  
class BookRepository(
    private val sqlClient: KSqlClient   
) {

    fun findBooks(
        specification: BookSpecification, 
        fetcher: Fetcher<Book>? = null
    ): List<Book> =  
        sqlClient
            .createQuery(Book::class) {
                where(specification)
                select(table.fetch(table))
            }
            .execute()  
}

We can see that with only one line of code, complex dynamic queries can be achieved.

It has the same capabilities as the last example in the previous article. No need to repeat here.