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.jdbc;
17  
18  import static org.assertj.core.api.Assertions.assertThat;
19  import static org.junit.jupiter.api.Assertions.assertEquals;
20  
21  import org.junit.jupiter.api.Test;
22  
23  class SQLTest {
24  
25    @Test
26    void shouldDemonstrateProvidedStringBuilder() {
27      //You can pass in your own StringBuilder
28      final StringBuilder sb = new StringBuilder();
29      //From the tutorial
30      final String sql = example1().usingAppender(sb).toString();
31  
32      assertEquals("SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
33          "FROM PERSON P, ACCOUNT A\n" +
34          "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
35          "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
36          "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
37          "OR (P.LAST_NAME like ?)\n" +
38          "GROUP BY P.ID\n" +
39          "HAVING (P.LAST_NAME like ?) \n" +
40          "OR (P.FIRST_NAME like ?)\n" +
41          "ORDER BY P.ID, P.FULL_NAME", sql);
42    }
43  
44    @Test
45    void shouldDemonstrateMixedStyle() {
46      //Mixed
47      final String sql = new SQL() {{
48        SELECT("id, name");
49        FROM("PERSON A");
50        WHERE("name like ?").WHERE("id = ?");
51      }}.toString();
52  
53      assertEquals("" +
54          "SELECT id, name\n" +
55          "FROM PERSON A\n" +
56          "WHERE (name like ? AND id = ?)", sql);
57    }
58  
59    @Test
60    void shouldDemonstrateFluentStyle() {
61      //Fluent Style
62      final String sql = new SQL()
63          .SELECT("id, name").FROM("PERSON A")
64          .WHERE("name like ?")
65          .WHERE("id = ?").toString();
66  
67      assertEquals("" +
68          "SELECT id, name\n" +
69          "FROM PERSON A\n" +
70          "WHERE (name like ? AND id = ?)", sql);
71    }
72  
73    @Test
74    void shouldProduceExpectedSimpleSelectStatement() {
75      final String expected =
76          "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
77              "FROM PERSON P\n" +
78              "WHERE (P.ID like #id# AND P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
79              "ORDER BY P.LAST_NAME";
80      assertEquals(expected, example2("a", "b", "c"));
81    }
82  
83    @Test
84    void shouldProduceExpectedSimpleSelectStatementMissingFirstParam() {
85      final String expected =
86          "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
87              "FROM PERSON P\n" +
88              "WHERE (P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
89              "ORDER BY P.LAST_NAME";
90      assertEquals(expected, example2(null, "b", "c"));
91    }
92  
93    @Test
94    void shouldProduceExpectedSimpleSelectStatementMissingFirstTwoParams() {
95      final String expected =
96          "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
97              "FROM PERSON P\n" +
98              "WHERE (P.LAST_NAME like #lastName#)\n" +
99              "ORDER BY P.LAST_NAME";
100     assertEquals(expected, example2(null, null, "c"));
101   }
102 
103   @Test
104   void shouldProduceExpectedSimpleSelectStatementMissingAllParams() {
105     final String expected =
106         "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
107             "FROM PERSON P\n" +
108             "ORDER BY P.LAST_NAME";
109     assertEquals(expected, example2(null, null, null));
110   }
111 
112   @Test
113   void shouldProduceExpectedComplexSelectStatement() {
114     final String expected =
115         "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
116             "FROM PERSON P, ACCOUNT A\n" +
117             "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
118             "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
119             "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
120             "OR (P.LAST_NAME like ?)\n" +
121             "GROUP BY P.ID\n" +
122             "HAVING (P.LAST_NAME like ?) \n" +
123             "OR (P.FIRST_NAME like ?)\n" +
124             "ORDER BY P.ID, P.FULL_NAME";
125     assertEquals(expected, example1().toString());
126   }
127 
128   private static SQL example1() {
129     return new SQL() {{
130       SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
131       SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
132       FROM("PERSON P");
133       FROM("ACCOUNT A");
134       INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
135       INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
136       WHERE("P.ID = A.ID");
137       WHERE("P.FIRST_NAME like ?");
138       OR();
139       WHERE("P.LAST_NAME like ?");
140       GROUP_BY("P.ID");
141       HAVING("P.LAST_NAME like ?");
142       OR();
143       HAVING("P.FIRST_NAME like ?");
144       ORDER_BY("P.ID");
145       ORDER_BY("P.FULL_NAME");
146     }};
147   }
148 
149   private static String example2(final String id, final String firstName, final String lastName) {
150     return new SQL() {{
151       SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
152       FROM("PERSON P");
153       if (id != null) {
154         WHERE("P.ID like #id#");
155       }
156       if (firstName != null) {
157         WHERE("P.FIRST_NAME like #firstName#");
158       }
159       if (lastName != null) {
160         WHERE("P.LAST_NAME like #lastName#");
161       }
162       ORDER_BY("P.LAST_NAME");
163     }}.toString();
164   }
165 
166 
167   @Test
168   void variableLengthArgumentOnSelect() {
169     final String sql = new SQL() {{
170       SELECT("P.ID", "P.USERNAME");
171     }}.toString();
172 
173     assertEquals("SELECT P.ID, P.USERNAME", sql);
174   }
175 
176   @Test
177   void variableLengthArgumentOnSelectDistinct() {
178     final String sql = new SQL() {{
179       SELECT_DISTINCT("P.ID", "P.USERNAME");
180     }}.toString();
181 
182     assertEquals("SELECT DISTINCT P.ID, P.USERNAME", sql);
183   }
184 
185   @Test
186   void variableLengthArgumentOnFrom() {
187     final String sql = new SQL() {{
188       SELECT().FROM("TABLE_A a", "TABLE_B b");
189     }}.toString();
190 
191     assertEquals("FROM TABLE_A a, TABLE_B b", sql);
192   }
193 
194   @Test
195   void variableLengthArgumentOnJoin() {
196     final String sql = new SQL() {{
197       SELECT().JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
198     }}.toString();
199 
200     assertEquals("JOIN TABLE_A b ON b.id = a.id\n" +
201         "JOIN TABLE_C c ON c.id = a.id", sql);
202   }
203 
204   @Test
205   void variableLengthArgumentOnInnerJoin() {
206     final String sql = new SQL() {{
207       SELECT().INNER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
208     }}.toString();
209 
210     assertEquals("INNER JOIN TABLE_A b ON b.id = a.id\n" +
211         "INNER JOIN TABLE_C c ON c.id = a.id", sql);
212   }
213 
214   @Test
215   void variableLengthArgumentOnOuterJoin() {
216     final String sql = new SQL() {{
217       SELECT().OUTER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
218     }}.toString();
219 
220     assertEquals("OUTER JOIN TABLE_A b ON b.id = a.id\n" +
221         "OUTER JOIN TABLE_C c ON c.id = a.id", sql);
222   }
223 
224   @Test
225   void variableLengthArgumentOnLeftOuterJoin() {
226     final String sql = new SQL() {{
227       SELECT().LEFT_OUTER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
228     }}.toString();
229 
230     assertEquals("LEFT OUTER JOIN TABLE_A b ON b.id = a.id\n" +
231         "LEFT OUTER JOIN TABLE_C c ON c.id = a.id", sql);
232   }
233 
234   @Test
235   void variableLengthArgumentOnRightOuterJoin() {
236     final String sql = new SQL() {{
237       SELECT().RIGHT_OUTER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
238     }}.toString();
239 
240     assertEquals("RIGHT OUTER JOIN TABLE_A b ON b.id = a.id\n" +
241         "RIGHT OUTER JOIN TABLE_C c ON c.id = a.id", sql);
242   }
243 
244   @Test
245   void variableLengthArgumentOnWhere() {
246     final String sql = new SQL() {{
247       SELECT().WHERE("a = #{a}", "b = #{b}");
248     }}.toString();
249 
250     assertEquals("WHERE (a = #{a} AND b = #{b})", sql);
251   }
252 
253   @Test
254   void variableLengthArgumentOnGroupBy() {
255     final String sql = new SQL() {{
256       SELECT().GROUP_BY("a", "b");
257     }}.toString();
258 
259     assertEquals("GROUP BY a, b", sql);
260   }
261 
262   @Test
263   void variableLengthArgumentOnHaving() {
264     final String sql = new SQL() {{
265       SELECT().HAVING("a = #{a}", "b = #{b}");
266     }}.toString();
267 
268     assertEquals("HAVING (a = #{a} AND b = #{b})", sql);
269   }
270 
271   @Test
272   void variableLengthArgumentOnOrderBy() {
273     final String sql = new SQL() {{
274       SELECT().ORDER_BY("a", "b");
275     }}.toString();
276 
277     assertEquals("ORDER BY a, b", sql);
278   }
279 
280   @Test
281   void variableLengthArgumentOnSet() {
282     final String sql = new SQL() {{
283       UPDATE("TABLE_A").SET("a = #{a}", "b = #{b}");
284     }}.toString();
285 
286     assertEquals("UPDATE TABLE_A\n" +
287         "SET a = #{a}, b = #{b}", sql);
288   }
289 
290   @Test
291   void variableLengthArgumentOnIntoColumnsAndValues() {
292     final String sql = new SQL() {{
293       INSERT_INTO("TABLE_A").INTO_COLUMNS("a", "b").INTO_VALUES("#{a}", "#{b}");
294     }}.toString();
295 
296     assertEquals("INSERT INTO TABLE_A\n (a, b)\nVALUES (#{a}, #{b})", sql);
297   }
298 
299   @Test
300   void fixFor903UpdateJoins() {
301     final SQL sql = new SQL().UPDATE("table1 a").INNER_JOIN("table2 b USING (ID)").SET("a.value = b.value");
302     assertThat(sql.toString()).isEqualTo("UPDATE table1 a\nINNER JOIN table2 b USING (ID)\nSET a.value = b.value");
303   }
304 
305   @Test
306   void selectUsingLimitVariableName() {
307     final String sql = new SQL() {{
308       SELECT("*").FROM("test").ORDER_BY("id").LIMIT("#{limit}");
309     }}.toString();
310 
311     assertEquals("SELECT *\nFROM test\nORDER BY id LIMIT #{limit}", sql);
312   }
313 
314   @Test
315   void selectUsingOffsetVariableName() {
316     final String sql = new SQL() {{
317       SELECT("*").FROM("test").ORDER_BY("id").OFFSET("#{offset}");
318     }}.toString();
319 
320     assertEquals("SELECT *\nFROM test\nORDER BY id OFFSET #{offset}", sql);
321   }
322 
323   @Test
324   void selectUsingLimitAndOffset() {
325     final String sql = new SQL() {{
326       SELECT("*").FROM("test").ORDER_BY("id").LIMIT(20).OFFSET(100);
327     }}.toString();
328 
329     assertEquals("SELECT *\nFROM test\nORDER BY id LIMIT 20 OFFSET 100", sql);
330   }
331 
332   @Test
333   void updateUsingLimit() {
334     final String sql = new SQL() {{
335       UPDATE("test").SET("status = #{updStatus}").WHERE("status = #{status}").LIMIT(20);
336     }}.toString();
337 
338     assertEquals("UPDATE test\nSET status = #{updStatus}\nWHERE (status = #{status}) LIMIT 20", sql);
339   }
340 
341   @Test
342   void deleteUsingLimit() {
343     final String sql = new SQL() {{
344       DELETE_FROM("test").WHERE("status = #{status}").LIMIT(20);
345     }}.toString();
346 
347     assertEquals("DELETE FROM test\nWHERE (status = #{status}) LIMIT 20", sql);
348   }
349 
350   @Test
351   void selectUsingFetchFirstRowsOnlyVariableName() {
352     final String sql = new SQL() {{
353       SELECT("*").FROM("test").ORDER_BY("id").FETCH_FIRST_ROWS_ONLY("#{fetchFirstRows}");
354     }}.toString();
355 
356     assertEquals("SELECT *\nFROM test\nORDER BY id FETCH FIRST #{fetchFirstRows} ROWS ONLY", sql);
357   }
358 
359   @Test
360   void selectUsingOffsetRowsVariableName() {
361     final String sql = new SQL() {{
362       SELECT("*").FROM("test").ORDER_BY("id").OFFSET_ROWS("#{offsetRows}");
363     }}.toString();
364 
365     assertEquals("SELECT *\nFROM test\nORDER BY id OFFSET #{offsetRows} ROWS", sql);
366   }
367 
368   @Test
369   void selectUsingOffsetRowsAndFetchFirstRowsOnly() {
370     final String sql = new SQL() {{
371       SELECT("*").FROM("test").ORDER_BY("id").OFFSET_ROWS(100).FETCH_FIRST_ROWS_ONLY(20);
372     }}.toString();
373 
374     assertEquals("SELECT *\nFROM test\nORDER BY id OFFSET 100 ROWS FETCH FIRST 20 ROWS ONLY", sql);
375   }
376 
377   @Test
378   void supportBatchInsert(){
379     final String sql =  new SQL(){{
380       INSERT_INTO("table1 a");
381       INTO_COLUMNS("col1,col2");
382       INTO_VALUES("val1","val2");
383       ADD_ROW();
384       INTO_VALUES("val1","val2");
385     }}.toString();
386 
387     assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO table1 a (col1,col2) VALUES (val1,val2), (val1,val2)");
388   }
389 
390   @Test
391   void singleInsert() {
392     final String sql = new SQL() {{
393       INSERT_INTO("table1 a");
394       INTO_COLUMNS("col1,col2");
395       INTO_VALUES("val1", "val2");
396     }}.toString();
397 
398     assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO table1 a (col1,col2) VALUES (val1,val2)");
399   }
400 
401   @Test
402   void singleInsertWithMultipleInsertValues() {
403     final String sql = new SQL() {{
404       INSERT_INTO("TABLE_A").INTO_COLUMNS("a", "b").INTO_VALUES("#{a}").INTO_VALUES("#{b}");
405     }}.toString();
406 
407     assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO TABLE_A (a, b) VALUES (#{a}, #{b})");
408   }
409 
410   @Test
411   void batchInsertWithMultipleInsertValues() {
412     final String sql = new SQL() {{
413       INSERT_INTO("TABLE_A");
414       INTO_COLUMNS("a", "b");
415       INTO_VALUES("#{a1}");
416       INTO_VALUES("#{b1}");
417       ADD_ROW();
418       INTO_VALUES("#{a2}");
419       INTO_VALUES("#{b2}");
420     }}.toString();
421 
422     assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO TABLE_A (a, b) VALUES (#{a1}, #{b1}), (#{a2}, #{b2})");
423   }
424 
425   @Test
426   void testValues() {
427     final String sql = new SQL() {{
428       INSERT_INTO("PERSON");
429       VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
430       VALUES("LAST_NAME", "#{lastName}");
431     }}.toString();
432 
433     assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO PERSON (ID, FIRST_NAME, LAST_NAME) VALUES (#{id}, #{firstName}, #{lastName})");
434   }
435 }