001: package jimm.datavision.source.sql;
002:
003: import jimm.datavision.*;
004: import jimm.datavision.source.*;
005: import jimm.util.StringUtils;
006: import java.util.*;
007: import java.sql.PreparedStatement;
008: import java.sql.SQLException;
009:
010: /**
011: * Queries build SQL query strings. They contain tables, joins, and
012: * where clauses.
013: *
014: * @author Jim Menard, <a href="mailto:jimm@io.com">jimm@io.com</a>
015: * @see ParserHelper
016: */
017: public class SQLQuery extends Query {
018:
019: protected Set tables;
020: protected ArrayList preparedStmtValues;
021:
022: /**
023: * Constructor.
024: *
025: * @param report the report for which this query will generate SQL
026: */
027: public SQLQuery(Report report) {
028: super (report);
029: tables = new HashSet();
030: }
031:
032: /**
033: * Returns the where clause string; may be <code>null</code>. If there
034: * are any parameter values, we return '?' in their place and save the
035: * values for later use.
036: * <p>
037: * This code may also modify the clause. For example, a parameter can
038: * change the previous comparison operator ("=", "is") based on its arity.
039: *
040: * @return the where clause string; may be <code>null</code>
041: * @see #getWhereClause
042: */
043: protected String getWhereClauseForPreparedStatement() {
044: if (whereClause == null)
045: return null;
046: return prepare(whereClause);
047: }
048:
049: /**
050: * Given a clause (really any string), replace all formulas and parameters
051: * with their values. Anything else in curly braces must be a column; we
052: * remove the curly braces and quote the name.
053: * <p>
054: * Implementation note: we can't use <code>StringUtils.replaceDelimited</code>
055: * because we modify the symbol that appears <i>before</i> some of the
056: * delimited items.
057: */
058: public String prepare(String clause) {
059: if (clause == null || clause.indexOf("{") == -1)
060: return clause;
061:
062: StringBuffer buf = new StringBuffer();
063:
064: int pos, endPos;
065: for (pos = 0, endPos = -1; (pos = clause.indexOf("{",
066: endPos + 1)) >= 0; pos = endPos + 1) {
067: int oldEndPos = endPos;
068: endPos = clause.indexOf("}", pos);
069: if (endPos == -1) {
070: buf.append(clause.substring(pos));
071: break;
072: }
073:
074: switch (clause.charAt(pos + 1)) {
075: case '@': // Formula
076: String idAsString = clause.substring(pos + 2, endPos);
077: preparedStmtValues.add(report.findFormula(idAsString)
078: .eval());
079:
080: buf.append(clause.substring(oldEndPos + 1, pos));
081: buf.append(" ? ");
082: break;
083: case '?': // Parameter
084: idAsString = clause.substring(pos + 2, endPos);
085:
086: // Find previous word
087: ParserHelper ph = new ParserHelper(clause, pos);
088:
089: // Append prev text without previous token
090: buf.append(clause.substring(oldEndPos + 1, ph
091: .getEndBeforeToken()));
092:
093: // Append possibly modified previous token and parameter
094: addParameter(buf, ph.getPrevToken(), idAsString);
095: break;
096: default: // Column field; remove curlies and quote value
097: buf.append(clause.substring(oldEndPos + 1, pos));
098: buf.append(' ');
099: 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: /**
113: * Given a parameter id string, add its value(s) to the parameter list
114: * and add prepared statement placeholders in the query buffer. Appends
115: * the previous word to the buffer. The previous word may be modified
116: * if the circumstances call for it. For example, we want to turn
117: * "foo in {?Range Parameter}" into "foo between ? and ?". The value
118: * of <var>prevWord</var> here would be "in". We would append "between"
119: * to the buffer and return the "word" "? and ".
120: *
121: * @param buf a string buffer containing the SQL query so far
122: * @param prevWord the previous word
123: * @param idAsString the parameter id
124: */
125: protected void addParameter(StringBuffer buf, String prevWord,
126: String idAsString) {
127: String word = null;
128: Long paramId = new Long(idAsString);
129: Parameter param = report.findParameter(paramId);
130:
131: // Ask report for parameter value so the report can ask the user to
132: // fill in the parameter's value.
133: Object val = report.getParameterValue(paramId);
134: if (val instanceof List) {
135: List list = (List) val;
136: if (param.getArity() == Parameter.ARITY_RANGE) {
137: // Modify prev word
138: if ("!=".equals(prevWord) || "<>".equals(prevWord))
139: buf.append(" not between ");
140: else if ("=".equals(prevWord)
141: || "in".equals(prevWord.toLowerCase()))
142: buf.append(" between ");
143: else {
144: buf.append(' ');
145: buf.append(prevWord);
146: buf.append(' ');
147: }
148:
149: word = "? and ?";
150: preparedStmtValues.add(list.get(0));
151: preparedStmtValues.add(list.get(1));
152: } else { // Build "(a,b,c)" list
153: switch (list.size()) {
154: case 0: // No items in list; "is null"
155: buf.append(" is null");
156: break;
157: case 1: // One item in list; simple equality
158: if ("in".equals(prevWord) || "<>".equals(prevWord))
159: buf.append(" = ");
160: else {
161: buf.append(' ');
162: buf.append(prevWord);
163: buf.append(' ');
164: }
165: word = "?";
166: preparedStmtValues.add(list.get(0));
167: break;
168: default:
169: if ("!=".equals(prevWord) || "<>".equals(prevWord))
170: buf.append(" not in ");
171: else if ("=".equals(prevWord)
172: || "in".equals(prevWord.toLowerCase()))
173: buf.append(" in ");
174: else {
175: buf.append(' ');
176: buf.append(prevWord);
177: buf.append(' ');
178: }
179:
180: StringBuffer wordBuf = new StringBuffer("(");
181: boolean first = true;
182: int len = list.size();
183: for (int i = 0; i < len; ++i) {
184: if (first)
185: first = false;
186: else
187: wordBuf.append(',');
188: wordBuf.append('?');
189: }
190: wordBuf.append(")");
191: word = wordBuf.toString();
192: preparedStmtValues.addAll(list);
193: }
194: }
195: } else {
196: buf.append(' ');
197: buf.append(prevWord); // Previous word
198: preparedStmtValues.add(val);
199: word = " ?"; // For prepared statement
200: }
201:
202: buf.append(word);
203: }
204:
205: /**
206: * Given a parameter id string, add it and a possible modified previous
207: * word to <var>buf</var>. Does not modify <var>preparedStmtValues</var>
208: * list.
209: *
210: * @param buf a string buffer containing the SQL query so far
211: * @param prevWord the previous word
212: * @param idAsString the parameter id
213: * @see #addParameter
214: */
215: protected void addParameterForDisplay(StringBuffer buf,
216: String prevWord, String idAsString) {
217: String word = null;
218: Long paramId = new Long(idAsString);
219: Parameter param = report.findParameter(paramId);
220: String name = param.designLabel();
221:
222: // Ask report for parameter value so the report can ask the user to
223: // fill in the parameter's value.
224: switch (param.getArity()) {
225: case Parameter.ARITY_RANGE:
226: // Modify prev word
227: if ("!=".equals(prevWord) || "<>".equals(prevWord))
228: buf.append(" not between ");
229: else if ("=".equals(prevWord)
230: || "in".equals(prevWord.toLowerCase()))
231: buf.append(" between ");
232: else {
233: buf.append(' ');
234: buf.append(prevWord);
235: buf.append(' ');
236: }
237:
238: word = name + " and " + name;
239: break;
240: case Parameter.ARITY_LIST_MULTIPLE:
241: if ("!=".equals(prevWord) || "<>".equals(prevWord))
242: buf.append(" not in ");
243: else if ("=".equals(prevWord)
244: || "in".equals(prevWord.toLowerCase()))
245: buf.append(" in ");
246: else {
247: buf.append(' ');
248: buf.append(prevWord);
249: buf.append(' ');
250: }
251:
252: word = "(" + name + ")";
253: break;
254: default:
255: buf.append(' ');
256: buf.append(prevWord); // Previous word
257: word = " " + name;
258: break;
259: }
260:
261: buf.append(word);
262: }
263:
264: /**
265: * Builds collections of the report tables and selectable fields actually used
266: * in the report.
267: */
268: public void findSelectablesUsed() {
269: super .findSelectablesUsed();
270: tables.clear();
271: for (Iterator iter = selectables.iterator(); iter.hasNext();)
272: addTable(((Selectable) iter.next()).getTable());
273:
274: // Add all tables used in joins.
275: for (Iterator iter = joins.iterator(); iter.hasNext();) {
276: Join join = (Join) iter.next();
277: addTable(((Column) join.getFrom()).getTable());
278: addTable(((Column) join.getTo()).getTable());
279: }
280:
281: // Add all selectables' tables used by subreports' joins.
282: for (Iterator iter = report.subreports(); iter.hasNext();) {
283: Subreport sub = (Subreport) iter.next();
284: for (Iterator subIter = sub.parentColumns(); subIter
285: .hasNext();) {
286: // maybe parentColumns should use same the Table-Object as the
287: // parent report...
288: addTable(((Column) subIter.next()).getTable());
289: }
290: }
291: }
292:
293: /**
294: * Adds the table <var>t</var> to <var>tables</var>, but only if <var>t</var>
295: * is not <code>null</code> and is not already in <var>tables</var>. We
296: * compare tables by name instead of value (pointer) because different table
297: * object may refer to the same table, for example if one is from the report
298: * and the other is from a subreport.
299: *
300: * @param t a Table
301: */
302: protected void addTable(Table t) {
303: if (t == null)
304: return;
305:
306: // Look for the same table name
307: String tableName = t.getName();
308: for (Iterator iter = tables.iterator(); iter.hasNext();)
309: if (((Table) iter.next()).getName().equals(tableName))
310: return; // Don't add if we have the same table name
311:
312: tables.add(t);
313: }
314:
315: /**
316: * Returns the number of tables in the query. Does not recalculate the
317: * columns or tables used; we assume this is being called after the query
318: * has been run, or at least after <code>findSelectablesUsed</code> has
319: * been called.
320: * <p>
321: * This method is only used for testing, so far.
322: */
323: public int getNumTables() {
324: return tables.size();
325: }
326:
327: /**
328: * Returns a collection containing the tables used by this query.
329: *
330: * @return the collection of tables used by this query
331: */
332: public Collection getTablesUsed() {
333: findSelectablesUsed();
334: return tables;
335: }
336:
337: /**
338: * Returns the where clause string; may be <code>null</code>. If there are
339: * any column names contained in curly braces, we remove the curly braces.
340: * Formulas, parameters, and user colums remain as-is.
341: * <p>
342: * Implementation note: we can't use <code>StringUtils.replaceDelimited</code>
343: * because we modify the symbol that appears <i>before</i> some of the
344: * delimited items.
345: *
346: * @return the where clause string; may be <code>null</code>
347: * @see #getWhereClause
348: */
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; (pos = whereClause.indexOf("{",
359: endPos + 1)) >= 0; pos = endPos + 1) {
360: int oldEndPos = endPos;
361: endPos = whereClause.indexOf("}", pos);
362: if (endPos == -1) {
363: buf.append(whereClause.substring(pos));
364: break;
365: }
366:
367: switch (whereClause.charAt(pos + 1)) {
368: case '@': // Formula
369: String idAsString = whereClause.substring(pos + 2,
370: endPos);
371:
372: buf.append(whereClause.substring(oldEndPos + 1, pos));
373: buf.append(" {@");
374: buf.append(report.findFormula(idAsString).getName());
375: buf.append("} ");
376: break;
377: case '?': // Parameter
378: idAsString = whereClause.substring(pos + 2, endPos);
379:
380: // Find previous word
381: ParserHelper ph = new ParserHelper(whereClause, pos);
382:
383: // Append prev text without previous token
384: buf.append(whereClause.substring(oldEndPos + 1, ph
385: .getEndBeforeToken()));
386:
387: // Append possibly modified previous token and parameter
388: addParameterForDisplay(buf, ph.getPrevToken(),
389: idAsString);
390: break;
391: default: // Column field; remove curlies
392: buf.append(whereClause.substring(oldEndPos + 1, pos));
393: buf.append(' ');
394: buf.append(quoted(whereClause
395: .substring(pos + 1, endPos)));
396: buf.append(' ');
397: break;
398: }
399: pos = endPos + 1;
400: }
401:
402: if ((endPos + 1) < whereClause.length())
403: buf.append(whereClause.substring(endPos + 1));
404:
405: return buf.toString();
406: }
407:
408: /**
409: * Returns the query as a human-readable SQL statement, including parameter,
410: * formula, and user column display strings.
411: *
412: * @return a SQL query string
413: */
414: public String toString() {
415: return queryAsString(true);
416: }
417:
418: /**
419: * Returns the query as a SQL string suitable for building a prepared
420: * statement.
421: *
422: * @return a SQL query string
423: */
424: public String toPreparedStatementString() {
425: preparedStmtValues = new ArrayList();
426: return queryAsString(false);
427: }
428:
429: /**
430: * Returns the query as either a human-readable SQL statement or a SQL
431: * string suitable for building a prepared statement.
432: *
433: * @param forDisplay if <code>true</code> return a human-readable string,
434: * else return a SQL string suitable for building a prepared statement
435: * @return a SQL string
436: */
437: protected String queryAsString(boolean forDisplay) {
438: // Rebuild collections of tables, columns, and user columns every time
439: // (not just first time) since the list of columns we want to use may
440: // have changed since last time.
441: findSelectablesUsed();
442:
443: if (tables.size() == 0 || selectables.size() == 0)
444: return "";
445:
446: StringBuffer str = new StringBuffer();
447: buildSelect(str);
448: buildFrom(str);
449: buildWhereClause(str, forDisplay);
450: buildOrderBy(str);
451: return str.toString();
452: }
453:
454: protected void buildSelect(StringBuffer str) {
455: str.append("select ");
456:
457: // Build list of database columns and user columns
458: ArrayList selectCols = new ArrayList();
459: for (Iterator iter = selectables.iterator(); iter.hasNext();) {
460: String sel = ((Selectable) iter.next())
461: .getSelectString(this );
462: if (sel != null)
463: selectCols.add(sel);
464: }
465: str.append(StringUtils.join(selectCols, ", "));
466: }
467:
468: protected void buildFrom(StringBuffer str) {
469: str.append(" from ");
470: boolean first = true;
471: for (Iterator iter = tables.iterator(); iter.hasNext();) {
472: if (first)
473: first = false;
474: else
475: str.append(", ");
476: str.append(quoted(((Table) iter.next()).getName()));
477: }
478: }
479:
480: protected void buildWhereClause(StringBuffer str, boolean forDisplay) {
481: if (joins.isEmpty()
482: && (whereClause == null || whereClause.length() == 0))
483: return;
484:
485: str.append(" where ");
486: if (!joins.isEmpty())
487: buildJoins(str);
488: if (whereClause != null && whereClause.length() > 0) {
489: if (!joins.isEmpty())
490: str.append(" and ");
491: buildUserWhereClause(str, forDisplay);
492: }
493: }
494:
495: protected void buildJoins(StringBuffer str) {
496: ArrayList quotedJoins = new ArrayList();
497: for (Iterator iter = joins.iterator(); iter.hasNext();) {
498: Join j = (Join) iter.next();
499: StringBuffer buf = new StringBuffer();
500: buf.append(quoted(((Column) j.getFrom()).fullName()));
501: buf.append(' ');
502: buf.append(j.getRelation());
503: buf.append(' ');
504: buf.append(quoted(((Column) j.getTo()).fullName()));
505: quotedJoins.add(buf.toString());
506: }
507:
508: str.append("(");
509: str.append(StringUtils.join(quotedJoins, ") and ("));
510: str.append(")");
511: }
512:
513: protected void buildUserWhereClause(StringBuffer str,
514: boolean forDisplay) {
515: str.append("(");
516: if (forDisplay)
517: str.append(getWhereClauseForDisplay());
518: else {
519: // Call getWhereClauseForPreparedStatement so parameter
520: // values are substituted and saved.
521: str.append(getWhereClauseForPreparedStatement());
522: }
523: str.append(")");
524: }
525:
526: protected void buildOrderBy(StringBuffer str) {
527: if (report.hasGroups() || !sortSelectables.isEmpty()) {
528: str.append(" order by ");
529: ArrayList orders = new ArrayList();
530: for (Iterator iter = report.groups(); iter.hasNext();) {
531: Group g = (Group) iter.next();
532: StringBuffer buf = new StringBuffer(g.getSelectable()
533: .getSortString(this ));
534: buf.append(' ');
535: buf
536: .append(g.getSortOrder() == Group.SORT_DESCENDING ? "desc"
537: : "asc");
538: orders.add(buf.toString());
539: }
540: for (Iterator iter = sortedSelectables(); iter.hasNext();) {
541: Selectable s = (Selectable) iter.next();
542: StringBuffer buf = new StringBuffer(s
543: .getSortString(this ));
544: buf.append(' ');
545: buf
546: .append(sortOrderOf(s) == Query.SORT_DESCENDING ? "desc"
547: : "asc");
548: }
549:
550: str.append(StringUtils.join(orders, ", "));
551: }
552: }
553:
554: /**
555: * Given a prepared statement created with the text returned by
556: * <code>toPreparedStatementString</code>, plug in all the parameter
557: * and formula values.
558: *
559: * @see #toPreparedStatementString
560: */
561: public void setParameters(PreparedStatement stmt)
562: throws SQLException {
563: int i = 1;
564: for (Iterator iter = preparedStmtValues.iterator(); iter
565: .hasNext(); ++i) {
566: // In Oracle, Java Dates are turned into timestamps, or something
567: // like that. This is an attempt to fix this problem.
568: Object val = iter.next();
569: if (val instanceof java.util.Date)
570: stmt.setDate(i, new java.sql.Date(
571: ((java.util.Date) val).getTime()));
572: else
573: stmt.setObject(i, val);
574: }
575: }
576:
577: /**
578: * Quotes those parts of a table or column name that need to be quoted.
579: * <p>
580: * Different databases and JDBC drivers treat case sensitively differently.
581: * We use the database metadata case sensitivity values to determine which
582: * parts of the name need to be quoted.
583: *
584: * @param name a table or column name
585: * @return a quoted version of the name
586: */
587: public String quoted(String name) {
588: Database db = (Database) report.getDataSource();
589:
590: List components = StringUtils.split(name, ".");
591: int len = components.size();
592: for (int i = 0; i < len; ++i) {
593: String component = (String) components.get(i);
594: // Put quotes around the component if (a) there is a space in the
595: // component, (b) the JDBC driver translates all names to lower
596: // case and we have non-lower-case letters, or (c) the JDBC driver
597: // translates all names to upper case and we have non-upper-case
598: // letters.
599: //
600: // The database has a method that lets us know if the user wants
601: // to skip quoting. We always quote fields with spaces in the name,
602: // though.
603: if (component.indexOf(" ") >= 0 // Always quote if spaces
604: || (report.caseSensitiveDatabaseNames() // Don't quote unless asked
605: && ((db.storesLowerCaseIdentifiers() && !component
606: .equals(component.toLowerCase())) || (db
607: .storesUpperCaseIdentifiers() && !component
608: .equals(component.toUpperCase())))))
609: components.set(i, "\"" + component + "\"");
610: }
611: return StringUtils.join(components, ".");
612: }
613:
614: }
|