View Javadoc

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 }