-
Notifications
You must be signed in to change notification settings - Fork 12.9k
/
Copy pathsqlmap-xml.html
1343 lines (1223 loc) · 92.9 KB
/
sqlmap-xml.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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<!--
| Generated by Apache Maven Doxia Site Renderer 2.0.0 from src/site/markdown/sqlmap-xml.md at 02 Jan 2025
| Rendered using Apache Maven Fluido Skin 2.0.1
-->
<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" />
<meta name="author" content="Clinton Begin" />
<title>MyBatis 3 | Mapper XML Files – mybatis</title>
<link rel="stylesheet" href="./css/apache-maven-fluido-2.0.1.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.1.min.js"></script>
</head>
<body>
<div class="container-fluid container-fluid-top">
<header>
<div id="banner">
<div class="pull-left"></div>
<div class="pull-right"><div id="bannerRight"><h1><a href="https://blog.mybatis.org/"><img class="class java.lang.Object" src="../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: 02 Jan 2025<span class="divider">|</span>
</li>
<li id="projectVersion">Version: 3.5.19</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">Reference Documentation</li>
<li><a href="index.html">Introduction</a></li>
<li><a href="getting-started.html">Getting Started</a></li>
<li><a href="configuration.html"><span class="icon-chevron-right"></span>Configuration XML</a></li>
<li class="active"><a><span class="icon-chevron-down"></span>Mapper XML Files</a>
<ul class="nav nav-list">
<li><a href="sqlmap-xml.html#select">select</a></li>
<li><a href="sqlmap-xml.html#insert-update-and-delete">insert, update and delete</a></li>
<li><a href="sqlmap-xml.html#parameters">Parameters</a></li>
<li><a href="sqlmap-xml.html#result-maps">Result Maps</a></li>
<li><a href="sqlmap-xml.html#auto-mapping">Auto-mapping</a></li>
<li><a href="sqlmap-xml.html#cache">cache</a></li>
</ul></li>
<li><a href="dynamic-sql.html">Dynamic SQL</a></li>
<li><a href="java-api.html"><span class="icon-chevron-right"></span>Java API</a></li>
<li><a href="statement-builders.html">SQL Builder Class</a></li>
<li><a href="logging.html">Logging</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="Avoid_blank_site"></a>
<h1 class="d-none">Avoid blank site</h1>
<section><a id="Mapper_XML_Files"></a>
<h2>Mapper XML Files</h2>
<p>The true power of MyBatis is in the Mapped Statements. This is where the magic happens. For all of their power, the Mapper XML files are relatively simple. Certainly if you were to compare them to the equivalent JDBC code, you would immediately see a savings of 95% of the code. MyBatis was built to focus on the SQL, and does its best to stay out of your way.</p>
<p>The Mapper XML files have only a few first class elements (in the order that they should be defined):</p>
<ul>
<li><code>cache</code> – Configuration of the cache for a given namespace.</li>
<li><code>cache-ref</code> – Reference to a cache configuration from another namespace.</li>
<li><code>resultMap</code> – The most complicated and powerful element that describes how to load your objects from the database result sets.</li>
<li><del><code>parameterMap</code> – Deprecated! Old-school way to map parameters. Inline parameters are preferred and this element may be removed in the future. Not documented here.</del></li>
<li><code>sql</code> – A reusable chunk of SQL that can be referenced by other statements.</li>
<li><code>insert</code> – A mapped INSERT statement.</li>
<li><code>update</code> – A mapped UPDATE statement.</li>
<li><code>delete</code> – A mapped DELETE statement.</li>
<li><code>select</code> – A mapped SELECT statement.</li>
</ul>
<p>The next sections will describe each of these elements in detail, starting with the statements themselves.</p><section><a id="select"></a>
<h3>select</h3>
<p>The select statement is one of the most popular elements that you'll use in MyBatis. Putting data in a database isn't terribly valuable until you get it back out, so most applications query far more than they modify the data. For every insert, update or delete, there are probably many selects. This is one of the founding principles of MyBatis, and is the reason so much focus and effort was placed on querying and result mapping. The select element is quite simple for simple cases. For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>
</code></pre>
<p>This statement is called selectPerson, takes a parameter of type int (or Integer), and returns a HashMap keyed by column names mapped to row values.</p>
<p>Notice the parameter notation:</p>
<pre><code class="nohighlight nocode">#{id}
</code></pre>
<p>This tells MyBatis to create a PreparedStatement parameter. With JDBC, such a parameter would be identified by a “?” in SQL passed to a new PreparedStatement, something like this:</p>
<pre class="prettyprint"><code class="language-java">// Similar JDBC code, NOT MyBatis…
String selectPerson = "SELECT * FROM PERSON WHERE ID=?";
PreparedStatement ps = conn.prepareStatement(selectPerson);
ps.setInt(1,id);
</code></pre>
<p>Of course, there's a lot more code required by JDBC alone to extract the results and map them to an instance of an object, which is what MyBatis saves you from having to do. There's a lot more to know about parameter and result mapping. Those details warrant their own section, which follows later in this section.</p>
<p>The select element has more attributes that allow you to configure the details of how each statement should behave.</p>
<pre class="prettyprint"><code class="language-xml"><select
id="selectPerson"
parameterType="int"
parameterMap="deprecated"
resultType="hashmap"
resultMap="personResultMap"
flushCache="false"
useCache="true"
timeout="10"
fetchSize="256"
statementType="PREPARED"
resultSetType="FORWARD_ONLY">
</code></pre>
<table class="table table-striped"><caption>Select Attributes</caption>
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>id</code></td>
<td>A unique identifier in this namespace that can be used to reference this statement.</td></tr>
<tr class="a">
<td><code>parameterType</code></td>
<td>The fully qualified class name or alias for the parameter that will be passed into this statement. This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is <code>unset</code>.</td></tr>
<tr class="b">
<td><code>parameterMap</code></td>
<td>This is a deprecated approach to referencing an external <code>parameterMap</code>. Use inline parameter mappings and the <code>parameterType</code> attribute.</td></tr>
<tr class="a">
<td><code>resultType</code></td>
<td>The fully qualified class name or alias for the expected type that will be returned from this statement. Note that in the case of collections, this should be the type that the collection contains, not the type of the collection itself. Use <code>resultType</code> OR <code>resultMap</code>, not both.</td></tr>
<tr class="b">
<td><code>resultMap</code></td>
<td>A named reference to an external <code>resultMap</code>. Result maps are the most powerful feature of MyBatis, and with a good understanding of them, many difficult mapping cases can be solved. Use <code>resultMap</code> OR <code>resultType</code>, not both.</td></tr>
<tr class="a">
<td><code>flushCache</code></td>
<td>Setting this to true will cause the local and 2nd level caches to be flushed whenever this statement is called. Default: <code>false</code> for select statements.</td></tr>
<tr class="b">
<td><code>useCache</code></td>
<td>Setting this to true will cause the results of this statement to be cached in 2nd level cache. Default: <code>true</code> for select statements.</td></tr>
<tr class="a">
<td><code>timeout</code></td>
<td>This sets the number of seconds the driver will wait for the database to return from a request, before throwing an exception. Default is <code>unset</code> (driver dependent).</td></tr>
<tr class="b">
<td><code>fetchSize</code></td>
<td>This is a driver hint that will attempt to cause the driver to return results in batches of rows numbering in size equal to this setting. Default is <code>unset</code> (driver dependent).</td></tr>
<tr class="a">
<td><code>statementType</code></td>
<td>Any one of <code>STATEMENT</code>, <code>PREPARED</code> or <code>CALLABLE</code>. This causes MyBatis to use <code>Statement</code>, <code>PreparedStatement</code> or <code>CallableStatement</code> respectively. Default: <code>PREPARED</code>.</td></tr>
<tr class="b">
<td><code>resultSetType</code></td>
<td>Any one of <code>FORWARD_ONLY</code>|<code>SCROLL_SENSITIVE</code>|<code>SCROLL_INSENSITIVE</code>|<code>DEFAULT</code>(same as unset). Default is <code>unset</code> (driver dependent).</td></tr>
<tr class="a">
<td><code>databaseId</code></td>
<td>In case there is a configured databaseIdProvider, MyBatis will load all statements with no <code>databaseId</code> attribute or with a <code>databaseId</code> that matches the current one. If case the same statement if found with and without the <code>databaseId</code> the latter will be discarded.</td></tr>
<tr class="b">
<td><code>resultOrdered</code></td>
<td>This is only applicable for nested result select statements: If this is true, it is assumed that nested results are contained or grouped together such that when a new main result row is returned, no references to a previous result row will occur anymore. This allows nested results to be filled much more memory friendly. Default: <code>false</code>.</td></tr>
<tr class="a">
<td><code>resultSets</code></td>
<td>This is only applicable for multiple result sets. It lists the result sets that will be returned by the statement and gives a name to each one. Names are separated by commas.</td></tr>
<tr class="b">
<td><code>affectData</code></td>
<td>Set this to true when writing a INSERT, UPDATE or DELETE statement that returns data so that the transaction is controlled properly. Also see <a href="./java-api.html#transaction-control-methods">Transaction Control Method</a>. Default: <code>false</code> (since 3.5.12)</td></tr></tbody>
</table>
</section><section><a id="insert.2C_update_and_delete"></a>
<h3>insert, update and delete</h3>
<p>The data modification statements insert, update and delete are very similar in their implementation:</p>
<pre class="prettyprint"><code class="language-xml"><insert
id="insertAuthor"
parameterType="domain.blog.Author"
flushCache="true"
statementType="PREPARED"
keyProperty=""
keyColumn=""
useGeneratedKeys=""
timeout="20">
<update
id="updateAuthor"
parameterType="domain.blog.Author"
flushCache="true"
statementType="PREPARED"
timeout="20">
<delete
id="deleteAuthor"
parameterType="domain.blog.Author"
flushCache="true"
statementType="PREPARED"
timeout="20">
</code></pre>
<table class="table table-striped"><caption>Insert, Update and Delete Attributes</caption>
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>id</code></td>
<td>A unique identifier in this namespace that can be used to reference this statement.</td></tr>
<tr class="a">
<td><code>parameterType</code></td>
<td>The fully qualified class name or alias for the parameter that will be passed into this statement. This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is <code>unset</code>.</td></tr>
<tr class="b">
<td><del><code>parameterMap</code></del></td>
<td><del>This is a deprecated approach to referencing an external parameterMap. Use inline parameter mappings and the parameterType attribute.</del></td></tr>
<tr class="a">
<td><code>flushCache</code></td>
<td>Setting this to true will cause the 2nd level and local caches to be flushed whenever this statement is called. Default: <code>true</code> for insert, update and delete statements.</td></tr>
<tr class="b">
<td><code>timeout</code></td>
<td>This sets the maximum number of seconds the driver will wait for the database to return from a request, before throwing an exception. Default is <code>unset</code> (driver dependent).</td></tr>
<tr class="a">
<td><code>statementType</code></td>
<td>Any one of <code>STATEMENT</code>, <code>PREPARED</code> or <code>CALLABLE</code>. This causes MyBatis to use <code>Statement</code>, <code>PreparedStatement</code> or <code>CallableStatement</code> respectively. Default: <code>PREPARED</code>.</td></tr>
<tr class="b">
<td><code>useGeneratedKeys</code></td>
<td>(insert and update only) This tells MyBatis to use the JDBC <code>getGeneratedKeys</code> method to retrieve keys generated internally by the database (e.g. auto increment fields in RDBMS like MySQL or SQL Server). Default: <code>false</code>.</td></tr>
<tr class="a">
<td><code>keyProperty</code></td>
<td>(insert and update only) Identifies a property into which MyBatis will set the key value returned by <code>getGeneratedKeys</code>, or by a <code>selectKey</code> child element of the insert statement. Default: <code>unset</code>. Can be a comma separated list of property names if multiple generated columns are expected.</td></tr>
<tr class="b">
<td><code>keyColumn</code></td>
<td>(insert and update only) Sets the name of the column in the table with a generated key. This is only required in certain databases (like PostgreSQL) when the key column is not the first column in the table. Can be a comma separated list of columns names if multiple generated columns are expected.</td></tr>
<tr class="a">
<td><code>databaseId</code></td>
<td>In case there is a configured databaseIdProvider, MyBatis will load all statements with no <code>databaseId</code> attribute or with a <code>databaseId</code> that matches the current one. If case the same statement if found with and without the <code>databaseId</code> the latter will be discarded.</td></tr></tbody>
</table>
<p>The following are some examples of insert, update and delete statements.</p>
<pre class="prettyprint"><code class="language-xml"><insert id="insertAuthor">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>
<update id="updateAuthor">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
<delete id="deleteAuthor">
delete from Author where id = #{id}
</delete>
</code></pre>
<p>As mentioned, insert is a little bit more rich in that it has a few extra attributes and sub-elements that allow it to deal with key generation in a number of ways.</p>
<p>First, if your database supports auto-generated key fields (e.g. MySQL and SQL Server), then you can simply set <code>useGeneratedKeys="true"</code> and set the <code>keyProperty</code> to the target property and you're done. For example, if the <code>Author</code> table above had used an auto-generated column type for the id, the statement would be modified as follows:</p>
<pre class="prettyprint"><code class="language-xml"><insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username,password,email,bio)
values (#{username},#{password},#{email},#{bio})
</insert>
</code></pre>
<p>If your database also supports multi-row insert, you can pass a list or an array of <code>Author</code>s and retrieve the auto-generated keys.</p>
<pre class="prettyprint"><code class="language-xml"><insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username, password, email, bio) values
<foreach item="item" collection="list" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.bio})
</foreach>
</insert>
</code></pre>
<p>MyBatis has another way to deal with key generation for databases that don't support auto-generated column types, or perhaps don't yet support the JDBC driver support for auto-generated keys.</p>
<p>Here's a simple (silly) example that would generate a random ID (something you'd likely never do, but this demonstrates the flexibility and how MyBatis really doesn't mind):</p>
<pre class="prettyprint"><code class="language-xml"><insert id="insertAuthor">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
</selectKey>
insert into Author
(id, username, password, email,bio, favourite_section)
values
(#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>
</code></pre>
<p>In the example above, the selectKey statement would be run first, the <code>Author</code> id property would be set, and then the insert statement would be called. This gives you a similar behavior to an auto-generated key in your database without complicating your Java code.</p>
<p>The selectKey element is described as follows:</p>
<pre class="prettyprint"><code class="language-xml"><selectKey
keyProperty="id"
resultType="int"
order="BEFORE"
statementType="PREPARED">
</code></pre>
<table class="table table-striped"><caption>selectKey Attributes</caption>
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>keyProperty</code></td>
<td>The target property where the result of the <code>selectKey</code> statement should be set. Can be a comma separated list of property names if multiple generated columns are expected.</td></tr>
<tr class="a">
<td><code>keyColumn</code></td>
<td>The column name(s) in the returned result set that match the properties. Can be a comma separated list of column names if multiple generated columns are expected.</td></tr>
<tr class="b">
<td><code>resultType</code></td>
<td>The type of the result. MyBatis can usually figure this out, but it doesn't hurt to add it to be sure. MyBatis allows any simple type to be used as the key, including Strings. If you are expecting multiple generated columns, then you can use an Object that contains the expected properties, or a Map.</td></tr>
<tr class="a">
<td><code>order</code></td>
<td>This can be set to <code>BEFORE</code> or <code>AFTER</code>. If set to <code>BEFORE</code>, then it will select the key first, set the <code>keyProperty</code> and then execute the insert statement. If set to <code>AFTER</code>, it runs the insert statement and then the <code>selectKey</code> statement – which is common with databases like Oracle that may have embedded sequence calls inside of insert statements.</td></tr>
<tr class="b">
<td><code>statementType</code></td>
<td>Same as above, MyBatis supports <code>STATEMENT</code>, <code>PREPARED</code> and <code>CALLABLE</code> statement types that map to <code>Statement</code>, <code>PreparedStatement</code> and <code>CallableStatement</code> respectively.</td></tr></tbody>
</table>
<p>As an irregular case, some databases allow INSERT, UPDATE or DELETE statement to return result set (e.g. <code>RETURNING</code> clause of PostgreSQL and MariaDB or <code>OUTPUT</code> clause of MS SQL Server). This type of statement must be written as <code><select></code> to map the returned data.</p>
<pre class="prettyprint"><code class="language-xml"><select id="insertAndGetAuthor" resultType="domain.blog.Author"
affectData="true" flushCache="true">
insert into Author (username, password, email, bio)
values (#{username}, #{password}, #{email}, #{bio})
returning id, username, password, email, bio
</select>
</code></pre></section><section><a id="sql"></a>
<h3>sql</h3>
<p>This element can be used to define a reusable fragment of SQL code that can be included in other statements. It can be statically (during load phase) parametrized. Different property values can vary in include instances. For example:</p>
<pre class="prettyprint"><code class="language-xml"><sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
</code></pre>
<p>The SQL fragment can then be included in another statement, for example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from some_table t1
cross join some_table t2
</select>
</code></pre>
<p>Property value can be also used in include refid attribute or property values inside include clause, for example:</p>
<pre class="prettyprint"><code class="language-xml"><sql id="sometable">
${prefix}Table
</sql>
<sql id="someinclude">
from
<include refid="${include_target}"/>
</sql>
<select id="select" resultType="map">
select
field1, field2, field3
<include refid="someinclude">
<property name="prefix" value="Some"/>
<property name="include_target" value="sometable"/>
</include>
</select>
</code></pre></section><section><a id="Parameters"></a>
<h3>Parameters</h3>
<p>In all of the past statements, you've seen examples of simple parameters. Parameters are very powerful elements in MyBatis. For simple situations, probably 90% of the cases, there's not much to them, for example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectUsers" resultType="User">
select id, username, password
from users
where id = #{id}
</select>
</code></pre>
<p>The example above demonstrates a very simple named parameter mapping. The parameterType is set to <code>int</code>, so therefore the parameter could be named anything. Primitive or simple data types such as <code>Integer</code> and <code>String</code> have no relevant properties, and thus will replace the full value of the parameter entirely. However, if you pass in a complex object, then the behavior is a little different. For example:</p>
<pre class="prettyprint"><code class="language-xml"><insert id="insertUser" parameterType="User">
insert into users (id, username, password)
values (#{id}, #{username}, #{password})
</insert>
</code></pre>
<p>If a parameter object of type User was passed into that statement, the id, username and password property would be looked up and their values passed to a <code>PreparedStatement</code> parameter.</p>
<p>That's nice and simple for passing parameters into statements. But there are a lot of other features of parameter maps.</p>
<p>First, like other parts of MyBatis, parameters can specify a more specific data type.</p>
<pre><code class="nohighlight nocode">#{property,javaType=int,jdbcType=NUMERIC}
</code></pre>
<p>Like the rest of MyBatis, the javaType can almost always be determined from the parameter object, unless that object is a <code>HashMap</code>. Then the <code>javaType</code> should be specified to ensure the correct <code>TypeHandler</code> is used.</p>
<p><span class="label important">NOTE</span> The JDBC Type is required by JDBC for all nullable columns, if <code>null</code> is passed as a value. You can investigate this yourself by reading the JavaDocs for the <code>PreparedStatement.setNull()</code> method.</p>
<p>To further customize type handling, you can also specify a specific <code>TypeHandler</code> class (or alias), for example:</p>
<pre><code class="nohighlight nocode">#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}
</code></pre>
<p>So already it seems to be getting verbose, but the truth is that you'll rarely set any of these.</p>
<p>For numeric types there's also a <code>numericScale</code> for determining how many decimal places are relevant.</p>
<pre><code class="nohighlight nocode">#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}
</code></pre>
<p>Finally, the mode attribute allows you to specify <code>IN</code>, <code>OUT</code> or <code>INOUT</code> parameters. If a parameter is <code>OUT</code> or <code>INOUT</code>, the actual value of the parameter object property will be changed, just as you would expect if you were calling for an output parameter. If the <code>mode=OUT</code> (or <code>INOUT</code>) and the <code>jdbcType=CURSOR</code> (i.e. Oracle REFCURSOR), you must specify a <code>resultMap</code> to map the <code>ResultSet</code> to the type of the parameter. Note that the <code>javaType</code> attribute is optional here, it will be automatically set to <code>ResultSet</code> if left blank with a <code>CURSOR</code> as the <code>jdbcType</code>.</p>
<pre><code class="nohighlight nocode">#{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentResultMap}
</code></pre>
<p>MyBatis also supports more advanced data types such as structs, but you must tell the statement the type name when registering the out parameter. For example (again, don't break lines like this in practice):</p>
<pre><code class="nohighlight nocode">#{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departmentResultMap}
</code></pre>
<p>Despite all of these powerful options, most of the time you'll simply specify the property name, and MyBatis will figure out the rest. At most, you'll specify the <code>jdbcType</code> for nullable columns.</p>
<pre><code class="nohighlight nocode">#{firstName}
#{middleInitial,jdbcType=VARCHAR}
#{lastName}
</code></pre><section><a id="String_Substitution"></a>
<h4>String Substitution</h4>
<p>By default, using the <code>#{}</code> syntax will cause MyBatis to generate <code>PreparedStatement</code> properties and set the values safely against the <code>PreparedStatement</code> parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject an unmodified string into the SQL Statement. For example, for ORDER BY, you might use something like this:</p>
<pre class="prettyprint"><code class="language-sql">ORDER BY ${columnName}
</code></pre>
<p>Here MyBatis won't modify or escape the string.</p>
<p>String Substitution can be very useful when the metadata(i.e. table name or column name) in the sql statement is dynamic, for example, if you want to <code>select</code> from a table by any one of its columns, instead of writing code like:</p>
<pre class="prettyprint"><code class="language-java">@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);
@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);
@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);
// and more "findByXxx" method
</code></pre>
<p>you can just write:</p>
<pre class="prettyprint"><code class="language-java">@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);
</code></pre>
<p>in which the <code>${column}</code> will be substituted directly and the <code>#{value}</code> will be “prepared”. Thus you can just do the same work by:</p>
<pre class="prettyprint"><code class="language-java">User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");
</code></pre>
<p>This idea can be applied to substitute the table name as well.</p>
<p><span class="label important">NOTE</span> It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks.</p></section></section><section><a id="Result_Maps"></a>
<h3>Result Maps</h3>
<p>The <code>resultMap</code> element is the most important and powerful element in MyBatis. It's what allows you to do away with 90% of the code that JDBC requires to retrieve data from <code>ResultSet</code>s, and in some cases allows you to do things that JDBC does not even support. In fact, to write the equivalent code for something like a join mapping for a complex statement could probably span thousands of lines of code. The design of the <code>ResultMap</code>s is such that simple statements don't require explicit result mappings at all, and more complex statements require no more than is absolutely necessary to describe the relationships.</p>
<p>You've already seen examples of simple mapped statements that don't have an explicit <code>resultMap</code>. For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectUsers" resultType="map">
select id, username, hashedPassword
from some_table
where id = #{id}
</select>
</code></pre>
<p>Such a statement simply results in all columns being automatically mapped to the keys of a <code>HashMap</code>, as specified by the <code>resultType</code> attribute. While useful in many cases, a <code>HashMap</code> doesn't make a very good domain model. It's more likely that your application will use JavaBeans or POJOs (Plain Old Java Objects) for the domain model. MyBatis supports both. Consider the following JavaBean:</p>
<pre class="prettyprint"><code class="language-java">package com.someapp.model;
public class User {
private int id;
private String username;
private String hashedPassword;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getHashedPassword() {
return hashedPassword;
}
public void setHashedPassword(String hashedPassword) {
this.hashedPassword = hashedPassword;
}
}
</code></pre>
<p>Based on the JavaBeans specification, the above class has 3 properties: id, username, and hashedPassword. These match up exactly with the column names in the select statement.</p>
<p>Such a JavaBean could be mapped to a <code>ResultSet</code> just as easily as the <code>HashMap</code>.</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectUsers" resultType="com.someapp.model.User">
select id, username, hashedPassword
from some_table
where id = #{id}
</select>
</code></pre>
<p>And remember that TypeAliases are your friends. Use them so that you don't have to keep typing the fully qualified path of your class out. For example:</p>
<pre class="prettyprint"><code class="language-xml"><!-- In Config XML file -->
<typeAlias type="com.someapp.model.User" alias="User"/>
<!-- In SQL Mapping XML file -->
<select id="selectUsers" resultType="User">
select id, username, hashedPassword
from some_table
where id = #{id}
</select>
</code></pre>
<p>In these cases MyBatis is automatically creating a <code>ResultMap</code> behind the scenes to auto-map the columns to the JavaBean properties based on name. If the column names did not match exactly, you could employ select clause aliases (a standard SQL feature) on the column names to make the labels match. For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectUsers" resultType="User">
select
user_id as "id",
user_name as "userName",
hashed_password as "hashedPassword"
from some_table
where id = #{id}
</select>
</code></pre>
<p>The great thing about <code>ResultMap</code>s is that you've already learned a lot about them, but you haven't even seen one yet! These simple cases don't require any more than you've seen here. Just for example sake, let's see what this last example would look like as an external <code>resultMap</code>, as that is another way to solve column name mismatches.</p>
<pre class="prettyprint"><code class="language-xml"><resultMap id="userResultMap" type="User">
<id property="id" column="user_id" />
<result property="username" column="user_name"/>
<result property="password" column="hashed_password"/>
</resultMap>
</code></pre>
<p>And the statement that references it uses the <code>resultMap</code> attribute to do so (notice we removed the <code>resultType</code> attribute). For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectUsers" resultMap="userResultMap">
select user_id, user_name, hashed_password
from some_table
where id = #{id}
</select>
</code></pre>
<p>Now if only the world was always that simple.</p><section><a id="Advanced_Result_Maps"></a>
<h4>Advanced Result Maps</h4>
<p>MyBatis was created with one idea in mind: Databases aren't always what you want or need them to be. While we'd love every database to be perfect 3rd normal form or BCNF, they aren't. And it would be great if it was possible to have a single database map perfectly to all of the applications that use it, it's not. Result Maps are the answer that MyBatis provides to this problem.</p>
<p>For example, how would we map this statement?</p>
<pre class="prettyprint"><code class="language-xml"><!-- Very Complex Statement -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio,
A.favourite_section as author_favourite_section,
P.id as post_id,
P.blog_id as post_blog_id,
P.author_id as post_author_id,
P.created_on as post_created_on,
P.section as post_section,
P.subject as post_subject,
P.draft as draft,
P.body as post_body,
C.id as comment_id,
C.post_id as comment_post_id,
C.name as comment_name,
C.comment as comment_text,
T.id as tag_id,
T.name as tag_name
from Blog B
left outer join Author A on B.author_id = A.id
left outer join Post P on B.id = P.blog_id
left outer join Comment C on P.id = C.post_id
left outer join Post_Tag PT on PT.post_id = P.id
left outer join Tag T on PT.tag_id = T.id
where B.id = #{id}
</select>
</code></pre>
<p>You'd probably want to map it to an intelligent object model consisting of a Blog that was written by an Author, and has many Posts, each of which may have zero or many Comments and Tags. The following is a complete example of a complex ResultMap (assume Author, Blog, Post, Comments and Tags are all type aliases). Have a look at it, but don't worry, we're going to go through each step. While it may look daunting at first, it's actually very simple.</p>
<pre class="prettyprint"><code class="language-xml"><!-- Very Complex Result Map -->
<resultMap id="detailedBlogResultMap" type="Blog">
<constructor>
<idArg column="blog_id" javaType="int"/>
</constructor>
<result property="title" column="blog_title"/>
<association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
<result property="favouriteSection" column="author_favourite_section"/>
</association>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<association property="author" javaType="Author"/>
<collection property="comments" ofType="Comment">
<id property="id" column="comment_id"/>
</collection>
<collection property="tags" ofType="Tag" >
<id property="id" column="tag_id"/>
</collection>
<discriminator javaType="int" column="draft">
<case value="1" resultType="DraftPost"/>
</discriminator>
</collection>
</resultMap>
</code></pre>
<p>The <code>resultMap</code> element has a number of sub-elements and a structure worthy of some discussion. The following is a conceptual view of the <code>resultMap</code> element.</p></section><section><a id="resultMap"></a>
<h4>resultMap</h4>
<ul>
<li><code>constructor</code> - used for injecting results into the constructor of a class upon instantiation
<ul>
<li><code>idArg</code> - ID argument; flagging results as ID will help improve overall performance</li>
<li><code>arg</code> - a normal result injected into the constructor</li>
</ul></li>
<li><code>id</code> – an ID result; flagging results as ID will help improve overall performance</li>
<li><code>result</code> – a normal result injected into a field or JavaBean property</li>
<li><code>association</code> – a complex type association; many results will roll up into this type
<ul>
<li>nested result mappings – associations are <code>resultMap</code>s themselves, or can refer to one</li>
</ul></li>
<li><code>collection</code> – a collection of complex types
<ul>
<li>nested result mappings – collections are <code>resultMap</code>s themselves, or can refer to one</li>
</ul></li>
<li><code>discriminator</code> – uses a result value to determine which <code>resultMap</code> to use
<ul>
<li><code>case</code> – a case is a result map based on some value
<ul>
<li>nested result mappings – a case is also a result map itself, and thus can contain many of these same elements, or it can refer to an external resultMap.</li>
</ul></li>
</ul></li>
</ul>
<table class="table table-striped"><caption>ResultMap Attributes</caption>
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>id</code></td>
<td>A unique identifier in this namespace that can be used to reference this result map.</td></tr>
<tr class="a">
<td><code>type</code></td>
<td>A fully qualified Java class name, or a type alias (see the table above for the list of built-in type aliases).</td></tr>
<tr class="b">
<td><code>autoMapping</code></td>
<td>If present, MyBatis will enable or disable the automapping for this ResultMap. This attribute overrides the global autoMappingBehavior. Default: unset.</td></tr></tbody>
</table>
<p><span class="label important">Best Practice</span> Always build ResultMaps incrementally. Unit tests really help out here. If you try to build a gigantic <code>resultMap</code> like the one above all at once, it's likely you'll get it wrong and it will be hard to work with. Start simple, and evolve it a step at a time. And unit test! The downside to using frameworks is that they are sometimes a bit of a black box (open source or not). Your best bet to ensure that you're achieving the behaviour that you intend, is to write unit tests. It also helps to have them when submitting bugs.</p>
<p>The next sections will walk through each of the elements in more detail.</p></section><section><a id="id_.26_result"></a>
<h4>id & result</h4>
<pre class="prettyprint"><code class="language-xml"><id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
</code></pre>
<p>These are the most basic of result mappings. Both <em>id</em> and <em>result</em> map a single column value to a single property or field of a simple data type (String, int, double, Date, etc.).</p>
<p>The only difference between the two is that <em>id</em> will flag the result as an identifier property to be used when comparing object instances. This helps to improve general performance, but especially performance of caching and nested result mapping (i.e. join mapping).</p>
<p>Each has a number of attributes:</p>
<table class="table table-striped"><caption>Id and Result Attributes</caption>
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>property</code></td>
<td>The field or property to map the column result to. If a matching JavaBeans property exists for the given name, then that will be used. Otherwise, MyBatis will look for a field of the given name. In both cases you can use complex property navigation using the usual dot notation. For example, you can map to something simple like: <code>username</code>, or to something more complicated like: <code>address.street.number</code>.</td></tr>
<tr class="a">
<td><code>column</code></td>
<td>The column name from the database, or the aliased column label. This is the same string that would normally be passed to <code>resultSet.getString(columnName)</code>.</td></tr>
<tr class="b">
<td><code>javaType</code></td>
<td>A fully qualified Java class name, or a type alias (see the table above for the list of built-in type aliases). MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behaviour.</td></tr>
<tr class="a">
<td><code>jdbcType</code></td>
<td>The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not a MyBatis one. So even if you were coding JDBC directly, you'd need to specify this type – but only for nullable values.</td></tr>
<tr class="b">
<td><code>typeHandler</code></td>
<td>We discussed default type handlers previously in this documentation. Using this property you can override the default type handler on a mapping-by-mapping basis. The value is either a fully qualified class name of a TypeHandler implementation, or a type alias.</td></tr></tbody>
</table>
</section><section><a id="Supported_JDBC_Types"></a>
<h4>Supported JDBC Types</h4>
<p>For future reference, MyBatis supports the following JDBC Types via the included JdbcType enumeration.</p>
<table class="table table-striped">
<thead></thead><tbody>
<tr class="a">
<td><code>BIT</code></td>
<td><code>FLOAT</code></td>
<td><code>CHAR</code></td>
<td><code>TIMESTAMP</code></td>
<td><code>OTHER</code></td>
<td><code>UNDEFINED</code></td></tr>
<tr class="b">
<td><code>TINYINT</code></td>
<td><code>REAL</code></td>
<td><code>VARCHAR</code></td>
<td><code>BINARY</code></td>
<td><code>BLOB</code></td>
<td><code>NVARCHAR</code></td></tr>
<tr class="a">
<td><code>SMALLINT</code></td>
<td><code>DOUBLE</code></td>
<td><code>LONGVARCHAR</code></td>
<td><code>VARBINARY</code></td>
<td><code>CLOB</code></td>
<td><code>NCHAR</code></td></tr>
<tr class="b">
<td><code>INTEGER</code></td>
<td><code>NUMERIC</code></td>
<td><code>DATE</code></td>
<td><code>LONGVARBINARY</code></td>
<td><code>BOOLEAN</code></td>
<td><code>NCLOB</code></td></tr>
<tr class="a">
<td><code>BIGINT</code></td>
<td><code>DECIMAL</code></td>
<td><code>TIME</code></td>
<td><code>NULL</code></td>
<td><code>CURSOR</code></td>
<td><code>ARRAY</code></td></tr></tbody>
</table>
</section><section><a id="constructor"></a>
<h4>constructor</h4>
<p>While properties will work for most Data Transfer Object (DTO) type classes, and likely most of your domain model, there may be some cases where you want to use immutable classes. Often tables that contain reference or lookup data that rarely or never changes is suited to immutable classes. Constructor injection allows you to set values on a class upon instantiation, without exposing public methods. MyBatis also supports private properties and private JavaBeans properties to achieve this, but some people prefer Constructor injection. The <em>constructor</em> element enables this.</p>
<p>Consider the following constructor:</p>
<pre class="prettyprint"><code class="language-java">public class User {
//...
public User(Integer id, String username, int age) {
//...
}
//...
}
</code></pre>
<p>In order to inject the results into the constructor, MyBatis needs to identify the constructor for somehow. In the following example, MyBatis searches a constructor declared with three parameters: <code>java.lang.Integer</code>, <code>java.lang.String</code> and <code>int</code> in this order.</p>
<pre class="prettyprint"><code class="language-xml"><constructor>
<idArg column="id" javaType="int"/>
<arg column="username" javaType="String"/>
<arg column="age" javaType="_int"/>
</constructor>
</code></pre>
<p>When you are dealing with a constructor with many parameters, maintaining the order of arg elements is error-prone.
Since 3.4.3, by specifying the name of each parameter, you can write arg elements in any order. To reference constructor parameters by their names, you can either add <code>@Param</code> annotation to them or compile the project with ‘-parameters’ compiler option and enable <code>useActualParamName</code> (this option is enabled by default). The following example is valid for the same constructor even though the order of the second and the third parameters does not match with the declared order.</p>
<pre class="prettyprint"><code class="language-xml"><constructor>
<idArg column="id" javaType="int" name="id" />
<arg column="age" javaType="_int" name="age" />
<arg column="username" javaType="String" name="username" />
</constructor>
</code></pre>
<p><code>javaType</code> can be omitted if there is a writable property with the same name and type.</p>
<p>The rest of the attributes and rules are the same as for the regular id and result elements.</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>column</code></td>
<td>The column name from the database, or the aliased column label. This is the same string that would normally be passed to <code>resultSet.getString(columnName)</code>.</td></tr>
<tr class="a">
<td><code>javaType</code></td>
<td>A fully qualified Java class name, or a type alias (see the table above for the list of built-in type aliases). MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behaviour.</td></tr>
<tr class="b">
<td><code>jdbcType</code></td>
<td>The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not an MyBatis one. So even if you were coding JDBC directly, you'd need to specify this type – but only for nullable values.</td></tr>
<tr class="a">
<td><code>typeHandler</code></td>
<td>We discussed default type handlers previously in this documentation. Using this property you can override the default type handler on a mapping-by-mapping basis. The value is either a fully qualified class name of a <code>TypeHandler</code> implementation, or a type alias.</td></tr>
<tr class="b">
<td><code>select</code></td>
<td>The ID of another mapped statement that will load the complex type required by this property mapping. The values retrieved from columns specified in the column attribute will be passed to the target select statement as parameters. See the Association element for more.</td></tr>
<tr class="a">
<td><code>resultMap</code></td>
<td>This is the ID of a ResultMap that can map the nested results of this argument into an appropriate object graph. This is an alternative to using a call to another select statement. It allows you to join multiple tables together into a single <code>ResultSet</code>. Such a <code>ResultSet</code> will contain duplicated, repeating groups of data that needs to be decomposed and mapped properly to a nested object graph. To facilitate this, MyBatis lets you “chain” result maps together, to deal with the nested results. See the Association element below for more.</td></tr>
<tr class="b">
<td><code>name</code></td>
<td>The name of the constructor parameter. Specifying name allows you to write arg elements in any order. See the above explanation. Since 3.4.3.</td></tr></tbody>
</table>
</section><section><a id="association"></a>
<h4>association</h4>
<pre class="prettyprint"><code class="language-xml"><association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
</association>
</code></pre>
<p>The association element deals with a “has-one” type relationship. For example, in our example, a Blog has one Author. An association mapping works mostly like any other result. You specify the target property, the <code>javaType</code> of the property (which MyBatis can figure out most of the time), the jdbcType if necessary and a typeHandler if you want to override the retrieval of the result values.</p>
<p>Where the association differs is that you need to tell MyBatis how to load the association. MyBatis can do so in two different ways:</p>
<ul>
<li>Nested Select: By executing another mapped SQL statement that returns the complex type desired.</li>
<li>Nested Results: By using nested result mappings to deal with repeating subsets of joined results.</li>
</ul>
<p>First, let's examine the properties of the element. As you'll see, it differs from a normal result mapping only by the select and resultMap attributes.</p>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>property</code></td>
<td>The field or property to map the column result to. If a matching JavaBeans property exists for the given name, then that will be used. Otherwise, MyBatis will look for a field of the given name. In both cases you can use complex property navigation using the usual dot notation. For example, you can map to something simple like: <code>username</code>, or to something more complicated like: <code>address.street.number</code>.</td></tr>
<tr class="a">
<td><code>javaType</code></td>
<td>A fully qualified Java class name, or a type alias (see the table above for the list of built- in type aliases). MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a <code>HashMap</code>, then you should specify the javaType explicitly to ensure the desired behaviour.</td></tr>
<tr class="b">
<td><code>jdbcType</code></td>
<td>The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not an MyBatis one. So even if you were coding JDBC directly, you'd need to specify this type – but only for nullable values.</td></tr>
<tr class="a">
<td><code>typeHandler</code></td>
<td>We discussed default type handlers previously in this documentation. Using this property you can override the default type handler on a mapping-by-mapping basis. The value is either a fully qualified class name of a TypeHandler implementation, or a type alias.</td></tr></tbody>
</table>
</section><section><a id="Nested_Select_for_Association"></a>
<h4>Nested Select for Association</h4>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>column</code></td>
<td>The column name from the database, or the aliased column label that holds the value that will be passed to the nested statement as an input parameter. This is the same string that would normally be passed to <code>resultSet.getString(columnName)</code>. Note: To deal with composite keys, you can specify multiple column names to pass to the nested select statement by using the syntax <code>column="{prop1=col1,prop2=col2}"</code>. This will cause <code>prop1</code> and <code>prop2</code> to be set against the parameter object for the target nested select statement.</td></tr>
<tr class="a">
<td><code>select</code></td>
<td>The ID of another mapped statement that will load the complex type required by this property mapping. The values retrieved from columns specified in the column attribute will be passed to the target select statement as parameters. A detailed example follows this table. Note: To deal with composite keys, you can specify multiple column names to pass to the nested select statement by using the syntax <code>column="{prop1=col1,prop2=col2}"</code>. This will cause <code>prop1</code> and <code>prop2</code> to be set against the parameter object for the target nested select statement.</td></tr>
<tr class="b">
<td><code>fetchType</code></td>
<td>Optional. Valid values are <code>lazy</code> and <code>eager</code>. If present, it supersedes the global configuration parameter <code>lazyLoadingEnabled</code> for this mapping.</td></tr></tbody>
</table>
<p>For example:</p>
<pre class="prettyprint"><code class="language-xml"><resultMap id="blogResult" type="Blog">
<association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>
<select id="selectBlog" resultMap="blogResult">
SELECT * FROM BLOG WHERE ID = #{id}
</select>
<select id="selectAuthor" resultType="Author">
SELECT * FROM AUTHOR WHERE ID = #{id}
</select>
</code></pre>
<p>That's it. We have two select statements: one to load the Blog, the other to load the Author, and the Blog's resultMap describes that the <code>selectAuthor</code> statement should be used to load its author property.</p>
<p>All other properties will be loaded automatically assuming their column and property names match.</p>
<p>While this approach is simple, it will not perform well for large data sets or lists. This problem is known as the “N+1 Selects Problem”. In a nutshell, the N+1 selects problem is caused like this:</p>
<ul>
<li>You execute a single SQL statement to retrieve a list of records (the “+1”).</li>
<li>For each record returned, you execute a select statement to load details for each (the “N”).</li>
</ul>
<p>This problem could result in hundreds or thousands of SQL statements to be executed. This is not always desirable.</p>
<p>The upside is that MyBatis can lazy load such queries, thus you might be spared the cost of these statements all at once. However, if you load such a list and then immediately iterate through it to access the nested data, you will invoke all of the lazy loads, and thus performance could be very bad.</p>
<p>And so, there is another way.</p></section><section><a id="Nested_Results_for_Association"></a>
<h4>Nested Results for Association</h4>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>resultMap</code></td>
<td>This is the ID of a ResultMap that can map the nested results of this association into an appropriate object graph. This is an alternative to using a call to another select statement. It allows you to join multiple tables together into a single ResultSet. Such a ResultSet will contain duplicated, repeating groups of data that needs to be decomposed and mapped properly to a nested object graph. To facilitate this, MyBatis lets you “chain” result maps together, to deal with the nested results. An example will be far easier to follow, and one follows this table.</td></tr>
<tr class="a">
<td><code>columnPrefix</code></td>
<td>When joining multiple tables, you would have to use column alias to avoid duplicated column names in the ResultSet. Specifying columnPrefix allows you to map such columns to an external resultMap. Please see the example explained later in this section.</td></tr>
<tr class="b">
<td><code>notNullColumn</code></td>
<td>By default a child object is created only if at least one of the columns mapped to the child's properties is non null. With this attribute you can change this behaviour by specifiying which columns must have a value so MyBatis will create a child object only if any of those columns is not null. Multiple column names can be specified using a comma as a separator. Default value: unset.</td></tr>
<tr class="a">
<td><code>autoMapping</code></td>
<td>If present, MyBatis will enable or disable automapping when mapping the result to this property. This attribute overrides the global autoMappingBehavior. Note that it has no effect on an external resultMap, so it is pointless to use it with <code>select</code> or <code>resultMap</code> attribute. Default value: unset.</td></tr></tbody>
</table>
<p>You've already seen a very complicated example of nested associations above. The following is a far simpler example to demonstrate how this works. Instead of executing a separate statement, we'll join the Blog and Author tables together, like so:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectBlog" resultMap="blogResult">
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio
from Blog B left outer join Author A on B.author_id = A.id
where B.id = #{id}
</select>
</code></pre>
<p>Notice the join, as well as the care taken to ensure that all results are aliased with a unique and clear name. This makes mapping far easier. Now we can map the results:</p>
<pre class="prettyprint"><code class="language-xml"><resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author" resultMap="authorResult" />
</resultMap>
<resultMap id="authorResult" type="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</resultMap>
</code></pre>
<p>In the example above you can see at the Blog's “author” association delegates to the “authorResult” resultMap to load the Author instance.</p>
<p><span class="label important">Very Important</span> id elements play a very important role in Nested Result mapping. You should always specify one or more properties that can be used to uniquely identify the results. The truth is that MyBatis will still work if you leave it out, but at a severe performance cost. Choose as few properties as possible that can uniquely identify the result. The primary key is an obvious choice (even if composite).</p>
<p>Now, the above example used an external resultMap element to map the association. This makes the Author resultMap reusable. However, if you have no need to reuse it, or if you simply prefer to co-locate your result mappings into a single descriptive resultMap, you can nest the association result mappings. Here's the same example using this approach:</p>
<pre class="prettyprint"><code class="language-xml"><resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</association>
</resultMap>
</code></pre>
<p>What if the blog has a co-author? The select statement would look like:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectBlog" resultMap="blogResult">
select
B.id as blog_id,
B.title as blog_title,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio,
CA.id as co_author_id,
CA.username as co_author_username,
CA.password as co_author_password,
CA.email as co_author_email,
CA.bio as co_author_bio
from Blog B
left outer join Author A on B.author_id = A.id
left outer join Author CA on B.co_author_id = CA.id
where B.id = #{id}
</select>
</code></pre>
<p>Recall that the resultMap for Author is defined as follows.</p>
<pre class="prettyprint"><code class="language-xml"><resultMap id="authorResult" type="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</resultMap>
</code></pre>
<p>Because the column names in the results differ from the columns defined in the resultMap, you need to specify <code>columnPrefix</code> to reuse the resultMap for mapping co-author results.</p>
<pre class="prettyprint"><code class="language-xml"><resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author"
resultMap="authorResult" />
<association property="coAuthor"
resultMap="authorResult"
columnPrefix="co_" />
</resultMap>
</code></pre></section><section><a id="Multiple_ResultSets_for_Association"></a>
<h4>Multiple ResultSets for Association</h4>
<table class="table table-striped">
<thead>
<tr class="a">
<th>Attribute</th>
<th>Description</th></tr></thead><tbody>
<tr class="b">
<td><code>column</code></td>
<td>When using multiple resultset this attribute specifies the columns (separated by commas) that will be correlated with the <code>foreignColumn</code> to identify the parent and the child of a relationship.</td></tr>
<tr class="a">
<td><code>foreignColumn</code></td>
<td>Identifies the name of the columns that contains the foreign keys which values will be matched against the values of the columns specified in the <code>column</code> attibute of the parent type.</td></tr>
<tr class="b">
<td><code>resultSet</code></td>
<td>Identifies the name of the result set where this complex type will be loaded from.</td></tr></tbody>
</table>
<p>Starting from version 3.2.3 MyBatis provides yet another way to solve the N+1 problem.</p>
<p>Some databases allow stored procedures to return more than one resultset or execute more than one statement at once and return a resultset per each one. This can be used to hit the database just once and return related data without using a join.</p>
<p>In the example, the stored procedure executes the following queries and returns two result sets. The first will contain Blogs and the second Authors.</p>
<pre class="prettyprint"><code class="language-sql">SELECT * FROM BLOG WHERE ID = #{id}
SELECT * FROM AUTHOR WHERE ID = #{id}
</code></pre>
<p>A name must be given to each result set by adding a <code>resultSets</code> attribute to the mapped statement with a list of names separated by commas.</p>