docs/kotlinSpring.html

<!DOCTYPE html>


<!--
 | Generated by Apache Maven Doxia Site Renderer 2.0.0-M18 from src/site/markdown/docs/kotlinSpring.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><a href="../docs/kotlinOverview.html"><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 class="active"><a>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_for_Spring"></a>
<h1>Kotlin Support for Spring</h1>
<p>MyBatis Dynamic SQL includes Kotlin extensions for Spring that simplify execution of statements generated by the library.
The library will render SQL in a format that is suitable for use with Spring's named parameter JDBC template. The only
difficulty with using the DSL directly is that the parameters for statements need to be formatted properly for Spring.
For example, this may involve the use of a <code>BeanPropertySqlParameterSource</code> or a <code>MapSqlParameterSource</code> depending on
the statement type. The Kotlin DSL hides all these details.</p>
<p>The Spring extensions also allow use of Spring's row mappers for ResultSets, and generated key holder for retrieving
generated keys on certain insert statements.</p>
<p>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 <code>src/test/kotlin/examples/kotlin/spring/canonical</code> directory in this repository.
That directory contains a complete example of using this library with Kotlin and Spring.</p>
<p>All Kotlin support for Spring is available in two packages:</p>
<ul>

<li><code>org.mybatis.dynamic.sql.util.kotlin.elements</code> - contains the basic DSL elements common to all runtimes</li>
<li><code>org.mybatis.dynamic.sql.util.kotlin.spring</code> - contains utilities that simplify integration with Spring JDBC Template</li>
</ul>
<p>The Kotlin support for Spring JDBC is implemented as extension methods to <code>NamedParameterJdbcTemplate</code>. 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:</p>
<ol style="list-style-type: decimal;">

<li>The first method is a two-step method. With this method you build SQL provider objects as shown on the Kotlin
overview page and then execute the generated SQL by passing the provider to an extension method
on <code>NamedParameterJdbcTemplate</code></li>
<li>The second method is a one-step method that combines these operations into a single step</li>
</ol>
<p>We will illustrate both approaches below.</p><section><a id="Kotlin_Dynamic_SQL_Support_Objects"></a>
<h2>Kotlin Dynamic SQL Support Objects</h2>
<p>The pattern for the metamodel is the same as shown on the Kotlin overview page. We'll repeat it here to show some
specifics for Spring.</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
    val addressId = person.addressId

    class Person : AlisableSqlTable&lt;Person&gt;(&quot;Person&quot;, ::Person) {
        val id = column&lt;Int&gt;(name = &quot;id&quot;)
        val firstName = column&lt;String&gt;(name = &quot;first_name&quot;)
        val lastName = column(
            name = &quot;last_name&quot;,
            parameterTypeConverter = lastNameConverter
        )
        val birthDate = column&lt;Date&gt;(name = &quot;birth_date&quot;)
        val employed = column(
            name = &quot;employed&quot;,
            parameterTypeConverter = booleanToStringConverter
        )
        val occupation = column&lt;String&gt;(name = &quot;occupation&quot;)
        val addressId = column&lt;Int&gt;(name = &quot;address_id&quot;)
    }
}
</code></pre>
<p>Note the use of a &#x201c;parameter type converter&#x201d; on the <code>employed</code> column. This allows us to use the column as a Boolean in
Kotlin, but store the values &#x201c;Yes&#x201d; or &#x201c;No&#x201d; on the database. The type converter looks like this:</p>

<pre><code class="language-kotlin">val booleanToStringConverter: (Boolean?) -&gt; String = { it?.let { if (it) &quot;Yes&quot; else &quot;No&quot; } ?: &quot;No&quot; }
</code></pre>
<p>The type converter will be used on general insert statements, update statements, and where clauses. It is not used on
insert statements that map insert fields to properties in a data class. So you will need to add properties to a data
class to use in that case. In the examples below, you will see use of a data class property <code>employedAsString</code>.
This can easily be implemented by reusing the converter function as shown below&#x2026;</p>

<pre><code class="language-kotlin">data class PersonRecord(
    ...
    var employed: Boolean? = null,
    ...
) {
    val employedAsString: String
        get() = booleanToStringConverter(employed)
}
</code></pre></section><section><a id="Count_Statements"></a>
<h2>Count Statements</h2><section><a id="Two-Step_Method"></a>
<h3>Two-Step Method</h3>
<p>Count statements are constructed as shown on the Kotlin overview page. These methods create a
<code>SelectStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.spring.count

val countStatement = count(...) // not shown... see the overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.count(countStatement) // rows is a Long
</code></pre></section><section><a id="One-Step_Method"></a>
<h3>One-Step Method</h3>
<p>Count statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.spring.count
import org.mybatis.dynamic.sql.util.kotlin.spring.countDistinct
import org.mybatis.dynamic.sql.util.kotlin.spring.countFrom

val template: NamedParameterJdbcTemplate = getTemplate() // not shown

val rowcount = template.countFrom(person) {
    where { id isLessThan 4 }
}

val columnCount = template.count(lastName) {
    from(person)
    where { id isLessThan 4 }
}

val distinctColumnCount = template.countDistinct(lastName) {
    from(person)
    where { id isLessThan 4 }
}
</code></pre>
<p>There is also a method that can be used to count all rows in a table:</p>

<pre><code class="language-kotlin">val rows = template.countFrom(Person) {
    allRows()
}
</code></pre></section></section><section><a id="Delete_Statement"></a>
<h2>Delete Statement</h2><section><a id="Two-Step_Method_1"></a>
<h3>Two-Step Method</h3>
<p>Delete statements are constructed as shown on the Kotlin overview page. These methods create a
<code>DeleteStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.spring.deleteFrom

val deleteStatement = deleteFrom(...) // not shown... see the overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.delete(deleteStatement)  // rows is an Int
</code></pre></section><section><a id="One-Step_Method_1"></a>
<h3>One-Step Method</h3>
<p>Delete statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">import org.mybatis.dynamic.sql.util.kotlin.spring.deleteFrom

val template: NamedParameterJdbcTemplate = getTemplate() // not shown

val rows = template.deleteFrom(person) {
    where { id isLessThan 4 }
}
</code></pre>
<p>There is also a method that can be used to count all rows in a table:</p>

<pre><code class="language-kotlin">val rows = template.deleteFrom(person) {
    allRows()
}
</code></pre></section></section><section><a id="Single_Row_Insert_Statement"></a>
<h2>Single Row Insert Statement</h2><section><a id="Two-Step_Method_2"></a>
<h3>Two-Step Method</h3>
<p>Single record insert statements are constructed as shown on the Kotlin overview page. These methods create a
<code>InsertStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">val insertStatement = insert(...)  // not shown, see the overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insert(insertStatement)  // rows is an Int
</code></pre>
<p>If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()
val rows = template.insert(insertStatement, keyHolder)  // rows is an Int
</code></pre></section><section><a id="One-Step_Method_2"></a>
<h3>One-Step Method</h3>
<p>Single record insert statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">val row = PersonRecord(100, &quot;Joe&quot;, &quot;Jones&quot;, Date(), true, &quot;Developer&quot;, 1)

val rows = template.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>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 the mapping methods to map insert fields to nulls and constants if desired.</p>
<p>Using a KeyHolder with the single step method looks like this:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()
val row = PersonRecord(100, &quot;Joe&quot;, &quot;Jones&quot;, Date(), true, &quot;Developer&quot;, 1)

val rows = template.withKeyHolder(keyHolder) {
    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></section></section><section><a id="General_Insert_Statement"></a>
<h2>General Insert Statement</h2><section><a id="Two-Step_Method_3"></a>
<h3>Two-Step Method</h3>
<p>General insert statements are constructed as shown on the Kotlin overview page. These methods create a
<code>GeneralInsertStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">val insertStatement = insertInto(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.generalInsert(insertStatement)  // rows is an Int
</code></pre>
<p>If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()
val rows = template.generalInsert(insertStatement, keyHolder)  // rows is an Int
</code></pre></section><section><a id="One-Step_Method_3"></a>
<h3>One-Step Method</h3>
<p>General insert statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">val myOccupation = &quot;Developer&quot;

val rows = template.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) toValueWhenPresent myOccupation
    set(addressId) toValue 1
}
</code></pre>
<p>Note the use of the <code>toValueWhenPresent</code> 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.</p>
<p>Using a KeyHolder with the single step method looks like this:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()
val myOccupation = &quot;Developer&quot;

val rows = template.withKeyHolder(keyHolder) {
    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) toValueWhenPresent myOccupation
        set(addressId) toValue 1
    }
}
</code></pre></section></section><section><a id="Multi-Row_Insert_Statement"></a>
<h2>Multi-Row Insert Statement</h2><section><a id="Two-Step_Method_4"></a>
<h3>Two-Step Method</h3>
<p>Multi-Row insert statements are constructed as shown on the Kotlin overview page. These methods create a
<code>MultiRowInsertStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">val insertStatement = insertMultiple(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insertMultiple(insertStatement) // rows is an Int
</code></pre>
<p>If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()
val rows = template.insertMultiple(insertStatement, keyHolder)  // rows is an Int
</code></pre></section><section><a id="One-Step_Method_4"></a>
<h3>One-Step Method</h3>
<p>Multi-Row insert statements can be constructed and executed in a single step with code like the following:</p>

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

val rows = template.insertMultiple(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>Using a KeyHolder with the single step method looks like this:</p>

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

val rows = template.withKeyHolder(keyHolder) {
    insertMultiple(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></section></section><section><a id="Batch_Insert_Statement"></a>
<h2>Batch Insert Statement</h2><section><a id="Two-Step_Method_5"></a>
<h3>Two-Step Method</h3>
<p>Batch insert statements are constructed as shown on the Kotlin overview page. These methods create a
<code>BatchInsert</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">val insertStatement = insertBatch(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insertBatch(insertStatement) // rows is an IntArray
</code></pre>
<p>Spring does not support retrieval of generated keys with batch insert statements.</p></section><section><a id="One-Step_Method_5"></a>
<h3>One-Step Method</h3>
<p>Batch statements can be constructed and executed in a single step with code like the following:</p>

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

val rows = template.insertBatch(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></section></section><section><a id="Insert_Select_Statement"></a>
<h2>Insert Select Statement</h2><section><a id="Two-Step_Method_6"></a>
<h3>Two-Step Method</h3>
<p>Insert select statements are constructed as shown on the Kotlin overview page. These methods create a
<code>InsertSelectStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">val insertStatement = insertSelect(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.insertSelect(insertStatement) // rows is an Int
</code></pre>
<p>If you want to retrieve generated keys, you can use Spring's KeyHolder as follows:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()
val rows = template.insertSelect(insertStatement, keyHolder)  // rows is an Int
</code></pre></section><section><a id="One-Step_Method_6"></a>
<h3>One-Step Method</h3>
<p>Insert select statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">val insertSelectRows: Int = template.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>Using a KeyHolder with the single step method looks like this:</p>

<pre><code class="language-kotlin">val keyHolder = GeneratedKeyHolder()

val rows = template.withKeyHolder(keyHolder) {
    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></section></section><section><a id="Select_Statement"></a>
<h2>Select Statement</h2><section><a id="Spring_Row_Mappers"></a>
<h3>Spring Row Mappers</h3>
<p>There are several ways to execute select statements with Spring. Many of the methods require the use of a row mapper.
A row mapper is a user provided function that creates objects based on the values in a <code>ResultSet</code>.
The <code>rowMapper</code> function will be called repeatedly until the end of the result set is reached. The function accepts two
parameters - the <code>ResultSet</code> and an <code>Int</code> which will be set to the current row number. Many times the row number is
ignored.</p>
<p>In Kotlin, you can declare the row mapper function either as a declared function, or as a function variable. A declared
function could look like the following:</p>

<pre><code class="language-kotlin">import java.sql.ResultSet

fun rowMapper(resultSet: ResultSet, rowNum: Int): PersonRecord =
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == &quot;Yes&quot;,
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
</code></pre>
<p>A function variable could look like this:</p>

<pre><code class="language-kotlin">import java.sql.ResultSet

val rowMapper: (ResultSet, Int) -&gt; PersonRecord = { rs, _ -&gt;
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == &quot;Yes&quot;,
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}
</code></pre>
<p>Note that in this case we are ignoring the row number. You can also pass a function like this directly to the extension
methods as a lambda as we will see below.</p></section><section><a id="Two-Step_Method_7"></a>
<h3>Two-Step Method</h3>
<p>Select statements are constructed as shown on the Kotlin overview page. These methods create a
<code>SelectStatementProvider</code> that can be executed with extension methods for <code>NamedParameterJdbcTemplate</code>. There are several
extension methods that can be used in the two-step method as detailed below:</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Method</th>
<th>Comments</th></tr></thead><tbody>
<tr class="b">
<td>selectList(SelectStatementProvider, RowMapper)</td>
<td>Executes a select statement and returns a list (the list will be empty if no records match). The row mapper is used to map result sets for rows.</td></tr>
<tr class="a">
<td>selectList(SelectStatementProvider, KClass)</td>
<td>Executes a select statement and returns a list (the list will be empty if no records match). This method can be used to execute a select statement that returns a single column. Spring will attempt to retrieve objects of type <code>KClass</code> from the result set.</td></tr>
<tr class="b">
<td>selectOne(SelectStatementProvider, RowMapper)</td>
<td>Executes a select statement and returns a single object (or null if no records match). The row mapper is used to map result sets for row.</td></tr>
<tr class="a">
<td>selectOne(SelectStatementProvider, KClass)</td>
<td>Executes a select statement and returns a single object (or null if no records match). This method can be used to execute a select statement that returns a single column. Spring will attempt to retrieve an object of type <code>KClass</code> from the result set.</td></tr></tbody>
</table>

<p>The following example shows the most common case: executing a statement that returns multiple rows with a user provided
row mapper. The row mapper is passed as a lambda and ignores the row number:</p>

<pre><code class="language-kotlin">val selectStatement = select(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.selectList(selectStatement) { rs, _ -&gt; // rows is a List of PersonRecord in this case
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == &quot;Yes&quot;,
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}
</code></pre></section><section><a id="One-Step_Method_7"></a>
<h3>One-Step Method</h3>
<p>Select statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">val personRecords: List&lt;PersonRecord&gt; = template.select(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)
}.withRowMapper { rs, _ -&gt;
    PersonRecord(
        id = rs.getInt(id.name()),
        firstName = rs.getString(firstName.name()),
        lastName = rs.getString(lastName.name()),
        birthDate = rs.getDate(birthDate.name()),
        employed = rs.getString(employed.name()) == &quot;Yes&quot;,
        occupation = rs.getString(occupation.name()),
        addressId = rs.getInt(addressId.name())
    )
}
</code></pre>
<p>There are similar methods for selecting a single row, or executing a select distinct query. A single row select looks
like this:</p>

<pre><code class="language-kotlin">val personRecord: PersonRecord? = template.selectOne(id, firstName, lastName, birthDate, employed, occupation, addressId) {
    from(Person)
    where { id isEqualTo key }
}.withRowMapper { rs, _ -&gt;
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == &quot;Yes&quot;,
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}
</code></pre>
<p>A distinct query looks like this:</p>

<pre><code class="language-kotlin">val personRecord: List&lt;PersonRecord&gt; = template.selectDistinct(id, firstName, lastName, birthDate, employed, occupation, addressId) {
    from(Person)
    where { id isLessThan key }
}.withRowMapper { rs, _ -&gt;
   PersonRecord(
      id = rs.getInt(id.name()),
      firstName = rs.getString(firstName.name()),
      lastName = rs.getString(lastName.name()),
      birthDate = rs.getDate(birthDate.name()),
      employed = rs.getString(employed.name()) == &quot;Yes&quot;,
      occupation = rs.getString(occupation.name()),
      addressId = rs.getInt(addressId.name())
   )
}
</code></pre></section></section><section><a id="Multi-Select_Statement_Support"></a>
<h2>Multi-Select Statement Support</h2>
<p>Multi-select statements are a special case of select statement. All the above information about row mappers applies
equally to multi-select statements.</p>
<p>The library does not provide a &#x201c;one-step&#x201d; shortcut for multi-select queries. You can execute a multi-select query
with the two-step method using either the &#x201c;selectList&#x201d; or &#x201c;selectOne&#x201d; extension methods as shown above.</p></section><section><a id="Update_Method_Support"></a>
<h2>Update Method Support</h2><section><a id="Two-Step_Method_8"></a>
<h3>Two-Step Method</h3>
<p>Update statements are constructed as shown on the Kotlin overview page. These methods create an
<code>UpdateStatementProvider</code> that can be executed with an extension method for <code>NamedParameterJdbcTemplate</code> like this:</p>

<pre><code class="language-kotlin">val updateStatement = update(...) // not shown... see overview page for examples
val template: NamedParameterJdbcTemplate = getTemplate() // not shown
val rows = template.update(updateStatement)  // rows is an Int
</code></pre></section><section><a id="One-Step_Method_8"></a>
<h3>One-Step Method</h3>
<p>Update statements can be constructed and executed in a single step with code like the following:</p>

<pre><code class="language-kotlin">val rows = template.update(Person) {
    set(firstName).equalTo(&quot;Sam&quot;)
    where { firstName isEqualTo &quot;Fred&quot; }
}
</code></pre>
<p>There a many set mappings that 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.</p>
<p>If you wish to update all rows in a table, simply omit the where clause:</p>

<pre><code class="language-kotlin">val rows = template.update(Person) {
    set(firstName).equalTo(&quot;Sam&quot;)
}
</code></pre></section></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>