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.
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.
A count query is a specialized select - it returns a single column - typically a long - and supports joins and a where clause.
The DSL for count methods looks like this:
val countStatement = countFrom(Person) { // countStatement is a SelectStatementProvider
where(id, isLessThan(4))
}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.count(countStatement) // rows is a LongThis 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))
}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 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 IntThis 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 method support enables the creation of arbitrary insert statements.
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 IntThis is the two step execution process. This 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.
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 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 IntThis 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")
}