Dynamic Filtering

Basic Usage

• Java API uses fluent style to build DSL, whereIf is provided to add conditional where clauses without breaking the fluent chaining.

• Kotlin API uses lambdas to build DSL, no special API is needed to support dynamic queries.

Java

public List<Book> findBooks(@Nullable String name) {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .whereIf(
            name != null && !name.isEmpty(),
            table.name().eq(name)
        )
        .orderBy(table.name().asc(), table.edition().desc())
        .select(table)
        .execute();
}

Kotlin

fun findBooks(name: String?): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            name?.takeIf { it.isNotEmpty() }?.let {
                where(table.name eq it)
            }
            orderBy(table.name.asc(), table.edition.desc())
            select(table)
        }
        .execute()

• Calling findBooks(null) generates:

  select
      tb_1_.ID,
      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

• Calling findBooks("SQL in Action") generates:

  select
      tb_1_.ID,
      tb_1_.NAME,
      tb_1_.EDITION,
      tb_1_.PRICE,
      tb_1_.STORE_ID
  from BOOK tb_1_
  where
      tb_1_.NAME = ? /* SQL in Action */
  order by
      tb_1_.NAME asc,
      tb_1_.EDITION desc

Notes for Java Developers

Java developers please note that in the code:

.whereIf(
    name != null && !name.isEmpty(),
    table.name().eq(name) 
)

Even if name is null, the second parameter table.name().eq(name) would still be evaluated. This is the behavior of most programming languages.

For eq (or ne), passing in null does not cause problems. eq(null) would be automatically changed to isNull(), and ne(null) would become isNotNull().

However, for most other conditional expressions, using null would cause exceptions.

tip

Don't worry, if you accidentally make mistakes, the exception messages are very informative to guide you to use the correct approach introduced below.

For greater than or equals ge, dynamic query conditions can be added like:

.whereIf(
    minPrice != null, 
    () -> table.price().ge(minPrice)
)

Here, a lambda expression is used to delay the expression construction until the condition is met.

For Kotlin there are no such issues, no precautions needed.

Let's look at a more complete example:

Java

public List<Book> findBooks(
    @Nullable String name,
    @Nullable BigDecimal minPrice,
    @Nullable BigDecimal maxPrice
) {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .whereIf(
            name != null && !name.isEmpty(),
            table.name().eq(name)
        )
        .whereIf(
            minPrice != null,
            () -> table.price().ge(minPrice)
        )
        .whereIf(
            maxPrice != null,
            () -> table.price().le(maxPrice)
        )
        .orderBy(table.name().asc(), table.edition().desc())
        .select(table)
        .execute();
} 

Kotlin

fun findBooks(
    name: String? = null,
    minPrice: BigDecimal? = null,
    maxPrice: BigDecimal? = null
): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            name?.takeIf { it.isNotEmpty() }?.let {
                where(table.name eq it)
            }
            minPrice?.let {
                where(table.price ge it)
            }
            maxPrice?.let {
                where(table.price le it)
            }
            orderBy(table.name.asc(), table.edition.desc())
            select(table)
        }
        .execute()

Multi-table Operations

So far, the dynamic queries are based on a single table.

Consider a more complex scenario where some dynamic conditions are on other tables instead of the current table (that is, those tables need to be joined first before dynamic where conditions can be added). How can this be achieved?

No other higher level SQL access solutions have elegantly solved this problem, it had been a blank in the domain. Jimmer perfectly solves this difficult problem (which is the very reason Jimmer was created).

We will systematically discuss this in next doc.

tip

In next doc you will see the uniqueness of Jimmer SQL DSL, gaining a powerful capability no other solution in the industry supports.