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 org.junit.jupiter.api.Test;
22
23 class SQLTest {
24
25 @Test
26 void shouldDemonstrateProvidedStringBuilder() {
27
28 final StringBuilder sb = new StringBuilder();
29
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
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
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 }