1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 java.util.ArrayList;
22 import java.util.List;
23
24 import org.junit.jupiter.api.Test;
25
26 class SQLTest {
27
28 @Test
29 void shouldDemonstrateProvidedStringBuilder() {
30
31 final StringBuilder sb = new StringBuilder();
32
33 final String sql = example1().usingAppender(sb).toString();
34
35 assertEquals("""
36 SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON
37 FROM PERSON P, ACCOUNT A
38 INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID
39 INNER JOIN COMPANY C on D.COMPANY_ID = C.ID
40 WHERE (P.ID = A.ID AND P.FIRST_NAME like ?)\s
41 OR (P.LAST_NAME like ?)
42 GROUP BY P.ID
43 HAVING (P.LAST_NAME like ?)\s
44 OR (P.FIRST_NAME like ?)
45 ORDER BY P.ID, P.FULL_NAME""", sql);
46 }
47
48 @Test
49 void shouldDemonstrateMixedStyle() {
50
51 final String sql = new SQL() {
52 {
53 SELECT("id, name");
54 FROM("PERSON A");
55 WHERE("name like ?").WHERE("id = ?");
56 }
57 }.toString();
58
59 assertEquals("""
60 SELECT id, name
61 FROM PERSON A
62 WHERE (name like ? AND id = ?)""", sql);
63 }
64
65 @Test
66 void shouldDemonstrateFluentStyle() {
67
68 final String sql = new SQL().SELECT("id, name").FROM("PERSON A").WHERE("name like ?").WHERE("id = ?").toString();
69
70 assertEquals("""
71 SELECT id, name
72 FROM PERSON A
73 WHERE (name like ? AND id = ?)""", sql);
74 }
75
76 @Test
77 void shouldProduceExpectedSimpleSelectStatement() {
78 final String expected = """
79 SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME
80 FROM PERSON P
81 WHERE (P.ID like #id# AND P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)
82 ORDER BY P.LAST_NAME""";
83 assertEquals(expected, example2("a", "b", "c"));
84 }
85
86 @Test
87 void shouldProduceExpectedSimpleSelectStatementMissingFirstParam() {
88 final String expected = """
89 SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME
90 FROM PERSON P
91 WHERE (P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)
92 ORDER BY P.LAST_NAME""";
93 assertEquals(expected, example2(null, "b", "c"));
94 }
95
96 @Test
97 void shouldProduceExpectedSimpleSelectStatementMissingFirstTwoParams() {
98 final String expected = """
99 SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME
100 FROM PERSON P
101 WHERE (P.LAST_NAME like #lastName#)
102 ORDER BY P.LAST_NAME""";
103 assertEquals(expected, example2(null, null, "c"));
104 }
105
106 @Test
107 void shouldProduceExpectedSimpleSelectStatementMissingAllParams() {
108 final String expected = """
109 SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME
110 FROM PERSON P
111 ORDER BY P.LAST_NAME""";
112 assertEquals(expected, example2(null, null, null));
113 }
114
115 @Test
116 void shouldProduceExpectedComplexSelectStatement() {
117 final String expected = """
118 SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON
119 FROM PERSON P, ACCOUNT A
120 INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID
121 INNER JOIN COMPANY C on D.COMPANY_ID = C.ID
122 WHERE (P.ID = A.ID AND P.FIRST_NAME like ?)\s
123 OR (P.LAST_NAME like ?)
124 GROUP BY P.ID
125 HAVING (P.LAST_NAME like ?)\s
126 OR (P.FIRST_NAME like ?)
127 ORDER BY P.ID, P.FULL_NAME""";
128 assertEquals(expected, example1().toString());
129 }
130
131 private static SQL example1() {
132 return new SQL() {
133 {
134 SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
135 SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
136 FROM("PERSON P");
137 FROM("ACCOUNT A");
138 INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
139 INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
140 WHERE("P.ID = A.ID");
141 WHERE("P.FIRST_NAME like ?");
142 OR();
143 WHERE("P.LAST_NAME like ?");
144 GROUP_BY("P.ID");
145 HAVING("P.LAST_NAME like ?");
146 OR();
147 HAVING("P.FIRST_NAME like ?");
148 ORDER_BY("P.ID");
149 ORDER_BY("P.FULL_NAME");
150 }
151 };
152 }
153
154 private static String example2(final String id, final String firstName, final String lastName) {
155 return new SQL() {
156 {
157 SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
158 FROM("PERSON P");
159 if (id != null) {
160 WHERE("P.ID like #id#");
161 }
162 if (firstName != null) {
163 WHERE("P.FIRST_NAME like #firstName#");
164 }
165 if (lastName != null) {
166 WHERE("P.LAST_NAME like #lastName#");
167 }
168 ORDER_BY("P.LAST_NAME");
169 }
170 }.toString();
171 }
172
173 @Test
174 void variableLengthArgumentOnSelect() {
175 final String sql = new SQL() {
176 {
177 SELECT("P.ID", "P.USERNAME");
178 }
179 }.toString();
180
181 assertEquals("SELECT P.ID, P.USERNAME", sql);
182 }
183
184 @Test
185 void variableLengthArgumentOnSelectDistinct() {
186 final String sql = new SQL() {
187 {
188 SELECT_DISTINCT("P.ID", "P.USERNAME");
189 }
190 }.toString();
191
192 assertEquals("SELECT DISTINCT P.ID, P.USERNAME", sql);
193 }
194
195 @Test
196 void variableLengthArgumentOnFrom() {
197 final String sql = new SQL() {
198 {
199 SELECT().FROM("TABLE_A a", "TABLE_B b");
200 }
201 }.toString();
202
203 assertEquals("FROM TABLE_A a, TABLE_B b", sql);
204 }
205
206 @Test
207 void variableLengthArgumentOnJoin() {
208 final String sql = new SQL() {
209 {
210 SELECT().JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
211 }
212 }.toString();
213
214 assertEquals("JOIN TABLE_A b ON b.id = a.id\n" + "JOIN TABLE_C c ON c.id = a.id", sql);
215 }
216
217 @Test
218 void variableLengthArgumentOnInnerJoin() {
219 final String sql = new SQL() {
220 {
221 SELECT().INNER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
222 }
223 }.toString();
224
225 assertEquals("INNER JOIN TABLE_A b ON b.id = a.id\n" + "INNER JOIN TABLE_C c ON c.id = a.id", sql);
226 }
227
228 @Test
229 void variableLengthArgumentOnOuterJoin() {
230 final String sql = new SQL() {
231 {
232 SELECT().OUTER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
233 }
234 }.toString();
235
236 assertEquals("OUTER JOIN TABLE_A b ON b.id = a.id\n" + "OUTER JOIN TABLE_C c ON c.id = a.id", sql);
237 }
238
239 @Test
240 void variableLengthArgumentOnLeftOuterJoin() {
241 final String sql = new SQL() {
242 {
243 SELECT().LEFT_OUTER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
244 }
245 }.toString();
246
247 assertEquals("LEFT OUTER JOIN TABLE_A b ON b.id = a.id\n" + "LEFT OUTER JOIN TABLE_C c ON c.id = a.id", sql);
248 }
249
250 @Test
251 void variableLengthArgumentOnRightOuterJoin() {
252 final String sql = new SQL() {
253 {
254 SELECT().RIGHT_OUTER_JOIN("TABLE_A b ON b.id = a.id", "TABLE_C c ON c.id = a.id");
255 }
256 }.toString();
257
258 assertEquals("RIGHT OUTER JOIN TABLE_A b ON b.id = a.id\n" + "RIGHT OUTER JOIN TABLE_C c ON c.id = a.id", sql);
259 }
260
261 @Test
262 void variableLengthArgumentOnWhere() {
263 final String sql = new SQL() {
264 {
265 SELECT().WHERE("a = #{a}", "b = #{b}");
266 }
267 }.toString();
268
269 assertEquals("WHERE (a = #{a} AND b = #{b})", sql);
270 }
271
272 @Test
273 void variableLengthArgumentOnGroupBy() {
274 final String sql = new SQL() {
275 {
276 SELECT().GROUP_BY("a", "b");
277 }
278 }.toString();
279
280 assertEquals("GROUP BY a, b", sql);
281 }
282
283 @Test
284 void variableLengthArgumentOnHaving() {
285 final String sql = new SQL() {
286 {
287 SELECT().HAVING("a = #{a}", "b = #{b}");
288 }
289 }.toString();
290
291 assertEquals("HAVING (a = #{a} AND b = #{b})", sql);
292 }
293
294 @Test
295 void variableLengthArgumentOnOrderBy() {
296 final String sql = new SQL() {
297 {
298 SELECT().ORDER_BY("a", "b");
299 }
300 }.toString();
301
302 assertEquals("ORDER BY a, b", sql);
303 }
304
305 @Test
306 void variableLengthArgumentOnSet() {
307 final String sql = new SQL() {
308 {
309 UPDATE("TABLE_A").SET("a = #{a}", "b = #{b}");
310 }
311 }.toString();
312
313 assertEquals("UPDATE TABLE_A\n" + "SET a = #{a}, b = #{b}", sql);
314 }
315
316 @Test
317 void variableLengthArgumentOnIntoColumnsAndValues() {
318 final String sql = new SQL() {
319 {
320 INSERT_INTO("TABLE_A").INTO_COLUMNS("a", "b").INTO_VALUES("#{a}", "#{b}");
321 }
322 }.toString();
323
324 assertEquals("INSERT INTO TABLE_A\n (a, b)\nVALUES (#{a}, #{b})", sql);
325 }
326
327 @Test
328 void fixFor903UpdateJoins() {
329 final SQL sql = new SQL().UPDATE("table1 a").INNER_JOIN("table2 b USING (ID)").SET("a.value = b.value");
330 assertThat(sql.toString()).isEqualTo("UPDATE table1 a\nINNER JOIN table2 b USING (ID)\nSET a.value = b.value");
331 }
332
333 @Test
334 void selectUsingLimitVariableName() {
335 final String sql = new SQL() {
336 {
337 SELECT("*").FROM("test").ORDER_BY("id").LIMIT("#{limit}");
338 }
339 }.toString();
340
341 assertEquals("SELECT *\nFROM test\nORDER BY id LIMIT #{limit}", sql);
342 }
343
344 @Test
345 void selectUsingOffsetVariableName() {
346 final String sql = new SQL() {
347 {
348 SELECT("*").FROM("test").ORDER_BY("id").OFFSET("#{offset}");
349 }
350 }.toString();
351
352 assertEquals("SELECT *\nFROM test\nORDER BY id OFFSET #{offset}", sql);
353 }
354
355 @Test
356 void selectUsingLimitAndOffset() {
357 final String sql = new SQL() {
358 {
359 SELECT("*").FROM("test").ORDER_BY("id").LIMIT(20).OFFSET(100);
360 }
361 }.toString();
362
363 assertEquals("SELECT *\nFROM test\nORDER BY id LIMIT 20 OFFSET 100", sql);
364 }
365
366 @Test
367 void updateUsingLimit() {
368 final String sql = new SQL() {
369 {
370 UPDATE("test").SET("status = #{updStatus}").WHERE("status = #{status}").LIMIT(20);
371 }
372 }.toString();
373
374 assertEquals("UPDATE test\nSET status = #{updStatus}\nWHERE (status = #{status}) LIMIT 20", sql);
375 }
376
377 @Test
378 void deleteUsingLimit() {
379 final String sql = new SQL() {
380 {
381 DELETE_FROM("test").WHERE("status = #{status}").LIMIT(20);
382 }
383 }.toString();
384
385 assertEquals("DELETE FROM test\nWHERE (status = #{status}) LIMIT 20", sql);
386 }
387
388 @Test
389 void selectUsingFetchFirstRowsOnlyVariableName() {
390 final String sql = new SQL() {
391 {
392 SELECT("*").FROM("test").ORDER_BY("id").FETCH_FIRST_ROWS_ONLY("#{fetchFirstRows}");
393 }
394 }.toString();
395
396 assertEquals("SELECT *\nFROM test\nORDER BY id FETCH FIRST #{fetchFirstRows} ROWS ONLY", sql);
397 }
398
399 @Test
400 void selectUsingOffsetRowsVariableName() {
401 final String sql = new SQL() {
402 {
403 SELECT("*").FROM("test").ORDER_BY("id").OFFSET_ROWS("#{offsetRows}");
404 }
405 }.toString();
406
407 assertEquals("SELECT *\nFROM test\nORDER BY id OFFSET #{offsetRows} ROWS", sql);
408 }
409
410 @Test
411 void selectUsingOffsetRowsAndFetchFirstRowsOnly() {
412 final String sql = new SQL() {
413 {
414 SELECT("*").FROM("test").ORDER_BY("id").OFFSET_ROWS(100).FETCH_FIRST_ROWS_ONLY(20);
415 }
416 }.toString();
417
418 assertEquals("SELECT *\nFROM test\nORDER BY id OFFSET 100 ROWS FETCH FIRST 20 ROWS ONLY", sql);
419 }
420
421 @Test
422 void supportBatchInsert() {
423 final String sql = new SQL() {
424 {
425 INSERT_INTO("table1 a");
426 INTO_COLUMNS("col1,col2");
427 INTO_VALUES("val1", "val2");
428 ADD_ROW();
429 INTO_VALUES("val1", "val2");
430 }
431 }.toString();
432
433 assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO table1 a (col1,col2) VALUES (val1,val2), (val1,val2)");
434 }
435
436 @Test
437 void singleInsert() {
438 final String sql = new SQL() {
439 {
440 INSERT_INTO("table1 a");
441 INTO_COLUMNS("col1,col2");
442 INTO_VALUES("val1", "val2");
443 }
444 }.toString();
445
446 assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO table1 a (col1,col2) VALUES (val1,val2)");
447 }
448
449 @Test
450 void singleInsertWithMultipleInsertValues() {
451 final String sql = new SQL() {
452 {
453 INSERT_INTO("TABLE_A").INTO_COLUMNS("a", "b").INTO_VALUES("#{a}").INTO_VALUES("#{b}");
454 }
455 }.toString();
456
457 assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO TABLE_A (a, b) VALUES (#{a}, #{b})");
458 }
459
460 @Test
461 void batchInsertWithMultipleInsertValues() {
462 final String sql = new SQL() {
463 {
464 INSERT_INTO("TABLE_A");
465 INTO_COLUMNS("a", "b");
466 INTO_VALUES("#{a1}");
467 INTO_VALUES("#{b1}");
468 ADD_ROW();
469 INTO_VALUES("#{a2}");
470 INTO_VALUES("#{b2}");
471 }
472 }.toString();
473
474 assertThat(sql).isEqualToIgnoringWhitespace("INSERT INTO TABLE_A (a, b) VALUES (#{a1}, #{b1}), (#{a2}, #{b2})");
475 }
476
477 @Test
478 void values() {
479 final String sql = new SQL() {
480 {
481 INSERT_INTO("PERSON");
482 VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
483 VALUES("LAST_NAME", "#{lastName}");
484 }
485 }.toString();
486
487 assertThat(sql).isEqualToIgnoringWhitespace(
488 "INSERT INTO PERSON (ID, FIRST_NAME, LAST_NAME) VALUES (#{id}, #{firstName}, #{lastName})");
489 }
490
491 @Test
492 void applyIf() {
493 Bean bean = new Bean();
494
495 String sqlString = new SQL()
496 .UPDATE("test")
497 .applyIf(bean.a != null, sql -> sql.SET("a=#{a}"))
498 .applyIf(bean.b != null, sql -> sql.SET("b=#{b}"))
499 .applyIf(bean::hasC, sql -> sql.SET("c=#{c}"))
500 .WHERE("id=#{id}").toString();
501
502
503 assertThat(sqlString).isEqualToIgnoringWhitespace("UPDATE test SET a=#{a} WHERE (id=#{id})");
504 }
505
506 @Test
507 void applyForEach() {
508 List<Bean> beans = new ArrayList<>();
509 beans.add(new Bean());
510 beans.add(new Bean());
511
512
513 String sqlString = new SQL()
514 .INSERT_INTO("test")
515 .INTO_COLUMNS("a", "b", "c")
516 .applyForEach(beans, (sql, element, index) ->
517 sql.INTO_VALUES(
518 String.format("#{list[%s].a}", index),
519 String.format("#{list[%s].b}", index),
520 String.format("#{list[%s].c}", index)
521 ).ADD_ROW())
522 .toString();
523
524
525 assertThat(sqlString).isEqualToIgnoringWhitespace(
526 "INSERT INTO test (a, b, c) VALUES (#{list[0].a}, #{list[0].b}, #{list[0].c}), (#{list[1].a}, #{list[1].b}, #{list[1].c})");
527 }
528
529 static class Bean {
530 private String a = "foo";
531 private String b;
532 private String c;
533
534 boolean hasC() {
535 return c != null;
536 }
537 }
538 }