Skip to main content

Overview

Select-After Style​

Unlike native SQL, in the DSL provided by Jimmer, the select statement comes after where, orderBy, groupBy, having, etc. For example:

BookTable table = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(table) ❶
    .where(table.name().eq("SQL in Action"))
    .orderBy(table.name().asc(), table.edition().desc())
    .select(table) ❷
    .limit(10, 100)
    .execute(); ❸
tip

Putting select after is not invented by Jimmer. This style was first brought to mainstream by LINQ introduced in C# 3.5.

This style has innate advantages for building strongly typed DSL frameworks, so Jimmer naturally adopts it.

Code Structure​

Now let's explain the code:

  • ❶ Create the query, get a mutable query object. Operations targeting this object continue until ❷.

    A mutable query object means any operations like where, orderBy, groupBy, having directly modify the current query object.

  • ❷ Call select, convert the mutable query object into an immutable one. Operations targeting this object continue until ❸.

    An immutable query object means any operations like distinct, limit, forUpdate, reselect, withoutSortingAndPaging, union, unionAll, minus, intersect, do not change the current object but create a new one.

    For example (to be concise, this pseudo-code uses Kotlin):

    val query = sqlClient.createQuery(Book::class) {
        where(...)
        orderBy(...)
        select(...)
    }
    val query1 = query.limit(10, 0); 
    val query2 = query.limit(10, 1);
    val query3 = query.limit(10, 2);

    Here query is the original query, query1, query2, query3 are other queries created based on it. limit does not change query itself but creates new immutable query objects.

    Finally, query1, query2, query3 share the same filters, sorting and structure projection as query, only differing in paging range. They can coexist without interfering with each other.

    Also, the mutable query before select has no return type, while the immutable query after select has a return type. That is, select assigns the final return type to the query.

  • ❸ Call execute to execute the query and return data.

    Before this step, no matter how the DSL is operated and tuned, no SQL operations will be executed.

    Besides execute, there are other methods to execute queries:

    • fetchOne: Assume the query returns exactly one row, get that row. Throw exception if 0 or more rows are returned during actual execution.

    • fetchOneOrNull: Assume the query returns 0 or 1 row, get that row or null. Throw exception if more rows are returned during actual execution.

    • fetchOptional: Similar to fetchOneOrNull but returns java.util.Optional<T> instead of null | T.

      caution

      This API only exists in Java API, not in Kotlin API since Kotlin does not need java.util.Optional<T>.

    • map: Execute directly and do an additional collection map operation at JVM level on top of the returned raw collection.

    • forEach: If returning too much data to avoid materializing a large collection, forEach can be called with a callback to traverse all data.

      This is what people often refer to as cursor queries.

    • stream: Return a java.util.stream.Stream backed by the JDBC cursor. Unlike execute, it does not materialize all rows as a list. The stream must be closed after use.

    • count: Based on the original data query written by users, automatically generate the count aggregate query needed for paging, execute and get total rows before paging.

      info

      This is a smart and advanced operation, please see Paging.

    tip

    All the methods above to execute queries have two overloaded versions:

    • Parameterless version (as shown in the example) utilizes Jimmer's connection management (usually integrating with Spring transaction management) to get JDBC connection and execute.

    • Version with a parameter accepting a JDBC connection. In special cases developers can discard Jimmer's connection management (usually integrating with Spring transaction management) and execute on a specified JDBC connection.

Now the process can be summarized as:

+----------------------+ 
|    ❶ createQuery    |
+-----------+----------+
            |
     Mutable Query
(Operations like where, orderBy
directly modify the query) 
            |
            |
           \|/
+----------------------+
|     ❷ select        |
+-----------+----------+
            |
    Immutable Query 
(Operations like limit, distinct
create new queries without modifying current one)
            |
            |
           \|/
+----------------------+
| ❸ execute or others |
+-----------+----------+
            |
           \|/
     Execution Result
(Result type is specified by ❷ select)

Streaming Results​

Use stream when the result can be processed sequentially and should not be fully retained in memory.

BookTable book = BookTable.$;

try (Stream<Tuple2<Integer, String>> rows = sqlClient
    .createQuery(book)
    .where(book.name().eq("Learning GraphQL"))
    .orderBy(book.edition())
    .select(book.edition(), book.name())
    .stream()
) {
    rows.forEach(this::exportRow);
}

Closing the stream closes its JDBC result set and statement, including when the stream is only partially consumed. Always use try-with-resources in Java or use in Kotlin.

The stream can contain scalar values, tuples, embeddable objects, or entities. An entity fetcher may use associations loaded by SQL joins, but it cannot use an association that requires a secondary select after the root rows are read. Such post-fetch processing needs the complete root result and is therefore rejected for streaming.

tip

Like execute, stream has an overload that accepts an explicit JDBC Connection.

JDBC Options​

The JDBC fetch size and query timeout can be configured for an individual query:

try (Stream<Book> books = sqlClient
    .createQuery(BookTable.$)
    .jdbcFetchSize(500)
    .jdbcQueryTimeout(30)
    .select(BookTable.$)
    .stream()
) {
    books.forEach(this::exportBook);
}

fetchSize is a driver hint for the number of rows fetched per round trip; drivers and databases may interpret it differently. queryTimeout uses the JDBC timeout in seconds.

Defaults for all statements can be set with setDefaultJdbcFetchSize and setDefaultJdbcQueryTimeout on the SQL client builder. A local value overrides the corresponding client default.