KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > jimm > datavision > source > sql > SQLQuery


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 JavaDoc;
7 import java.sql.SQLException JavaDoc;
8
9 /**
10  * Queries build SQL query strings. They contain tables, joins, and
11  * where clauses.
12  *
13  * @author Jim Menard, <a HREF="mailto:jimm@io.com">jimm@io.com</a>
14  * @see ParserHelper
15  */

16 public class SQLQuery extends Query {
17
18 protected Set tables;
19 protected ArrayList preparedStmtValues;
20
21 /**
22  * Constructor.
23  *
24  * @param report the report for which this query will generate SQL
25  */

26 public SQLQuery(Report report) {
27     super(report);
28     tables = new HashSet();
29 }
30
31 /**
32  * Returns the where clause string; may be <code>null</code>. If there
33  * are any parameter values, we return '?' in their place and save the
34  * values for later use.
35  * <p>
36  * This code may also modify the clause. For example, a parameter can
37  * change the previous comparison operator ("=", "is") based on its arity.
38  *
39  * @return the where clause string; may be <code>null</code>
40  * @see #getWhereClause
41  */

42 protected String JavaDoc getWhereClauseForPreparedStatement() {
43    if (whereClause == null)
44     return null;
45     return prepare(whereClause);
46 }
47
48 /**
49  * Given a clause (really any string), replace all formulas and parameters
50  * with their values. Anything else in curly braces must be a column; we
51  * remove the curly braces and quote the name.
52  * <p>
53  * Implementation note: we can't use <code>StringUtils.replaceDelimited</code>
54  * because we modify the symbol that appears <i>before</i> some of the
55  * delimited items.
56  */

57 public String JavaDoc prepare(String JavaDoc clause) {
58     if (clause == null || clause.indexOf("{") == -1)
59     return clause;
60
61     StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
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 '@': // Formula
77
String JavaDoc 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 '?': // Parameter
84
idAsString = clause.substring(pos + 2, endPos);
85
86         // Find previous word
87
ParserHelper ph = new ParserHelper(clause, pos);
88
89         // Append prev text without previous token
90
buf.append(clause.substring(oldEndPos + 1,
91                     ph.getEndBeforeToken()));
92
93         // Append possibly modified previous token and parameter
94
addParameter(buf, ph.getPrevToken(), idAsString);
95         break;
96     default: // Column field; remove curlies and quote value
97
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 /**
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  * @return a new "previous word"
125  */

126 protected void addParameter(StringBuffer JavaDoc buf, String JavaDoc prevWord,
127                 String JavaDoc idAsString)
128 {
129     String JavaDoc word = null;
130     Long JavaDoc paramId = new Long JavaDoc(idAsString);
131     Parameter param = report.findParameter(paramId);
132
133     // Ask report for parameter value so the report can ask the user to
134
// fill in the parameter's value.
135
Object JavaDoc val = report.getParameterValue(paramId);
136     if (val instanceof List) {
137     List list = (List)val;
138     if (param.getArity() == Parameter.ARITY_RANGE) {
139         // Modify prev word
140
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 { // Build "(a,b,c)" list
156
switch (list.size()) {
157         case 0: // No items in list; "is null"
158
buf.append(" is null");
159         break;
160         case 1: // One item in list; simple equality
161
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 JavaDoc wordBuf = new StringBuffer JavaDoc("(");
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); // Previous word
200
preparedStmtValues.add(val);
201     word =" ?"; // For prepared statement
202
}
203
204     buf.append(word);
205 }
206
207 /**
208  * Given a parameter id string, add it and a possible modified previous
209  * word to <var>buf</var>. Does not modify <var>preparedStmtValues</var>
210  * list.
211  *
212  * @param buf a string buffer containing the SQL query so far
213  * @param prevWord the previous word
214  * @param idAsString the parameter id
215  * @return a new "previous word"
216  * @see #addParameter
217  */

218 protected void addParameterForDisplay(StringBuffer JavaDoc buf, String JavaDoc prevWord,
219                       String JavaDoc idAsString)
220 {
221     String JavaDoc word = null;
222     Long JavaDoc paramId = new Long JavaDoc(idAsString);
223     Parameter param = report.findParameter(paramId);
224     String JavaDoc name = param.designLabel();
225
226     // Ask report for parameter value so the report can ask the user to
227
// fill in the parameter's value.
228
switch (param.getArity()) {
229     case Parameter.ARITY_RANGE:
230     // Modify prev word
231
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); // Previous word
261
word = " " + name;
262     break;
263     }
264
265     buf.append(word);
266 }
267
268 /**
269  * Builds collections of the report tables and selectable fields actually used
270  * in the report.
271  */

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     // Add all tables used in joins.
279
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     // Add all selectables' tables used by subreports' joins.
286
for (Iterator iter = report.subreports(); iter.hasNext(); ) {
287     Subreport sub = (Subreport)iter.next();
288     for (Iterator subIter = sub.parentColumns(); subIter.hasNext(); ) {
289         // maybe parentColumns should use same the Table-Object as the
290
// parent report...
291
addTable(((Column)subIter.next()).getTable());
292     }
293     }
294 }
295
296 /**
297  * Adds the table <var>t</var> to <var>tables</var>, but only if <var>t</var>
298  * is not <code>null</code> and is not already in <var>tables</var>. We
299  * compare tables by name instead of value (pointer) because different table
300  * object may refer to the same table, for example if one is from the report
301  * and the other is from a subreport.
302  *
303  * @param t a Table
304  */

305 protected void addTable(Table t) {
306     if (t == null)
307     return;
308
309     // Look for the same table name
310
String JavaDoc tableName = t.getName();
311     for (Iterator iter = tables.iterator(); iter.hasNext(); )
312         if (((Table)iter.next()).getName().equals(tableName))
313             return; // Don't add if we have the same table name
314

315     tables.add(t);
316 }
317 /**
318  * Returns the number of tables in the query. Does not recalculate the
319  * columns or tables used; we assume this is being called after the query
320  * has been run, or at least after <code>findSelectablesUsed</code> has
321  * been called.
322  * <p>
323  * This method is only used for testing, so far.
324  */

325 public int getNumTables() { return tables.size(); }
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 JavaDoc getWhereClauseForDisplay() {
350    if (whereClause == null)
351     return null;
352     if (whereClause.indexOf("{") == -1)
353     return whereClause;
354
355     StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
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 '@': // Formula
371
String JavaDoc 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 '?': // Parameter
379
idAsString = whereClause.substring(pos + 2, endPos);
380
381         // Find previous word
382
ParserHelper ph = new ParserHelper(whereClause, pos);
383
384         // Append prev text without previous token
385
buf.append(whereClause.substring(oldEndPos + 1,
386                          ph.getEndBeforeToken()));
387
388         // Append possibly modified previous token and parameter
389
addParameterForDisplay(buf, ph.getPrevToken(), 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.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 /**
408  * Returns the query as a human-readable SQL statement, including parameter,
409  * formula, and user column display strings.
410  *
411  * @return a SQL query string
412  */

413 public String JavaDoc toString() {
414     return queryAsString(true);
415 }
416
417 /**
418  * Returns the query as a SQL string suitable for building a prepared
419  * statement.
420  *
421  * @return a SQL query string
422  */

423 public String JavaDoc toPreparedStatementString() {
424     preparedStmtValues = new ArrayList();
425     return queryAsString(false);
426 }
427
428 /**
429  * Returns the query as either a human-readable SQL statement or a SQL
430  * string suitable for building a prepared statement.
431  *
432  * @param forDisplay if <code>true</code> return a human-readable string,
433  * else return a SQL string suitable for building a prepared statement
434  * @return a SQL string
435  */

436 protected String JavaDoc queryAsString(boolean forDisplay) {
437     // Rebuild collections of tables, columns, and user columns every time
438
// (not just first time) since the list of columns we want to use may
439
// have changed since last time.
440
findSelectablesUsed();
441
442     if (tables.size() == 0 || selectables.size() == 0)
443     return "";
444
445     StringBuffer JavaDoc str = new StringBuffer JavaDoc();
446     buildSelect(str);
447     buildFrom(str);
448     buildWhereClause(str, forDisplay);
449     buildOrderBy(str);
450     return str.toString();
451 }
452
453 protected void buildSelect(StringBuffer JavaDoc str) {
454     str.append("select ");
455
456     // Build list of database columns and user columns
457
ArrayList selectCols = new ArrayList();
458     for (Iterator iter = selectables.iterator(); iter.hasNext(); ) {
459     String JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc str) {
491     ArrayList quotedJoins = new ArrayList();
492     for (Iterator iter = joins.iterator(); iter.hasNext(); ) {
493     Join j = (Join)iter.next();
494     StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
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 JavaDoc str, boolean forDisplay) {
509     str.append("(");
510     if (forDisplay)
511     str.append(getWhereClauseForDisplay());
512     else {
513     // Call getWhereClauseForPreparedStatement so parameter
514
// values are substituted and saved.
515
str.append(getWhereClauseForPreparedStatement());
516     }
517     str.append(")");
518 }
519
520 protected void buildOrderBy(StringBuffer JavaDoc 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 JavaDoc buf = new StringBuffer JavaDoc(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 JavaDoc 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 /**
546  * Given a prepared statement created with the text returned by
547  * <code>toPreparedStatementString</code>, plug in all the parameter
548  * and formula values.
549  *
550  * @see #toPreparedStatementString
551  */

552 public void setParameters(PreparedStatement JavaDoc stmt) throws SQLException JavaDoc {
553     int i = 1;
554     for (Iterator iter = preparedStmtValues.iterator(); iter.hasNext(); ++i) {
555     // In Oracle, Java Dates are turned into timestamps, or something
556
// like that. This is an attempt to fix this problem.
557
Object JavaDoc val = iter.next();
558     if (val instanceof java.util.Date JavaDoc)
559         stmt.setDate(i,
560              new java.sql.Date JavaDoc(((java.util.Date JavaDoc)val).getTime()));
561     else
562         stmt.setObject(i, val);
563     }
564 }
565
566 /**
567  * Quotes those parts of a table or column name that need to be quoted.
568  * <p>
569  * Different databases and JDBC drivers treat case sensitively differently.
570  * We use the database metadata case sensitivity values to determine which
571  * parts of the name need to be quoted.
572  *
573  * @param name a table or column name
574  * @return a quoted version of the name
575  */

576 public String JavaDoc quoted(String JavaDoc 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 JavaDoc component = (String JavaDoc)components.get(i);
583     // Put quotes around the component if (a) there is a space in the
584
// component, (b) the JDBC driver translates all names to lower
585
// case and we have non-lower-case letters, or (c) the JDBC driver
586
// translates all names to upper case and we have non-upper-case
587
// letters.
588
//
589
// The database has a method that lets us know if the user wants
590
// to skip quoting. We always quote fields with spaces in the name,
591
// though.
592
if (component.indexOf(" ") >= 0 // Always quote if spaces
593
|| (report.caseSensitiveDatabaseNames() // Don't quote unless asked
594
&& ((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