-
Notifications
You must be signed in to change notification settings - Fork 12.9k
/
Copy pathdynamic-sql.html
305 lines (283 loc) · 17.1 KB
/
dynamic-sql.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
<!DOCTYPE html>
<!--
| Generated by Apache Maven Doxia Site Renderer 2.0.0 from src/site/markdown/dynamic-sql.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 | Dynamic SQL – 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><a href="sqlmap-xml.html"><span class="icon-chevron-right"></span>Mapper XML Files</a></li>
<li class="active"><a>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="Dynamic_SQL"></a>
<h2>Dynamic SQL</h2>
<p>One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.</p>
<p>While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.</p>
<p>The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:</p>
<ul>
<li>if</li>
<li>choose (when, otherwise)</li>
<li>trim (where, set)</li>
<li>foreach</li>
</ul><section><a id="if"></a>
<h3>if</h3>
<p>The most common thing to do in dynamic SQL is conditionally include a part of a where clause. For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
</code></pre>
<p>This statement would provide an optional text search type of functionality. If you passed in no title, then all active Blogs would be returned. But if you do pass in a title, it will look for a title like that (for the keen eyed, yes in this case your parameter value would need to include any masking or wildcard characters).</p>
<p>What if we wanted to optionally search by title and author? First, I’d change the name of the statement to make more sense. Then simply add another condition.</p>
<pre class="prettyprint"><code class="language-xml"><select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
</code></pre></section><section><a id="choose.2C_when.2C_otherwise"></a>
<h3>choose, when, otherwise</h3>
<p>Sometimes we don’t want all of the conditionals to apply, instead we want to choose only one case among many options. Similar to a switch statement in Java, MyBatis offers a choose element.</p>
<p>Let’s use the example above, but now let’s search only on title if one is provided, then only by author if one is provided. If neither is provided, let’s only return featured blogs (perhaps a strategically list selected by administrators, instead of returning a huge meaningless list of random blogs).</p>
<pre class="prettyprint"><code class="language-xml"><select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
</code></pre></section><section><a id="trim.2C_where.2C_set"></a>
<h3>trim, where, set</h3>
<p>The previous examples have been conveniently dancing around a notorious dynamic SQL challenge. Consider what would happen if we return to our “if” example, but this time we make “ACTIVE = 1” a dynamic condition as well.</p>
<pre class="prettyprint"><code class="language-xml"><select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
</code></pre>
<p>What happens if none of the conditions are met? You would end up with SQL that looked like this:</p>
<pre class="prettyprint"><code class="language-sql">SELECT * FROM BLOG
WHERE
</code></pre>
<p>This would fail. What if only the second condition was met? You would end up with SQL that looked like this:</p>
<pre class="prettyprint"><code class="language-sql">SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
</code></pre>
<p>This would also fail. This problem is not easily solved with conditionals, and if you’ve ever had to write it, then you likely never want to do so again.</p>
<p>MyBatis has a simple answer that will likely work in 90% of the cases. And in cases where it doesn’t, you can customize it so that it does. With one simple change, everything works fine:</p>
<pre class="prettyprint"><code class="language-xml"><select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
</code></pre>
<p>The <em>where</em> element knows to only insert “WHERE” if there is any content returned by the containing tags. Furthermore, if that content begins with “AND” or “OR”, it knows to strip it off.</p>
<p>If the <em>where</em> element does not behave exactly as you like, you can customize it by defining your own trim element. For example, the trim equivalent to the <em>where</em> element is:</p>
<pre class="prettyprint"><code class="language-xml"><trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
</code></pre>
<p>The <em>prefixOverrides</em> attribute takes a pipe delimited list of text to override, where whitespace <u>is</u> relevant. The result is the removal of anything specified in the <em>prefixOverrides</em> attribute, and the insertion of anything in the <em>prefix</em> attribute.</p>
<p>There is a similar solution for dynamic update statements called <em>set</em>. The <em>set</em> element can be used to dynamically include columns to update, and leave out others. For example:</p>
<pre class="prettyprint"><code class="language-xml"><update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
</code></pre>
<p>Here, the <em>set</em> element will dynamically prepend the SET keyword, and also eliminate any extraneous commas that might trail the value assignments after the conditions are applied.</p>
<p>Alternatively, you can achieve the same effect by using <em>trim</em> element:</p>
<pre class="prettyprint"><code class="language-xml"><trim prefix="SET" suffixOverrides=",">
...
</trim>
</code></pre>
<p>Notice that in this case we’re overriding a suffix, while we’re still appending a prefix.</p></section><section><a id="foreach"></a>
<h3>foreach</h3>
<p>Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition. For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
<foreach item="item" index="index" collection="list"
open="ID in (" separator="," close=")" nullable="true">
#{item}
</foreach>
</where>
</select>
</code></pre>
<p>The <em>foreach</em> element is very powerful, and allows you to specify a collection, declare item and index variables that can be used inside the body of the element. It also allows you to specify opening and closing strings, and add a separator to place in between iterations. The element is smart in that it won’t accidentally append extra separators.</p>
<p><span class="label important">NOTE</span> You can pass any Iterable object (for example List, Set, etc.), as well as any Map or Array object to foreach as collection parameter. When using an Iterable or Array, index will be the number of current iteration and value item will be the element retrieved in this iteration. When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object.</p>
<p>This wraps up the discussion regarding the XML configuration file and XML mapping files. The next section will discuss the Java API in detail, so that you can get the most out of the mappings that you’ve created.</p></section><section><a id="script"></a>
<h3>script</h3>
<p>For using dynamic SQL in annotated mapper class, <em>script</em> element can be used. For example:</p>
<pre class="prettyprint"><code class="language-java"> @Update({"<script>",
"update Author",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" <if test='email != null'>email=#{email},</if>",
" <if test='bio != null'>bio=#{bio}</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateAuthorValues(Author author);
</code></pre></section><section><a id="bind"></a>
<h3>bind</h3>
<p>The <code>bind</code> element lets you create a variable out of an OGNL expression and bind it to the context. For example:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
</code></pre></section><section><a id="Multi-db_vendor_support"></a>
<h3>Multi-db vendor support</h3>
<p>If a databaseIdProvider was configured a “_databaseId” variable is available for dynamic code, so you can build different statements depending on database vendor. Have a look at the following example:</p>
<pre class="prettyprint"><code class="language-xml"><insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
</code></pre></section><section><a id="Pluggable_Scripting_Languages_For_Dynamic_SQL"></a>
<h3>Pluggable Scripting Languages For Dynamic SQL</h3>
<p>Starting from version 3.2 MyBatis supports pluggable scripting languages, so you can plug a language driver and use that language to write your dynamic SQL queries.</p>
<p>You can plug a language by implementing the following interface:</p>
<pre class="prettyprint"><code class="language-java">public interface LanguageDriver {
ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
}
</code></pre>
<p>Once you have your custom language driver you can set it to be the default by configuring it in the mybatis-config.xml file:</p>
<pre class="prettyprint"><code class="language-xml"><typeAliases>
<typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/>
</typeAliases>
<settings>
<setting name="defaultScriptingLanguage" value="myLanguage"/>
</settings>
</code></pre>
<p>Instead of changing the default, you can specify the language for an specific statement by adding the <code>lang</code> attribute as follows:</p>
<pre class="prettyprint"><code class="language-xml"><select id="selectBlog" lang="myLanguage">
SELECT * FROM BLOG
</select>
</code></pre>
<p>Or, in the case you are using mappers, using the <code>@Lang</code> annotation:</p>
<pre class="prettyprint"><code class="language-java">public interface Mapper {
@Lang(MyLanguageDriver.class)
@Select("SELECT * FROM BLOG")
List<Blog> selectBlog();
}
</code></pre>
<p><span class="label important">NOTE</span> You can use Apache Velocity as your dynamic language. Have a look at the MyBatis-Velocity project for the details.</p>
<p>All the xml tags you have seen in the previous sections are provided by the default MyBatis language that is provided by the driver <code>org.apache.ibatis.scripting.xmltags.XmlLanguageDriver</code> which is aliased as <code>xml</code>.</p></section></section></section> </main>
</div>
</div>
<hr/>
<footer>
<div class="container-fluid">
<div class="row-fluid">
<p>© 2009–2025
<a href="https://www.mybatis.org/">MyBatis.org</a>
</p>
</div>
</div>
</footer>
</body>
</html>