1 package sharin.sql.generator;
2
3 import java.lang.reflect.Field;
4 import java.lang.reflect.Modifier;
5 import java.util.ArrayList;
6 import java.util.HashMap;
7 import java.util.LinkedHashMap;
8 import java.util.LinkedHashSet;
9 import java.util.List;
10 import java.util.Map;
11 import java.util.Set;
12 import java.util.regex.Matcher;
13 import java.util.regex.Pattern;
14
15 import javax.persistence.JoinColumn;
16 import javax.persistence.ManyToOne;
17 import javax.persistence.OneToOne;
18
19 import sharin.sql.Sql;
20 import sharin.util.PropertyUtils;
21
22 public class BasicSqlGenerator implements SqlGenerator {
23
24 private static final Pattern propEntryPattern = Pattern
25 .compile("^(-?)(?:([^.]*)\\.)?([^.]+)$");
26
27 private static final Pattern directionPattern = Pattern.compile(
28 "^(?:A|DE)SC$", Pattern.CASE_INSENSITIVE);
29
30 private final EntityInfo entityInfo;
31
32 private final Map<String, JoinInfo> joinInfoMap;
33
34 public BasicSqlGenerator(EntityInfo entityInfo) {
35 this(entityInfo, null);
36 }
37
38 public BasicSqlGenerator(EntityInfo entityInfo,
39 Map<String, JoinInfo> joinInfoMap) {
40
41 this.entityInfo = entityInfo;
42
43 if (joinInfoMap == null) {
44 joinInfoMap = new HashMap<String, JoinInfo>();
45
46 } else {
47 joinInfoMap = new HashMap<String, JoinInfo>(joinInfoMap);
48 }
49
50 for (Field field : entityInfo.getEntityClass().getDeclaredFields()) {
51
52 if (Modifier.isStatic(field.getModifiers())) {
53 continue;
54 }
55
56 if (field.isAnnotationPresent(ManyToOne.class)
57 || field.isAnnotationPresent(OneToOne.class)) {
58
59 String targetTableAlias = field.getName();
60
61 if (joinInfoMap.containsKey(targetTableAlias)) {
62 continue;
63 }
64
65 String sourceColumnName = null;
66 JoinColumn joinColumn = field.getAnnotation(JoinColumn.class);
67
68 if (joinColumn != null) {
69 sourceColumnName = joinColumn.name();
70 }
71
72 EntityInfo targetEntityInfo = new EntityInfo(field.getType());
73 JoinInfo joinInfo = new JoinInfo(sourceColumnName,
74 targetEntityInfo, targetTableAlias);
75 joinInfoMap.put(targetTableAlias, joinInfo);
76 }
77 }
78
79 this.joinInfoMap = joinInfoMap;
80 }
81
82 public Sql countAll() {
83 return countByExamples();
84 }
85
86 public Sql countByExample(Object example) {
87 return countByExamples(example);
88 }
89
90 public Sql countByExamples(Object... examples) {
91 StringBuilder textBuilder = new StringBuilder();
92 textBuilder.append("SELECT COUNT(*) FROM ");
93 textBuilder.append(entityInfo.getTableName());
94 List<Object> paramList = new ArrayList<Object>();
95
96 if (examples.length > 0) {
97 appendWhereExamples(textBuilder, examples, false, paramList);
98 }
99
100 return new Sql(textBuilder.toString(), paramList.toArray());
101 }
102
103 public Sql selectAll(String propExpr, String orderExpr) {
104 return selectByExamples(propExpr, orderExpr);
105 }
106
107 public Sql selectByExample(String propExpr, String orderExpr, Object example) {
108 return selectByExamples(propExpr, orderExpr, example);
109 }
110
111 public Sql selectByExamples(String propExpr, String orderExpr,
112 Object... examples) {
113
114 StringBuilder textBuilder = new StringBuilder();
115 Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr, true);
116 Set<JoinInfo> joinInfoSet = appendSelectFrom(textBuilder,
117 propertyJoinMap);
118 List<Object> paramList = new ArrayList<Object>();
119
120 if (examples.length > 0) {
121 appendWhereExamples(textBuilder, examples, !joinInfoSet.isEmpty(),
122 paramList);
123 }
124
125 appendOrderBy(textBuilder, orderExpr, propertyJoinMap);
126 return new Sql(textBuilder.toString(), paramList.toArray());
127 }
128
129 public Sql selectById(String propExpr, Object id) {
130 return selectByIds(propExpr, null, id);
131 }
132
133 public Sql selectByIds(String propExpr, String orderExpr, Object... ids) {
134 StringBuilder textBuilder = new StringBuilder();
135 Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr, true);
136 appendSelectFrom(textBuilder, propertyJoinMap);
137 List<Object> paramList = new ArrayList<Object>();
138
139 if (ids.length > 0) {
140 appendWhereIds(textBuilder, ids, paramList);
141 }
142
143 appendOrderBy(textBuilder, orderExpr, propertyJoinMap);
144 return new Sql(textBuilder.toString(), paramList.toArray());
145 }
146
147 public Sql insert(String propExpr, Object entity) {
148 StringBuilder textBuilder = new StringBuilder();
149 Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr,
150 false);
151 List<Object> paramList = new ArrayList<Object>();
152 boolean appended = false;
153
154 for (String propertyName : propertyJoinMap.keySet()) {
155 Object param = PropertyUtils.getSimplePropertyValue(entity,
156 propertyName);
157 paramList.add(param);
158
159 if (!appended) {
160 textBuilder.append("INSERT INTO ");
161 textBuilder.append(entityInfo.getTableName());
162 textBuilder.append(" (");
163 appended = true;
164
165 } else {
166 textBuilder.append(", ");
167 }
168
169 textBuilder.append(entityInfo.getColumnName(propertyName));
170 }
171
172 textBuilder.append(") VALUES (");
173
174 for (int i = 0; i < paramList.size(); i++) {
175
176 if (i > 0) {
177 textBuilder.append(", ");
178 }
179
180 textBuilder.append('?');
181 }
182
183 textBuilder.append(')');
184 return new Sql(textBuilder.toString(), paramList.toArray());
185 }
186
187 public Sql updateAll(String propExpr, Object entity) {
188 return updateByExamples(propExpr, entity);
189 }
190
191 public Sql updateByExample(String propExpr, Object entity, Object example) {
192 return updateByExamples(propExpr, entity, example);
193 }
194
195 public Sql updateByExamples(String propExpr, Object entity,
196 Object... examples) {
197
198 StringBuilder textBuilder = new StringBuilder();
199 Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr,
200 false);
201 List<Object> paramList = appendUpdateSet(textBuilder, propertyJoinMap
202 .keySet(), entity);
203
204 if (examples.length > 0) {
205 appendWhereExamples(textBuilder, examples, false, paramList);
206 }
207
208 return new Sql(textBuilder.toString(), paramList.toArray());
209 }
210
211 public Sql updateById(String propExpr, Object entity, Object id) {
212 return updateByIds(propExpr, entity, id);
213 }
214
215 public Sql updateByIds(String propExpr, Object entity, Object... ids) {
216 StringBuilder textBuilder = new StringBuilder();
217 Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr,
218 false);
219 List<Object> paramList = appendUpdateSet(textBuilder, propertyJoinMap
220 .keySet(), entity);
221
222 if (ids.length > 0) {
223 appendWhereIds(textBuilder, ids, paramList);
224 }
225
226 return new Sql(textBuilder.toString(), paramList.toArray());
227 }
228
229 public Sql deleteAll() {
230 return deleteByExamples();
231 }
232
233 public Sql deleteByExample(Object example) {
234 return deleteByExamples(example);
235 }
236
237 public Sql deleteByExamples(Object... examples) {
238 StringBuilder textBuilder = new StringBuilder();
239 textBuilder.append("DELETE FROM ");
240 textBuilder.append(entityInfo.getTableName());
241 List<Object> paramList = new ArrayList<Object>();
242
243 if (examples.length > 0) {
244 appendWhereExamples(textBuilder, examples, false, paramList);
245 }
246
247 return new Sql(textBuilder.toString(), paramList.toArray());
248 }
249
250 public Sql deleteById(Object id) {
251 return deleteByIds(id);
252 }
253
254 public Sql deleteByIds(Object... ids) {
255 StringBuilder textBuilder = new StringBuilder();
256 textBuilder.append("DELETE FROM ");
257 textBuilder.append(entityInfo.getTableName());
258 List<Object> paramList = new ArrayList<Object>();
259
260 if (ids.length > 0) {
261 appendWhereIds(textBuilder, ids, paramList);
262 }
263
264 return new Sql(textBuilder.toString(), paramList.toArray());
265 }
266
267 private Map<String, JoinInfo> evaluatePropExpr(String propExpr,
268 boolean joined) {
269
270 Map<String, JoinInfo> result = null;
271
272 if (propExpr == null || propExpr.length() == 0) {
273 propExpr = "*";
274 }
275
276 result = new LinkedHashMap<String, JoinInfo>();
277
278 for (String propEntry : propExpr.trim().split("\\s*,\\s*")) {
279
280 if (propEntry.length() == 0) {
281 continue;
282 }
283
284 Matcher matcher = propEntryPattern.matcher(propEntry);
285
286 if (!matcher.matches()) {
287 continue;
288 }
289
290 String sign = matcher.group(1);
291 String tableAlias = matcher.group(2);
292 String simpleName = matcher.group(3);
293
294 Map<String, JoinInfo> tempMap = result;
295
296 if (sign.equals("-")) {
297 tempMap = new LinkedHashMap<String, JoinInfo>();
298 }
299
300 if (tableAlias == null) {
301
302 if (simpleName.equals("*")) {
303
304 for (String s : entityInfo.getPropertyNames()) {
305 tempMap.put(s, null);
306 }
307
308 if (joined) {
309
310 for (JoinInfo joinInfo : joinInfoMap.values()) {
311
312 for (String s : joinInfo.getTargetColumnAliases()) {
313
314 tempMap.put(s, joinInfo);
315 }
316 }
317 }
318
319 } else {
320
321 if (entityInfo.hasColumnName(simpleName)) {
322 tempMap.put(simpleName, null);
323 }
324 }
325
326 } else {
327
328 if (tableAlias.length() == 0) {
329
330 if (simpleName.equals("*")) {
331
332 for (String s : entityInfo.getPropertyNames()) {
333 tempMap.put(s, null);
334 }
335 }
336
337 } else {
338 JoinInfo joinInfo = joinInfoMap.get(tableAlias);
339
340 if (joinInfo != null) {
341
342 if (simpleName.equals("*")) {
343
344 for (String s : joinInfo.getTargetColumnAliases()) {
345
346 tempMap.put(s, joinInfo);
347 }
348
349 } else {
350
351 if (joinInfo.hasTargetColumnName(simpleName)) {
352 tempMap.put(tableAlias + '.' + simpleName,
353 joinInfo);
354 }
355 }
356 }
357 }
358 }
359
360 if (tempMap != result) {
361
362 for (String s : tempMap.keySet()) {
363 result.remove(s);
364 }
365 }
366 }
367
368 return result;
369 }
370
371 private Set<JoinInfo> appendSelectFrom(StringBuilder textBuilder,
372 Map<String, JoinInfo> propertyJoinMap) {
373
374 Set<JoinInfo> joinInfoSet = new LinkedHashSet<JoinInfo>();
375
376 for (JoinInfo joinInfo : propertyJoinMap.values()) {
377
378 if (joinInfo != null) {
379 joinInfoSet.add(joinInfo);
380 }
381 }
382
383 String tableName = entityInfo.getTableName();
384 String tablePrefix = joinInfoSet.isEmpty() ? "" : tableName + '.';
385 boolean appended = false;
386
387 for (Map.Entry<String, JoinInfo> entry : propertyJoinMap.entrySet()) {
388
389 if (!appended) {
390 textBuilder.append("SELECT ");
391 appended = true;
392
393 } else {
394 textBuilder.append(", ");
395 }
396
397 String propertyName = entry.getKey();
398 JoinInfo joinInfo = entry.getValue();
399
400 if (joinInfo != null) {
401 String simpleName = propertyName.substring(joinInfo
402 .getTargetTableAlias().length() + 1);
403 joinInfo.appendField(textBuilder, simpleName);
404
405 } else {
406 textBuilder.append(tablePrefix);
407 textBuilder.append(entityInfo.getColumnName(propertyName));
408 textBuilder.append(" AS \"");
409 textBuilder.append(propertyName);
410 textBuilder.append('"');
411 }
412 }
413
414 textBuilder.append(" FROM ");
415 textBuilder.append(tableName);
416
417 for (JoinInfo joinInfo : joinInfoSet) {
418 textBuilder.append(" ");
419 joinInfo.appendJoin(textBuilder, tableName);
420 }
421
422 return joinInfoSet;
423 }
424
425 private void appendOrderBy(StringBuilder textBuilder, String orderExpr,
426 Map<String, JoinInfo> propertyJoinMap) {
427
428 if (orderExpr != null) {
429 orderExpr = orderExpr.trim();
430
431 if (orderExpr.length() > 0) {
432 String[] orders = orderExpr.split(",");
433 boolean appended = false;
434
435 for (int i = 0; i < orders.length; i++) {
436 String[] ss = orders[i].trim().split("\\s+");
437 String propertyName = ss[0];
438
439 if (propertyJoinMap.containsKey(propertyName)) {
440
441 if (!appended) {
442 textBuilder.append(" ORDER BY ");
443 appended = true;
444
445 } else {
446 textBuilder.append(", ");
447 }
448
449 textBuilder.append('"');
450 textBuilder.append(propertyName);
451 textBuilder.append('"');
452
453 if (ss.length > 1) {
454
455 if (directionPattern.matcher(ss[1].toUpperCase())
456 .matches()) {
457
458 textBuilder.append(' ');
459 textBuilder.append(ss[1]);
460 }
461 }
462 }
463 }
464 }
465 }
466 }
467
468 private List<Object> appendUpdateSet(StringBuilder textBuilder,
469 Set<String> propertyNameSet, Object entity) {
470
471 List<Object> paramList = new ArrayList<Object>();
472 boolean appended = false;
473
474 for (String propertyName : propertyNameSet) {
475 Object param = PropertyUtils.getSimplePropertyValue(entity,
476 propertyName);
477 paramList.add(param);
478
479 if (!appended) {
480 textBuilder.append("UPDATE ");
481 textBuilder.append(entityInfo.getTableName());
482 textBuilder.append(" SET ");
483 appended = true;
484
485 } else {
486 textBuilder.append(", ");
487 }
488
489 textBuilder.append(entityInfo.getColumnName(propertyName));
490 textBuilder.append(" = ?");
491 }
492
493 return paramList;
494 }
495
496 private void appendWhereExamples(StringBuilder textBuilder,
497 Object[] examples, boolean joined, List<Object> paramList) {
498
499 boolean appended = false;
500
501 for (Object example : examples) {
502
503 if (!appended) {
504 textBuilder.append(" WHERE ");
505
506 if (examples.length > 1) {
507 textBuilder.append('(');
508 }
509
510 appended = true;
511
512 } else {
513 textBuilder.append(") OR (");
514 }
515
516 appendWhereExample(textBuilder, example, joined, paramList);
517 }
518
519 if (examples.length > 1) {
520 textBuilder.append(')');
521 }
522 }
523
524 private void appendWhereExample(StringBuilder textBuilder, Object example,
525 boolean joined, List<Object> paramList) {
526
527 boolean appended = false;
528
529 for (String propertyName : entityInfo.getPropertyNames()) {
530 Object param = PropertyUtils.getSimplePropertyValue(example,
531 propertyName);
532
533 if (param != null) {
534 paramList.add(param);
535
536 if (!appended) {
537 appended = true;
538
539 } else {
540 textBuilder.append(" AND ");
541 }
542
543 if (joined) {
544 textBuilder.append(entityInfo.getTableName());
545 textBuilder.append('.');
546 }
547
548 textBuilder.append(entityInfo.getColumnName(propertyName));
549 textBuilder.append(" = ?");
550 }
551 }
552 }
553
554 private void appendWhereIds(StringBuilder textBuilder, Object[] ids,
555 List<Object> paramList) {
556
557 textBuilder.append(" WHERE ");
558 textBuilder.append(entityInfo.getIdColumnName());
559
560 if (ids.length == 1) {
561 textBuilder.append(" = ?");
562 paramList.add(ids[0]);
563
564 } else {
565 boolean appended = false;
566
567 for (Object id : ids) {
568 paramList.add(id);
569
570 if (!appended) {
571 textBuilder.append(" IN (");
572 appended = true;
573
574 } else {
575 textBuilder.append(", ");
576 }
577
578 textBuilder.append('?');
579 }
580
581 textBuilder.append(')');
582 }
583 }
584 }