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() }

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() }

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.

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

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")
    }
}

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.

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.

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

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.