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:

Java

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(); ❸

Kotlin

val books = sqlClient
    .createQuery(Book::class) { ❶
        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.

  • 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)