1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.ibatis.jdbc;
17
18 import java.io.IOException;
19 import java.util.ArrayList;
20 import java.util.Arrays;
21 import java.util.Collections;
22 import java.util.List;
23
24
25
26
27
28
29
30 public abstract class AbstractSQL<T> {
31
32 private static final String AND = ") \nAND (";
33 private static final String OR = ") \nOR (";
34
35 private final SQLStatement sql = new SQLStatement();
36
37 public abstract T getSelf();
38
39 public T UPDATE(String table) {
40 sql().statementType = SQLStatement.StatementType.UPDATE;
41 sql().tables.add(table);
42 return getSelf();
43 }
44
45 public T SET(String sets) {
46 sql().sets.add(sets);
47 return getSelf();
48 }
49
50
51
52
53 public T SET(String... sets) {
54 sql().sets.addAll(Arrays.asList(sets));
55 return getSelf();
56 }
57
58 public T INSERT_INTO(String tableName) {
59 sql().statementType = SQLStatement.StatementType.INSERT;
60 sql().tables.add(tableName);
61 return getSelf();
62 }
63
64 public T VALUES(String columns, String values) {
65 INTO_COLUMNS(columns);
66 INTO_VALUES(values);
67 return getSelf();
68 }
69
70
71
72
73 public T INTO_COLUMNS(String... columns) {
74 sql().columns.addAll(Arrays.asList(columns));
75 return getSelf();
76 }
77
78
79
80
81 public T INTO_VALUES(String... values) {
82 List<String> list = sql().valuesList.get(sql().valuesList.size() - 1);
83 Collections.addAll(list, values);
84 return getSelf();
85 }
86
87 public T SELECT(String columns) {
88 sql().statementType = SQLStatement.StatementType.SELECT;
89 sql().select.add(columns);
90 return getSelf();
91 }
92
93
94
95
96 public T SELECT(String... columns) {
97 sql().statementType = SQLStatement.StatementType.SELECT;
98 sql().select.addAll(Arrays.asList(columns));
99 return getSelf();
100 }
101
102 public T SELECT_DISTINCT(String columns) {
103 sql().distinct = true;
104 SELECT(columns);
105 return getSelf();
106 }
107
108
109
110
111 public T SELECT_DISTINCT(String... columns) {
112 sql().distinct = true;
113 SELECT(columns);
114 return getSelf();
115 }
116
117 public T DELETE_FROM(String table) {
118 sql().statementType = SQLStatement.StatementType.DELETE;
119 sql().tables.add(table);
120 return getSelf();
121 }
122
123 public T FROM(String table) {
124 sql().tables.add(table);
125 return getSelf();
126 }
127
128
129
130
131 public T FROM(String... tables) {
132 sql().tables.addAll(Arrays.asList(tables));
133 return getSelf();
134 }
135
136 public T JOIN(String join) {
137 sql().join.add(join);
138 return getSelf();
139 }
140
141
142
143
144 public T JOIN(String... joins) {
145 sql().join.addAll(Arrays.asList(joins));
146 return getSelf();
147 }
148
149 public T INNER_JOIN(String join) {
150 sql().innerJoin.add(join);
151 return getSelf();
152 }
153
154
155
156
157 public T INNER_JOIN(String... joins) {
158 sql().innerJoin.addAll(Arrays.asList(joins));
159 return getSelf();
160 }
161
162 public T LEFT_OUTER_JOIN(String join) {
163 sql().leftOuterJoin.add(join);
164 return getSelf();
165 }
166
167
168
169
170 public T LEFT_OUTER_JOIN(String... joins) {
171 sql().leftOuterJoin.addAll(Arrays.asList(joins));
172 return getSelf();
173 }
174
175 public T RIGHT_OUTER_JOIN(String join) {
176 sql().rightOuterJoin.add(join);
177 return getSelf();
178 }
179
180
181
182
183 public T RIGHT_OUTER_JOIN(String... joins) {
184 sql().rightOuterJoin.addAll(Arrays.asList(joins));
185 return getSelf();
186 }
187
188 public T OUTER_JOIN(String join) {
189 sql().outerJoin.add(join);
190 return getSelf();
191 }
192
193
194
195
196 public T OUTER_JOIN(String... joins) {
197 sql().outerJoin.addAll(Arrays.asList(joins));
198 return getSelf();
199 }
200
201 public T WHERE(String conditions) {
202 sql().where.add(conditions);
203 sql().lastList = sql().where;
204 return getSelf();
205 }
206
207
208
209
210 public T WHERE(String... conditions) {
211 sql().where.addAll(Arrays.asList(conditions));
212 sql().lastList = sql().where;
213 return getSelf();
214 }
215
216 public T OR() {
217 sql().lastList.add(OR);
218 return getSelf();
219 }
220
221 public T AND() {
222 sql().lastList.add(AND);
223 return getSelf();
224 }
225
226 public T GROUP_BY(String columns) {
227 sql().groupBy.add(columns);
228 return getSelf();
229 }
230
231
232
233
234 public T GROUP_BY(String... columns) {
235 sql().groupBy.addAll(Arrays.asList(columns));
236 return getSelf();
237 }
238
239 public T HAVING(String conditions) {
240 sql().having.add(conditions);
241 sql().lastList = sql().having;
242 return getSelf();
243 }
244
245
246
247
248 public T HAVING(String... conditions) {
249 sql().having.addAll(Arrays.asList(conditions));
250 sql().lastList = sql().having;
251 return getSelf();
252 }
253
254 public T ORDER_BY(String columns) {
255 sql().orderBy.add(columns);
256 return getSelf();
257 }
258
259
260
261
262 public T ORDER_BY(String... columns) {
263 sql().orderBy.addAll(Arrays.asList(columns));
264 return getSelf();
265 }
266
267
268
269
270
271
272
273
274
275 public T LIMIT(String variable) {
276 sql().limit = variable;
277 sql().limitingRowsStrategy = SQLStatement.LimitingRowsStrategy.OFFSET_LIMIT;
278 return getSelf();
279 }
280
281
282
283
284
285
286
287
288
289 public T LIMIT(int value) {
290 return LIMIT(String.valueOf(value));
291 }
292
293
294
295
296
297
298
299
300
301 public T OFFSET(String variable) {
302 sql().offset = variable;
303 sql().limitingRowsStrategy = SQLStatement.LimitingRowsStrategy.OFFSET_LIMIT;
304 return getSelf();
305 }
306
307
308
309
310
311
312
313
314
315 public T OFFSET(long value) {
316 return OFFSET(String.valueOf(value));
317 }
318
319
320
321
322
323
324
325
326
327 public T FETCH_FIRST_ROWS_ONLY(String variable) {
328 sql().limit = variable;
329 sql().limitingRowsStrategy = SQLStatement.LimitingRowsStrategy.ISO;
330 return getSelf();
331 }
332
333
334
335
336
337
338
339
340
341 public T FETCH_FIRST_ROWS_ONLY(int value) {
342 return FETCH_FIRST_ROWS_ONLY(String.valueOf(value));
343 }
344
345
346
347
348
349
350
351
352
353 public T OFFSET_ROWS(String variable) {
354 sql().offset = variable;
355 sql().limitingRowsStrategy = SQLStatement.LimitingRowsStrategy.ISO;
356 return getSelf();
357 }
358
359
360
361
362
363
364
365
366
367 public T OFFSET_ROWS(long value) {
368 return OFFSET_ROWS(String.valueOf(value));
369 }
370
371
372
373
374
375
376 public T ADD_ROW() {
377 sql().valuesList.add(new ArrayList<>());
378 return getSelf();
379 }
380
381 private SQLStatement sql() {
382 return sql;
383 }
384
385 public <A extends Appendable> A usingAppender(A a) {
386 sql().sql(a);
387 return a;
388 }
389
390 @Override
391 public String toString() {
392 StringBuilder sb = new StringBuilder();
393 sql().sql(sb);
394 return sb.toString();
395 }
396
397 private static class SafeAppendable {
398 private final Appendable a;
399 private boolean empty = true;
400
401 public SafeAppendable(Appendable a) {
402 super();
403 this.a = a;
404 }
405
406 public SafeAppendable append(CharSequence s) {
407 try {
408 if (empty && s.length() > 0) {
409 empty = false;
410 }
411 a.append(s);
412 } catch (IOException e) {
413 throw new RuntimeException(e);
414 }
415 return this;
416 }
417
418 public boolean isEmpty() {
419 return empty;
420 }
421
422 }
423
424 private static class SQLStatement {
425
426 public enum StatementType {
427 DELETE, INSERT, SELECT, UPDATE
428 }
429
430 private enum LimitingRowsStrategy {
431 NOP {
432 @Override
433 protected void appendClause(SafeAppendable builder, String offset, String limit) {
434
435 }
436 },
437 ISO {
438 @Override
439 protected void appendClause(SafeAppendable builder, String offset, String limit) {
440 if (offset != null) {
441 builder.append(" OFFSET ").append(offset).append(" ROWS");
442 }
443 if (limit != null) {
444 builder.append(" FETCH FIRST ").append(limit).append(" ROWS ONLY");
445 }
446 }
447 },
448 OFFSET_LIMIT {
449 @Override
450 protected void appendClause(SafeAppendable builder, String offset, String limit) {
451 if (limit != null) {
452 builder.append(" LIMIT ").append(limit);
453 }
454 if (offset != null) {
455 builder.append(" OFFSET ").append(offset);
456 }
457 }
458 };
459
460 protected abstract void appendClause(SafeAppendable builder, String offset, String limit);
461
462 }
463
464 StatementType statementType;
465 List<String> sets = new ArrayList<>();
466 List<String> select = new ArrayList<>();
467 List<String> tables = new ArrayList<>();
468 List<String> join = new ArrayList<>();
469 List<String> innerJoin = new ArrayList<>();
470 List<String> outerJoin = new ArrayList<>();
471 List<String> leftOuterJoin = new ArrayList<>();
472 List<String> rightOuterJoin = new ArrayList<>();
473 List<String> where = new ArrayList<>();
474 List<String> having = new ArrayList<>();
475 List<String> groupBy = new ArrayList<>();
476 List<String> orderBy = new ArrayList<>();
477 List<String> lastList = new ArrayList<>();
478 List<String> columns = new ArrayList<>();
479 List<List<String>> valuesList = new ArrayList<>();
480 boolean distinct;
481 String offset;
482 String limit;
483 LimitingRowsStrategy limitingRowsStrategy = LimitingRowsStrategy.NOP;
484
485 public SQLStatement() {
486
487 valuesList.add(new ArrayList<>());
488 }
489
490 private void sqlClause(SafeAppendable builder, String keyword, List<String> parts, String open, String close,
491 String conjunction) {
492 if (!parts.isEmpty()) {
493 if (!builder.isEmpty()) {
494 builder.append("\n");
495 }
496 builder.append(keyword);
497 builder.append(" ");
498 builder.append(open);
499 String last = "________";
500 for (int i = 0, n = parts.size(); i < n; i++) {
501 String part = parts.get(i);
502 if (i > 0 && !part.equals(AND) && !part.equals(OR) && !last.equals(AND) && !last.equals(OR)) {
503 builder.append(conjunction);
504 }
505 builder.append(part);
506 last = part;
507 }
508 builder.append(close);
509 }
510 }
511
512 private String selectSQL(SafeAppendable builder) {
513 if (distinct) {
514 sqlClause(builder, "SELECT DISTINCT", select, "", "", ", ");
515 } else {
516 sqlClause(builder, "SELECT", select, "", "", ", ");
517 }
518
519 sqlClause(builder, "FROM", tables, "", "", ", ");
520 joins(builder);
521 sqlClause(builder, "WHERE", where, "(", ")", " AND ");
522 sqlClause(builder, "GROUP BY", groupBy, "", "", ", ");
523 sqlClause(builder, "HAVING", having, "(", ")", " AND ");
524 sqlClause(builder, "ORDER BY", orderBy, "", "", ", ");
525 limitingRowsStrategy.appendClause(builder, offset, limit);
526 return builder.toString();
527 }
528
529 private void joins(SafeAppendable builder) {
530 sqlClause(builder, "JOIN", join, "", "", "\nJOIN ");
531 sqlClause(builder, "INNER JOIN", innerJoin, "", "", "\nINNER JOIN ");
532 sqlClause(builder, "OUTER JOIN", outerJoin, "", "", "\nOUTER JOIN ");
533 sqlClause(builder, "LEFT OUTER JOIN", leftOuterJoin, "", "", "\nLEFT OUTER JOIN ");
534 sqlClause(builder, "RIGHT OUTER JOIN", rightOuterJoin, "", "", "\nRIGHT OUTER JOIN ");
535 }
536
537 private String insertSQL(SafeAppendable builder) {
538 sqlClause(builder, "INSERT INTO", tables, "", "", "");
539 sqlClause(builder, "", columns, "(", ")", ", ");
540 for (int i = 0; i < valuesList.size(); i++) {
541 sqlClause(builder, i > 0 ? "," : "VALUES", valuesList.get(i), "(", ")", ", ");
542 }
543 return builder.toString();
544 }
545
546 private String deleteSQL(SafeAppendable builder) {
547 sqlClause(builder, "DELETE FROM", tables, "", "", "");
548 sqlClause(builder, "WHERE", where, "(", ")", " AND ");
549 limitingRowsStrategy.appendClause(builder, null, limit);
550 return builder.toString();
551 }
552
553 private String updateSQL(SafeAppendable builder) {
554 sqlClause(builder, "UPDATE", tables, "", "", "");
555 joins(builder);
556 sqlClause(builder, "SET", sets, "", "", ", ");
557 sqlClause(builder, "WHERE", where, "(", ")", " AND ");
558 limitingRowsStrategy.appendClause(builder, null, limit);
559 return builder.toString();
560 }
561
562 public String sql(Appendable a) {
563 SafeAppendable builder = new SafeAppendable(a);
564 if (statementType == null) {
565 return null;
566 }
567
568 String answer;
569
570 switch (statementType) {
571 case DELETE:
572 answer = deleteSQL(builder);
573 break;
574
575 case INSERT:
576 answer = insertSQL(builder);
577 break;
578
579 case SELECT:
580 answer = selectSQL(builder);
581 break;
582
583 case UPDATE:
584 answer = updateSQL(builder);
585 break;
586
587 default:
588 answer = null;
589 }
590
591 return answer;
592 }
593 }
594 }