docs/kotlinOverview.html

<!DOCTYPE html>


<!--
 | Generated by Apache Maven Doxia Site Renderer 2.0.0-M18 from src/site/markdown/docs/kotlinOverview.md at 03 Jun 2024
 | Rendered using Apache Maven Fluido Skin 2.0.0-M9
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <meta name="generator" content="Apache Maven Doxia Site Renderer 2.0.0-M18" />
    <title>MyBatis Dynamic SQL</title>
    <link rel="stylesheet" href="../css/apache-maven-fluido-2.0.0-M9.min.css" />
    <link rel="stylesheet" href="../css/site.css" />
    <link rel="stylesheet" href="../css/print.css" media="print" />
    <script src="../js/apache-maven-fluido-2.0.0-M9.min.js"></script>
  </head>
  <body>
    <div class="container-fluid container-fluid-top">
      <header>
        <div id="banner">
          <div class="pull-left"><div id="bannerLeft"><h1><a href="../docs/introduction.html">MyBatis Dynamic SQL</a></h1></div></div>
          <div class="pull-right"><div id="bannerRight"><h1><a href="http://www.mybatis.org/" class="externalLink"><img class="imageLink" src="https://mybatis.org/images/mybatis-logo.png" alt="MyBatis logo" /> MyBatis</a></h1></div></div>
          <div class="clear"><hr/></div>
        </div>

        <div id="breadcrumbs">
          <ul class="breadcrumb">
        <li id="publishDate">Last Published: 03 Jun 2024<span class="divider">|</span>
</li>
          <li id="projectVersion">Version: 1.5.2</li>
          </ul>
        </div>
      </header>
      <div class="row-fluid">
        <header id="leftColumn" class="span2">
          <nav class="well sidebar-nav">
  <ul class="nav nav-list">
   <li class="nav-header">User&apos;s Guide</li>
    <li><a href="../docs/introduction.html">Introduction</a></li>
    <li><a href="../docs/CHANGELOG.html">Change Log</a></li>
    <li><a href="../docs/quickStart.html">Quick Start</a></li>
    <li><a href="../docs/exceptions.html">Exceptions thrown by the Library</a></li>
    <li><a href="../docs/configuration.html">Configuration of the Library</a></li>
    <li><a href="../docs/databaseObjects.html">Modeling Database Objects</a></li>
    <li><a href="../docs/whereClauses.html"><span class="icon-chevron-down"></span>WHERE Clause Support</a>
     <ul class="nav nav-list">
      <li><a href="../docs/conditions.html">WHERE Conditions</a></li>
     </ul></li>
    <li><a href="../docs/select.html"><span class="icon-chevron-down"></span>SELECT Statements</a>
     <ul class="nav nav-list">
      <li><a href="../docs/caseExpressions.html">Case Expressions</a></li>
      <li><a href="../docs/complexQueries.html">Complex Queries</a></li>
     </ul></li>
    <li><a href="../docs/delete.html">DELETE Statements</a></li>
    <li><a href="../docs/insert.html">INSERT Statements</a></li>
    <li><a href="../docs/update.html">UPDATE Statements</a></li>
    <li><a href="../docs/subQueries.html">SubQuery Support</a></li>
    <li><a href="../docs/functions.html">Database Functions</a></li>
    <li><a href="../docs/mybatis3.html">MyBatis3 Support</a></li>
    <li><a href="../docs/spring.html">Spring Support</a></li>
    <li><a href="../docs/springBatch.html">Spring Batch Support</a></li>
    <li class="active"><a><span class="icon-chevron-down"></span>Kotlin Support</a>
     <ul class="nav nav-list">
      <li><a href="../docs/kotlinCaseExpressions.html">Kotlin Case Expressions</a></li>
      <li><a href="../docs/kotlinWhereClauses.html">Kotlin Where Clauses</a></li>
      <li><a href="../docs/kotlinMyBatis3.html">Kotlin Support for MyBatis3</a></li>
      <li><a href="../docs/kotlinSpring.html">Kotlin Support for Spring</a></li>
     </ul></li>
    <li><a href="../docs/howItWorks.html">How it Works</a></li>
    <li><a href="../docs/extending.html">Extending the Library</a></li>
    <li><a href="../docs/codingStandards.html">Coding Standards</a></li>
    <li><a href="../docs/motivation.html">Motivation</a></li>
   <li class="nav-header">Project Documentation</li>
    <li><a href="../project-info.html"><span class="icon-chevron-right"></span>Project Information</a></li>
    <li><a href="../project-reports.html"><span class="icon-chevron-right"></span>Project Reports</a></li>
  </ul>
          </nav>
          <div class="well sidebar-nav">
            <div id="poweredBy">
              <div class="clear"></div>
              <div class="clear"></div>
<a href="https://maven.apache.org/" class="builtBy" target="_blank"><img class="builtBy" alt="Built by Maven" src="../images/logos/maven-feather.png" /></a>
            </div>
          </div>
        </header>
        <main id="bodyColumn" class="span10">
<section><a id="Kotlin_Support"></a>
<h1>Kotlin Support</h1>
<p>MyBatis Dynamic SQL includes Kotlin extensions that provide an SQL DSL for Kotlin. This is the recommended method
of using the library with Kotlin. For the most part, the Kotlin DSL provides a thin wrapper over the underlying Java
DSL. You certainly can use the Java DSL with Kotlin. However, using the more specific Kotlin DSL will provide some
benefits:</p>
<ol style="list-style-type: decimal;">

<li>The Kotlin DSL generally masks the platform types that are inferred with the underlying Java DSL</li>
<li>The Kotlin DSL accurately expresses the nullability expectations of the underlying Java DSL</li>
<li>Using the Kotlin DSL will avoid some confusion with overloaded function names that are present in the Java DSL</li>
<li>The Kotlin DSL makes extensive use of Kotlin DSL construction features. It more closely mimics actual SQL than the
Java DSL and will likely feel more natural to Kotlin developers</li>
</ol>
<p>We take the customary approach to DSL building in Kotlin in that we attempt to create a somewhat natural feel for SQL,
but not an exact replacement of SQL. The Kotlin DSL relies on the capabilities of the underlying Java DSL. This means
that the Kotlin DSL does not add any capabilities that are not already present in the Java DSL.
You can continue to use the underlying Java DSL at any time - it functions properly in Kotlin. One of the main features
of the Kotlin DSL is that we move away from the method chaining paradigm of the Java DSL and move towards a more
idiomatic Kotlin DSL based on lambdas and receiver objects. We think the Kotlin DSL feels more natural - certainly it
is a more natural experience for Kotlin.</p>
<p>One consequence of the more natural feel of the Kotlin DSL is that you are free to write unusual looking SQL. For
example, you could write a SELECT statement with a WHERE clause after a UNION. Most of the time these unusual usages
of the DSL will yield correct results. However, it would be best to use the DSL as shown below to avoid hard to
diagnose problems.</p>
<p>If you plan to use the Kotlin DSL, we recommend that you do not use any function from
<code>org.mybatis.dynamic.sql.SqlBuilder</code> (the Java DSL entry points). Many functions from that class have been duplicated
for the Kotlin DSL, but in a more Kotlin native manner.</p><section><a id="Package_Structure"></a>
<h2>Package Structure</h2>
<p>We have implemented all Kotlin DSL functions as &#x201c;top level&#x201d; 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.</p>
<p>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:</p>
<ol style="list-style-type: decimal;">

<li>&#x201c;Model&#x201d; objects are generated by the DSL, but are not rendered into a &#x201c;provider&#x201d;. 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 &#x201c;model&#x201d; objects (this is an
unusual use case)</li>
<li>&#x201c;Provider&#x201d; 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 &#x201c;provider&#x201d; objects in some form or another</li>
</ol>
<p>When creating model objects, import the following packages:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.model.*
</code></pre>
<p>When creating provider objects rendered for MyBatis3 (and using other MyBatis3 specific functions), import the following
packages:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.mybatis3.*
</code></pre>
<p>When creating provider objects rendered for Spring JDBC Template (and using other Spring specific functions), import
the following packages:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.elements.*
import org.mybatis.dynamic.sql.util.kotlin.spring.*
</code></pre>
<p>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.</p></section><section><a id="Kotlin_Dynamic_SQL_Support_Objects"></a>
<h2>Kotlin Dynamic SQL Support Objects</h2>
<p>MyBatis Dynamic SQL relies on a database &#x201c;meta model&#x201d; - objects that describe database tables and columns.</p>
<p>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 <code>object</code> and <code>class</code>. Like the Java pattern, this pattern will allow you to use table and column names
in a &#x201c;qualified&#x201d; or &#x201c;un-qualified&#x201d; manner that looks like natural SQL. For example, in the
following a column could be referred to as <code>firstName</code> or <code>person.firstName</code>.</p>

<pre><code class="language-kotlin">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&lt;Person&gt;(&quot;Person&quot;, ::Person) {
        val id = column&lt;Int&gt;(name = &quot;id&quot;, jdbcType = JDBCType.INTEGER)
        val firstName = column&lt;String&gt;(name = &quot;first_name&quot;, jdbcType = JDBCType.VARCHAR)
        val lastName = column&lt;String&gt;(name = &quot;last_name&quot;, jdbcType = JDBCType.VARCHAR)
        val birthDate = column&lt;Date&gt;(name = &quot;birth_date&quot;, jdbcType = JDBCType.DATE)
        val employed = column&lt;Boolean&gt;(
           name = &quot;employed&quot;,
           jdbcType = JDBCType.VARCHAR,
           typeHandler = &quot;examples.kotlin.mybatis3.canonical.YesNoTypeHandler&quot;
        )
        val occupation = column&lt;String&gt;(name = &quot;occupation&quot;, jdbcType = JDBCType.VARCHAR)
        val addressId = column&lt;Int&gt;(name = &quot;address_id&quot;, jdbcType = JDBCType.INTEGER)
    }
}
</code></pre>
<p>Notes:</p>
<ol style="list-style-type: decimal;">

<li>The outer object is a singleton containing the <code>AlisableSqlTable</code> and <code>SqlColumn</code> objects that map to the database table.</li>
<li>The inner <code>AlisableSqlTable</code> is declared as a <code>class</code> rather than an <code>object</code> - this allows you to create additional
instances for use in self-joins.</li>
<li>Note the use of the <code>column</code> 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 <code>column</code> and
<code>withXXX</code> methods in the Java native DSL because the extension method will retain the non-nullable type information
associated with the column.</li>
</ol></section><section><a id="Statements"></a>
<h2>Statements</h2>
<p>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.</p>
<p>The library supports the following types of statements:</p>
<ol style="list-style-type: decimal;">

<li>Count statements of various types - these are specialized select statements that return a single Long column, Count
statements support where clauses, joins, and subqueries.</li>
<li>Delete statement with or without a where clause.</li>
<li>Insert statements of various types:
<ol style="list-style-type: decimal;">

<li>Single row insert - a statement where the insert values are obtained from a record class</li>
<li>General insert - a statement where the insert values are set directly in the statement</li>
<li>Multi-row Insert - a statement where the insert values are derived from a collection of records</li>
<li>Batch insert - a set of insert statements appropriate for use as a JDBC batch</li>
<li>Insert select - a statement where the insert values are obtained from a select statement</li>
</ol></li>
<li>Select statement that supports joins, subqueries, where clauses, order by clauses, group by clauses, etc.</li>
<li>Multi-Select statements - multiple full select statements (including order by and paging clauses) merged together
with &#x201c;union&#x201d; or &#x201c;union all&#x201d; operators</li>
<li>Update Statement with or without a where clause</li>
</ol></section><section><a id="Count_Statements"></a>
<h2>Count Statements</h2>
<p>A count statement is a specialized select - it returns a single column - typically a long - and supports joins and a
where clause.</p>
<p>The library supports three types of count statements:</p>
<ol style="list-style-type: decimal;">

<li><code>count(*)</code> - counts the number of rows that match a where clause</li>
<li><code>count(column)</code> - counts the number of non-null column values that match a where clause</li>
<li><code>count(distinct column)</code> - counts the number of unique column values that match a where clause</li>
</ol>
<p>The DSL for count statements looks like this:</p>

<pre><code class="language-kotlin">// 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)
}
</code></pre>
<p>These methods create models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.select.SelectModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Delete_Statement"></a>
<h2>Delete Statement</h2>
<p>Delete statement support enables the creation of arbitrary delete statements including where clauses.</p>
<p>The DSL for delete statements looks like this:</p>

<pre><code class="language-kotlin">val deleteStatement = deleteFrom(person) {
   where { id isLessThan 4 }
}
</code></pre>
<p>There is also a method that can be used to delete all rows in a table:</p>

<pre><code class="language-kotlin">val rows = template.deleteFrom(person) {
   allRows()
}
</code></pre>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.delete.DeleteModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Single_Row_Insert_Statement"></a>
<h2>Single Row Insert Statement</h2>
<p>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.</p>
<p>The DSL for insert statements looks like this:</p>

<pre><code class="language-kotlin">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, &quot;Joe&quot;, &quot;Jones&quot;, Date(), true, &quot;Developer&quot;, 1)

val insertRecordStatement = insert(row) {
   into(person)
   map(id) toProperty &quot;id&quot;
   map(firstName) toProperty &quot;firstName&quot;
   map(lastName) toProperty &quot;lastName&quot;
   map(birthDate) toProperty &quot;birthDate&quot;
   map(employed) toProperty &quot;employedAsString&quot;
   map(occupation).toPropertyWhenPresent(&quot;occupation&quot;, row::occupation)
   map(addressId) toProperty &quot;addressId&quot;
}
</code></pre>
<p>This statement maps insert columns to properties in a class. Note the use of the <code>toPropertyWhenPresent</code> 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.</p>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.insert.InsertModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.insert.render.InsertStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.insert.render.InsertStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="General_Insert_Statement"></a>
<h2>General Insert Statement</h2>
<p>General insert method support enables the creation of arbitrary insert statements and does not require the creation of
a class matching the database row.</p>
<p>The DSL for general insert statements looks like this:</p>

<pre><code class="language-kotlin">val generalInsertStatement = insertInto(person) {
    set(id) toValue 100
    set(firstName) toValue &quot;Joe&quot;
    set(lastName) toValue &quot;Jones&quot;
    set(birthDate) toValue Date()
    set(employed) toValue true
    set(occupation) toValue &quot;Developer&quot;
    set(addressId) toValue 1
}
</code></pre>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.insert.GeneralInsertModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Multi-Row_Insert_Statement"></a>
<h2>Multi-Row Insert Statement</h2>
<p>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).</p>
<p>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:</p>

<pre><code class="language-sql">insert into baz (foo, bar) values (1, 2), (3, 4), (5, 6)
</code></pre>
<p>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.</p>
<p>The DSL for multi-row insert statements looks like this:</p>

<pre><code class="language-kotlin">val record1 = PersonRecord(100, &quot;Joe&quot;, &quot;Jones&quot;, Date(), true, &quot;Developer&quot;, 1)
val record2 = PersonRecord(101, &quot;Sarah&quot;, &quot;Smith&quot;, Date(), true, &quot;Architect&quot;, 2)

val multiRowInsertStatement = insertMultiple(listOf(record1, record2)) {
   into(person)
   map(id) toProperty &quot;id&quot;
   map(firstName) toProperty &quot;firstName&quot;
   map(lastName) toProperty &quot;lastNameAsString&quot;
   map(birthDate) toProperty &quot;birthDate&quot;
   map(employed) toProperty &quot;employedAsString&quot;
   map(occupation) toProperty &quot;occupation&quot;
   map(addressId) toProperty &quot;addressId&quot;
}
</code></pre>
<p>Note there is no <code>toPropertyWhenPresent</code> mapping available on a multi-row insert.</p>
<p>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.</p>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.insert.MultiRowInsertModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Batch_Insert_Statement"></a>
<h2>Batch Insert Statement</h2>
<p>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.</p>
<p>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.</p>
<p>The DSL for batch insert statements looks like this:</p>

<pre><code class="language-kotlin">val record1 = PersonRecord(100, &quot;Joe&quot;, &quot;Jones&quot;, Date(), true, &quot;Developer&quot;, 1)
val record2 = PersonRecord(101, &quot;Sarah&quot;, &quot;Smith&quot;, Date(), true, &quot;Architect&quot;, 2)

val batchInsertStatement = insertBatch(listOf(record1, record2)) {
   into(person)
   map(id) toProperty &quot;id&quot;
   map(firstName) toProperty &quot;firstName&quot;
   map(lastName) toProperty &quot;lastNameAsString&quot;
   map(birthDate) toProperty &quot;birthDate&quot;
   map(employed) toProperty &quot;employedAsString&quot;
   map(occupation) toProperty &quot;occupation&quot;
   map(addressId) toProperty &quot;addressId&quot;
}
</code></pre>
<p>Note there is no <code>toPropertyWhenPresent</code> mapping available on a batch insert.</p>
<p>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.</p>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.insert.BatchInsertModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.insert.render.BatchInsert (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.insert.render.BatchInsert (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Insert_Select_Statement"></a>
<h2>Insert Select Statement</h2>
<p>An insert select statement obtains insert values from a nested select statement.</p>
<p>The DSL for an insert select statement looks like this:</p>

<pre><code class="language-kotlin">val insertSelectStatement = insertSelect(person) {
    columns(id, firstName, lastName, birthDate, employed, occupation, addressId)
    select(
        add(id, constant&lt;Int&gt;(&quot;100&quot;)), firstName, lastName, birthDate, employed, occupation, addressId
    ) {
        from(person)
        where { employed.isTrue() }
    }
}
</code></pre>
<p>The <code>columns</code> method accepts a list of <code>SqlColumn</code> objects that are rendered as the columns to insert. The <code>select</code>
method is a query whose value list should match the <code>columns</code>. The number of rows inserted will generally match the
number of rows returned from the query.</p>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.insert.InsertSelectModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Select_Statement"></a>
<h2>Select Statement</h2>
<p>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.</p>
<p>The full DSL for select statements looks like this:</p>

<pre><code class="language-kotlin">val selectStatement = select(orderMaster.orderId, orderMaster.orderDate, orderDetail.lineNumber,
   orderDetail.description, orderDetail.quantity
) {
   from(orderMaster, &quot;om&quot;)
   join(orderDetail, &quot;od&quot;) {
      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)
}
</code></pre>
<p>In a select statement you must specify a table in a <code>from</code> clause. Everything else is optional.</p>
<p>Multiple join clauses can be specified if you need to join additional tables. In a join clause, you must
specify an <code>on</code> condition, and you may specify additional <code>and</code> conditions as necessary. Full, left, right, inner,
and outer joins are supported.</p>
<p>Where clauses can be of arbitrary complexity and support all SQL operators including exists operators, subqueries, etc.
You can nest <code>and</code>, <code>or</code>, and <code>not</code> clauses as necessary in where clauses.</p>
<p>There is also a method that will create a &#x201c;distinct&#x201d; query (<code>select distinct ...</code>) as follows:</p>

<pre><code class="language-kotlin">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)
}
</code></pre>
<p>These methods create models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.select.SelectModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Multi-Select_Statement"></a>
<h2>Multi-Select Statement</h2>
<p>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 &#x201c;order by&#x201d; and paging clauses on the select statements that are
merged with a &#x201c;union&#x201d; or &#x201c;union all&#x201d; operator. You can also apply &#x201c;order by&#x201d; and paging clauses to the query as a whole.</p>
<p>The full DSL for multi-select statements looks like this:</p>

<pre><code class="language-kotlin">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)
}
</code></pre>
<p>Each nested select statement can be either &#x201c;select&#x201d; or &#x201c;selectDistinct&#x201d;. They can be merged with either
&#x201c;union&#x201d; or &#x201c;unionAll&#x201d;. There is no limit to the number of statements that can be merged.</p>
<p>These methods create models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.select.MultiSelectModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.select.render.SelectStatementProvider (rendered for Spring)</td></tr></tbody>
</table>
</section><section><a id="Update_Statement"></a>
<h2>Update Statement</h2>
<p>Update statement support enables the creation of methods that execute an update allowing a user to specify SET clauses
and where clauses.</p>
<p>The DSL for update statements looks like this:</p>

<pre><code class="language-kotlin">val updateStatement = update(person) {
   set(firstName).equalTo(&quot;Sam&quot;)
   where { firstName isEqualTo &quot;Fred&quot; }
}
</code></pre>
<p>If you omit the <code>where</code> clause, the statement will update every row in a table.</p>
<p>This method creates models or providers depending on which package is used:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Package</th>
<th>Resulting Object</th></tr></thead><tbody>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.model</td>
<td>org.mybatis.dynamic.sql.update.UpdateModel</td></tr>
<tr class="a">
<td>org.mybatis.dynamic.sql.util.kotlin.mybatis3</td>
<td>org.mybatis.dynamic.sql.update.render.UpdateStatementProvider (rendered for MyBatis3)</td></tr>
<tr class="b">
<td>org.mybatis.dynamic.sql.util.kotlin.spring</td>
<td>org.mybatis.dynamic.sql.update.render.UpdateStatementProvider (rendered for Spring)</td></tr></tbody>
</table></section></section>
        </main>
      </div>
    </div>
    <hr/>
    <footer>
      <div class="container-fluid">
        <div class="row-fluid">
            <p>©      2016–2024
<a href="https://www.mybatis.org/">MyBatis.org</a>
</p>
        </div>
      </div>
    </footer>
<script>
  if(anchors) {
    anchors.add();
  }
</script>
  </body>
</html>