1 package jimm.datavision.source.sql; 2 import jimm.datavision.*; 3 import jimm.datavision.source.*; 4 import jimm.util.StringUtils; 5 import java.util.*; 6 import java.sql.PreparedStatement ; 7 import java.sql.SQLException ; 8 9 16 public class SQLQuery extends Query { 17 18 protected Set tables; 19 protected ArrayList preparedStmtValues; 20 21 26 public SQLQuery(Report report) { 27 super(report); 28 tables = new HashSet(); 29 } 30 31 42 protected String getWhereClauseForPreparedStatement() { 43 if (whereClause == null) 44 return null; 45 return prepare(whereClause); 46 } 47 48 57 public String prepare(String clause) { 58 if (clause == null || clause.indexOf("{") == -1) 59 return clause; 60 61 StringBuffer buf = new StringBuffer (); 62 63 int pos, endPos; 64 for (pos = 0, endPos = -1; 65 (pos = clause.indexOf("{", endPos + 1)) >= 0; 66 pos = endPos + 1) 67 { 68 int oldEndPos = endPos; 69 endPos = clause.indexOf("}", pos); 70 if (endPos == -1) { 71 buf.append(clause.substring(pos)); 72 break; 73 } 74 75 switch (clause.charAt(pos + 1)) { 76 case '@': String idAsString = clause.substring(pos + 2, endPos); 78 preparedStmtValues.add(report.findFormula(idAsString).eval()); 79 80 buf.append(clause.substring(oldEndPos + 1, pos)); 81 buf.append(" ? "); 82 break; 83 case '?': idAsString = clause.substring(pos + 2, endPos); 85 86 ParserHelper ph = new ParserHelper(clause, pos); 88 89 buf.append(clause.substring(oldEndPos + 1, 91 ph.getEndBeforeToken())); 92 93 addParameter(buf, ph.getPrevToken(), idAsString); 95 break; 96 default: buf.append(clause.substring(oldEndPos + 1, pos)); 98 buf.append(' '); 99 buf.append(quoted(clause.substring(pos + 1, endPos))); 100 buf.append(' '); 101 break; 102 } 103 pos = endPos + 1; 104 } 105 106 if ((endPos + 1) < clause.length()) 107 buf.append(clause.substring(endPos + 1)); 108 109 return buf.toString(); 110 } 111 112 126 protected void addParameter(StringBuffer buf, String prevWord, 127 String idAsString) 128 { 129 String word = null; 130 Long paramId = new Long (idAsString); 131 Parameter param = report.findParameter(paramId); 132 133 Object val = report.getParameterValue(paramId); 136 if (val instanceof List) { 137 List list = (List)val; 138 if (param.getArity() == Parameter.ARITY_RANGE) { 139 if ("!=".equals(prevWord) || "<>".equals(prevWord)) 141 buf.append(" not between "); 142 else if ("=".equals(prevWord) 143 || "in".equals(prevWord.toLowerCase())) 144 buf.append(" between "); 145 else { 146 buf.append(' '); 147 buf.append(prevWord); 148 buf.append(' '); 149 } 150 151 word = "? and ?"; 152 preparedStmtValues.add(list.get(0)); 153 preparedStmtValues.add(list.get(1)); 154 } 155 else { switch (list.size()) { 157 case 0: buf.append(" is null"); 159 break; 160 case 1: if ("in".equals(prevWord) || "<>".equals(prevWord)) 162 buf.append(" = "); 163 else { 164 buf.append(' '); 165 buf.append(prevWord); 166 buf.append(' '); 167 } 168 word = "?"; 169 preparedStmtValues.add(list.get(0)); 170 break; 171 default: 172 if ("!=".equals(prevWord) || "<>".equals(prevWord)) 173 buf.append(" not in "); 174 else if ("=".equals(prevWord) 175 || "in".equals(prevWord.toLowerCase())) 176 buf.append(" in "); 177 else { 178 buf.append(' '); 179 buf.append(prevWord); 180 buf.append(' '); 181 } 182 183 StringBuffer wordBuf = new StringBuffer ("("); 184 boolean first = true; 185 int len = list.size(); 186 for (int i = 0; i < len; ++i) { 187 if (first) first = false; 188 else wordBuf.append(','); 189 wordBuf.append('?'); 190 } 191 wordBuf.append(")"); 192 word = wordBuf.toString(); 193 preparedStmtValues.addAll(list); 194 } 195 } 196 } 197 else { 198 buf.append(' '); 199 buf.append(prevWord); preparedStmtValues.add(val); 201 word =" ?"; } 203 204 buf.append(word); 205 } 206 207 218 protected void addParameterForDisplay(StringBuffer buf, String prevWord, 219 String idAsString) 220 { 221 String word = null; 222 Long paramId = new Long (idAsString); 223 Parameter param = report.findParameter(paramId); 224 String name = param.designLabel(); 225 226 switch (param.getArity()) { 229 case Parameter.ARITY_RANGE: 230 if ("!=".equals(prevWord) || "<>".equals(prevWord)) 232 buf.append(" not between "); 233 else if ("=".equals(prevWord) 234 || "in".equals(prevWord.toLowerCase())) 235 buf.append(" between "); 236 else { 237 buf.append(' '); 238 buf.append(prevWord); 239 buf.append(' '); 240 } 241 242 word = name + " and " + name; 243 break; 244 case Parameter.ARITY_LIST_MULTIPLE: 245 if ("!=".equals(prevWord) || "<>".equals(prevWord)) 246 buf.append(" not in "); 247 else if ("=".equals(prevWord) 248 || "in".equals(prevWord.toLowerCase())) 249 buf.append(" in "); 250 else { 251 buf.append(' '); 252 buf.append(prevWord); 253 buf.append(' '); 254 } 255 256 word = "(" + name + ")"; 257 break; 258 default: 259 buf.append(' '); 260 buf.append(prevWord); word = " " + name; 262 break; 263 } 264 265 buf.append(word); 266 } 267 268 272 public void findSelectablesUsed() { 273 super.findSelectablesUsed(); 274 tables.clear(); 275 for (Iterator iter = selectables.iterator(); iter.hasNext(); ) 276 addTable(((Selectable)iter.next()).getTable()); 277 278 for (Iterator iter = joins.iterator(); iter.hasNext(); ) { 280 Join join = (Join)iter.next(); 281 addTable(((Column)join.getFrom()).getTable()); 282 addTable(((Column)join.getTo()).getTable()); 283 } 284 285 for (Iterator iter = report.subreports(); iter.hasNext(); ) { 287 Subreport sub = (Subreport)iter.next(); 288 for (Iterator subIter = sub.parentColumns(); subIter.hasNext(); ) { 289 addTable(((Column)subIter.next()).getTable()); 292 } 293 } 294 } 295 296 305 protected void addTable(Table t) { 306 if (t == null) 307 return; 308 309 String tableName = t.getName(); 311 for (Iterator iter = tables.iterator(); iter.hasNext(); ) 312 if (((Table)iter.next()).getName().equals(tableName)) 313 return; 315 tables.add(t); 316 } 317 325 public int getNumTables() { return tables.size(); } 326 327 332 public Collection getTablesUsed() { 333 findSelectablesUsed(); 334 return tables; 335 } 336 337 349 protected String getWhereClauseForDisplay() { 350 if (whereClause == null) 351 return null; 352 if (whereClause.indexOf("{") == -1) 353 return whereClause; 354 355 StringBuffer buf = new StringBuffer (); 356 357 int pos, endPos; 358 for (pos = 0, endPos = -1; 359 (pos = whereClause.indexOf("{", endPos + 1)) >= 0; 360 pos = endPos + 1) 361 { 362 int oldEndPos = endPos; 363 endPos = whereClause.indexOf("}", pos); 364 if (endPos == -1) { 365 buf.append(whereClause.substring(pos)); 366 break; 367 } 368 369 switch (whereClause.charAt(pos + 1)) { 370 case '@': String idAsString = whereClause.substring(pos + 2, endPos); 372 373 buf.append(whereClause.substring(oldEndPos + 1, pos)); 374 buf.append(" {@"); 375 buf.append(report.findFormula(idAsString).getName()); 376 buf.append("} "); 377 break; 378 case '?': idAsString = whereClause.substring(pos + 2, endPos); 380 381 ParserHelper ph = new ParserHelper(whereClause, pos); 383 384 buf.append(whereClause.substring(oldEndPos + 1, 386 ph.getEndBeforeToken())); 387 388 addParameterForDisplay(buf, ph.getPrevToken(), idAsString); 390 break; 391 default: buf.append(whereClause.substring(oldEndPos + 1, pos)); 393 buf.append(' '); 394 buf.append(quoted(whereClause.substring(pos + 1, endPos))); 395 buf.append(' '); 396 break; 397 } 398 pos = endPos + 1; 399 } 400 401 if ((endPos + 1) < whereClause.length()) 402 buf.append(whereClause.substring(endPos + 1)); 403 404 return buf.toString(); 405 } 406 407 413 public String toString() { 414 return queryAsString(true); 415 } 416 417 423 public String toPreparedStatementString() { 424 preparedStmtValues = new ArrayList(); 425 return queryAsString(false); 426 } 427 428 436 protected String queryAsString(boolean forDisplay) { 437 findSelectablesUsed(); 441 442 if (tables.size() == 0 || selectables.size() == 0) 443 return ""; 444 445 StringBuffer str = new StringBuffer (); 446 buildSelect(str); 447 buildFrom(str); 448 buildWhereClause(str, forDisplay); 449 buildOrderBy(str); 450 return str.toString(); 451 } 452 453 protected void buildSelect(StringBuffer str) { 454 str.append("select "); 455 456 ArrayList selectCols = new ArrayList(); 458 for (Iterator iter = selectables.iterator(); iter.hasNext(); ) { 459 String sel = ((Selectable)iter.next()).getSelectString(this); 460 if (sel != null) 461 selectCols.add(sel); 462 } 463 str.append(StringUtils.join(selectCols, ", ")); 464 } 465 466 protected void buildFrom(StringBuffer str) { 467 str.append(" from "); 468 boolean first = true; 469 for (Iterator iter = tables.iterator(); iter.hasNext(); ) { 470 if (first) first = false; 471 else str.append(", "); 472 str.append(quoted(((Table)iter.next()).getName())); 473 } 474 } 475 476 protected void buildWhereClause(StringBuffer str, boolean forDisplay) { 477 if (joins.isEmpty() && (whereClause == null || whereClause.length() == 0)) 478 return; 479 480 str.append(" where "); 481 if (!joins.isEmpty()) 482 buildJoins(str); 483 if (whereClause != null && whereClause.length() > 0) { 484 if (!joins.isEmpty()) 485 str.append(" and "); 486 buildUserWhereClause(str, forDisplay); 487 } 488 } 489 490 protected void buildJoins(StringBuffer str) { 491 ArrayList quotedJoins = new ArrayList(); 492 for (Iterator iter = joins.iterator(); iter.hasNext(); ) { 493 Join j = (Join)iter.next(); 494 StringBuffer buf = new StringBuffer (); 495 buf.append(quoted(((Column)j.getFrom()).fullName())); 496 buf.append(' '); 497 buf.append(j.getRelation()); 498 buf.append(' '); 499 buf.append(quoted(((Column)j.getTo()).fullName())); 500 quotedJoins.add(buf.toString()); 501 } 502 503 str.append("("); 504 str.append(StringUtils.join(quotedJoins, ") and (")); 505 str.append(")"); 506 } 507 508 protected void buildUserWhereClause(StringBuffer str, boolean forDisplay) { 509 str.append("("); 510 if (forDisplay) 511 str.append(getWhereClauseForDisplay()); 512 else { 513 str.append(getWhereClauseForPreparedStatement()); 516 } 517 str.append(")"); 518 } 519 520 protected void buildOrderBy(StringBuffer str) { 521 if (report.hasGroups() || !sortSelectables.isEmpty()) { 522 str.append(" order by "); 523 ArrayList orders = new ArrayList(); 524 for (Iterator iter = report.groups(); iter.hasNext(); ) { 525 Group g = (Group)iter.next(); 526 StringBuffer buf = new StringBuffer (g.getSortString(this)); 527 528 switch (g.getSortOrder()) { 529 case Group.SORT_ASCENDING: buf.append(" asc"); break; 530 case Group.SORT_DESCENDING: buf.append(" desc"); break; 531 } 532 orders.add(buf.toString()); 533 } 534 for (Iterator iter = sortedSelectables(); iter.hasNext(); ) { 535 Selectable s = (Selectable)iter.next(); 536 String order = sortOrderOf(s) == Query.SORT_DESCENDING 537 ? "desc" : "asc"; 538 orders.add(s.getSortString(this) + ' ' + order); 539 } 540 541 str.append(StringUtils.join(orders, ", ")); 542 } 543 } 544 545 552 public void setParameters(PreparedStatement stmt) throws SQLException { 553 int i = 1; 554 for (Iterator iter = preparedStmtValues.iterator(); iter.hasNext(); ++i) { 555 Object val = iter.next(); 558 if (val instanceof java.util.Date ) 559 stmt.setDate(i, 560 new java.sql.Date (((java.util.Date )val).getTime())); 561 else 562 stmt.setObject(i, val); 563 } 564 } 565 566 576 public String quoted(String name) { 577 Database db = (Database)report.getDataSource(); 578 579 List components = StringUtils.split(name, "."); 580 int len = components.size(); 581 for (int i = 0; i < len; ++i) { 582 String component = (String )components.get(i); 583 if (component.indexOf(" ") >= 0 || (report.caseSensitiveDatabaseNames() && ((db.storesLowerCaseIdentifiers() 595 && !component.equals(component.toLowerCase())) 596 || (db.storesUpperCaseIdentifiers() 597 && !component.equals(component.toUpperCase()))) 598 ) 599 ) 600 components.set(i, "\"" + component + "\""); 601 } 602 return StringUtils.join(components, "."); 603 } 604 605 } 606 | Popular Tags |