View Javadoc
1   /*
2    *    Copyright 2009-2021 the original author or authors.
3    *
4    *    Licensed under the Apache License, Version 2.0 (the "License");
5    *    you may not use this file except in compliance with the License.
6    *    You may obtain a copy of the License at
7    *
8    *       http://www.apache.org/licenses/LICENSE-2.0
9    *
10   *    Unless required by applicable law or agreed to in writing, software
11   *    distributed under the License is distributed on an "AS IS" BASIS,
12   *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   *    See the License for the specific language governing permissions and
14   *    limitations under the License.
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 }