Kotlin Support for Spring

MyBatis Dynamic SQL includes Kotlin extensions for Spring that simplify execution of statements generated by the library. The library will render SQL in a format that is suitable for use with Spring's named parameter JDBC template. The only difficulty with using the DSL directly is that the parameters for statements need to be formatted properly for Spring. For example, this may involve the use of a BeanPropertySqlParameterSource or a MapSqlParameterSource depending on the statement type. The Kotlin DSL hides all these details.

The Spring extensions also allow use of Spring's row mappers for ResultSets, and generated key holder for retrieving generated keys on certain insert statements.

This page will show our recommended pattern for using the MyBatis Dynamic SQL with Kotlin and Spring JDBC Template. The code shown on this page is from the src/test/kotlin/examples/kotlin/spring/canonical directory in this repository. That directory contains a complete example of using this library with Kotlin and Spring.

All Kotlin support for Spring is available in two packages:

org.mybatis.dynamic.sql.util.kotlin.elements - contains the basic DSL elements common to all runtimes
org.mybatis.dynamic.sql.util.kotlin.spring - contains utilities that simplify integration with Spring JDBC Template

The Kotlin support for Spring JDBC is implemented as extension methods to NamedParameterJdbcTemplate. There are extension methods to support count, delete, insert, select, and update operations based on SQL generated by this library. For each operation, there are two different methods of executing SQL:

The first method is a two-step method. With this method you build SQL provider objects as shown on the Kotlin overview page and then execute the generated SQL by passing the provider to an extension method on NamedParameterJdbcTemplate
The second method is a one-step method that combines these operations into a single step

We will illustrate both approaches below.

Kotlin Dynamic SQL Support Objects

The pattern for the metamodel is the same as shown on the Kotlin overview page. We'll repeat it here to show some specifics for Spring.

import org.mybatis.dynamic.sql.AlisableSqlTable
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
    val addressId = person.addressId

    class Person : AlisableSqlTable<Person>("Person", ::Person) {
        val id = column<Int>(name = "id")
        val firstName = column<String>(name = "first_name")
        val lastName = column(
            name = "last_name",
            parameterTypeConverter = lastNameConverter
        )
        val birthDate = column<Date>(name = "birth_date")
        val employed = column(
            name = "employed",
            parameterTypeConverter = booleanToStringConverter
        )
        val occupation = column<String>(name = "occupation")
        val addressId = column<Int>(name = "address_id")
    }
}

Note the use of a “parameter type converter” on the employed column. This allows us to use the column as a Boolean in Kotlin, but store the values “Yes” or “No” on the database. The type converter looks like this:

val booleanToStringConverter: (Boolean?) -> String = { it?.let { if (it) "Yes" else "No" } ?: "No" }

The type converter will be used on general insert statements, update statements, and where clauses. It is not used on insert statements that map insert fields to properties in a data class. So you will need to add properties to a data class to use in that case. In the examples below, you will see use of a data class property employedAsString. This can easily be implemented by reusing the converter function as shown below…

data class PersonRecord(
    ...
    var employed: Boolean? = null,
    ...
) {
    val employedAsString: String
        get() = booleanToStringConverter(employed)
}

Count Statements

Two-Step Method

Count statements are constructed as shown on the Kotlin overview page. These methods create a SelectStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

import org.mybatis.dynamic.sql.util.kotlin.spring.count

val countStatement = count(...) // not shown... see the overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.count(countStatement) // rows is a Long

One-Step Method

Count statements can be constructed and executed in a single step with code like the following:

import org.mybatis.dynamic.sql.util.kotlin.spring.count
import org.mybatis.dynamic.sql.util.kotlin.spring.countDistinct
import org.mybatis.dynamic.sql.util.kotlin.spring.countFrom

val template: NamedParameterJdbcTemplate = getTemplate() // not shown

val rowcount = template.countFrom(person) {
    where { id isLessThan 4 }
}

val columnCount = template.count(lastName) {
    from(person)
    where { id isLessThan 4 }
}

val distinctColumnCount = template.countDistinct(lastName) {
    from(person)
    where { id isLessThan 4 }
}

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

val rows = template.countFrom(Person) {
    allRows()
}

Delete Statement

Two-Step Method

Delete statements are constructed as shown on the Kotlin overview page. These methods create a DeleteStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

import org.mybatis.dynamic.sql.util.kotlin.spring.deleteFrom

val deleteStatement = deleteFrom(...) // not shown... see the overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.delete(deleteStatement)  // rows is an Int

One-Step Method

Delete statements can be constructed and executed in a single step with code like the following:

import org.mybatis.dynamic.sql.util.kotlin.spring.deleteFrom

val template: NamedParameterJdbcTemplate = getTemplate() // not shown

val rows = template.deleteFrom(person) {
    where { id isLessThan 4 }
}

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

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

Single Row Insert Statement

Two-Step Method

Single record insert statements are constructed as shown on the Kotlin overview page. These methods create a InsertStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

val insertStatement = insert(...)  // not shown, see the overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insert(insertStatement)  // rows is an Int

If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:

val keyHolder = GeneratedKeyHolder()
val rows = template.insert(insertStatement, keyHolder)  // rows is an Int

One-Step Method

Single record insert statements can be constructed and executed in a single step with code like the following:

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

val rows = template.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"
}

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 the mapping methods to map insert fields to nulls and constants if desired.

Using a KeyHolder with the single step method looks like this:

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

val rows = template.withKeyHolder(keyHolder) {
    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"
    }
}

General Insert Statement

Two-Step Method

General insert statements are constructed as shown on the Kotlin overview page. These methods create a GeneralInsertStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

val insertStatement = insertInto(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.generalInsert(insertStatement)  // rows is an Int

If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:

val keyHolder = GeneratedKeyHolder()
val rows = template.generalInsert(insertStatement, keyHolder)  // rows is an Int

One-Step Method

General insert statements can be constructed and executed in a single step with code like the following:

val myOccupation = "Developer"

val rows = template.insertInto(Person) {
    set(id) toValue 100
    set(firstName) toValue "Joe"
    set(lastName) toValue "Jones"
    set(birthDate) toValue Date()
    set(employed) toValue true
    set(occupation) toValueWhenPresent myOccupation
    set(addressId) toValue 1
}

Note the use of the toValueWhenPresent mapping - this will only set the insert value if the value of the property is non-null. Also note that you can use the mapping methods to map insert fields to nulls and constants if desired.

Using a KeyHolder with the single step method looks like this:

val keyHolder = GeneratedKeyHolder()
val myOccupation = "Developer"

val rows = template.withKeyHolder(keyHolder) {
    insertInto(Person) {
        set(id) toValue 100
        set(firstName) toValue "Joe"
        set(lastName) toValue "Jones"
        set(birthDate) toValue Date()
        set(employed) toValue true
        set(occupation) toValueWhenPresent myOccupation
        set(addressId) toValue 1
    }
}

Multi-Row Insert Statement

Two-Step Method

Multi-Row insert statements are constructed as shown on the Kotlin overview page. These methods create a MultiRowInsertStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

val insertStatement = insertMultiple(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insertMultiple(insertStatement) // rows is an Int

If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:

val keyHolder = GeneratedKeyHolder()
val rows = template.insertMultiple(insertStatement, keyHolder)  // rows is an Int

One-Step Method

Multi-Row insert statements can be constructed and executed in a single step with code like the following:

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

val rows = template.insertMultiple(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"
}

Using a KeyHolder with the single step method looks like this:

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

val rows = template.withKeyHolder(keyHolder) {
    insertMultiple(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"
    }
}

Batch Insert Statement

Two-Step Method

Batch insert statements are constructed as shown on the Kotlin overview page. These methods create a BatchInsert that can be executed with an extension method for NamedParameterJdbcTemplate like this:

val insertStatement = insertBatch(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insertBatch(insertStatement) // rows is an IntArray

Spring does not support retrieval of generated keys with batch insert statements.

One-Step Method

Batch statements can be constructed and executed in a single step with code like the following:

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

val rows = template.insertBatch(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"
}

Insert Select Statement

Two-Step Method

Insert select statements are constructed as shown on the Kotlin overview page. These methods create a InsertSelectStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

val insertStatement = insertSelect(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insertSelect(insertStatement) // rows is an Int

If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:

val keyHolder = GeneratedKeyHolder()
val rows = template.insertSelect(insertStatement, keyHolder)  // rows is an Int

One-Step Method

Insert select statements can be constructed and executed in a single step with code like the following:

val insertSelectRows: Int = template.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() }
    }
}

Using a KeyHolder with the single step method looks like this:

val keyHolder = GeneratedKeyHolder()

val rows = template.withKeyHolder(keyHolder) {
    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() }
       }
    }
}

Select Statement

Spring Row Mappers

There are several ways to execute select statements with Spring. Many of the methods require the use of a row mapper. A row mapper is a user provided function that creates objects based on the values in a ResultSet. The rowMapper function will be called repeatedly until the end of the result set is reached. The function accepts two parameters - the ResultSet and an Int which will be set to the current row number. Many times the row number is ignored.

In Kotlin, you can declare the row mapper function either as a declared function, or as a function variable. A declared function could look like the following:

import java.sql.ResultSet

fun rowMapper(resultSet: ResultSet, rowNum: Int): PersonRecord =
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == "Yes",
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )

A function variable could look like this:

import java.sql.ResultSet

val rowMapper: (ResultSet, Int) -> PersonRecord = { rs, _ ->
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == "Yes",
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}

Note that in this case we are ignoring the row number. You can also pass a function like this directly to the extension methods as a lambda as we will see below.

Two-Step Method

Select statements are constructed as shown on the Kotlin overview page. These methods create a SelectStatementProvider that can be executed with extension methods for NamedParameterJdbcTemplate. There are several extension methods that can be used in the two-step method as detailed below:

Method	Comments
selectList(SelectStatementProvider, RowMapper)	Executes a select statement and returns a list (the list will be empty if no records match). The row mapper is used to map result sets for rows.
selectList(SelectStatementProvider, KClass)	Executes a select statement and returns a list (the list will be empty if no records match). This method can be used to execute a select statement that returns a single column. Spring will attempt to retrieve objects of type `KClass` from the result set.
selectOne(SelectStatementProvider, RowMapper)	Executes a select statement and returns a single object (or null if no records match). The row mapper is used to map result sets for row.
selectOne(SelectStatementProvider, KClass)	Executes a select statement and returns a single object (or null if no records match). This method can be used to execute a select statement that returns a single column. Spring will attempt to retrieve an object of type `KClass` from the result set.

The following example shows the most common case: executing a statement that returns multiple rows with a user provided row mapper. The row mapper is passed as a lambda and ignores the row number:

val selectStatement = select(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.selectList(selectStatement) { rs, _ -> // rows is a List of PersonRecord in this case
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == "Yes",
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}

One-Step Method

Select statements can be constructed and executed in a single step with code like the following:

val personRecords: List<PersonRecord> = template.select(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)
}.withRowMapper { rs, _ ->
    PersonRecord(
        id = rs.getInt(id.name()),
        firstName = rs.getString(firstName.name()),
        lastName = rs.getString(lastName.name()),
        birthDate = rs.getDate(birthDate.name()),
        employed = rs.getString(employed.name()) == "Yes",
        occupation = rs.getString(occupation.name()),
        addressId = rs.getInt(addressId.name())
    )
}

There are similar methods for selecting a single row, or executing a select distinct query. A single row select looks like this:

val personRecord: PersonRecord? = template.selectOne(id, firstName, lastName, birthDate, employed, occupation, addressId) {
    from(Person)
    where { id isEqualTo key }
}.withRowMapper { rs, _ ->
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == "Yes",
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}

A distinct query looks like this:

val personRecord: List<PersonRecord> = template.selectDistinct(id, firstName, lastName, birthDate, employed, occupation, addressId) {
    from(Person)
    where { id isLessThan key }
}.withRowMapper { rs, _ ->
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == "Yes",
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}

Multi-Select Statement Support

Multi-select statements are a special case of select statement. All the above information about row mappers applies equally to multi-select statements.

The library does not provide a “one-step” shortcut for multi-select queries. You can execute a multi-select query with the two-step method using either the “selectList” or “selectOne” extension methods as shown above.

Update Method Support

Two-Step Method

Update statements are constructed as shown on the Kotlin overview page. These methods create an UpdateStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

val updateStatement = update(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.update(updateStatement)  // rows is an Int

One-Step Method

Update statements can be constructed and executed in a single step with code like the following:

val rows = template.update(Person) {
    set(firstName).equalTo("Sam")
    where { firstName isEqualTo "Fred" }
}

There a many set mappings that allow setting values to null, constants, etc. There is also a mapping that will only set the column value if the passed value is non-null.

If you wish to update all rows in a table, simply omit the where clause:

val rows = template.update(Person) {
    set(firstName).equalTo("Sam")
}