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 }