001: package org.apache.torque.util;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.lang.reflect.Array;
023: import java.util.Date;
024: import java.util.HashSet;
025: import java.util.Iterator;
026: import java.util.List;
027:
028: import org.apache.commons.lang.StringUtils;
029: import org.apache.torque.TorqueException;
030: import org.apache.torque.adapter.DB;
031: import org.apache.torque.om.DateKey;
032: import org.apache.torque.om.ObjectKey;
033: import org.apache.torque.om.StringKey;
034:
035: /**
036: * This class represents a part of an SQL query found in the <code>WHERE</code>
037: * section. For example:
038: * <pre>
039: * table_a.column_a = table_b.column_a
040: * column LIKE 'F%'
041: * table.column < 3
042: * </pre>
043: * This class is used primarily by {@link org.apache.torque.util.BasePeer}.
044: *
045: * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
046: * @author <a href="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
047: * @author <a href="mailto:fedor@apache.org">Fedor Karpelevitch</a>
048: * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
049: * @version $Id: SqlExpression.java 476550 2006-11-18 16:08:37Z tfischer $
050: */
051: public final class SqlExpression {
052: /** escaped single quote */
053: private static final char SINGLE_QUOTE = '\'';
054: /** escaped backslash */
055: private static final char BACKSLASH = '\\';
056:
057: /**
058: * Private constructor to prevent instantiation.
059: *
060: * Class contains only static method ans should therefore not be
061: * instantiated.
062: */
063: private SqlExpression() {
064: }
065:
066: /**
067: * Used to specify a join on two columns.
068: *
069: * @param column A column in one of the tables to be joined.
070: * @param relatedColumn The column in the other table to be joined.
071: * @return A join expression, e.g. UPPER(table_a.column_a) =
072: * UPPER(table_b.column_b).
073: */
074: public static String buildInnerJoin(String column,
075: String relatedColumn) {
076: // 'db' can be null because 'ignoreCase' is false.
077: return buildInnerJoin(column, relatedColumn, false, null);
078: }
079:
080: /**
081: * Used to specify a join on two columns.
082: *
083: * @param column A column in one of the tables to be joined.
084: * @param relatedColumn The column in the other table to be joined.
085: * @param ignoreCase If true and columns represent Strings, the appropriate
086: * function defined for the database will be used to ignore
087: * differences in case.
088: * @param db Represents the database in use for vendor-specific functions.
089: * @return A join expression, e.g. UPPER(table_a.column_a) =
090: * UPPER(table_b.column_b).
091: */
092: public static String buildInnerJoin(String column,
093: String relatedColumn, boolean ignoreCase, DB db) {
094: int addlength = (ignoreCase) ? 25 : 1;
095: StringBuffer sb = new StringBuffer(column.length()
096: + relatedColumn.length() + addlength);
097: buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
098: return sb.toString();
099: }
100:
101: /**
102: * Used to specify a join on two columns.
103: *
104: * @param column A column in one of the tables to be joined.
105: * @param relatedColumn The column in the other table to be joined.
106: * @param ignoreCase If true and columns represent Strings, the appropriate
107: * function defined for the database will be used to ignore
108: * differences in case.
109: * @param db Represents the database in use for vendor-specific functions.
110: * @param whereClause A StringBuffer to which the sql expression will be
111: * appended.
112: */
113: public static void buildInnerJoin(String column,
114: String relatedColumn, boolean ignoreCase, DB db,
115: StringBuffer whereClause) {
116: if (ignoreCase) {
117: whereClause.append(db.ignoreCase(column)).append('=')
118: .append(db.ignoreCase(relatedColumn));
119: } else {
120: whereClause.append(column).append('=')
121: .append(relatedColumn);
122: }
123: }
124:
125: /**
126: * Builds a simple SQL expression.
127: *
128: * @param columnName A column.
129: * @param criteria The value to compare the column against.
130: * @param comparison One of =, <, >, ^lt;=, >=, <>,
131: * !=, LIKE, etc.
132: * @return A simple SQL expression, e.g. UPPER(table_a.column_a)
133: * LIKE UPPER('ab%c').
134: * @throws TorqueException Any exceptions caught during processing will be
135: * rethrown wrapped into a TorqueException.
136: */
137: public static String build(String columnName, Object criteria,
138: SqlEnum comparison) throws TorqueException {
139: // 'db' can be null because 'ignoreCase' is null
140: return build(columnName, criteria, comparison, false, null);
141: }
142:
143: /**
144: * Builds a simple SQL expression.
145: *
146: * @param columnName A column.
147: * @param criteria The value to compare the column against.
148: * @param comparison One of =, <, >, ^lt;=, >=, <>,
149: * !=, LIKE, etc.
150: * @param ignoreCase If true and columns represent Strings, the appropriate
151: * function defined for the database will be used to ignore
152: * differences in case.
153: * @param db Represents the database in use, for vendor specific functions.
154: * @return A simple sql expression, e.g. UPPER(table_a.column_a)
155: * LIKE UPPER('ab%c').
156: * @throws TorqueException Any exceptions caught during processing will be
157: * rethrown wrapped into a TorqueException.
158: */
159: public static String build(String columnName, Object criteria,
160: SqlEnum comparison, boolean ignoreCase, DB db)
161: throws TorqueException {
162: int addlength = (ignoreCase ? 40 : 20);
163: StringBuffer sb = new StringBuffer(columnName.length()
164: + addlength);
165: build(columnName, criteria, comparison, ignoreCase, db, sb);
166: return sb.toString();
167: }
168:
169: /**
170: * Builds a simple SQL expression.
171: *
172: * @param columnName A column.
173: * @param criteria The value to compare the column against.
174: * @param comparison One of =, <, >, ^lt;=, >=, <>,
175: * !=, LIKE, etc.
176: * @param ignoreCase If true and columns represent Strings, the appropriate
177: * function defined for the database will be used to ignore
178: * differences in case.
179: * @param db Represents the database in use, for vendor specific functions.
180: * @param whereClause A StringBuffer to which the sql expression will be
181: * appended.
182: */
183: public static void build(String columnName, Object criteria,
184: SqlEnum comparison, boolean ignoreCase, DB db,
185: StringBuffer whereClause) throws TorqueException {
186: // Allow null criteria
187: // This will result in queries like
188: // insert into table (name, parent) values ('x', null);
189: //
190:
191: /* Check to see if the criteria is an ObjectKey
192: * and if the value of that ObjectKey is null.
193: * In that case, criteria should be null.
194: */
195:
196: if (criteria != null && criteria instanceof ObjectKey) {
197: if (((ObjectKey) criteria).getValue() == null) {
198: criteria = null;
199: }
200: }
201: /* If the criteria is null, check to see comparison
202: * is an =, <>, or !=. If so, replace the comparison
203: * with the proper IS or IS NOT.
204: */
205:
206: if (criteria == null) {
207: criteria = "null";
208: if (comparison.equals(Criteria.EQUAL)) {
209: comparison = Criteria.ISNULL;
210: } else if (comparison.equals(Criteria.NOT_EQUAL)) {
211: comparison = Criteria.ISNOTNULL;
212: } else if (comparison.equals(Criteria.ALT_NOT_EQUAL)) {
213: comparison = Criteria.ISNOTNULL;
214: }
215: } else {
216: if (criteria instanceof String
217: || criteria instanceof StringKey) {
218: criteria = quoteAndEscapeText(criteria.toString(), db);
219: } else if (criteria instanceof Date) {
220: Date dt = (Date) criteria;
221: criteria = db.getDateString(dt);
222: } else if (criteria instanceof DateKey) {
223: Date dt = (Date) ((DateKey) criteria).getValue();
224: criteria = db.getDateString(dt);
225: } else if (criteria instanceof Boolean) {
226: criteria = db.getBooleanString((Boolean) criteria);
227: } else if (criteria instanceof Criteria) {
228: Query subquery = SQLBuilder.buildQueryClause(
229: (Criteria) criteria, null,
230: new SQLBuilder.QueryCallback() {
231: public String process(
232: Criteria.Criterion criterion,
233: List params) {
234: return criterion.toString();
235: }
236: });
237: if (comparison.equals(Criteria.IN)
238: || comparison.equals(Criteria.NOT_IN)) {
239: // code below takes care of adding brackets
240: criteria = subquery.toString();
241: } else {
242: criteria = "(" + subquery.toString() + ")";
243: }
244: }
245: }
246:
247: if (comparison.equals(Criteria.LIKE)
248: || comparison.equals(Criteria.NOT_LIKE)
249: || comparison.equals(Criteria.ILIKE)
250: || comparison.equals(Criteria.NOT_ILIKE)) {
251: buildLike(columnName, (String) criteria, comparison,
252: ignoreCase, db, whereClause);
253: } else if (comparison.equals(Criteria.IN)
254: || comparison.equals(Criteria.NOT_IN)) {
255: buildIn(columnName, criteria, comparison, ignoreCase, db,
256: whereClause);
257: } else {
258: // Do not put the upper/lower keyword around IS NULL
259: // or IS NOT NULL
260: if (comparison.equals(Criteria.ISNULL)
261: || comparison.equals(Criteria.ISNOTNULL)) {
262: whereClause.append(columnName).append(comparison);
263: } else {
264: String columnValue = criteria.toString();
265: if (ignoreCase && db != null) {
266: columnName = db.ignoreCase(columnName);
267: columnValue = db.ignoreCase(columnValue);
268: }
269: whereClause.append(columnName).append(comparison)
270: .append(columnValue);
271: }
272: }
273: }
274:
275: /**
276: * Takes a columnName and criteria and builds an SQL phrase based
277: * on whether wildcards are present and the state of the
278: * ignoreCase flag. Multicharacter wildcards % and * may be used
279: * as well as single character wildcards, _ and ?. These
280: * characters can be escaped with \.
281: *
282: * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
283: * -> UPPER(columnName) LIKE UPPER('fre%')
284: * criteria = "50\%" -> columnName = '50%'
285: *
286: * @param columnName A column.
287: * @param criteria The value to compare the column against.
288: * @param comparison Whether to do a LIKE or a NOT LIKE
289: * @param ignoreCase If true and columns represent Strings, the
290: * appropriate function defined for the database will be used to
291: * ignore differences in case.
292: * @param db Represents the database in use, for vendor specific functions.
293: * @return An SQL expression.
294: */
295: static String buildLike(String columnName, String criteria,
296: SqlEnum comparison, boolean ignoreCase, DB db) {
297: StringBuffer whereClause = new StringBuffer();
298: buildLike(columnName, criteria, comparison, ignoreCase, db,
299: whereClause);
300: return whereClause.toString();
301: }
302:
303: /**
304: * Takes a columnName and criteria and builds an SQL phrase based
305: * on whether wildcards are present and the state of the
306: * ignoreCase flag. Multicharacter wildcards % and * may be used
307: * as well as single character wildcards, _ and ?. These
308: * characters can be escaped with \.
309: *
310: * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
311: * -> UPPER(columnName) LIKE UPPER('fre%')
312: * criteria = "50\%" -> columnName = '50%'
313: *
314: * @param columnName A column name.
315: * @param criteria The value to compare the column against.
316: * @param comparison Whether to do a LIKE or a NOT LIKE
317: * @param ignoreCase If true and columns represent Strings, the
318: * appropriate function defined for the database will be used to
319: * ignore differences in case.
320: * @param db Represents the database in use, for vendor specific functions.
321: * @param whereClause A StringBuffer to which the sql expression
322: * will be appended.
323: */
324: static void buildLike(String columnName, String criteria,
325: SqlEnum comparison, boolean ignoreCase, DB db,
326: StringBuffer whereClause) {
327: // If selection criteria contains wildcards use LIKE otherwise
328: // use = (equals). Wildcards can be escaped by prepending
329: // them with \ (backslash). However, if we switch from
330: // like to equals, we need to remove the escape characters.
331: // from the wildcards.
332: // So we need two passes: The first replaces * and ? by % and _,
333: // and checks whether we switch to equals,
334: // the second removes escapes if we have switched to equals.
335: int position = 0;
336: StringBuffer sb = new StringBuffer();
337: boolean replaceWithEquals = true;
338: while (position < criteria.length()) {
339: char checkWildcard = criteria.charAt(position);
340:
341: switch (checkWildcard) {
342: case BACKSLASH:
343: // if text is escaped, all backslashes are already escaped,
344: // so the next character after the backslash is the doubled
345: // backslash from escaping.
346: int charsToProceed = db.escapeText() ? 2 : 1;
347: if (position + charsToProceed >= criteria.length()) {
348: charsToProceed = criteria.length() - position - 1;
349: } else if (criteria.charAt(position + charsToProceed) == BACKSLASH
350: && db.escapeText()) {
351: // the escaped backslash is also escaped,
352: // so we need to proceed another character
353: charsToProceed += 1;
354: }
355: sb.append(criteria.substring(position, position
356: + charsToProceed));
357: position += charsToProceed;
358: // code below copies escaped character into sb
359: checkWildcard = criteria.charAt(position);
360: break;
361: case '%':
362: case '_':
363: replaceWithEquals = false;
364: break;
365: case '*':
366: replaceWithEquals = false;
367: checkWildcard = '%';
368: break;
369: case '?':
370: replaceWithEquals = false;
371: checkWildcard = '_';
372: break;
373: }
374:
375: sb.append(checkWildcard);
376: position++;
377: }
378: criteria = sb.toString();
379:
380: if (ignoreCase) {
381: if (db.useIlike() && !replaceWithEquals) {
382: if (SqlEnum.LIKE.equals(comparison)) {
383: comparison = SqlEnum.ILIKE;
384: } else if (SqlEnum.NOT_LIKE.equals(comparison)) {
385: comparison = SqlEnum.NOT_ILIKE;
386: }
387: } else {
388: // no native case insensitive like is offered by the DB,
389: // or the LIKE was replaced with equals.
390: // need to ignore case manually.
391: columnName = db.ignoreCase(columnName);
392: }
393: }
394: whereClause.append(columnName);
395:
396: if (replaceWithEquals) {
397: if (comparison.equals(Criteria.NOT_LIKE)
398: || comparison.equals(Criteria.NOT_ILIKE)) {
399: whereClause.append(" ").append(Criteria.NOT_EQUAL)
400: .append(" ");
401: } else {
402: whereClause.append(" ").append(Criteria.EQUAL).append(
403: " ");
404: }
405:
406: // remove escape backslashes from String
407: position = 0;
408: sb = new StringBuffer();
409: while (position < criteria.length()) {
410: char checkWildcard = criteria.charAt(position);
411:
412: if (checkWildcard == BACKSLASH) {
413: // if text is escaped, all backslashes are already escaped,
414: // so the next character after the backslash is the doubled
415: // backslash from escaping.
416: int charsToSkip = db.escapeText() ? 2 : 1;
417: if (position + charsToSkip >= criteria.length()) {
418: charsToSkip = criteria.length() - position - 1;
419: } else if (criteria.charAt(position + charsToSkip) == BACKSLASH
420: && db.escapeText()) {
421: // the escaped backslash is also escaped,
422: // so we need to skip another character
423: // but add the escaped backslash to sb
424: // so that the escaping remains.
425: sb.append(BACKSLASH);
426: charsToSkip += 1;
427: }
428: position += charsToSkip;
429: // code below copies escaped character into sb
430: checkWildcard = criteria.charAt(position);
431: }
432: sb.append(checkWildcard);
433: position++;
434: }
435: criteria = sb.toString();
436: } else {
437: whereClause.append(comparison);
438: }
439:
440: // If selection is case insensitive use SQL UPPER() function
441: // on criteria.
442: if (ignoreCase && (!(db.useIlike()) || replaceWithEquals)) {
443: criteria = db.ignoreCase(criteria);
444: }
445: whereClause.append(criteria);
446:
447: if (!replaceWithEquals && db.useEscapeClauseForLike()) {
448: whereClause.append(SqlEnum.ESCAPE).append("'\\'");
449: }
450: }
451:
452: /**
453: * Takes a columnName and criteria (which must be an array) and
454: * builds a SQL 'IN' expression taking into account the ignoreCase
455: * flag.
456: *
457: * @param columnName A column.
458: * @param criteria The value to compare the column against.
459: * @param comparison Either " IN " or " NOT IN ".
460: * @param ignoreCase If true and columns represent Strings, the
461: * appropriate function defined for the database will be used to
462: * ignore differences in case.
463: * @param db Represents the database in use, for vendor specific functions.
464: * @return An SQL expression.
465: */
466: static String buildIn(String columnName, Object criteria,
467: SqlEnum comparison, boolean ignoreCase, DB db) {
468: StringBuffer whereClause = new StringBuffer();
469: buildIn(columnName, criteria, comparison, ignoreCase, db,
470: whereClause);
471: return whereClause.toString();
472: }
473:
474: /**
475: * Takes a columnName and criteria (which must be an array) and
476: * builds a SQL 'IN' expression taking into account the ignoreCase
477: * flag.
478: *
479: * @param columnName A column.
480: * @param criteria The value to compare the column against.
481: * @param comparison Either " IN " or " NOT IN ".
482: * @param ignoreCase If true and columns represent Strings, the
483: * appropriate function defined for the database will be used to
484: * ignore differences in case.
485: * @param db Represents the database in use, for vendor specific functions.
486: * @param whereClause A StringBuffer to which the sql expression
487: * will be appended.
488: */
489: static void buildIn(String columnName, Object criteria,
490: SqlEnum comparison, boolean ignoreCase, DB db,
491: StringBuffer whereClause) {
492: if (ignoreCase) {
493: whereClause.append(db.ignoreCase(columnName));
494: } else {
495: whereClause.append(columnName);
496: }
497:
498: whereClause.append(comparison);
499: HashSet inClause = new HashSet();
500: if (criteria instanceof List) {
501: Iterator iter = ((List) criteria).iterator();
502: while (iter.hasNext()) {
503: Object value = iter.next();
504:
505: // The method processInValue() quotes the string
506: // and/or wraps it in UPPER().
507: inClause.add(processInValue(value, ignoreCase, db));
508: }
509: } else if (criteria instanceof String) {
510: // subquery
511: inClause.add(criteria);
512: } else {
513: // Assume array.
514: for (int i = 0; i < Array.getLength(criteria); i++) {
515: Object value = Array.get(criteria, i);
516:
517: // The method processInValue() quotes the string
518: // and/or wraps it in UPPER().
519: inClause.add(processInValue(value, ignoreCase, db));
520: }
521: }
522: whereClause.append('(').append(
523: StringUtils.join(inClause.iterator(), ",")).append(')');
524: }
525:
526: /**
527: * Creates an appropriate string for an 'IN' clause from an
528: * object. Adds quoting and/or UPPER() as appropriate. This is
529: * broken out into a seperate method as it is used in two places
530: * in buildIn, depending on whether an array or List is being
531: * looped over.
532: *
533: * @param value The value to process.
534: * @param ignoreCase Coerce the value suitably for ignoring case.
535: * @param db Represents the database in use for vendor specific functions.
536: * @return Processed value as String.
537: */
538: static String processInValue(Object value, boolean ignoreCase, DB db) {
539: String ret = null;
540: if (value instanceof String) {
541: ret = quoteAndEscapeText((String) value, db);
542: } else {
543: ret = value.toString();
544: }
545: if (ignoreCase) {
546: ret = db.ignoreCase(ret);
547: }
548: return ret;
549: }
550:
551: /**
552: * Quotes and escapes raw text for placement in a SQL expression.
553: * For simplicity, the text is assumed to be neither quoted nor
554: * escaped.
555: *
556: * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
557: * @param db the db
558: * @return Quoted and escaped text.
559: */
560: public static String quoteAndEscapeText(String rawText, DB db) {
561: StringBuffer buf = new StringBuffer(
562: (int) (rawText.length() * 1.1));
563:
564: // Some databases do not need escaping.
565: String escapeString;
566: if (db != null && !db.escapeText()) {
567: escapeString = String.valueOf(BACKSLASH);
568: } else {
569: escapeString = String.valueOf(BACKSLASH)
570: + String.valueOf(BACKSLASH);
571: }
572:
573: char[] data = rawText.toCharArray();
574: buf.append(SINGLE_QUOTE);
575: for (int i = 0; i < data.length; i++) {
576: switch (data[i]) {
577: case SINGLE_QUOTE:
578: buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
579: break;
580: case BACKSLASH:
581: buf.append(escapeString);
582: break;
583: default:
584: buf.append(data[i]);
585: }
586: }
587: buf.append(SINGLE_QUOTE);
588:
589: return buf.toString();
590: }
591: }
|