001: /*
002: $Header: /cvsroot/xorm/xorm/src/org/xorm/datastore/sql/SQLQuery.java,v 1.44 2004/05/19 20:00:32 wbiggs Exp $
003:
004: This file is part of XORM.
005:
006: XORM is free software; you can redistribute it and/or modify
007: it under the terms of the GNU General Public License as published by
008: the Free Software Foundation; either version 2 of the License, or
009: (at your option) any later version.
010:
011: XORM is distributed in the hope that it will be useful,
012: but WITHOUT ANY WARRANTY; without even the implied warranty of
013: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: GNU General Public License for more details.
015:
016: You should have received a copy of the GNU General Public License
017: along with XORM; if not, write to the Free Software
018: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020: package org.xorm.datastore.sql;
021:
022: import java.sql.Timestamp;
023: import java.util.ArrayList;
024: import java.util.Collection;
025: import java.util.Collections;
026: import java.util.Date;
027: import java.util.Iterator;
028: import java.util.LinkedHashSet;
029: import java.util.List;
030: import java.util.Set;
031:
032: import org.xorm.datastore.Table;
033: import org.xorm.datastore.Column;
034:
035: import org.xorm.query.Operator;
036: import org.xorm.query.Condition;
037: import org.xorm.query.SimpleCondition;
038: import org.xorm.query.CompoundCondition;
039: import org.xorm.query.AndCondition;
040: import org.xorm.query.OrCondition;
041: import org.xorm.query.RawCondition;
042: import org.xorm.query.Selector;
043:
044: import org.xorm.ObjectId;
045:
046: /**
047: * Converts a selector tree to a SQL statement.
048: */
049: public class SQLQuery {
050: public static class Alias {
051: private String name;
052: private Table table;
053: private Column joinColumn;
054: private Set fetchColumns;
055:
056: public Alias(String name, Table table, Set fetchColumns,
057: Column joinColumn) {
058: this .name = name;
059: this .table = table;
060: this .fetchColumns = fetchColumns;
061: this .joinColumn = joinColumn;
062: }
063:
064: public String getName() {
065: return name;
066: }
067:
068: public Table getTable() {
069: return table;
070: }
071:
072: public Column getJoinColumn() {
073: return joinColumn;
074: }
075:
076: public Set getFetchColumns() {
077: return (fetchColumns == null) ? Collections.EMPTY_SET
078: : fetchColumns;
079: }
080: }
081:
082: private Selector selector;
083: private int aliasIndex = 0;
084: private Set aliases = new LinkedHashSet();
085: private StringBuffer fromClause = new StringBuffer();
086: private StringBuffer whereClause = new StringBuffer();
087: private StringBuffer orderingClause = new StringBuffer();
088: private boolean distinct;
089: private List parameters = new ArrayList();
090:
091: public SQLQuery(Selector selector) {
092: this .selector = selector;
093: Table table = selector.getTable();
094: Alias alias = makeAlias(selector);
095: parse(alias, selector);
096: }
097:
098: public Table getTargetTable() {
099: return selector.getTable();
100: }
101:
102: public Set getAliases() {
103: return aliases;
104: }
105:
106: public List getParameters() {
107: return parameters;
108: }
109:
110: private Alias makeAlias(Selector selector) {
111: String aliasName;
112: if (selector.getCondition() instanceof RawCondition) {
113: aliasName = selector.getTable().getName();
114: } else {
115: aliasName = "T" + aliasIndex++;
116: }
117: Alias alias = new Alias(aliasName, selector.getTable(),
118: selector.getFetchColumns(), selector.getJoinColumn());
119: aliases.add(alias);
120: return alias;
121: }
122:
123: private void parse(Alias alias, Selector selector) {
124:
125: if (!selector.isOuterJoin()) {
126: if (fromClause.length() > 0) {
127: fromClause.append(", ");
128: }
129: fromClause.append(alias.getTable().getName()).append(" ")
130: .append(alias.getName());
131: Condition condition = selector.getCondition();
132: if (condition != null) {
133:
134: // Having to explicitly check this here is a bit kludgy
135: boolean outerJoin = false;
136: if (condition instanceof SimpleCondition) {
137: Object operand = ((SimpleCondition) condition)
138: .getValue();
139: if (operand instanceof Selector) {
140: Selector next = (Selector) operand;
141: outerJoin = next.isOuterJoin();
142: }
143: }
144:
145: if (!outerJoin && (whereClause.length() > 0)) {
146: whereClause.append(" AND ");
147: }
148: parseBranch(alias, condition);
149: }
150: }
151:
152: parseOrdering(selector);
153: }
154:
155: /**
156: * Parses the inverted status of the condition and then passes
157: * control to parse(CompoundCondition) or
158: * parseSimple(Condition).
159: *
160: * @return true if the last clause parsed required an outer join
161: */
162: private boolean parseBranch(Alias alias, Condition condition) {
163: /*
164: The situation where condition == null arises when the
165: ordering clause attempts to traverse relationships that
166: are not otherwise part of the query. In this case we treat
167: this as on outer join. There may be an optimization possible
168: whereby if the foreign key column is not nullable, we can
169: use inner joins instead.
170: */
171: if (condition == null)
172: return true;
173: if (condition.isInverted()) {
174: whereClause.append("NOT (");
175: }
176: try {
177: if (condition instanceof CompoundCondition) {
178: parseCompound(alias, (CompoundCondition) condition);
179: return false;
180: } else if (condition instanceof SimpleCondition) {
181: return parseSimple(alias, (SimpleCondition) condition);
182: } else if (condition instanceof RawCondition) {
183: parseRaw((RawCondition) condition);
184: return false;
185: }
186: } finally {
187: if (condition.isInverted()) {
188: whereClause.append(")");
189: }
190: }
191: return false;
192: }
193:
194: /**
195: * Parses a compound condition by inserting appropriate
196: * parentheses and the "OR"/"AND" keyword between left hand
197: * and right hand sides of the compound condition.
198: * Recursively parses the left and right hand sides by calling
199: * parseBranch().
200: */
201: private void parseCompound(Alias alias, CompoundCondition condition) {
202: whereClause.append("(");
203: if (parseBranch(alias, condition.getLHS())) {
204: // LHS expression was an outer join with no where clause
205:
206: // ugly.. remove the '(' we just added
207: whereClause.deleteCharAt(whereClause.length() - 1);
208:
209: parseBranch(alias, condition.getRHS());
210: return;
211: } else {
212: if (condition instanceof OrCondition) {
213: whereClause.append(" OR ");
214: } else if (condition instanceof AndCondition) {
215: whereClause.append(" AND ");
216: }
217: }
218: parseBranch(alias, condition.getRHS());
219: whereClause.append(")");
220: }
221:
222: /** Parses the non-compound Condition. */
223: private boolean parseSimple(Alias alias, SimpleCondition condition) {
224: Column column = condition.getColumn();
225: Object operand = condition.getValue();
226: boolean wasOuter = false;
227: StringBuffer buffer = whereClause;
228: if (operand instanceof Selector) {
229: Selector next = (Selector) operand;
230: Alias nextAlias = makeAlias(next);
231: if (next.isOuterJoin()) {
232: if (next.getCondition() instanceof SimpleCondition) {
233: parseSimple(nextAlias, (SimpleCondition) next
234: .getCondition());
235: } else {
236: wasOuter = true;
237: }
238: fromClause.append(" LEFT OUTER JOIN ").append(
239: nextAlias.getTable().getName()).append(" ")
240: .append(nextAlias.getName()).append(" ON ");
241: buffer = fromClause;
242: } else {
243: buffer.append("(");
244: }
245: buffer.append(alias.getName()).append(".").append(
246: column.getName()).append(" = ");
247:
248: // In the usual case this should be nextAlias.primaryKey,
249: // but in the case of CONTAINS it should be nextAlias.foreignKey.
250: buffer.append(nextAlias.getName()).append(".").append(
251: nextAlias.getJoinColumn().getName());
252:
253: // Because Many-to-Many operations can cause multiple
254: // copies, set the distinct flag.
255: if (condition.getOperator() == Operator.CONTAINS) {
256: distinct = true;
257: }
258:
259: if (!next.isOuterJoin()) {
260: parse(nextAlias, next);
261: buffer.append(")");
262: }
263: } else {
264: whereClause.append("(");
265: whereClause.append(alias.getName()).append(".").append(
266: column.getName());
267: parseOperand(condition.getOperator(), operand);
268: whereClause.append(")");
269: }
270: return wasOuter;
271: }
272:
273: private void parseRaw(RawCondition condition) {
274: distinct = true;
275: aliases.clear(); // Raw condition won't use aliases
276: fromClause = new StringBuffer();
277:
278: Iterator i = condition.getTables().iterator();
279: boolean first = true;
280: while (i.hasNext()) {
281: Table t = (Table) i.next();
282: Alias alias;
283: if (first) {
284: alias = new Alias(t.getName(), t, selector
285: .getFetchColumns(), t.getPrimaryKey());
286: first = false;
287: } else {
288: alias = new Alias(t.getName(), t, null, t
289: .getPrimaryKey());
290: fromClause.append(",");
291: }
292: aliases.add(alias);
293: fromClause.append(t.getName());
294: }
295: whereClause.append(condition.getRawQuery());
296: }
297:
298: /**
299: * Parses an operator/operand pair and appends them to the whereClause.
300: */
301: private void parseOperand(Operator operator, Object operand) {
302: if (operand == null) {
303: whereClause.append(" IS ");
304: if (Operator.NOT_EQUAL.equals(operator)) {
305: whereClause.append("NOT ");
306: }
307: } else {
308: appendOperator(operator, whereClause);
309: if (operand instanceof String) {
310: String strOp = operand.toString();
311: if (Operator.ENDS_WITH.equals(operator)) {
312: strOp = "%" + escapeLike(strOp);
313: } else if (Operator.STARTS_WITH.equals(operator)) {
314: strOp = escapeLike(strOp) + "%";
315: } else if (Operator.STR_CONTAINS.equals(operator)) {
316: strOp = "%" + escapeLike(strOp) + "%";
317: }
318: parameters.add(strOp);
319: operand = "?";
320: } else if (operand instanceof Date) {
321: operand = "{ts '"
322: + new Timestamp(((Date) operand).getTime())
323: + "'}";
324: } else if (operand instanceof Collection) {
325: StringBuffer sb = new StringBuffer("(");
326: // Iterate through collection
327: Iterator it = ((Collection) operand).iterator();
328: boolean seenOne = false;
329: while (it.hasNext()) {
330: Object obj = it.next();
331: if (seenOne) {
332: sb.append(", ");
333: } else {
334: seenOne = true;
335: }
336: if (obj instanceof String) {
337: obj = escapeSQLString((String) obj);
338: }
339: sb.append(obj);
340: }
341: operand = sb.append(")").toString();
342: } else {
343: parameters.add(operand);
344: operand = "?";
345: }
346: }
347: whereClause.append(operand);
348: }
349:
350: /**
351: * Appends the correct SQL for the given operator to the buffer.
352: * Does not deal with the operand itself. Therefore all of
353: * STARTS_WITH, ENDS_WITH and STR_CONTAINS append the value " LIKE ".
354: *
355: * Returns the same buffer as passed in.
356: */
357: public static StringBuffer appendOperator(Operator operator,
358: StringBuffer buffer) {
359: if (Operator.EQUAL.equals(operator)) {
360: buffer.append(" = ");
361: } else if (Operator.NOT_EQUAL.equals(operator)) {
362: buffer.append(" != ");
363: } else if (Operator.LT.equals(operator)) {
364: buffer.append(" < ");
365: } else if (Operator.GT.equals(operator)) {
366: buffer.append(" > ");
367: } else if (Operator.LTE.equals(operator)) {
368: buffer.append(" <= ");
369: } else if (Operator.GTE.equals(operator)) {
370: buffer.append(" >= ");
371: } else if (Operator.STARTS_WITH.equals(operator)
372: || Operator.ENDS_WITH.equals(operator)
373: || Operator.STR_CONTAINS.equals(operator)) {
374: buffer.append(" LIKE ");
375: } else if (Operator.IN.equals(operator)) {
376: buffer.append(" IN ");
377: }
378: return buffer;
379: }
380:
381: private static StringBuffer escapeImpl(StringBuffer b,
382: char toEscape, char escapeChar) {
383: for (int i = b.length() - 1; i >= 0; i--) {
384: if (b.charAt(i) == toEscape)
385: b.insert(i, escapeChar);
386: }
387: return b;
388: }
389:
390: /**
391: * Escapes an SQL String by quoting all single-quotes as
392: * two single-quotes. Also appends leading and trailing single
393: * quotes. For example, "Bob" becomes "'Bob'" and "Bob's" becomes
394: * "'Bob''s'".
395: */
396: public static String escapeSQLString(String operand) {
397: if (operand == null)
398: return null;
399: return "'"
400: + escapeImpl(new StringBuffer(operand), '\'', '\'')
401: .append('\'').toString();
402: }
403:
404: /**
405: * Escapes a SQL string by replacing "%" literals with "~%".
406: */
407: public static String escapeLike(String operand) {
408: if (operand == null)
409: return null;
410: return escapeImpl(new StringBuffer(operand), '%', '~')
411: .toString();
412: }
413:
414: private void parseOrdering(Selector selector) {
415: Selector.Ordering[] ordering = selector.getOrdering();
416: if (ordering == null) {
417: return;
418: }
419:
420: if (orderingClause.length() > 0) {
421: orderingClause.append(",");
422: }
423:
424: for (int i = 0; i < ordering.length; i++) {
425: if (i > 0) {
426: orderingClause.append(", ");
427: }
428: Column column = ordering[i].getColumn();
429: Alias alias = null;
430: Iterator it = aliases.iterator();
431: while (it.hasNext()) {
432: alias = (Alias) it.next();
433: if (alias.getTable() == column.getTable()) {
434: break;
435: }
436: }
437: orderingClause.append(alias.getName()).append('.').append(
438: column.getName()).append(' ');
439:
440: int order = ordering[i].getOrder();
441: switch (order) {
442: case Selector.Ordering.ASCENDING:
443: orderingClause.append("ASC");
444: break;
445: case Selector.Ordering.DESCENDING:
446: orderingClause.append("DESC");
447: break;
448: }
449: }
450: }
451:
452: /** SQL needed to run the count() query. */
453: public String toCountSQL() {
454: Alias first = (Alias) aliases.iterator().next();
455: StringBuffer sql = new StringBuffer().append("SELECT COUNT(");
456: if (distinct) {
457: sql.append("DISTINCT ");
458: sql.append(first.getName()).append('.').append(
459: first.getTable().getPrimaryKey().getName());
460: } else {
461: sql.append('*');
462: }
463: sql.append(") FROM ").append(fromClause.toString());
464:
465: if (whereClause.length() > 0) {
466: sql.append(" WHERE ").append(whereClause.toString());
467: }
468: return sql.toString();
469: }
470:
471: /**
472: * Returns the SQL generated by this query.
473: */
474: public String toSQL() {
475: StringBuffer sql = new StringBuffer().append("SELECT ");
476: if (distinct) {
477: sql.append("DISTINCT ");
478: }
479:
480: // Examine all selectors for fetchColumns
481: Iterator i = aliases.iterator();
482: boolean seenAny = false;
483: while (i.hasNext()) {
484: Alias alias = (Alias) i.next();
485: Table t = alias.getTable();
486: Set s = alias.getFetchColumns();
487: Iterator j = s.iterator();
488: while (j.hasNext()) {
489: if (seenAny) {
490: sql.append(", ");
491: } else {
492: seenAny = true;
493: }
494: Column c = (Column) j.next();
495: sql.append(alias.getName()).append(".").append(
496: c.getName());
497: }
498: }
499:
500: sql.append(" FROM ").append(fromClause.toString());
501:
502: if (whereClause.length() > 0) {
503: sql.append(" WHERE ").append(whereClause.toString());
504: }
505:
506: if (orderingClause.length() > 0) {
507: sql.append(" ORDER BY ").append(orderingClause.toString());
508: }
509:
510: return sql.toString();
511: }
512: }
|