Kotlin Support for MyBatis3

MyBatis Dynamic SQL includes Kotlin extensions for MyBatis3 that simplify execution of statements generated by the library.

The standard usage patterns for MyBatis Dynamic SQL and MyBatis3 in Java must be modified somewhat for Kotlin. Kotlin interfaces can contain both abstract and non-abstract methods (somewhat similar to Java's default methods in an interface). Using these methods in Kotlin based mapper interfaces will cause a failure with MyBatis because of the underlying Kotlin implementation.

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

All Kotlin support is available in the following packages:

org.mybatis.dynamic.sql.util.kotlin - contains DSL support classes
org.mybatis.dynamic.sql.util.kotlin.elements - contains the basic DSL elements common to all runtimes
org.mybatis.dynamic.sql.util.kotlin.mybatis3 - contains utilities specifically to simplify MyBatis3 based clients

Using the support in these packages, it is possible to create reusable Kotlin classes, interfaces, and extension methods that simplify usage of the library with MyBatis3.

The Kotlin support for MyBatis3 is implemented as utility functions that can be used with MyBatis3 mapper interfaces. There are functions 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 a MyBatis3 mapper method
The second method is a one-step method that uses utility functions to combine these operations into a single step. With this method it is common to build extension methods for MyBatis3 mappers that are specific to a table (this is the code that MyBatis Generator will create)

We will illustrate both approaches below.

Kotlin Dynamic SQL Support Objects

The pattern for the meta-model is the same as shown on the Kotlin overview page. We'll repeat it here to show some specifics for MyBatis3.

import org.mybatis.dynamic.sql.AlisableSqlTable
import org.mybatis.dynamic.sql.util.kotlin.elements.column
import java.sql.JDBCType
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", jdbcType = JDBCType.INTEGER)
        val firstName = column<String>(name = "first_name", jdbcType = JDBCType.VARCHAR)
        val lastName = column(
            name = "last_name",
            jdbcType = JDBCType.VARCHAR,
            typeHandler = "foo.bar.LastNameTypeHandler"
        )
        val birthDate = column<Date>(name = "birth_date", jdbcType = JDBCType.DATE)
        val employed = column(
            name = "employed",
            jdbcType = JDBCType.VARCHAR,
            typeHandler = "foo.bar.StringToBooleanTypeHandler"
        )
        val occupation = column<String>(name = "occupation", jdbcType = JDBCType.VARCHAR)
        val addressId = column<Int>(name = "address_id", jdbcType = JDBCType.INTEGER)
    }
}

Note the use of a "type handler" on the employed and lastName columns. This allows us to use the column as a Boolean in Kotlin, but store the values "Yes" or "No" on the database. This uses the MyBatis3 standard type handler support.

Also note that we specify the "jdbcType" for each column. This is a best practice with MyBatis3 and will avoid errors will nullable fields.

About MyBatis Mappers

Many MyBatis operations can be standardized, and you can use functionality in this library to dramatically reduce the amount of boilerplate code you need to write. In particular, all COUNT, DELETE, and UPDATE statements can be executed with utilities built into the library. The examples below will show how this works.

Many INSERT statements can also be executed with built-in utilities. The only INSERT statements that require custom coding are INSERT statements that return generated keys. For these statements, you must code a custom mapper method with the MyBatis @Options annotation specifying how to retrieve generated keys.

SELECT statements present unique challenges. One of the key functions of MyBatis is the mapping of result sets to objects. This is a very useful capability, but it requires that the mapping between result set and object be predefined and hard coded. Some SELECT statements can be executed without coding custom result maps. This library includes common SELECT support with the following capabilities:

Execute arbitrary SELECT statements and return List<Map<String, Object>> as the return value. This support essentially bypasses MyBatis' result mapping capabilities and returns a low level list of results.
Execute SELECT statements that return a single column of various types (String, Integer, BigDecimal, etc.)

The bottom line is this - if your query returns more than one column, and you want to utilize MyBatis' result mapping functionality, you will need to code a custom result mapping.

This library was initially conceived as a tool to improve the code created by MyBatis Generator - and the "one step" methods shown below are based on the convention used by MyBatis generator where a set of mapper methods is created for each table individually. If you are not using MyBatis generator, or are adding custom queries such as join queries to an application bootstrapped with MyBatis Generator, then it is likely you will need to code custom SELECT methods with custom result maps.

Kotlin Mappers for MyBatis

The pattern we recommend involves two types of mapper methods: standard MyBatis mapper methods and extension methods.

The standard MyBatis mapper abstract methods accept Provider objects created by the library. These methods use the normal MyBatis annotations to specify result mappings, generated key mappings, statement types, etc. Using these methods directly involves two steps: create the provider object, then execute the MyBatis call.

The extension methods will reuse the abstract methods and add functionality to mappers that will build and execute the SQL statements in a one-step process. The extension methods shown below assume that you will create a set of CRUD methods for each table you are accessing (as is the case with code created by MyBatis Generator).

If you create a Kotlin mapper interface that includes both abstract and non-abstract methods, MyBatis will throw errors. By default, Kotlin does not create Java default methods in an interface. For this reason, Kotlin mapper interfaces should only contain the actual MyBatis mapper abstract interface methods. What would normally be coded as default or static methods in a Java mapper interface should be coded as extension methods in Kotlin. For example, a simple MyBatis mapper could be coded like this:

@Mapper
interface PersonMapper {
    @SelectProvider(type = SqlProviderAdapter::class, method = "select")
    @Results(id = "PersonRecordResult", value = [
        Result(column = "a_id", property = "id"),
        Result(column = "first_name", property = "firstName"),
        Result(column = "last_name", property = "lastName"),
        Result(column = "birth_date", property = "birthDate"),
        Result(column = "employed", property = "employed", typeHandler = YesNoTypeHandler::class),
        Result(column = "occupation", property = "occupation"),
        Result(column = "address_id", property = "addressId")
    ])
    fun selectMany(selectStatement: SelectStatementProvider): List<PersonRecord>
}

Then extensions could be added to make a shortcut method as follows:

import org.mybatis.dynamic.sql.util.kotlin.SelectCompleter
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.selectList
import org.mybatis.dynamic.sql.util.kotlin.elements.`as`

private val columnList = listOf(id `as` "A_ID", firstName, lastName, birthDate, employed, occupation, addressId)

fun PersonMapper.select(completer: SelectCompleter) =
    selectList(this::selectMany, columnList, Person, completer)

The extension method shows the use of the SelectCompleter type alias. This is a DSL extension supplied with the library. We will detail its use below. For now see that the extension method can be used in client code to supply a where clause and an order by clause as follows:

val rows = mapper.select {
    where { id isLessThan 100 }
    or {
        employed.isTrue()
        and { occupation isEqualTo "Developer" }
    }
    orderBy(id)
}

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 a MyBatis3 mapper method. MyBatis3 mappers should declare a count method as follows:

@Mapper
interface PersonMapper {
    @SelectProvider(type = SqlProviderAdapter::class, method = "select")
    fun count(selectStatement: SelectStatementProvider): Long
}

This is a standard method for MyBatis Dynamic SQL that executes a query and returns a Long. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonCountMapper

CommonCountMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val countStatement = count() // not shown... see the overview page for examples
val mapper: PersonMapper = getMapper() // not shown
val rows: Long = mapper.count(countStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of count statements. The extension functions will reuse the abstract method and supply everything needed to build the select statement except the where clause:

import org.mybatis.dynamic.sql.util.kotlin.CountCompleter
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.count
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.countDistinct
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.countFrom

fun PersonMapper.count(completer: CountCompleter) = // count(*)
    countFrom(this::count, person, completer)

fun PersonMapper.count(column: BasicColumn, completer: CountCompleter) = // count(column)
    count(this::count, column, person, completer)

fun PersonMapper.countDistinct(column: BasicColumn, completer: CountCompleter) = // count(distinct column)
    countDistinct(this::count, column, person, completer)

The methods are constructed to execute count queries on one specific table - person in this case.

The methods show the use of CountCompleter which is a Kotlin typealias for a function with a receiver that will allow a user to supply a where clause. This also shows use of the Kotlin countFrom, count, and countDistinct methods which are supplied by the library. Those methods will build and execute the select count statements with the supplied where clause. Clients can use the methods as follows:

val rows = mapper.count {
    where { occupation.isNull() }
    and { employed.isFalse() }
}

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

val rows = mapper.count { allRows() }

Delete Method Support

Two-Step Method

Delete statements are constructed as shown on the Kotlin overview page. This method creates a DeleteStatementProvider that can be executed with a MyBatis3 mapper method. MyBatis3 mappers should declare a delete method as follows:

@Mapper
interface PersonMapper {
   @DeleteProvider(type = SqlProviderAdapter::class, method = "delete")
   fun delete(deleteStatement: DeleteStatementProvider): Int
}

This is a standard method for MyBatis Dynamic SQL that executes a query and returns an Int - the number of rows deleted. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonDeleteMapper

CommonDeleteMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val deleteStatement = deleteFrom() // not shown... see the overview page for examples
val mapper: PersonMapper = getMapper() // not shown
val rows: Int = mapper.delete(deleteStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of delete statements. The extension functions will reuse the abstract method and supply everything needed to build the delete statement except the where clause:

import org.mybatis.dynamic.sql.util.kotlin.DeleteCompleter
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.deleteFrom

fun PersonMapper.delete(completer: DeleteCompleter) =
    deleteFrom(this::delete, person, completer)

The method is constructed to execute delete statements on one specific table - person in this case.

The method shows the use of DeleteCompleter which is a Kotlin typealias for a function with a receiver that will allow a user to supply a where clause. This also shows use of the Kotlin deleteFrom method which are supplied by the library. Those methods will build and execute the delete statement with the supplied where clause. Clients can use the method as follows:

val rows = mapper.delete {
    where { occupation.isNull() }
}

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

val rows = mapper.delete { allRows() }

Single Row Insert Statement

Two-Step Method

Single row insert statements are constructed as shown on the Kotlin overview page. This method creates an InsertStatementProvider that can be executed with a MyBatis3 mapper method. MyBatis3 mappers should declare an insert method as follows:

@Mapper
interface PersonMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insert")
    fun insert(insertStatement: InsertStatementProvider<PersonRecord>): Int
}

This is a standard method for MyBatis Dynamic SQL that executes an insert and returns a Int - the number of rows inserted. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonInsertMapper<T>

CommonInsertMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val insertStatement = insert() // not shown, see overview page
val mapper: PersonMapper = getMapper() // not shown
val rows: Int = mapper.insert(insertStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of single record insert statements. The extension functions will reuse the abstract method and supply everything needed to build the insert statement:

import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insert

fun PersonMapper.insert(row: PersonRecord) =
    insert(this::insert, row, Person) {
        map(id) toProperty "id"
        map(firstName) toProperty "firstName"
        map(lastName) toProperty "lastName"
        map(birthDate) toProperty "birthDate"
        map(employed) toProperty "employed"
        map(occupation) toProperty "occupation"
        map(addressId) toProperty "addressId"
    }

This extension method reuses the mapper method and supplies all the column mappings. Clients can use the method as follows:

val record = PersonRecord(100, "Joe", LastName("Jones"), Date(), true, "Developer", 1)
val mapper: PersonMapper = getMapper() // not shown
val rows = mapper.insert(record)

Generated Key Support

Single record insert statements support returning a generated key using normal MyBatis generated key support. When generated keys are expected you must code the mapper method manually and supply the @Options annotation that configures generated key support. You cannot use the built-in base interface when there are generated keys. For example:

interface GeneratedAlwaysMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insert")
    @Options(useGeneratedKeys = true, keyProperty = "row.id,row.fullName", keyColumn = "id,full_name")
    fun insert(insertStatement: InsertStatementProvider<GeneratedAlwaysRecord>): Int
}

This method will return two generated values in each row: id and full_name. The values will be placed into the row properties id and fullName respectively.

General Insert Statement

Two-Step Method

General insert statements are constructed as shown on the Kotlin overview page. This method creates a GeneralInsertStatementProvider that can be executed with a MyBatis3 mapper method. MyBatis3 mappers should declare a generalInsert method as follows:

@Mapper
interface PersonMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "generalInsert")
    fun generalInsert(insertStatement: GeneralInsertStatementProvider): Int
}

This is a standard method for MyBatis Dynamic SQL that executes an insert and returns a Int - the number of rows inserted. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonInsertMapper<T>

CommonInsertMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val insertStatement = insertInto() // not shown, see overview page
val mapper: PersonMapper = getMapper() // not shown
val rows: Int = mapper.generalInsert(insertStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of general insert statements. The extension functions will reuse the abstract method and supply everything needed to build the insert statement except the values to insert:

import org.mybatis.dynamic.sql.util.kotlin.GeneralInsertCompleter
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insertInto

fun PersonMapper.generalInsert(completer: GeneralInsertCompleter) =
    insertInto(this::generalInsert, person, completer)

The method is constructed to execute insert statements on one specific table - person in this case.

The method shows the use of GeneralInsertCompleter which is a Kotlin typealias for a function with a receiver that will allow a user to supply values to insert. This also shows use of the Kotlin insertInto method which are supplied by the library. Those methods will build and execute the insert statement with the supplied values. Clients can use the method as follows:

val rows = mapper.generalInsert {
    set(id) toValue 100
    set(firstName) toValue "Joe"
    set(lastName) toValue LastName("Jones")
    set(employed) toValue true
    set(occupation) toValue "Developer"
    set(addressId) toValue 1
    set(birthDate) toValue Date()
}

Generated Key Support

You cen retrieve generated keys from general insert statements if you use the two-step method. You cannot use the built-in base interface when there are generated keys. First, code the abstract mapper method as follows:

interface GeneratedAlwaysMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "generalInsert")
    @Options(useGeneratedKeys = true, keyProperty="parameters.id,parameters.fullName", keyColumn = "id,full_name")
    fun generalInsert(insertStatement: GeneralInsertStatementProvider): Int
}

This method will return two generated values: id and full_name. The values will be placed into the parameter map in the GeneralInsertStatementProvider with keys id and fullName respectively.

The method can be used as follows:

val mapper = getMapper() // not shown
val insertStatement = insertInto(generatedAlways) {
    set(firstName).toValue("Fred")
    set(lastName).toValue("Flintstone")
}

val rows = mapper.generalInsert(insertStatement)

After the statement completes, then generated keys are available in the mapper:

val id = insertStatement.parameters["id"] as Int
val fullName = insertStatement.parameters["fullName"] as String

Multi-Row Insert Statement

Two-Step Method

Multi-row insert statements are constructed as shown on the Kotlin overview page. This method creates a MultiRowInsertStatementProvider that can be executed with a MyBatis3 mapper method. MyBatis3 mappers should declare an insertMultiple method as follows:

@Mapper
interface PersonMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insertMultiple")
    fun insertMultiple(insertStatement: MultiRowInsertStatementProvider<PersonRecord>): Int
}

This is a standard method for MyBatis Dynamic SQL that executes an insert and returns a Int - the number of rows inserted. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonInsertMapper<T>

CommonInsertMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val insertStatement = insertMultiple() // not shown, see overview page
val mapper: PersonMapper = getMapper() // not shown
val rows: Int = mapper.insertMultiple(insertStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of multi-row insert statements. The extension functions will reuse the abstract method and supply everything needed to build the insert statement except the values to insert:

import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insertMultiple

fun PersonMapper.insertMultiple(vararg records: PersonRecord) =
    insertMultiple(records.toList())

fun PersonMapper.insertMultiple(records: Collection<PersonRecord>) =
    insertMultiple(this::insertMultiple, records, person) {
        map(id) toProperty "id"
        map(firstName) toProperty "firstName"
        map(lastName) toProperty "lastName"
        map(birthDate) toProperty "birthDate"
        map(employed) toProperty "employed"
        map(occupation) toProperty "occupation"
        map(addressId) toProperty "addressId"
    }

The method is constructed to execute multi-row insert statements on one specific table - person in this case.

This extension method reuses the mapper method and supplies all the column mappings. Clients can use the method as follows:

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

val rows = mapper.insertMultiple(record1, record2)

Generated Key Support

Multi-row insert statements support returning a generated key using normal MyBatis generated key support. However, generated keys require some care for multi-row insert statements. In this section we will show how to use the library's built-in support. When generated keys are expected you must code the mapper method manually and supply the @Options annotation that configures generated key support. You cannot use the built-in base interface when there are generated keys. For example:

interface GeneratedAlwaysMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insertMultipleWithGeneratedKeys")
    @Options(useGeneratedKeys = true, keyProperty="records.id,records.fullName", keyColumn = "id,full_name")
    fun insertMultiple(insertStatement: String, @Param("records") records: List<GeneratedAlwaysRecord>): Int
}

Note that this method uses a different SQLProviderAdapter method and also uses a decomposed version of the provider class. This is done to code around some limitations in MyBatis3. This method will return two generated values in each row: id and full_name. The values will be placed into the record properties id and fullName respectively.

For the one-step method, the mapper extension method should use a different utility function as shown below:

import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insertMultipleWithGeneratedKeys

fun GeneratedAlwaysMapper.insertMultiple(records: Collection<GeneratedAlwaysRecord>): Int {
    return insertMultipleWithGeneratedKeys(this::insertMultiple, records, generatedAlways) {
        map(firstName).toProperty("firstName")
        map(lastName).toProperty("lastName")
    }
}

Batch Insert Statement

Two-Step Method

Batch insert statements are constructed as shown on the Kotlin overview page. This method creates a BatchInsert that can be executed with a MyBatis3 mapper method.

Batch inserts will reuse the regular insert method created for single record inserts. It is also convenient to create a method to flush the batch statements - this causes a commit and returns detailed information about the batch such as update counts. The methods are coded as follows:

import org.apache.ibatis.annotations.Flush
import org.apache.ibatis.annotations.InsertProvider
import org.apache.ibatis.executor.BatchResult

@Mapper
interface PersonMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insert")
    fun insert(insertStatement: InsertStatementProvider<PersonRecord>): Int

    @Flush
    fun flush(): List<BatchResult>
}

These are standard methods for MyBatis. Note that the return value of the "insert" statement will NOT be the number of rows when using batch mode operations. In batch mode the rows are not actually inserted until the statements are flushed, or the session is committed. In batch mode, the return value is a constant with no actual meaning. The methods can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonInsertMapper<T>

CommonInsertMapper can also be used on its own if you inject it into a MyBatis configuration.

MyBatis batch executions are coded as multiple invocations of a simple insert method. The difference is in the construction of the mapper. The SqlSession associated with the mapper must be in "batch mode". This is accomplished when opening the session. For example:

import org.apache.ibatis.session.ExecutorType
import org.apache.ibatis.session.SqlSession
import org.apache.ibatis.session.SqlSessionFactory

val sqlSessionFactory: SqlSessionFactory = getSessionFactory() // not shown
val sqlSession: SqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)
val mapper: PersonMaper = sqlSession.getMapper(PersonMapper::class.java)

The mapper is now associated with a BATCH session. The mapper method can be used as follows:

import org.apache.ibatis.executor.BatchResult
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insertBatch

val sqlSessionFactory: SqlSessionFactory = getSessionFactory() // not shown
val sqlSession: SqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)
val mapper: PersonMapper = sqlSession.getMapper(PersonMapper::class.java)

val batchInsert = insertBatch() // not shown, see overview page
batchInsert.execute(mapper) // see note below about return value
val batchResults = mapper.flush()

Note the use of the extension function BatchInsert.execute(mapper). This function simply loops over all insert statements in the batch and executes them with the supplied mapper. Note also that BatchInsert.execute(mapper) will return a List<Int>. However, when the mapper is in batch mode the values in the list will not be useful. In batch mode you must execute the flush method (or sqlSession.flushStatements()) to obtain update counts. The flush call will also commit the batch. Note that this built-in support executes all inserts as a single transaction. If you have a large batch of records and want to process intermediate commits, you can do so by writing code to loop through the list of insert statements obtained from BatchInsert.insertStatements() and execute flush/commit as desired.

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of batch insert statements. The extension functions will reuse the abstract method and supply everything needed to build the insert statement except the values to insert:

import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insertBatch

fun PersonMapper.insertBatch(vararg records: PersonRecord): List<Int> =
    insertBatch(records.toList())

fun PersonMapper.insertBatch(records: Collection<PersonRecord>): List<Int> =
    insertBatch(this::insert, records, person) {
        map(id) toProperty "id"
        map(firstName) toProperty "firstName"
        map(lastName) toProperty "lastName"
        map(birthDate) toProperty "birthDate"
        map(employed) toProperty "employed"
        map(occupation) toProperty "occupation"
        map(addressId) toProperty "addressId"
    }

The method is constructed to execute batch insert statements on one specific table - person in this case.

This extension method reuses the mapper method and supplies all the column mappings. Clients can use the method as follows:

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

val sqlSessionFactory: SqlSessionFactory = getSessionFactory() // not shown
val sqlSession: SqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)
val mapper: PersonMapper = sqlSession.getMapper(PersonMapper::class.java)
mapper.insertBatch(record1, record2)
val batchResults = mapper.flush()

Generated Key Support

Batch insert statements support returning a generated key using normal MyBatis generated key support. If you code the @Options annotation on the insert statement, then the generated keys will be populated into the input records when the transaction is committed or flushed. When generated keys are expected you must code the mapper method manually and supply the @Options annotation that configures generated key support. You cannot use the built-in base interface when there are generated keys. For example:

interface GeneratedAlwaysMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insert")
    @Options(useGeneratedKeys = true, keyProperty="record.id,record.fullName", keyColumn = "id,full_name")
    fun insert(insertStatement: InsertStatementProvider<GeneratedAlwaysRecord>): Int
}

This insert method can be used with mappers in batch mode as shown above.

Insert Select Statement

Two-Step Method

Insert select statements are constructed as shown on the Kotlin overview page. This method creates an InsertSelectStatementProvider that can be executed with a MyBatis3 mapper method. MyBatis3 mappers should declare a generalInsert method as follows:

@Mapper
interface PersonMapper {
    @InsertProvider(type = SqlProviderAdapter::class, method = "insertSelect")
    fun insertSelect(insertSelectStatement: InsertSelectStatementProvider): Int
}

This is a standard method for MyBatis Dynamic SQL that executes an insert and returns a Int - the number of rows inserted. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonInsertMapper<T>

CommonInsertMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val insertStatement = insertSelect() // not shown, see overview page
val mapper: PersonMapper = getMapper() // not shown
val rows: Int = mapper.insertSelect(insertStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of insert select statements. The extension functions will reuse the abstract method and supply everything needed to build the insert statement except the values to insert:

import org.mybatis.dynamic.sql.util.kotlin.InsertSelectCompleter
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.insertSelect

fun PersonMapper.insertSelect(completer: InsertSelectCompleter) =
    insertSelect(this::insertSelect, person, completer)

The method is constructed to execute insert statements on one specific table - person in this case.

The method shows the use of InsertSelectCompleter which is a Kotlin typealias for a function with a receiver that will allow a user to supply value column list ans select statement. This also shows use of the Kotlin insertSelect method which is supplied by the library. This method will build and execute the insert statement with the supplied column list and select statement. Clients can use the method as follows:

val mapper = getMapper() // not shown
val rows = mapper.insertSelect {
    columns(id, firstName, lastName, employed, occupation, addressId, birthDate)
    select(add(id, constant<Int>("100")), firstName, lastName, employed, occupation, addressId, birthDate) {
        from(person)
        orderBy(id)
    }
}

Generated Key Support

Generated keys with insert select are not directly supported by library utilities and can be quite challenging. There are examples in the source repository if you have a need to do this.

Select Method Support

Two-Step Method

Select statements are constructed as shown on the Kotlin overview page. Those methods create a SelectStatementProvider that can be executed with MyBatis3 mapper methods. We recommend creating two mapper methods: one that returns a list of records, and another that returns a single record or null:

@Mapper
interface PersonMapper {
    @SelectProvider(type = SqlProviderAdapter::class, method = "select")
    @Results(id = "PersonResult", value = [
        Result(column = "A_ID", property = "id", jdbcType = JdbcType.INTEGER, id = true),
        Result(column = "first_name", property = "firstName", jdbcType = JdbcType.VARCHAR),
        Result(column = "last_name", property = "lastName", jdbcType = JdbcType.VARCHAR,
                typeHandler = LastNameTypeHandler::class),
        Result(column = "birth_date", property = "birthDate", jdbcType = JdbcType.DATE),
        Result(column = "employed", property = "employed", jdbcType = JdbcType.VARCHAR,
                typeHandler = YesNoTypeHandler::class),
        Result(column = "occupation", property = "occupation", jdbcType = JdbcType.VARCHAR),
        Result(column = "address_id", property = "addressId", jdbcType = JdbcType.INTEGER)])
    fun selectMany(selectStatement: SelectStatementProvider): List<PersonRecord>

    @SelectProvider(type = SqlProviderAdapter::class, method = "select")
    @ResultMap("PersonResult")
    fun selectOne(selectStatement: SelectStatementProvider): PersonRecord?
}

Note that the result map is shared between the two methods.

The methods can be used as follows:

val mapper: PersonMapper = getMapper() // not shown

val selectStatement = select() // not shown... see the overview page for examples
val rows: List<PersonRecord> = mapper.selectMany(selectStatement)

val selectOneStatement = select() // not shown... see the overview page for examples
val row: PersonRecord? = mapper.selectOne(selectStatement)

Note that the select statement is the same whether multiple or single rows are expected. Also note that a select distinct can be executed with the selectMany method.

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of select statements. The extension functions will reuse the abstract methods and supply the table and column list for the statement. We recommend three extension methods for select multiple records, select multiple records with the distinct keyword, and selecting a single record:

import org.mybatis.dynamic.sql.util.kotlin.SelectCompleter
import org.mybatis.dynamic.sql.util.kotlin.elements.`as`
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.selectDistinct
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.selectList
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.selectOne

private val columnList = listOf(id `as` "A_ID", firstName, lastName, birthDate, employed, occupation, addressId)

fun PersonMapper.selectOne(completer: SelectCompleter) =
    selectOne(this::selectOne, columnList, Person, completer)

fun PersonMapper.select(completer: SelectCompleter) =
    selectList(this::selectMany, columnList, Person, completer)

fun PersonMapper.selectDistinct(completer: SelectCompleter) =
    selectDistinct(this::selectMany, columnList, Person, completer)

The methods are constructed to execute select statements on one specific table - person in this case - and with a fixed column list that matches the MyBatis result mapping.

The methods show the use of SelectCompleter which is a Kotlin typealias for a function with a receiver that will allow a user to supply a where clause. This also shows use of the Kotlin selectDistinct, selectList, and selectOne methods which are supplied by the library. Those methods will build and execute the select statement. Clients can use the methods as follows:

val mapper = getMapper() // not shown
val distinctRecords = mapper.selectDistinct {
    where { id isGreaterThan 5 }
}

val rows = mapper.select {
    where { firstName.isIn("Fred", "Barney") }
    orderBy(id)
    limit(3)
}

val record = mapper.selectOne {
    where { id isEqualTo 1 }
}

The general selectOne method can also be used to implement a selectByPrimaryKey method:

fun PersonMapper.selectByPrimaryKey(id_: Int) =
    selectOne {
        where { id isEqualTo id_ }
    }

There is a utility method that will select all rows in a table:

val rows = mapper.select { allRows() }

The following query will select all rows in a specified order:

val rows = mapper.select {
    allRows()
    orderBy(lastName, firstName)
}

Join Support

You can implement functions that support a reusable select method based on a join. In this way, you can create the start of the select statement (the column list and join specifications) and allow the user to supply where clauses and other parts of a select statement. For example, you could code a mapper extension method like this:

fun PersonWithAddressMapper.select(completer: SelectCompleter): List<PersonWithAddress> =
    select(
        id `as` "A_ID", firstName, lastName, birthDate,
        employed, occupation, address.id, address.streetAddress, address.city, address.state
    ) {
        from(person, "p")
        fullJoin(address) {
            on(person.addressId) equalTo address.id
        }
        completer()
    }.run(this::selectMany)

This method creates the start of a select statement with a join, and accepts user input to complete the statement. This shows reuse of a regular MyBatis mapper method - selectMany as shown above - with a result map that matches the select list. Like other select methods, this method can be used as follows:

val records = mapper.select {
    where { id isLessThan 100 }
    limit(5)
}

Multi-Select Statement Support

Multi-select statements are a special case of select statement. All the above information about MyBatis 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 a "selectMany" or "selectOne" mapper method as shown above.

Update Method Support

Two-Step Method

Update statements are constructed as shown on the Kotlin overview page. This method creates an UpdateStatementProvider that can be executed with a MyBatis3 mapper method. MyBatis3 mappers should declare an update method as follows:

@Mapper
interface PersonMapper {
    @UpdateProvider(type = SqlProviderAdapter::class, method = "update")
    fun update(updateStatement: UpdateStatementProvider): Int
}

This is a standard method for MyBatis Dynamic SQL that executes an update and returns an Int - the number of rows updated. This method can also be implemented by using a built-in base interface as follows:

@Mapper
interface PersonMapper : CommonUpdateMapper

CommonUpdateMapper can also be used on its own if you inject it into a MyBatis configuration.

The mapper method can be used as follows:

val updateStatement = update() // not shown... see the overview page for examples
val mapper: PersonMapper = getMapper() // not shown
val rows: Int = mapper.update(updateStatement)

One-Step Method

You can use built-in utility functions to create mapper extension functions that simplify execution of update statements. The extension functions will reuse the abstract method and supply everything needed to build the update statement except the set and where clauses:

import org.mybatis.dynamic.sql.util.kotlin.UpdateCompleter
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.update

fun PersonMapper.update(completer: UpdateCompleter) =
    update(this::update, person, completer)

The method is constructed to execute update statements on one specific table - person in this case.

The method shows the use of UpdateCompleter which is a Kotlin typealias for a function with a receiver that will allow a user to supply set phrases and a where clause. This also shows use of the Kotlin update method which is supplied by the library. Those methods will build and execute the update statement with the supplied set phrases and where clause. Clients can use the method as follows:

val rows = mapper.update {
    set(occupation).equalTo("Programmer")
    where { id isEqualTo 100 }
    and(firstName, isEqualTo("Joe"))
}

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

// update all rows...
val rows = mapper.update {
    set(occupation) equalTo "Programmer"
}

It is also possible to write utility methods that will set values. For example:

fun KotlinUpdateBuilder.updateSelectiveColumns(record: PersonRecord) =
    apply {
        set(id) equalToWhenPresent record::id
        set(firstName) equalToWhenPresent record::firstName
        set(lastName) equalToWhenPresent record::lastName
        set(birthDate) equalToWhenPresent record::birthDate
        set(employed) equalToWhenPresent record::employed
        set(occupation) equalToWhenPresent record::occupation
        set(addressId) equalToWhenPresent record::addressId
    }

This method will selectively set values if corresponding fields in a record are non-null. This method can be used as follows:

val rows = mapper.update {
    updateSelectiveColumns(updateRecord)
    where { id isEqualTo 100 }
}

If you wish to implement an "update by primary key" method, you can reuse the extension method as follows:

fun PersonMapper.updateByPrimaryKey(record: PersonRecord) =
    update {
        set(firstName) equalToOrNull record::firstName
        set(lastName) equalToOrNull record::lastName
        set(birthDate) equalToOrNull record::birthDate
        set(employed) equalToOrNull record::employed
        set(occupation) equalToOrNull record::occupation
        set(addressId) equalToOrNull record::addressId
        where { id isEqualTo record.id!! }
    }

fun PersonMapper.updateByPrimaryKeySelective(record: PersonRecord) =
    update {
        set(firstName) equalToWhenPresent record::firstName
        set(lastName) equalToWhenPresent record::lastName
        set(birthDate) equalToWhenPresent record::birthDate
        set(employed) equalToWhenPresent record::employed
        set(occupation) equalToWhenPresent record::occupation
        set(addressId) equalToWhenPresent record::addressId
        where { id isEqualTo record.id!! }
    }

The method updateByPrimaryKey will update every column - if a property in the record is null, the column will be set to null.

The method updateByPrimaryKeySelective will update every column that has a non-null corresponding property in the record. If a property in the record is null, the column will not be updated.

Files

kotlinMyBatis3.md

Latest commit

History

kotlinMyBatis3.md

File metadata and controls

Kotlin Support for MyBatis3

Kotlin Dynamic SQL Support Objects

About MyBatis Mappers

Kotlin Mappers for MyBatis

Count Statements

Two-Step Method

One-Step Method

Delete Method Support

Two-Step Method

One-Step Method

Single Row Insert Statement

Two-Step Method

One-Step Method

Generated Key Support

General Insert Statement

Two-Step Method

One-Step Method

Generated Key Support

Multi-Row Insert Statement

Two-Step Method

One-Step Method

Generated Key Support

Batch Insert Statement

Two-Step Method

One-Step Method

Generated Key Support

Insert Select Statement

Two-Step Method

One-Step Method

Generated Key Support

Select Method Support

Two-Step Method

One-Step Method

Join Support

Multi-Select Statement Support

Update Method Support

Two-Step Method

One-Step Method