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 }