Native SQL Expressions

Native SQL expressions are an important feature to leverage database-specific capabilities, as database products always have proprietary features.

Example 1: Regular Expression Matching

This example shows how to use regular expression matching in Oracle and HSQLDB:

Java

AuthorTable table = Tables.AUTHOR_TABLE;

List<Author> authors = sqlClient
    .createQuery(table)
    .where(
        Predicate.sql(
            "regexp_like(%e, %v)", 
            it -> it
                .expression(table.firstName())
                .value("^Ste(v|ph)en$")
        )
    )
    .select(table)
    .execute();

Kotlin

val authors = sqlClient
    .createQuery(Author::class) {
        where(
            sql(Boolean::class, "regexp_like(%e, %v)") {
                expression(table.firstName)
                value("^Ste(v|ph)en$")
            }
        )
        select(table)
    }
    .execute() 

info

In the Java code, Predicate.sql is called to create a query condition based on native SQL. In fact, other expression types also support native SQL expressions. There are 5 sql functions:

1. Predicate.sql(...)
2. Expression.string().sql(...)
3. Expression.numeric().sql(...)
4. Expression.comparable().sql(...)
5. Expression.any().sql(...)

Kotlin does not have this issue, its API is unified.

The first parameter of sql(...) is the SQL template string, which can contain special symbols %e and %v:

• %e: Expression, to embed an expression
• %v: Value, to embed a value

The second parameter of sql(...) is optional, a lambda expression whose parameter is an object supporting two methods:

• expression(Expression<?>): Embed an expression, corresponding to %e in SQL template.
• value(Object): Embed a value, corresponding to %v in SQL template.

The final generated SQL is:

select
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME, 
    tb_1_.GENDER
from AUTHOR as tb_1_  
where
    /* highlight-next-line */
    regexp_like(tb_1_.FIRST_NAME, ?) 

Example 2: Analytic Functions

Let's look at another example using analytic functions:

Java

List<Tuple3<Book, Integer, Integer>> tuples = sqlClient
    .createQuery(table)
    .select(
        table,
        Expression.numeric().sql(
            Integer.class,
            "rank() over(order by %e desc)",
            table.price()
        ),
        Expression.numeric().sql(
            Integer.class,
            "rank() over(partition by %e order by %e desc)",
            new Expression[] { table.storeId(), table.price() }
        )
    )
    .execute();

Kotlin

val tuples = sqlClient
    .createQuery(Author::class) {
        select(
            table,
            sql(Int::class, "rank() over(order by %e desc)") {
                expression(table.price)
            },
            sql("rank() over(partition by %e order by %e desc)") {
                expression(table.store.id)
                expression(table.price)
            }
        )
    }
    .execute()

Three columns are queried here:

• First column: Book object
• Second column: The rank of the book price among all books
• Third column: The rank of the book price within its store

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID,
    /* highlight-next-line */
    rank() over(
        order by tb_1_.PRICE desc
    ),
    /* highlight-next-line */ 
    rank() over(
        partition by tb_1_.STORE_ID
        order by tb_1_.PRICE desc 
    )
from BOOK tb_1_