-
Notifications
You must be signed in to change notification settings - Fork 212
/
Copy pathselect.html
341 lines (312 loc) · 18.8 KB
/
select.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
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
<!DOCTYPE html>
<!--
| Generated by Apache Maven Doxia Site Renderer 2.0.0-M18 from src/site/markdown/docs/select.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 class="active"><a><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-right"></span>Kotlin Support</a></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="Select_Statements"></a>
<h1>Select Statements</h1>
<p>Select statements are the most complex SQL statements. This library duplicates the syntax of the most common
select statements, but purposely does not cover every possibility.</p>
<p>In general, the following are supported:</p>
<ol style="list-style-type: decimal;">
<li>The typical parts of a select statement including SELECT, DISTINCT, FROM, JOIN, WHERE, GROUP BY, UNION,
UNION ALL, ORDER BY, HAVING</li>
<li>Tables can be aliased per select statement</li>
<li>Columns can be aliased per select statement</li>
<li>Some support for aggregates (avg, min, max, sum)</li>
<li>Equijoins of type INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER</li>
<li>Subqueries in where clauses. For example, <code>where foo in (select foo from foos where id < 36)</code></li>
<li>Select from another select. For example <code>select count(*) from (select foo from foos where id < 36)</code></li>
<li>Multi-Selects. For example <code>(select * from foo order by id limit 3) union (select * from foo order by id desc limit 3)</code></li>
</ol>
<p>At this time, the library does not support the following:</p>
<ol style="list-style-type: decimal;">
<li>WITH expressions</li>
<li>INTERSECT, EXCEPT, etc.</li>
</ol>
<p>The user guide page for WHERE Clauses shows examples of many types of SELECT statements with different complexities of
the WHERE clause including support for sub-queries. We will just show a single example here, including an ORDER BY clause:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
.from(animalData)
.where(id, isIn(1, 5, 7))
.and(bodyWeight, isBetween(1.0).and(3.0))
.orderBy(id.descending(), bodyWeight)
.build()
.render(RenderingStrategies.MYBATIS3);
List<AnimalData> animals = mapper.selectMany(selectStatement);
</code></pre>
<p>The WHERE and ORDER BY clauses are optional.</p><section><a id="Joins"></a>
<h2>Joins</h2>
<p>The library supports the generation of equijoin statements - joins defined by column matching. For example:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
.from(orderMaster, "om")
.join(orderDetail, "od").on(orderMaster.orderId, equalTo(orderDetail.orderId))
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre>
<p>Notice that you can give an alias to a table if desired. If you don't specify an alias, the full table name will be used in the generated SQL.</p>
<p>Multiple tables can be joined in a single statement. For example:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
.from(orderMaster, "om")
.join(orderLine, "ol").on(orderMaster.orderId, equalTo(orderLine.orderId))
.join(itemMaster, "im").on(orderLine.itemId, equalTo(itemMaster.itemId))
.where(orderMaster.orderId, isEqualTo(2))
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre>
<p>Join queries will likely require you to define a MyBatis result mapping in XML. This is the only instance where XML is required. This is due to the limitations of the MyBatis annotations when mapping collections.</p>
<p>The library supports four join types:</p>
<ol style="list-style-type: decimal;">
<li><code>.join(...)</code> is an INNER join</li>
<li><code>.leftJoin(...)</code> is a LEFT OUTER join</li>
<li><code>.rightJoin(...)</code> is a RIGHT OUTER join</li>
<li><code>.fullJoin(...)</code> is a FULL OUTER join</li>
</ol></section><section><a id="Union_Queries"></a>
<h2>Union Queries</h2>
<p>The library supports the generation of UNION and UNION ALL queries. For example:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
.from(animalData)
.union()
.selectDistinct(id, animalName, bodyWeight, brainWeight)
.from(animalData)
.orderBy(id)
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre>
<p>Any number of SELECT statements can be added to a UNION query. Only one ORDER BY phrase is allowed.</p>
<p>With this type of union query, the “order by” and paging clauses are applied to the query as a whole. If
you need to apply “order by” or paging clauses to the nested queries, use a multi-select query as shown
below.</p></section><section><a id="Multi-Select_Queries"></a>
<h2>Multi-Select Queries</h2>
<p>Multi-select queries are a special case of union select statements. The difference is that “order by” and
paging clauses can be applied to the merged queries. For example:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = multiSelect(
select(id, animalName, bodyWeight, brainWeight)
.from(animalData)
.orderBy(id)
.limit(2)
).union(
selectDistinct(id, animalName, bodyWeight, brainWeight)
.from(animalData)
.orderBy(id.descending())
.limit(3)
)
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre></section><section><a id="MyBatis_Mapper_for_Select_Statements"></a>
<h2>MyBatis Mapper for Select Statements</h2>
<p>The SelectStatementProvider object can be used as a parameter to a MyBatis mapper method directly. If you
are using an annotated mapper, the select method should look like this (note that we recommend coding a “selectMany” and a “selectOne” method with a shared result mapping):</p>
<pre><code class="language-java">import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
...
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@Results(id="AnimalDataResult", value={
@Result(column="id", property="id", id=true),
@Result(column="animal_name", property="animalName"),
@Result(column="brain_weight", property="brainWeight"),
@Result(column="body_weight", property="bodyWeight")
})
List<AnimalData> selectMany(SelectStatementProvider selectStatement);
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@ResultMap("AnimalDataResult")
AnimalData selectOne(SelectStatementProvider selectStatement);
...
</code></pre></section><section><a id="XML_Mapper_for_Join_Statements"></a>
<h2>XML Mapper for Join Statements</h2>
<p>If you are coding a join, it is likely you will need to code an XML mapper to define the result map. This is due to a MyBatis limitation - the annotations cannot define a collection mapping. If you have to do this, the Java code looks like this:</p>
<pre><code class="language-java"> @SelectProvider(type=SqlProviderAdapter.class, method="select")
@ResultMap("SimpleJoinResult")
List<OrderMaster> selectMany(SelectStatementProvider selectStatement);
</code></pre>
<p>And the corresponding XML looks like this:</p>
<pre><code class="language-xml"><mapper namespace="examples.joins.JoinMapper">
<resultMap id="SimpleJoinResult" type="examples.joins.OrderMaster">
<id column="order_id" jdbcType="INTEGER" property="id" />
<result column="order_date" jdbcType="DATE" property="orderDate" />
<collection property="details" ofType="examples.joins.OrderDetail">
<id column="order_id" jdbcType="INTEGER" property="orderId"/>
<id column="line_number" jdbcType="INTEGER" property="lineNumber"/>
<result column="description" jdbcType="VARCHAR" property="description"/>
<result column="quantity" jdbcType="INTEGER" property="quantity"/>
</collection>
</resultMap>
</mapper>
</code></pre>
<p>Notice that the resultMap is the only element in the XML mapper. This is our recommended practice.</p></section><section><a id="XML_Mapper_for_Select_Statements"></a>
<h2>XML Mapper for Select Statements</h2>
<p>We do not recommend using an XML mapper for select statements, but if you want to do so the SelectStatementProvider object can be used as a parameter to a MyBatis mapper method directly.</p>
<p>If you are using an XML mapper, the select method should look like this in the Java interface:</p>
<pre><code class="language-java">import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
...
List<AnimalData> selectMany(SelectStatementProvider selectStatement);
...
</code></pre>
<p>The XML element should look like this:</p>
<pre><code class="language-xml"> <resultMap id="animalResult" type="examples.animal.data.AnimalData">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="animal_name" jdbcType="VARCHAR" property="animalName" />
<result column="brain_weight" jdbcType="DOUBLE" property="brainWeight" />
<result column="body_weight" jdbcType="DOUBLE" property="bodyWeight" />
</resultMap>
<select id="selectMany" resultMap="animalResult">
${selectStatement}
</select>
</code></pre></section><section><a id="Notes_on_Order_By"></a>
<h2>Notes on Order By</h2>
<p>Order by phrases can be difficult to calculate when there are aliased columns, aliased tables, unions, and joins.
This library has taken a relatively simple approach:</p>
<ol style="list-style-type: decimal;">
<li>When specifying an SqlColumn in an ORDER BY phrase the library will either write the column alias or the column
name into the ORDER BY phrase. For the ORDER BY phrase, the table alias (if there is one) will be ignored. Use this pattern
when the ORDER BY column is a member of the select list. For example <code>orderBy(foo)</code>. If the column has an alias, then
it is easist to use the “arbitrary string” method with the column alias as shown below.</li>
<li>It is also possible to explicitly specify a table alias for a column in an ORDER BY phrase. Use this pattern when
there is a join, and the ORDER BY column is in two or more tables, and the ORDER BY column is not in the select
list. For example <code>orderBy(sortColumn("t1", foo))</code>.</li>
<li>If none of the above use cases meet your needs, then you can specify an arbitrary String to write into the rendered ORDER BY
phrase (see below for an example).</li>
</ol>
<p>In our testing, this caused an issue in only one case. When there is an outer join and the select list contains
both the left and right join column. In that case, the workaround is to supply a column alias for both columns.</p>
<p>When using a column function (lower, upper, etc.), then it is customary to give the calculated column an alias so you will have a predictable result set. In cases like this there will not be a column to use for an alias. The library supports arbitrary values in an ORDER BY expression like this:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(substring(gender, 1, 1).as("ShortGender"), avg(age).as("AverageAge"))
.from(person, "a")
.groupBy(substring(gender, 1, 1))
.orderBy(sortColumn("ShortGender").descending())
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre>
<p>In this example the <code>substring</code> function is used in both the select list and the GROUP BY expression. In the ORDER BY expression, we use the <code>sortColumn</code> function to duplicate the alias given to the column in the select list.</p></section><section><a id="Limit_and_Offset_Support"></a>
<h2>Limit and Offset Support</h2>
<p>Since version 1.1.1 the select statement supports limit and offset for paging (or slicing) queries. You can specify:</p>
<ul>
<li>Limit only</li>
<li>Offset only</li>
<li>Both limit and offset</li>
</ul>
<p>It is important to note that the select renderer writes limit and offset clauses into the generated select statement as is. The library does not attempt to normalize those values for databases that don't support limit and offset directly. Therefore, it is very important for users to understand whether or not the target database supports limit and offset. If the target database does not support limit and offset, then it is likely that using this support will create SQL that has runtime errors.</p>
<p>An example follows:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(animalData.allColumns())
.from(animalData)
.orderBy(id)
.limit(3)
.offset(22)
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre></section><section><a id="Fetch_First_Support"></a>
<h2>Fetch First Support</h2>
<p>Since version 1.1.2 the select statement supports fetch first for paging (or slicing) queries. You can specify:</p>
<ul>
<li>Fetch first only</li>
<li>Offset only</li>
<li>Both offset and fetch first</li>
</ul>
<p>Fetch first is an SQL standard and is supported by most databases.</p>
<p>An example follows:</p>
<pre><code class="language-java"> SelectStatementProvider selectStatement = select(animalData.allColumns())
.from(animalData)
.orderBy(id)
.offset(22)
.fetchFirst(3).rowsOnly()
.build()
.render(RenderingStrategies.MYBATIS3);
</code></pre></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>