Kotlin Support for Spring

MyBatis Dynamic SQL includes Kotlin extension methods that enable an SQL DSL for Kotlin. This is the recommended method of using the library in Kotlin with Spring JDBC template.

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 - contains extension methods and utilities to enable an idiomatic Kotlin DSL for MyBatis Dynamic SQL. These objects can be used for clients using any execution target (i.e. MyBatis3 or Spring JDBC Templates)
org.mybatis.dynamic.sql.util.kotlin.spring - contains utlities specifically to simplify integration with Spring JDBC Template

The Kotlin support for Spring 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. With the first method you build the appropriate SQL provider object as a separate step before executing the SQL. The second method combines these two operations into a single step. We will illustrate both approaches below.

Kotlin Dynamic SQL Support Objects

Because Kotlin does not support static class members, we recommend a simpler pattern for creating the class containing the support objects. For example:

object PersonDynamicSqlSupport {
    object Person : SqlTable("Person") {
        val id = column<Int>("id", JDBCType.INTEGER)
        val firstName = column<String>("first_name", JDBCType.VARCHAR)
        val lastName = column<String>("last_name", JDBCType.VARCHAR)
        val birthDate = column<Date>("birth_date", JDBCType.DATE)
        val employed = column<String>("employed", JDBCType.VARCHAR)
        val occupation = column<String>("occupation", JDBCType.VARCHAR)
        val addressId = column<Int>("address_id", JDBCType.INTEGER)
    }
}

This object is a singleton containing the SqlTable and SqlColumn objects that map to the database table.

Important Note: Spring JDBC template does not support type handlers, so column definitions in the support class should match the data types of the corresponding column.

Count Method Support

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

The library supports three types of count statements:

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

The DSL for count methods looks like this:

    // count(*)
    val countStatement = countFrom(Person) {  // countStatement is a SelectStatementProvider
        where(id, isLessThan(4))
    }

    // count(column)
    val countStatement = countColumn(lastName).from(Person) {  // countStatement is a SelectStatementProvider
        allRows()
    }

    // count(distinct column)
    val countStatement = countDistinctColumn(lastName).from(Person) {  // countStatement is a SelectStatementProvider
        allRows()
    }

Note the somewhat awkward method names countColumn, and countDistinctColumn. The methods are named this way to avoid a name collision with other methods in the SqlBuilder. This awkwardness can be avoided by using the one step method shown below.

These methods create a SelectStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

    val template: NamedParameterJdbcTemplate = getTemplate() // not shown
    val rows = template.count(countStatement) // rows is a Long

This is the two step execution process. This can be combined into a single step with code like the following:

    val rows = template.countFrom(Person) {
        where(id, isLessThan(4))
    }

    val rows = template.count(lastName).from(Person) {
        where(id, isLessThan(4))
    }

    val rows = template.countDistinct(lastName).from(Person) {
        where(id, isLessThan(4))
    }

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

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

Delete Method Support

Delete method support enables the creation of methods that execute a delete statement allowing a user to specify a where clause at runtime, but abstracting away all other details.

The DSL for delete methods looks like this:

    val deleteStatement = deleteFrom(Person) {  // deleteStatement is a DeleteStatementProvider
        where(id, isLessThan(4))
    }

This code creates a DeleteStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

    val template: NamedParameterJdbcTemplate = getTemplate() // not shown
    val rows = template.delete(deleteStatement)  // rows is an Int

This is the two step execution process. This can be combined into a single step with code like the following:

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

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

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

Insert Record Method Support

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

The DSL for insert methods looks like this:

    val record = PersonRecord(100, "Joe", "Jones", Date(), "Yes", "Developer", 1)

    val insertStatement = insert(record).into(Person) {  // insertStatement is an InsertStatementProvider
        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 code creates an InsertStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

    val template: NamedParameterJdbcTemplate = getTemplate() // not shown
    val rows = template.insert(insertStatement)  // rows is an Int

This is the two step execution process. These steps can be combined into a single step with code like the following:

    val record = PersonRecord(100, "Joe", "Jones", Date(), "Yes", "Developer", 1)

    val rows = template.insert(record, Person) {
        map(id).toProperty("id")
        map(firstName).toProperty("firstName")
        map(lastName).toProperty("lastName")
        map(birthDate).toProperty("birthDate")
        map(employed).toProperty("employed")
        map(occupation).toPropertyWhenPresent("occupation", record::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.

General Insert Method Support

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

The DSL for general insert methods looks like this:

    val insertStatement = insertInto(Person) {  // insertStatement is a GeneralInsertStatementProvider
        set(id).toValue(100)
        set(firstName).toValue("Joe")
        set(lastName).toValue("Jones")
        set(birthDate).toValue(Date())
        set(employed).toValue("Yes")
        set(occupation).toValue("Developer")
        set(addressId).toValue(1)
    }

This code creates a GeneralInsertStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

    val template: NamedParameterJdbcTemplate = getTemplate() // not shown
    val rows = template.insert(insertStatement)  // rows is an Int

This is the two step execution process. These steps can be combined into 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("Yes")
        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.

Select Method Support

Select method support enables the creation of methods that execute a query allowing a user to specify a where clause and/or an order by clause and/or pagination clauses at runtime, but abstracting away all other details.

The DSL for select methods looks like this:

    val selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,  // selectStatement is a SelectStatementProvider
        addressId).from(Person) {
        where(id, isLessThan(5))
        and(id, isLessThan(4)) {
            and(id, isLessThan(3)) {
                and(id, isLessThan(2))
            }
        }
        orderBy(id)
        limit(3)
    }

This code creates a SelectStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

    val template: NamedParameterJdbcTemplate = getTemplate() // not shown
    val rows = template.selectList(selectStatement) { rs, _ -> // rows is a List of PersonRecord in this case
        val record = PersonRecord()
        record.id = rs.getInt(1)
        record.firstName = rs.getString(2)
        record.lastName = rs.getString(3)
        record.birthDate = rs.getTimestamp(4)
        record.employed = rs.getString(5)
        record.occupation = rs.getString(6)
        record.addressId = rs.getInt(7)
        record
    }

Note that you must provide a row mapper to tell Spring JDBC how to create result objects.

This is the two step execution process. This can be combined into a single step with code like the following:

    val rows = template.select(id, firstName, lastName, birthDate, employed, occupation, addressId)
        .from(Person) {
            where(id, isLessThan(4)) {
                and(occupation, isNotNull())
            }
            and(occupation, isNotNull())
            orderBy(id)
            limit(3)
        }.withRowMapper { rs, _ ->
            val record = PersonRecord()
            record.id = rs.getInt(1)
            record.firstName = rs.getString(2)
            record.lastName = rs.getString(3)
            record.birthDate = rs.getTimestamp(4)
            record.employed = rs.getString(5)
            record.occupation = rs.getString(6)
            record.addressId = rs.getInt(7)
            record
        }

There are similar methods for selecing a single row, or executing a select distinct query. For example, you could implement a "select by primary key" method using code like this:

    val record = template.selectOne(id, firstName, lastName, birthDate, employed, occupation, addressId)
        .from(Person) {
            where(id, isEqualTo(key))
        }.withRowMapper { rs, _ ->
            val record = PersonRecord()
            record.id = rs.getInt(1)
            record.firstName = rs.getString(2)
            record.lastName = rs.getString(3)
            record.birthDate = rs.getTimestamp(4)
            record.employed = rs.getString(5)
            record.occupation = rs.getString(6)
            record.addressId = rs.getInt(7)
            record
        }

In this case, the data type for record would be PersonRecord? - a nullable value.

There is also an extention method that can be used to select all rows in a table:

    val rows = template.select(id, firstName, lastName, birthDate, employed, occupation, addressId)
        .from(Person) {
            allRows()
            orderBy(id)
        }.withRowMapper { rs, _ ->
            val record = PersonRecord()
            record.id = rs.getInt(1)
            record.firstName = rs.getString(2)
            record.lastName = rs.getString(3)
            record.birthDate = rs.getTimestamp(4)
            record.employed = rs.getString(5)
            record.occupation = rs.getString(6)
            record.addressId = rs.getInt(7)
            record
        }

Note that we have supplied an order by clause as well.

Update Method Support

Update method support enables the creation of methods that execute an update allowing a user to specify SET clauses and/or a WHERE clause, but abstracting away all other details.

The DSL for delete methods looks like this:

    val updateStatement = update(Person) {  // updateStatement is an UpdateStatementProvider
        set(firstName).equalTo("Sam")
        where(firstName, isEqualTo("Fred"))
    }

This code creates an UpdateStatementProvider that can be executed with an extension method for NamedParameterJdbcTemplate like this:

    val template: NamedParameterJdbcTemplate = getTemplate() // not shown
    val rows = template.update(updateStatement)  // rows is an Int

This is the two step execution process. This can be combined into a single step with code like the following:

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

There a many different set mappings the 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")
    }

陈振远/mybatis-dynamic-sql

Kotlin Support for Spring

Kotlin Dynamic SQL Support Objects

Count Method Support

Delete Method Support

Insert Record Method Support

General Insert Method Support

Select Method Support

Update Method Support

简介

发行版

贡献者

近期动态

陈振远/mybatis-dynamic-sql .gitee-modal { width: 500px !important; }

Kotlin Support for Spring

Kotlin Dynamic SQL Support Objects

Count Method Support

Delete Method Support

Insert Record Method Support

General Insert Method Support

Select Method Support

Update Method Support

简介

发行版

贡献者

近期动态

搜索帮助

陈振远/mybatis-dynamic-sql