-
Notifications
You must be signed in to change notification settings - Fork 212
/
Copy pathkotlinCaseExpressions.html
280 lines (262 loc) · 14.1 KB
/
kotlinCaseExpressions.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
<!DOCTYPE html>
<!--
| Generated by Apache Maven Doxia Site Renderer 2.0.0-M18 from src/site/markdown/docs/kotlinCaseExpressions.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'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 class="active"><a>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="Case_Expressions_in_the_Kotlin_DSL"></a>
<h1>Case Expressions in the Kotlin DSL</h1>
<p>Support for case expressions was added in version 1.5.1. For information about case expressions in the Java DSL, see
the <a href="caseExpressions.html">Java Case Expressions</a> page.</p><section><a id="Case_Statements_in_SQL"></a>
<h2>Case Statements in SQL</h2>
<p>The library supports different types of case expressions - a “simple” case expression, and a “searched” case
expressions.</p>
<p>A simple case expression checks the values of a single column. It looks like this:</p>
<pre><code class="language-sql">select case id
when 1, 2, 3 then true
else false
end as small_id
from foo
</code></pre>
<p>Some databases also support simple comparisons on simple case expressions, which look lke this:</p>
<pre><code class="language-sql">select case total_length
when < 10 then 'small'
when > 20 then 'large'
else 'medium'
end as tshirt_size
from foo
</code></pre>
<p>A searched case expression allows arbitrary logic, and it can check the values of multiple columns. It looks like this:</p>
<pre><code class="language-sql">select case
when animal_name = 'Small brown bat' or animal_name = 'Large brown bat' then 'Bat'
when animal_name = 'Artic fox' or animal_name = 'Red fox' then 'Fox'
else 'Other'
end as animal_type
from foo
</code></pre></section><section><a id="Bind_Variables_and_Casting"></a>
<h2>Bind Variables and Casting</h2>
<p>The library will always render the “when” part of a case expression using bind variables. Rendering of the “then” and
“else” parts of a case expression may or may not use bind variables depending on how you write the query. In general,
the library will render “then” and “else” as constants - meaning not using bind variables. If you wish to use bind
variables for these parts of a case expressions, then you can use the <code>value</code> function to turn a constant into a
bind variable. We will show examples of the different renderings in the following sections.</p>
<p>If you choose to use bind variables for all “then” and “else” values, it is highly likely that the database will
require you to specify an expected datatype by using a <code>cast</code> function.</p>
<p>Even for “then” and “else” sections that are rendered with constants, you may still desire to use a <code>cast</code> in some
cases. For example, if you specify Strings for all “then” and “else” values, the database will likely return all
values as datatype CHAR with the length of the longest constant string. Typically, we would prefer the use of VARCHAR,
so we don't have to strip trailing blanks from the results. This is a good use for a <code>cast</code> with a constant.
Similarly, Kotlin float constants are often interpreted by databases as BigDecimal. You can use a <code>cast</code> to have them
returned as floats.</p>
<p>Note: in the following sections we will use <code>?</code> to show a bind variable, but the actual rendered SQL will be different
because bind variables will be rendered appropriately for the execution engine you are using (either MyBatis or Spring).</p>
<p>Also note: in Kotlin, <code>when</code> and <code>else</code> are reserved words - meaning we cannot use them as method names. For this
reason, the library uses <code>`when`</code> and <code>`else`</code> respectively as method names.</p>
<p>Full examples for case expressions are in the test code for the library here:
<a href="https://github.com/mybatis/mybatis-dynamic-sql/blob/master/src/test/kotlin/examples/kotlin/animal/data/KCaseExpressionTest.kt" class="externalLink">https://github.com/mybatis/mybatis-dynamic-sql/blob/master/src/test/kotlin/examples/kotlin/animal/data/KCaseExpressionTest.kt</a></p></section><section><a id="Kotlin_DSL_for_Simple_Case_Statements_with_Simple_Values"></a>
<h2>Kotlin DSL for Simple Case Statements with Simple Values</h2>
<p>A simple case expression can be coded like the following in the Kotlin DSL:</p>
<pre><code class="language-kotlin">select(case(id) {
`when`(1, 2, 3) then true
`else`(false)
} `as` "small_id"
) {
from(foo)
}
</code></pre>
<p>A statement written this way will render as follows:</p>
<pre><code class="language-sql">select case id when ?, ?, ? then true else false end as small_id from foo
</code></pre>
<p>Note that the “then” and “else” parts are NOT rendered with bind variables. If you with to use bind variables, then
you can write the query as follows:</p>
<pre><code class="language-kotlin">select(case(id) {
`when`(1, 2, 3) then value(true)
`else`(value(false))
} `as` "small_id"
) {
from(foo)
}
</code></pre>
<p>In this case, we are using the <code>value</code> function to denote a bind variable. The SQL will now be rendered as follows:</p>
<pre><code class="language-sql">select case id when ?, ?, ? then ? else ? end as small_id from foo
</code></pre>
<p><em>Important</em>: Be aware that your database may throw an exception for SQL like this because the database cannot determine
the datatype of the resulting column. If that happens, you will need to cast one or more of the variables to the
expected data type. Here's an example of using the <code>cast</code> function:</p>
<pre><code class="language-kotlin">select(case(id) {
`when`(1, 2, 3) then value(true)
`else`(cast { value(false) `as` "BOOLEAN" })
} `as` "small_id"
) {
from(foo)
}
</code></pre>
<p>In this case, the SQL will render as follows:</p>
<pre><code class="language-sql">select case id when ?, ?, ? then ? else cast(? as BOOLEAN) end as small_id from foo
</code></pre>
<p>In our testing, casting a single bound value is enough to inform the database of your expected datatype, but
you should perform your own testing.</p></section><section><a id="Kotlin_DSL_for_Simple_Case_Statements_with_Conditions"></a>
<h2>Kotlin DSL for Simple Case Statements with Conditions</h2>
<p>A simple case expression can be coded like the following in the Kotlin DSL:</p>
<pre><code class="language-kotlin">select(case(total_length) {
`when`(isLessThan(10)) then "small"
`when`(isGreaterThan(20)) then "large"
`else`("medium")
} `as` "tshirt_size"
) {
from(foo)
}
</code></pre>
<p>A statement written this way will render as follows:</p>
<pre><code class="language-sql">select case total_length when < ? then 'small' when > ? then 'large' else 'medium' end as tshirt_size from foo
</code></pre>
<p>Note that the “then” and “else” parts are NOT rendered with bind variables. If you with to use bind variables, then
you can use the <code>value</code> function as shown above.</p>
<p>A query like this could be a good place to use casting with constants. Most databases will return the calculated
“tshirt_size” column as CHAR(6) - so the “small” and “large” values will have a trailing blank. If you wish to use
VARCHAR, you can use the <code>cast</code> function as follows:</p>
<pre><code class="language-kotlin">select(case(total_length) {
`when`(isLessThan(10)) then "small"
`when`(isGreaterThan(20)) then "large"
`else`(cast { "medium" `as` "VARCHAR(6)" })
} `as` "tshirt_size"
) {
from(foo)
}
</code></pre>
<p>In this case, we are using the <code>cast</code> function to specify the datatype of a constant. The SQL will now be rendered as
follows (without the line breaks):</p>
<pre><code class="language-sql">select case total_length
when < ? then 'small' when > ? then 'large'
else cast('medium' as VARCHAR(6)) end as tshirt_size from foo
</code></pre></section><section><a id="Kotlin_DSL_for_Searched_Case_Statements"></a>
<h2>Kotlin DSL for Searched Case Statements</h2>
<p>A searched case statement is written as follows:</p>
<pre><code class="language-kotlin">select(case {
`when` {
animalName isEqualTo "Small brown bat"
or { animalName isEqualTo "Large brown bat"}
then("Bat")
}
`when` {
animalName isEqualTo "Artic fox"
or { animalName isEqualTo "Red fox"}
then("Fox")
}
`else`("Other")
} `as` "animal_type"
) {
from(foo)
}
</code></pre>
<p>The full syntax of “where” and “having” clauses is supported in the “when” clause - but that may or may not be supported
by your database. Testing is crucial. The library supports optional conditions in “when” clauses, but at least one
condition must render, else the library will throw an <code>InvalidSqlException</code>.</p>
<p>The rendered SQL will be as follows (without the line breaks):</p>
<pre><code class="language-sql">select case
when animal_name = ? or animal_name = ? then 'Bat'
when animal_name = ? or animal_name = ? then 'Fox'
else 'Other'
end as animal_type
from foo
</code></pre>
<p>The use of the <code>value</code> function to support bind variables, and the use of casting, is the same is shown above.</p></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>