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