Kotlin Support

MyBatis Dynamic SQL includes Kotlin extensions that provide an SQL DSL for Kotlin. This is the recommended method of using the library with Kotlin. For the most part, the Kotlin DSL provides a thin wrapper over the underlying Java DSL. You certainly can use the Java DSL with Kotlin. However, using the more specific Kotlin DSL will provide some benefits:

  1. The Kotlin DSL generally masks the platform types that are inferred with the underlying Java DSL
  2. The Kotlin DSL accurately expresses the nullability expectations of the underlying Java DSL
  3. Using the Kotlin DSL will avoid some confusion with overloaded function names that are present in the Java DSL
  4. The Kotlin DSL makes extensive use of Kotlin DSL construction features. It more closely mimics actual SQL than the Java DSL and will likely feel more natural to Kotlin developers

We take the customary approach to DSL building in Kotlin in that we attempt to create a somewhat natural feel for SQL, but not an exact replacement of SQL. The Kotlin DSL relies on the capabilities of the underlying Java DSL. This means that the Kotlin DSL does not add any capabilities that are not already present in the Java DSL. You can continue to use the underlying Java DSL at any time - it functions properly in Kotlin. One of the main features of the Kotlin DSL is that we move away from the method chaining paradigm of the Java DSL and move towards a more idiomatic Kotlin DSL based on lambdas and receiver objects. We think the Kotlin DSL feels more natural - certainly it is a more natural experience for Kotlin.

One consequence of the more natural feel of the Kotlin DSL is that you are free to write unusual looking SQL. For example, you could write a SELECT statement with a WHERE clause after a UNION. Most of the time these unusual usages of the DSL will yield correct results. However, it would be best to use the DSL as shown below to avoid hard to diagnose problems.

If you plan to use the Kotlin DSL, we recommend that you do not use any function from org.mybatis.dynamic.sql.SqlBuilder (the Java DSL entry points). Many functions from that class have been duplicated for the Kotlin DSL, but in a more Kotlin native manner.

Package Structure

We have implemented all Kotlin DSL functions as “top level” functions in their respective packages, so they can be used with a wildcard import statement. Until you become more familiar with the package structure, it is easiest to simply import the packages based on the type of object you wish to create.

To fully understand the package structure, it is important to understand the different types of objects that can be generated by the DSL. In general, the DSL can be used to generate the following types of objects:

  1. “Model” objects are generated by the DSL, but are not rendered into a “provider”. For most users these objects can be considered intermediate objects and will not need to be accessed directly. However, if you want to implement a custom rendering strategy then you might need to work with “model” objects (this is an unusual use case)
  2. “Provider” objects have been rendered into a form that can be used with SQL execution engines directly. Currently, the library supports rendering for MyBatis3 and Spring JDBC Template. Most users will interact with “provider” objects in some form or another

When creating model objects, import the following packages:

import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.model.*

When creating provider objects rendered for MyBatis3 (and using other MyBatis3 specific functions), import the following packages:

import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.*

When creating provider objects rendered for Spring JDBC Template (and using other Spring specific functions), import the following packages:

import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.spring.*

Every example shown on this page will compile and run accurately with either set of import statements. The only difference is the final object type produced by the library.

Kotlin Dynamic SQL Support Objects

MyBatis Dynamic SQL relies on a database “meta model” - objects that describe database tables and columns.

The pattern shown below is similar to the pattern recommended with Java. Kotlin does not support static class members, so the pattern for Kotlin varies a bit from Java by using a combination of Kotlin object and class. Like the Java pattern, this pattern will allow you to use table and column names in a “qualified” or “un-qualified” manner that looks like natural SQL. For example, in the following a column could be referred to as firstName or person.firstName.

import org.mybatis.dynamic.sql.SqlTable
import org.mybatis.dynamic.sql.util.kotlin.elements.column
import java.util.Date

object PersonDynamicSqlSupport {
    val person: Person()
    val id = person.id
    val firstName = person.firstName
    val lastName = person.lastName
    val birthDate = person.birthDate
    val employed = person.employed
    val occupation = person.occupation
   
    class Person : SqlTable("Person") {
        val id = column<Int>(name = "id", jdbcType = JDBCType.INTEGER)
        val firstName = column<String>(name = "first_name", jdbcType = JDBCType.VARCHAR)
        val lastName = column<String>(name = "last_name", jdbcType = JDBCType.VARCHAR)
        val birthDate = column<Date>(name = "birth_date", jdbcType = JDBCType.DATE)
        val employed = column<Boolean>(
           name = "employed",
           jdbcType = JDBCType.VARCHAR,
           typeHandler = "examples.kotlin.mybatis3.canonical.YesNoTypeHandler"
        )
        val occupation = column<String>(name = "occupation", jdbcType = JDBCType.VARCHAR)
        val addressId = column<Int>(name = "address_id", jdbcType = JDBCType.INTEGER)
    }
}

Notes:

  1. The outer object is a singleton containing the SqlTable and SqlColumn objects that map to the database table.
  2. The inner SqlTable is declared as a class rather than an object - this allows you to create additional instances for use in self-joins.
  3. Note the use of the column extension function. This function accepts different parameters for the different attributes that can be assigned to a column (such as a MyBatis3 type handler, or a custom rendering strategy). We recommend using this extension function rather than the corresponding column and withXXX methods in the Java native DSL because the extension method will retain the non-nullable type information associated with the column.

Statements

The DSL will generate a wide variety of SQL statements. We'll cover the details below with examples for each statement type. Included with both the MyBatis3 and Spring support are additional features specific to those platforms, we will cover those additions on separate pages. On this page, we'll cover what is common for all platforms.

The library supports the following types of statements:

  1. Count statements of various types - these are specialized select statements that return a single Long column, Count statements support where clauses, joins, and subqueries.
  2. Delete statement with or without a where clause.
  3. Insert statements of various types:
    1. Single row insert - a statement where the insert values are obtained from a record class
    2. General insert - a statement where the insert values are set directly in the statement
    3. Multi-row Insert - a statement where the insert values are derived from a collection of records
    4. Batch insert - a set of insert statements appropriate for use as a JDBC batch
    5. Insert select - a statement where the insert values are obtained from a select statement
  4. Select statement that supports joins, subqueries, where clauses, order by clauses, group by clauses, etc.
  5. Update Statement with or without a where clause

Count Statements

A count statement is a specialized select - it returns a single column - typically a long - and supports joins and a where clause.

The library supports three types of count statements:

  1. count(*) - counts the number of rows that match a where clause
  2. count(column) - counts the number of non-null column values that match a where clause
  3. count(distinct column) - counts the number of unique column values that match a where clause

The DSL for count statements looks like this:

// count(*)
val countRowsStatement = countFrom(person) {
    where { id isLessThan 4 }
}

// count(column)
val countColumnStatement = count(lastName) {
   from(person)
}

// count(distinct column) 
val countDistinctColumnStatement = countDistinct(lastName) {
   from(person)
}

These methods create models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.select.SelectModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for Spring)

Delete Statement

Delete statement support enables the creation of arbitrary delete statements including where clauses.

The DSL for delete statements looks like this:

val deleteStatement = deleteFrom(person) {
   where { id isLessThan 4 }
}

There is also a method that can be used to delete all rows in a table:

val rows = template.deleteFrom(person) {
   allRows()
}

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.delete.DeleteModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider (rendered for Spring)

Single Row Insert Statement

This method support enables the creation of arbitrary insert statements given a class that matches a database row. If you do not with to create such a class, then see the general insert support following this section.

The DSL for insert statements looks like this:

data class PersonRecord(
   val id: Int,
   val firstName: String,
   val lastName: String,
   val birthDate: Date,
   val employed: Boolean,
   val occupation: String?,
   val addressId: Int
)

val row = PersonRecord(100, "Joe", "Jones", Date(), true, "Developer", 1)

val insertRecordStatement = insert(row) {
   into(person)
   map(id) toProperty "id" 
   map(firstName) toProperty "firstName"
   map(lastName) toProperty "lastName"
   map(birthDate) toProperty "birthDate"
   map(employed) toProperty "employedAsString"
   map(occupation).toPropertyWhenPresent("occupation", row::occupation)
   map(addressId) toProperty "addressId"
}

This statement maps insert columns to properties in a class. Note the use of the toPropertyWhenPresent mapping - this will only set the insert value if the value of the property is non-null. Also note that you can use other mapping methods to map insert fields to nulls and constants if desired. Many of the mappings can be called via infix as shown above.

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.insert.InsertModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.insert.render.InsertStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.insert.render.InsertStatementProvider (rendered for Spring)

General Insert Statement

General insert method support enables the creation of arbitrary insert statements and does not require the creation of a class matching the database row.

The DSL for general insert statements looks like this:

val generalInsertStatement = insertInto(person) {
    set(id) toValue 100
    set(firstName) toValue "Joe"
    set(lastName) toValue "Jones"
    set(birthDate) toValue Date()
    set(employed) toValue true
    set(occupation) toValue "Developer"
    set(addressId) toValue 1
}

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.insert.GeneralInsertModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider (rendered for Spring)

Multi-Row Insert Statement

Multi-row inserts allow you to insert multiple rows into a table with a single insert statement. This is a convenient way to insert multiple rows, but there are some limitations. Multi-row inserts are not intended for large bulk inserts because it is possible to create insert statements that exceed the number of prepared statement parameters allowed in JDBC. For bulk inserts, please consider using a JDBC batch (see below).

Note the distinction between multi-row inserts and batch inserts. A multi-row insert is a single insert statement that inserts multiple rows into the database. It is formatted as follows:

insert into baz (foo, bar) values (1, 2), (3, 4), (5, 6)

A multi-row insert is a single insert statement with many (perhaps very many) parameters. Multi-row inserts are viewed as a single transaction by the database. In addition, most JDBC drivers place some limit on the number of prepared statement parameters. So it is best to use a multi-row insert with a small number of rows.

The DSL for multi-row insert statements looks like this:

val record1 = PersonRecord(100, "Joe", "Jones", Date(), true, "Developer", 1)
val record2 = PersonRecord(101, "Sarah", "Smith", Date(), true, "Architect", 2)

val multiRowInsertStatement = insertMultiple(listOf(record1, record2)) {
   into(person)
   map(id) toProperty "id"
   map(firstName) toProperty "firstName"
   map(lastName) toProperty "lastNameAsString"
   map(birthDate) toProperty "birthDate"
   map(employed) toProperty "employedAsString"
   map(occupation) toProperty "occupation"
   map(addressId) toProperty "addressId"
}

Note there is no toPropertyWhenPresent mapping available on a multi-row insert.

Also note that there is no overload of this function that accepts a vararg of rows because it would cause an overload resolution ambiguity error. This limitation is overcome in the utility functions for MyBatis and Spring as shown on the documentation pages for those utilities.

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.insert.MultiRowInsertModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider (rendered for Spring)

Batch Insert Statement

A batch insert is a sequence of insert statements that can be handled as a batch by the JDBC driver. Batches have virtually no limit on the number of statements that can be executed. Batches also support intermediate commits. Some care must be taken with the underlying database engine to ensure that batch statements are executed as a batch and not just a collection of individual inserts. This is especially true with MyBatis. Spring has support for executing a batch with a single commit. Intermediate commits must be handled manually.

MyBatis and Spring have different ways of executing batch inserts - you can see details on those specific pages. The library generates objects that can be used by either MyBatis or Spring.

The DSL for batch insert statements looks like this:

val record1 = PersonRecord(100, "Joe", "Jones", Date(), true, "Developer", 1)
val record2 = PersonRecord(101, "Sarah", "Smith", Date(), true, "Architect", 2)

val batchInsertStatement = insertBatch(listOf(record1, record2)) {
   into(person)
   map(id) toProperty "id"
   map(firstName) toProperty "firstName"
   map(lastName) toProperty "lastNameAsString"
   map(birthDate) toProperty "birthDate"
   map(employed) toProperty "employedAsString"
   map(occupation) toProperty "occupation"
   map(addressId) toProperty "addressId"
}

Note there is no toPropertyWhenPresent mapping available on a batch insert.

Also note that there is no overload of this function that accepts a vararg of rows because it would cause an overload resolution ambiguity error. This limitation is overcome in the utility functions for MyBatis and Spring as shown on the documentation pages for those utilities.

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.insert.BatchInsertModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.insert.render.BatchInsert (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.insert.render.BatchInsert (rendered for Spring)

Insert Select Statement

An insert select statement obtains insert values from a nested select statement.

The DSL for an insert select statement looks like this:

val insertSelectStatement = insertSelect(person) {
    columns(id, firstName, lastName, birthDate, employed, occupation, addressId)
    select(
        add(id, constant<Int>("100")), firstName, lastName, birthDate, employed, occupation, addressId
    ) {
        from(person)
        where { employed.isTrue() }
    }
}

The columns method accepts a list of SqlColumn objects that are rendered as the columns to insert. The select method is a query whose value list should match the columns. The number of rows inserted will generally match the number of rows returned from the query.

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.insert.InsertSelectModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider (rendered for Spring)

Select Statement

Select statement support enables the creation of methods that execute a query allowing a user to specify a where clause, join specifications, order by clauses, group by clauses, pagination clauses, etc.

The full DSL for select statements looks like this:

val selectStatement = select(orderMaster.orderId, orderMaster.orderDate, orderDetail.lineNumber,
   orderDetail.description, orderDetail.quantity
) {
   from(orderMaster, "om")
   join(orderDetail, "od") {
      on(orderMaster.orderId) equalTo orderDetail.orderId
      and(orderMaster.orderId) equalTo orderDetail.orderId
   }
   where { orderMaster.orderId isEqualTo 1 }
   or {
      orderMaster.orderId isEqualTo 2
      and { orderDetail.quantity isLessThan 6 }
   }
   orderBy(orderMaster.orderId)
   limit(3)
}

In a select statement you must specify a table in a from clause. Everything else is optional.

Multiple join clauses can be specified if you need to join additional tables. In a join clause, you must specify an on condition, and you may specify additional and conditions as necessary. Full, left, right, inner, and outer joins are supported.

Where clauses can be of arbitrary complexity and support all SQL operators including exists operators, subqueries, etc. You can nest and, or, and not clauses as necessary in where clauses.

There is also a method that will create a “distinct” query (select distinct ...) as follows:

val selectStatement = selectDistinct(id, firstName, lastName, birthDate, employed, occupation, addressId) {
   from(person)
   where { id isLessThan 5 }
   and {
      id isLessThan 4
      and {
         id isLessThan 3
         or { id isLessThan 2 }
      }
   }
   orderBy(id)
   limit(3)
}

These methods create models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.select.SelectModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for Spring)

Update Statement

Update statement support enables the creation of methods that execute an update allowing a user to specify SET clauses and where clauses.

The DSL for update statements looks like this:

val updateStatement = update(person) {
   set(firstName).equalTo("Sam")
   where { firstName isEqualTo "Fred" }
}

If you omit the where clause, the statement will update every row in a table.

This method creates models or providers depending on which package is used:

Package Resulting Object
org.mybatis.dynamic.sql.util.kotlin.model org.mybatis.dynamic.sql.update.UpdateModel
org.mybatis.dynamic.sql.util.kotlin.mybatis3 org.mybatis.dynamic.sql.update.render.UpdateStatementProvider (rendered for MyBatis3)
org.mybatis.dynamic.sql.util.kotlin.spring org.mybatis.dynamic.sql.update.render.UpdateStatementProvider (rendered for Spring)