1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.ibatis.builder.xml.dynamic;
17
18 import static org.junit.jupiter.api.Assertions.assertEquals;
19
20 import java.io.IOException;
21 import java.io.Reader;
22 import java.sql.SQLException;
23 import java.util.ArrayList;
24 import java.util.Arrays;
25 import java.util.HashMap;
26 import java.util.List;
27 import java.util.Map;
28
29 import org.apache.ibatis.BaseDataTest;
30 import org.apache.ibatis.io.Resources;
31 import org.apache.ibatis.mapping.BoundSql;
32 import org.apache.ibatis.scripting.xmltags.ChooseSqlNode;
33 import org.apache.ibatis.scripting.xmltags.DynamicSqlSource;
34 import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
35 import org.apache.ibatis.scripting.xmltags.IfSqlNode;
36 import org.apache.ibatis.scripting.xmltags.MixedSqlNode;
37 import org.apache.ibatis.scripting.xmltags.SetSqlNode;
38 import org.apache.ibatis.scripting.xmltags.SqlNode;
39 import org.apache.ibatis.scripting.xmltags.TextSqlNode;
40 import org.apache.ibatis.scripting.xmltags.WhereSqlNode;
41 import org.apache.ibatis.session.Configuration;
42 import org.apache.ibatis.session.SqlSessionFactory;
43 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
44 import org.junit.jupiter.api.Assertions;
45 import org.junit.jupiter.api.Test;
46
47 class DynamicSqlSourceTest extends BaseDataTest {
48
49 @Test
50 void shouldDemonstrateSimpleExpectedTextWithNoLoopsOrConditionals() throws Exception {
51 final String expected = "SELECT * FROM BLOG";
52 final MixedSqlNode sqlNode = mixedContents(new TextSqlNode(expected));
53 DynamicSqlSource source = createDynamicSqlSource(sqlNode);
54 BoundSql boundSql = source.getBoundSql(null);
55 assertEquals(expected, boundSql.getSql());
56 }
57
58 @Test
59 void shouldDemonstrateMultipartExpectedTextWithNoLoopsOrConditionals() throws Exception {
60 final String expected = "SELECT * FROM BLOG WHERE ID = ?";
61 DynamicSqlSource source = createDynamicSqlSource(
62 new TextSqlNode("SELECT * FROM BLOG"),
63 new TextSqlNode("WHERE ID = ?"));
64 BoundSql boundSql = source.getBoundSql(null);
65 assertEquals(expected, boundSql.getSql());
66 }
67
68 @Test
69 void shouldConditionallyIncludeWhere() throws Exception {
70 final String expected = "SELECT * FROM BLOG WHERE ID = ?";
71 DynamicSqlSource source = createDynamicSqlSource(
72 new TextSqlNode("SELECT * FROM BLOG"),
73 new IfSqlNode(mixedContents(new TextSqlNode("WHERE ID = ?")), "true"
74 ));
75 BoundSql boundSql = source.getBoundSql(null);
76 assertEquals(expected, boundSql.getSql());
77 }
78
79 @Test
80 void shouldConditionallyExcludeWhere() throws Exception {
81 final String expected = "SELECT * FROM BLOG";
82 DynamicSqlSource source = createDynamicSqlSource(
83 new TextSqlNode("SELECT * FROM BLOG"),
84 new IfSqlNode(mixedContents(new TextSqlNode("WHERE ID = ?")), "false"
85 ));
86 BoundSql boundSql = source.getBoundSql(null);
87 assertEquals(expected, boundSql.getSql());
88 }
89
90 @Test
91 void shouldConditionallyDefault() throws Exception {
92 final String expected = "SELECT * FROM BLOG WHERE CATEGORY = 'DEFAULT'";
93 DynamicSqlSource source = createDynamicSqlSource(
94 new TextSqlNode("SELECT * FROM BLOG"),
95 new ChooseSqlNode(new ArrayList<SqlNode>() {{
96 add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = ?")), "false"
97 ));
98 add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = 'NONE'")), "false"
99 ));
100 }}, mixedContents(new TextSqlNode("WHERE CATEGORY = 'DEFAULT'"))));
101 BoundSql boundSql = source.getBoundSql(null);
102 assertEquals(expected, boundSql.getSql());
103 }
104
105 @Test
106 void shouldConditionallyChooseFirst() throws Exception {
107 final String expected = "SELECT * FROM BLOG WHERE CATEGORY = ?";
108 DynamicSqlSource source = createDynamicSqlSource(
109 new TextSqlNode("SELECT * FROM BLOG"),
110 new ChooseSqlNode(new ArrayList<SqlNode>() {{
111 add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = ?")), "true"
112 ));
113 add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = 'NONE'")), "false"
114 ));
115 }}, mixedContents(new TextSqlNode("WHERE CATEGORY = 'DEFAULT'"))));
116 BoundSql boundSql = source.getBoundSql(null);
117 assertEquals(expected, boundSql.getSql());
118 }
119
120 @Test
121 void shouldConditionallyChooseSecond() throws Exception {
122 final String expected = "SELECT * FROM BLOG WHERE CATEGORY = 'NONE'";
123 DynamicSqlSource source = createDynamicSqlSource(
124 new TextSqlNode("SELECT * FROM BLOG"),
125 new ChooseSqlNode(new ArrayList<SqlNode>() {{
126 add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = ?")), "false"
127 ));
128 add(new IfSqlNode(mixedContents(new TextSqlNode("WHERE CATEGORY = 'NONE'")), "true"
129 ));
130 }}, mixedContents(new TextSqlNode("WHERE CATEGORY = 'DEFAULT'"))));
131 BoundSql boundSql = source.getBoundSql(null);
132 assertEquals(expected, boundSql.getSql());
133 }
134
135 @Test
136 void shouldTrimWHEREInsteadOfANDForFirstCondition() throws Exception {
137 final String expected = "SELECT * FROM BLOG WHERE ID = ?";
138 DynamicSqlSource source = createDynamicSqlSource(
139 new TextSqlNode("SELECT * FROM BLOG"),
140 new WhereSqlNode(new Configuration(),mixedContents(
141 new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "true"
142 ),
143 new IfSqlNode(mixedContents(new TextSqlNode(" or NAME = ? ")), "false"
144 )
145 )));
146 BoundSql boundSql = source.getBoundSql(null);
147 assertEquals(expected, boundSql.getSql());
148 }
149
150 @Test
151 void shouldTrimWHEREANDWithLFForFirstCondition() throws Exception {
152 final String expected = "SELECT * FROM BLOG WHERE \n ID = ?";
153 DynamicSqlSource source = createDynamicSqlSource(
154 new TextSqlNode("SELECT * FROM BLOG"),
155 new WhereSqlNode(new Configuration(),mixedContents(
156 new IfSqlNode(mixedContents(new TextSqlNode(" and\n ID = ? ")), "true"
157 )
158 )));
159 BoundSql boundSql = source.getBoundSql(null);
160 assertEquals(expected, boundSql.getSql());
161 }
162
163 @Test
164 void shouldTrimWHEREANDWithCRLFForFirstCondition() throws Exception {
165 final String expected = "SELECT * FROM BLOG WHERE \r\n ID = ?";
166 DynamicSqlSource source = createDynamicSqlSource(
167 new TextSqlNode("SELECT * FROM BLOG"),
168 new WhereSqlNode(new Configuration(),mixedContents(
169 new IfSqlNode(mixedContents(new TextSqlNode(" and\r\n ID = ? ")), "true"
170 )
171 )));
172 BoundSql boundSql = source.getBoundSql(null);
173 assertEquals(expected, boundSql.getSql());
174 }
175
176 @Test
177 void shouldTrimWHEREANDWithTABForFirstCondition() throws Exception {
178 final String expected = "SELECT * FROM BLOG WHERE \t ID = ?";
179 DynamicSqlSource source = createDynamicSqlSource(
180 new TextSqlNode("SELECT * FROM BLOG"),
181 new WhereSqlNode(new Configuration(),mixedContents(
182 new IfSqlNode(mixedContents(new TextSqlNode(" and\t ID = ? ")), "true"
183 )
184 )));
185 BoundSql boundSql = source.getBoundSql(null);
186 assertEquals(expected, boundSql.getSql());
187 }
188
189 @Test
190 void shouldTrimWHEREORWithLFForFirstCondition() throws Exception {
191 final String expected = "SELECT * FROM BLOG WHERE \n ID = ?";
192 DynamicSqlSource source = createDynamicSqlSource(
193 new TextSqlNode("SELECT * FROM BLOG"),
194 new WhereSqlNode(new Configuration(),mixedContents(
195 new IfSqlNode(mixedContents(new TextSqlNode(" or\n ID = ? ")), "true"
196 )
197 )));
198 BoundSql boundSql = source.getBoundSql(null);
199 assertEquals(expected, boundSql.getSql());
200 }
201
202 @Test
203 void shouldTrimWHEREORWithCRLFForFirstCondition() throws Exception {
204 final String expected = "SELECT * FROM BLOG WHERE \r\n ID = ?";
205 DynamicSqlSource source = createDynamicSqlSource(
206 new TextSqlNode("SELECT * FROM BLOG"),
207 new WhereSqlNode(new Configuration(),mixedContents(
208 new IfSqlNode(mixedContents(new TextSqlNode(" or\r\n ID = ? ")), "true"
209 )
210 )));
211 BoundSql boundSql = source.getBoundSql(null);
212 assertEquals(expected, boundSql.getSql());
213 }
214
215 @Test
216 void shouldTrimWHEREORWithTABForFirstCondition() throws Exception {
217 final String expected = "SELECT * FROM BLOG WHERE \t ID = ?";
218 DynamicSqlSource source = createDynamicSqlSource(
219 new TextSqlNode("SELECT * FROM BLOG"),
220 new WhereSqlNode(new Configuration(),mixedContents(
221 new IfSqlNode(mixedContents(new TextSqlNode(" or\t ID = ? ")), "true"
222 )
223 )));
224 BoundSql boundSql = source.getBoundSql(null);
225 assertEquals(expected, boundSql.getSql());
226 }
227
228 @Test
229 void shouldTrimWHEREInsteadOfORForSecondCondition() throws Exception {
230 final String expected = "SELECT * FROM BLOG WHERE NAME = ?";
231 DynamicSqlSource source = createDynamicSqlSource(
232 new TextSqlNode("SELECT * FROM BLOG"),
233 new WhereSqlNode(new Configuration(),mixedContents(
234 new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "false"
235 ),
236 new IfSqlNode(mixedContents(new TextSqlNode(" or NAME = ? ")), "true"
237 )
238 )));
239 BoundSql boundSql = source.getBoundSql(null);
240 assertEquals(expected, boundSql.getSql());
241 }
242
243 @Test
244 void shouldTrimWHEREInsteadOfANDForBothConditions() throws Exception {
245 final String expected = "SELECT * FROM BLOG WHERE ID = ? OR NAME = ?";
246 DynamicSqlSource source = createDynamicSqlSource(
247 new TextSqlNode("SELECT * FROM BLOG"),
248 new WhereSqlNode(new Configuration(),mixedContents(
249 new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "true"
250 ),
251 new IfSqlNode(mixedContents(new TextSqlNode("OR NAME = ? ")), "true"
252 )
253 )));
254 BoundSql boundSql = source.getBoundSql(null);
255 assertEquals(expected, boundSql.getSql());
256 }
257
258 @Test
259 void shouldTrimNoWhereClause() throws Exception {
260 final String expected = "SELECT * FROM BLOG";
261 DynamicSqlSource source = createDynamicSqlSource(
262 new TextSqlNode("SELECT * FROM BLOG"),
263 new WhereSqlNode(new Configuration(),mixedContents(
264 new IfSqlNode(mixedContents(new TextSqlNode(" and ID = ? ")), "false"
265 ),
266 new IfSqlNode(mixedContents(new TextSqlNode("OR NAME = ? ")), "false"
267 )
268 )));
269 BoundSql boundSql = source.getBoundSql(null);
270 assertEquals(expected, boundSql.getSql());
271 }
272
273 @Test
274 void shouldTrimSETInsteadOfCOMMAForBothConditions() throws Exception {
275 final String expected = "UPDATE BLOG SET ID = ?, NAME = ?";
276 DynamicSqlSource source = createDynamicSqlSource(
277 new TextSqlNode("UPDATE BLOG"),
278 new SetSqlNode(new Configuration(),mixedContents(
279 new IfSqlNode(mixedContents(new TextSqlNode(" ID = ?, ")), "true"
280 ),
281 new IfSqlNode(mixedContents(new TextSqlNode(" NAME = ?, ")), "true"
282 )
283 )));
284 BoundSql boundSql = source.getBoundSql(null);
285 assertEquals(expected, boundSql.getSql());
286 }
287
288 @Test
289 void shouldTrimCommaAfterSET() throws Exception {
290 final String expected = "UPDATE BLOG SET NAME = ?";
291 DynamicSqlSource source = createDynamicSqlSource(
292 new TextSqlNode("UPDATE BLOG"),
293 new SetSqlNode(new Configuration(), mixedContents(
294 new IfSqlNode(mixedContents(new TextSqlNode("ID = ?")), "false"),
295 new IfSqlNode(mixedContents(new TextSqlNode(", NAME = ?")), "true"))));
296 BoundSql boundSql = source.getBoundSql(null);
297 assertEquals(expected, boundSql.getSql());
298 }
299
300 @Test
301 void shouldTrimNoSetClause() throws Exception {
302 final String expected = "UPDATE BLOG";
303 DynamicSqlSource source = createDynamicSqlSource(
304 new TextSqlNode("UPDATE BLOG"),
305 new SetSqlNode(new Configuration(),mixedContents(
306 new IfSqlNode(mixedContents(new TextSqlNode(" , ID = ? ")), "false"
307 ),
308 new IfSqlNode(mixedContents(new TextSqlNode(", NAME = ? ")), "false"
309 )
310 )));
311 BoundSql boundSql = source.getBoundSql(null);
312 assertEquals(expected, boundSql.getSql());
313 }
314
315 @Test
316 void shouldIterateOnceForEachItemInCollection() throws Exception {
317 final HashMap<String, String[]> parameterObject = new HashMap<String, String[]>() {{
318 put("array", new String[]{"one", "two", "three"});
319 }};
320 final String expected = "SELECT * FROM BLOG WHERE ID in ( one = ? AND two = ? AND three = ? )";
321 DynamicSqlSource source = createDynamicSqlSource(
322 new TextSqlNode("SELECT * FROM BLOG WHERE ID in"),
323 new ForEachSqlNode(new Configuration(),mixedContents(new TextSqlNode("${item} = #{item}")), "array", "index", "item", "(", ")", "AND"));
324 BoundSql boundSql = source.getBoundSql(parameterObject);
325 assertEquals(expected, boundSql.getSql());
326 assertEquals(3, boundSql.getParameterMappings().size());
327 assertEquals("__frch_item_0", boundSql.getParameterMappings().get(0).getProperty());
328 assertEquals("__frch_item_1", boundSql.getParameterMappings().get(1).getProperty());
329 assertEquals("__frch_item_2", boundSql.getParameterMappings().get(2).getProperty());
330 }
331
332 @Test
333 void shouldHandleOgnlExpression() throws Exception {
334 final HashMap<String, String> parameterObject = new HashMap<String, String>() {{
335 put("name", "Steve");
336 }};
337 final String expected = "Expression test: 3 / yes.";
338 DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("Expression test: ${name.indexOf('v')} / ${name in {'Bob', 'Steve'\\} ? 'yes' : 'no'}."));
339 BoundSql boundSql = source.getBoundSql(parameterObject);
340 assertEquals(expected, boundSql.getSql());
341 }
342
343 @Test
344 void shouldSkipForEachWhenCollectionIsEmpty() throws Exception {
345 final HashMap<String, Integer[]> parameterObject = new HashMap<String, Integer[]>() {{
346 put("array", new Integer[] {});
347 }};
348 final String expected = "SELECT * FROM BLOG";
349 DynamicSqlSource source = createDynamicSqlSource(new TextSqlNode("SELECT * FROM BLOG"),
350 new ForEachSqlNode(new Configuration(), mixedContents(
351 new TextSqlNode("#{item}")), "array", null, "item", "WHERE id in (", ")", ","));
352 BoundSql boundSql = source.getBoundSql(parameterObject);
353 assertEquals(expected, boundSql.getSql());
354 assertEquals(0, boundSql.getParameterMappings().size());
355 }
356
357 @Test
358 void shouldPerformStrictMatchOnForEachVariableSubstitution() throws Exception {
359 final Map<String, Object> param = new HashMap<>();
360 final Map<String, String> uuu = new HashMap<>();
361 uuu.put("u", "xyz");
362 List<Bean> uuuu = new ArrayList<>();
363 uuuu.add(new Bean("bean id"));
364 param.put("uuu", uuu);
365 param.put("uuuu", uuuu);
366 DynamicSqlSource source = createDynamicSqlSource(
367 new TextSqlNode("INSERT INTO BLOG (ID, NAME, NOTE, COMMENT) VALUES"),
368 new ForEachSqlNode(new Configuration(),mixedContents(
369 new TextSqlNode("#{uuu.u}, #{u.id}, #{ u,typeHandler=org.apache.ibatis.type.StringTypeHandler},"
370 + " #{u:VARCHAR,typeHandler=org.apache.ibatis.type.StringTypeHandler}")), "uuuu", "uu", "u", "(", ")", ","));
371 BoundSql boundSql = source.getBoundSql(param);
372 assertEquals(4, boundSql.getParameterMappings().size());
373 assertEquals("uuu.u", boundSql.getParameterMappings().get(0).getProperty());
374 assertEquals("__frch_u_0.id", boundSql.getParameterMappings().get(1).getProperty());
375 assertEquals("__frch_u_0", boundSql.getParameterMappings().get(2).getProperty());
376 assertEquals("__frch_u_0", boundSql.getParameterMappings().get(3).getProperty());
377 }
378
379 private DynamicSqlSource createDynamicSqlSource(SqlNode... contents) throws IOException, SQLException {
380 createBlogDataSource();
381 final String resource = "org/apache/ibatis/builder/MapperConfig.xml";
382 final Reader reader = Resources.getResourceAsReader(resource);
383 SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder().build(reader);
384 Configuration configuration = sqlMapper.getConfiguration();
385 MixedSqlNode sqlNode = mixedContents(contents);
386 return new DynamicSqlSource(configuration, sqlNode);
387 }
388
389 private MixedSqlNode mixedContents(SqlNode... contents) {
390 return new MixedSqlNode(Arrays.asList(contents));
391 }
392
393 @Test
394 void shouldMapNullStringsToEmptyStrings() {
395 final String expected = "id=${id}";
396 final MixedSqlNode sqlNode = mixedContents(new TextSqlNode(expected));
397 final DynamicSqlSource source = new DynamicSqlSource(new Configuration(), sqlNode);
398 String sql = source.getBoundSql(new Bean(null)).getSql();
399 Assertions.assertEquals("id=", sql);
400 }
401
402 public static class Bean {
403 public String id;
404 Bean(String property) {
405 this.id = property;
406 }
407 public String getId() {
408 return id;
409 }
410 public void setId(String property) {
411 this.id = property;
412 }
413 }
414
415 }