Package Structure

We have implemented all Kotlin DSL functions as “top level” functions in their respective packages, so they can be used with a wildcard import statement. Until you become more familiar with the package structure, it is easiest to simply import the packages based on the type of object you wish to create.

To fully understand the package structure, it is important to understand the different types of objects that can be generated by the DSL. In general, the DSL can be used to generate the following types of objects:

1. “Model” objects are generated by the DSL, but are not rendered into a “provider”. For most users these objects can be considered intermediate objects and will not need to be accessed directly. However, if you want to implement a custom rendering strategy then you might need to work with “model” objects (this is an unusual use case)
2. “Provider” objects have been rendered into a form that can be used with SQL execution engines directly. Currently, the library supports rendering for MyBatis3 and Spring JDBC Template. Most users will interact with “provider” objects in some form or another

When creating model objects, import the following packages:

import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.model.*

When creating provider objects rendered for MyBatis3 (and using other MyBatis3 specific functions), import the following packages:

import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.*

When creating provider objects rendered for Spring JDBC Template (and using other Spring specific functions), import the following packages:

import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.spring.*

Every example shown on this page will compile and run accurately with either set of import statements. The only difference is the final object type produced by the library.

Kotlin Dynamic SQL Support Objects

MyBatis Dynamic SQL relies on a database “meta model” - objects that describe database tables and columns.

The pattern shown below is similar to the pattern recommended with Java. Kotlin does not support static class members, so the pattern for Kotlin varies a bit from Java by using a combination of Kotlin object and class. Like the Java pattern, this pattern will allow you to use table and column names in a “qualified” or “un-qualified” manner that looks like natural SQL. For example, in the following a column could be referred to as firstName or person.firstName.

import org.mybatis.dynamic.sql.AlisableSqlTable
import org.mybatis.dynamic.sql.util.kotlin.elements.column
import java.util.Date

object PersonDynamicSqlSupport {
    val person = Person()
    val id = person.id
    val firstName = person.firstName
    val lastName = person.lastName
    val birthDate = person.birthDate
    val employed = person.employed
    val occupation = person.occupation

    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<String>(name = "last_name", jdbcType = JDBCType.VARCHAR)
        val birthDate = column<Date>(name = "birth_date", jdbcType = JDBCType.DATE)
        val employed = column<Boolean>(
           name = "employed",
           jdbcType = JDBCType.VARCHAR,
           typeHandler = "examples.kotlin.mybatis3.canonical.YesNoTypeHandler"
        )
        val occupation = column<String>(name = "occupation", jdbcType = JDBCType.VARCHAR)
        val addressId = column<Int>(name = "address_id", jdbcType = JDBCType.INTEGER)
    }
}

Notes:

1. The outer object is a singleton containing the AlisableSqlTable and SqlColumn objects that map to the database table.
2. The inner AlisableSqlTable is declared as a class rather than an object - this allows you to create additional instances for use in self-joins.
3. Note the use of the column extension function. This function accepts different parameters for the different attributes that can be assigned to a column (such as a MyBatis3 type handler, or a custom rendering strategy). We recommend using this extension function rather than the corresponding column and withXXX methods in the Java native DSL because the extension method will retain the non-nullable type information associated with the column.

Statements

The DSL will generate a wide variety of SQL statements. We'll cover the details below with examples for each statement type. Included with both the MyBatis3 and Spring support are additional features specific to those platforms, we will cover those additions on separate pages. On this page, we'll cover what is common for all platforms.

The library supports the following types of statements:

1. Count statements of various types - these are specialized select statements that return a single Long column, Count statements support where clauses, joins, and subqueries.
2. Delete statement with or without a where clause.
3. Insert statements of various types:
   1. Single row insert - a statement where the insert values are obtained from a record class
   2. General insert - a statement where the insert values are set directly in the statement
   3. Multi-row Insert - a statement where the insert values are derived from a collection of records
   4. Batch insert - a set of insert statements appropriate for use as a JDBC batch
   5. Insert select - a statement where the insert values are obtained from a select statement
4. Select statement that supports joins, subqueries, where clauses, order by clauses, group by clauses, etc.
5. Multi-Select statements - multiple full select statements (including order by and paging clauses) merged together with “union” or “union all” operators
6. Update Statement with or without a where clause

Count Statements

A count statement 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:

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

The DSL for count statements looks like this:

// count(*)
val countRowsStatement = countFrom(person) {
    where { id isLessThan 4 }
}

// count(column)
val countColumnStatement = count(lastName) {
   from(person)
}

// count(distinct column)
val countDistinctColumnStatement = countDistinct(lastName) {
   from(person)
}

These methods create models or providers depending on which package is used:

Delete Statement

Delete statement support enables the creation of arbitrary delete statements including where clauses.

The DSL for delete statements looks like this:

val deleteStatement = deleteFrom(person) {
   where { id isLessThan 4 }
}

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

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

This method creates models or providers depending on which package is used:

Single Row Insert Statement

This 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 statements looks like this:

data class PersonRecord(
   val id: Int,
   val firstName: String,
   val lastName: String,
   val birthDate: Date,
   val employed: Boolean,
   val occupation: String?,
   val addressId: Int
)

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

val insertRecordStatement = insert(row) {
   into(person)
   map(id) toProperty "id"
   map(firstName) toProperty "firstName"
   map(lastName) toProperty "lastName"
   map(birthDate) toProperty "birthDate"
   map(employed) toProperty "employedAsString"
   map(occupation).toPropertyWhenPresent("occupation", row::occupation)
   map(addressId) toProperty "addressId"
}

This statement maps insert columns to properties in a class. 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 other mapping methods to map insert fields to nulls and constants if desired. Many of the mappings can be called via infix as shown above.

This method creates models or providers depending on which package is used:

General Insert Statement

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 statements looks like this:

val generalInsertStatement = insertInto(person) {
    set(id) toValue 100
    set(firstName) toValue "Joe"
    set(lastName) toValue "Jones"
    set(birthDate) toValue Date()
    set(employed) toValue true
    set(occupation) toValue "Developer"
    set(addressId) toValue 1
}

This method creates models or providers depending on which package is used:

Multi-Row Insert Statement

Multi-row inserts allow you to insert multiple rows into a table with a single insert statement. This is a convenient way to insert multiple rows, but there are some limitations. Multi-row inserts are not intended for large bulk inserts because it is possible to create insert statements that exceed the number of prepared statement parameters allowed in JDBC. For bulk inserts, please consider using a JDBC batch (see below).

Note the distinction between multi-row inserts and batch inserts. A multi-row insert is a single insert statement that inserts multiple rows into the database. It is formatted as follows:

insert into baz (foo, bar) values (1, 2), (3, 4), (5, 6)

A multi-row insert is a single insert statement with many (perhaps very many) parameters. Multi-row inserts are viewed as a single transaction by the database. In addition, most JDBC drivers place some limit on the number of prepared statement parameters. So it is best to use a multi-row insert with a small number of rows.

The DSL for multi-row insert statements looks like this:

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

val multiRowInsertStatement = insertMultiple(listOf(record1, record2)) {
   into(person)
   map(id) toProperty "id"
   map(firstName) toProperty "firstName"
   map(lastName) toProperty "lastNameAsString"
   map(birthDate) toProperty "birthDate"
   map(employed) toProperty "employedAsString"
   map(occupation) toProperty "occupation"
   map(addressId) toProperty "addressId"
}

Note there is no toPropertyWhenPresent mapping available on a multi-row insert.

Also note that there is no overload of this function that accepts a vararg of rows because it would cause an overload resolution ambiguity error. This limitation is overcome in the utility functions for MyBatis and Spring as shown on the documentation pages for those utilities.

This method creates models or providers depending on which package is used:

Batch Insert Statement

A batch insert is a sequence of insert statements that can be handled as a batch by the JDBC driver. Batches have virtually no limit on the number of statements that can be executed. Batches also support intermediate commits. Some care must be taken with the underlying database engine to ensure that batch statements are executed as a batch and not just a collection of individual inserts. This is especially true with MyBatis. Spring has support for executing a batch with a single commit. Intermediate commits must be handled manually.

MyBatis and Spring have different ways of executing batch inserts - you can see details on those specific pages. The library generates objects that can be used by either MyBatis or Spring.

The DSL for batch insert statements looks like this:

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

val batchInsertStatement = insertBatch(listOf(record1, record2)) {
   into(person)
   map(id) toProperty "id"
   map(firstName) toProperty "firstName"
   map(lastName) toProperty "lastNameAsString"
   map(birthDate) toProperty "birthDate"
   map(employed) toProperty "employedAsString"
   map(occupation) toProperty "occupation"
   map(addressId) toProperty "addressId"
}

Note there is no toPropertyWhenPresent mapping available on a batch insert.

Also note that there is no overload of this function that accepts a vararg of rows because it would cause an overload resolution ambiguity error. This limitation is overcome in the utility functions for MyBatis and Spring as shown on the documentation pages for those utilities.

This method creates models or providers depending on which package is used:

Insert Select Statement

An insert select statement obtains insert values from a nested select statement.

The DSL for an insert select statement looks like this:

val insertSelectStatement = insertSelect(person) {
    columns(id, firstName, lastName, birthDate, employed, occupation, addressId)
    select(
        add(id, constant<Int>("100")), firstName, lastName, birthDate, employed, occupation, addressId
    ) {
        from(person)
        where { employed.isTrue() }
    }
}

The columns method accepts a list of SqlColumn objects that are rendered as the columns to insert. The select method is a query whose value list should match the columns. The number of rows inserted will generally match the number of rows returned from the query.

This method creates models or providers depending on which package is used:

Select Statement

Select statement support enables the creation of methods that execute a query allowing a user to specify a where clause, join specifications, order by clauses, group by clauses, pagination clauses, etc.

The full DSL for select statements looks like this:

val selectStatement = select(orderMaster.orderId, orderMaster.orderDate, orderDetail.lineNumber,
   orderDetail.description, orderDetail.quantity
) {
   from(orderMaster, "om")
   join(orderDetail, "od") {
      on(orderMaster.orderId) equalTo orderDetail.orderId
      and(orderMaster.orderId) equalTo orderDetail.orderId
   }
   where { orderMaster.orderId isEqualTo 1 }
   or {
      orderMaster.orderId isEqualTo 2
      and { orderDetail.quantity isLessThan 6 }
   }
   orderBy(orderMaster.orderId)
   limit(3)
}

In a select statement you must specify a table in a from clause. Everything else is optional.

Multiple join clauses can be specified if you need to join additional tables. In a join clause, you must specify an on condition, and you may specify additional and conditions as necessary. Full, left, right, inner, and outer joins are supported.

Where clauses can be of arbitrary complexity and support all SQL operators including exists operators, subqueries, etc. You can nest and, or, and not clauses as necessary in where clauses.

There is also a method that will create a “distinct” query (select distinct ...) as follows:

val selectStatement = selectDistinct(id, firstName, lastName, birthDate, employed, occupation, addressId) {
   from(person)
   where { id isLessThan 5 }
   and {
      id isLessThan 4
      and {
         id isLessThan 3
         or { id isLessThan 2 }
      }
   }
   orderBy(id)
   limit(3)
}

These methods create models or providers depending on which package is used:

Multi-Select Statement

A multi-select statement is a special case of a union query. In a multi-select statement, each select statement is wrapped with parentheses. This means that you can use “order by” and paging clauses on the select statements that are merged with a “union” or “union all” operator. You can also apply “order by” and paging clauses to the query as a whole.

The full DSL for multi-select statements looks like this:

val selectStatement = multiSelect {
   selectDistinct(id, firstName, lastName, birthDate, employed, occupation, addressId) {
      from(person)
      where { id isLessThanOrEqualTo 2 }
      orderBy(id)
      limit(1)
   }
   unionAll {
      select(id, firstName, lastName, birthDate, employed, occupation, addressId) {
         from(person)
         where { id isGreaterThanOrEqualTo 4 }
         orderBy(id.descending())
         limit(1)
      }
   }
   orderBy(id)
   fetchFirst(1)
   offset(1)
}

Each nested select statement can be either “select” or “selectDistinct”. They can be merged with either “union” or “unionAll”. There is no limit to the number of statements that can be merged.

These methods create models or providers depending on which package is used:

Update Statement

Update statement support enables the creation of methods that execute an update allowing a user to specify SET clauses and where clauses.

The DSL for update statements looks like this:

val updateStatement = update(person) {
   set(firstName).equalTo("Sam")
   where { firstName isEqualTo "Fred" }
}

If you omit the where clause, the statement will update every row in a table.

This method creates models or providers depending on which package is used: