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