1 package org.apache.torque.util;
2
3 /* ====================================================================
4 * The Apache Software License, Version 1.1
5 *
6 * Copyright (c) 2001-2003 The Apache Software Foundation. All rights
7 * reserved.
8 *
9 * Redistribution and use in source and binary forms, with or without
10 * modification, are permitted provided that the following conditions
11 * are met:
12 *
13 * 1. Redistributions of source code must retain the above copyright
14 * notice, this list of conditions and the following disclaimer.
15 *
16 * 2. Redistributions in binary form must reproduce the above copyright
17 * notice, this list of conditions and the following disclaimer in
18 * the documentation and/or other materials provided with the
19 * distribution.
20 *
21 * 3. The end-user documentation included with the redistribution,
22 * if any, must include the following acknowledgment:
23 * "This product includes software developed by the
24 * Apache Software Foundation (http://www.apache.org/)."
25 * Alternately, this acknowledgment may appear in the software itself,
26 * if and wherever such third-party acknowledgments normally appear.
27 *
28 * 4. The names "Apache" and "Apache Software Foundation" and
29 * "Apache Turbine" must not be used to endorse or promote products
30 * derived from this software without prior written permission. For
31 * written permission, please contact apache@apache.org.
32 *
33 * 5. Products derived from this software may not be called "Apache",
34 * "Apache Turbine", nor may "Apache" appear in their name, without
35 * prior written permission of the Apache Software Foundation.
36 *
37 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
38 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
39 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
40 * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
41 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
42 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
43 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
44 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
45 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
46 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
47 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
48 * SUCH DAMAGE.
49 * ====================================================================
50 *
51 * This software consists of voluntary contributions made by many
52 * individuals on behalf of the Apache Software Foundation. For more
53 * information on the Apache Software Foundation, please see
54 * <http://www.apache.org/>.
55 */
56
57 import java.lang.reflect.Array;
58 import java.util.Date;
59 import java.util.HashSet;
60 import java.util.Iterator;
61 import java.util.List;
62
63 import org.apache.commons.lang.StringUtils;
64 import org.apache.torque.TorqueException;
65 import org.apache.torque.adapter.DB;
66 import org.apache.torque.om.DateKey;
67 import org.apache.torque.om.ObjectKey;
68 import org.apache.torque.om.StringKey;
69
70
71 /***
72 * This class represents a part of an SQL query found in the <code>WHERE</code>
73 * section. For example:
74 * <pre>
75 * table_a.column_a = table_b.column_a
76 * column LIKE 'F%'
77 * table.column < 3
78 * </pre>
79 * This class is used primarily by {@link org.apache.torque.util.BasePeer}.
80 *
81 * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
82 * @author <a href="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
83 * @author <a href="mailto:fedor@apache.org">Fedor Karpelevitch</a>
84 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
85 * @version $Id: SqlExpression.java,v 1.24 2003/08/27 22:50:11 mpoeschl Exp $
86 */
87 public class SqlExpression
88 {
89 /*** escaped single quote */
90 private static final char SINGLE_QUOTE = '\'';
91 /*** escaped backslash */
92 private static final char BACKSLASH = '//';
93
94 /***
95 * Used to specify a join on two columns.
96 *
97 * @param column A column in one of the tables to be joined.
98 * @param relatedColumn The column in the other table to be joined.
99 * @return A join expression, e.g. UPPER(table_a.column_a) =
100 * UPPER(table_b.column_b).
101 */
102 public static String buildInnerJoin(String column, String relatedColumn)
103 {
104 // 'db' can be null because 'ignoreCase' is false.
105 return buildInnerJoin(column, relatedColumn, false, null);
106 }
107
108 /***
109 * Used to specify a join on two columns.
110 *
111 * @param column A column in one of the tables to be joined.
112 * @param relatedColumn The column in the other table to be joined.
113 * @param ignoreCase If true and columns represent Strings, the appropriate
114 * function defined for the database will be used to ignore
115 * differences in case.
116 * @param db Represents the database in use for vendor-specific functions.
117 * @return A join expression, e.g. UPPER(table_a.column_a) =
118 * UPPER(table_b.column_b).
119 */
120 public static String buildInnerJoin(String column,
121 String relatedColumn,
122 boolean ignoreCase,
123 DB db)
124 {
125 int addlength = (ignoreCase) ? 25 : 1;
126 StringBuffer sb = new StringBuffer(column.length()
127 + relatedColumn.length() + addlength);
128 buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
129 return sb.toString();
130 }
131
132 /***
133 * Used to specify a join on two columns.
134 *
135 * @param column A column in one of the tables to be joined.
136 * @param relatedColumn The column in the other table to be joined.
137 * @param ignoreCase If true and columns represent Strings, the appropriate
138 * function defined for the database will be used to ignore
139 * differences in case.
140 * @param db Represents the database in use for vendor-specific functions.
141 * @param whereClause A StringBuffer to which the sql expression will be
142 * appended.
143 */
144 public static void buildInnerJoin(String column,
145 String relatedColumn,
146 boolean ignoreCase,
147 DB db,
148 StringBuffer whereClause)
149 {
150 if (ignoreCase)
151 {
152 whereClause.append(db.ignoreCase(column))
153 .append('=')
154 .append(db.ignoreCase(relatedColumn));
155 }
156 else
157 {
158 whereClause.append(column)
159 .append('=')
160 .append(relatedColumn);
161 }
162 }
163
164
165 /***
166 * Builds a simple SQL expression.
167 *
168 * @param columnName A column.
169 * @param criteria The value to compare the column against.
170 * @param comparison One of =, <, >, ^lt;=, >=, <>,
171 * !=, LIKE, etc.
172 * @return A simple SQL expression, e.g. UPPER(table_a.column_a)
173 * LIKE UPPER('ab%c').
174 * @throws TorqueException Any exceptions caught during processing will be
175 * rethrown wrapped into a TorqueException.
176 */
177 public static String build(String columnName,
178 Object criteria,
179 SqlEnum comparison)
180 throws TorqueException
181 {
182 // 'db' can be null because 'ignoreCase' is null
183 return build(columnName, criteria, comparison, false, null);
184 }
185
186 /***
187 * Builds a simple SQL expression.
188 *
189 * @param columnName A column.
190 * @param criteria The value to compare the column against.
191 * @param comparison One of =, <, >, ^lt;=, >=, <>,
192 * !=, LIKE, etc.
193 * @param ignoreCase If true and columns represent Strings, the appropriate
194 * function defined for the database will be used to ignore
195 * differences in case.
196 * @param db Represents the database in use, for vendor specific functions.
197 * @return A simple sql expression, e.g. UPPER(table_a.column_a)
198 * LIKE UPPER('ab%c').
199 * @throws TorqueException Any exceptions caught during processing will be
200 * rethrown wrapped into a TorqueException.
201 */
202 public static String build(String columnName,
203 Object criteria,
204 SqlEnum comparison,
205 boolean ignoreCase,
206 DB db)
207 throws TorqueException
208 {
209 int addlength = (ignoreCase ? 40 : 20);
210 StringBuffer sb = new StringBuffer(columnName.length() + addlength);
211 build(columnName, criteria, comparison, ignoreCase, db, sb);
212 return sb.toString();
213 }
214
215 /***
216 * Builds a simple SQL expression.
217 *
218 * @param columnName A column.
219 * @param criteria The value to compare the column against.
220 * @param comparison One of =, <, >, ^lt;=, >=, <>,
221 * !=, LIKE, etc.
222 * @param ignoreCase If true and columns represent Strings, the appropriate
223 * function defined for the database will be used to ignore
224 * differences in case.
225 * @param db Represents the database in use, for vendor specific functions.
226 * @param whereClause A StringBuffer to which the sql expression will be
227 * appended.
228 */
229 public static void build(String columnName,
230 Object criteria,
231 SqlEnum comparison,
232 boolean ignoreCase,
233 DB db,
234 StringBuffer whereClause)
235 {
236 // Allow null criteria
237 // This will result in queries like
238 // insert into table (name, parent) values ('x', null);
239 //
240
241 /* Check to see if the criteria is an ObjectKey
242 * and if the value of that ObjectKey is null.
243 * In that case, criteria should be null.
244 */
245
246 if (criteria != null && criteria instanceof ObjectKey)
247 {
248 if (((ObjectKey) criteria).getValue() == null)
249 {
250 criteria = null;
251 }
252 }
253 /* If the criteria is null, check to see comparison
254 * is an =, <>, or !=. If so, replace the comparison
255 * with the proper IS or IS NOT.
256 */
257
258 if (criteria == null)
259 {
260 criteria = "null";
261 if (comparison.equals(Criteria.EQUAL))
262 {
263 comparison = Criteria.ISNULL;
264 }
265 else if (comparison.equals(Criteria.NOT_EQUAL))
266 {
267 comparison = Criteria.ISNOTNULL;
268 }
269 else if (comparison.equals(Criteria.ALT_NOT_EQUAL))
270 {
271 comparison = Criteria.ISNOTNULL;
272 }
273 }
274 else
275 {
276 if (criteria instanceof String || criteria instanceof StringKey)
277 {
278 criteria = quoteAndEscapeText(criteria.toString(), db);
279 }
280 else if (criteria instanceof Date)
281 {
282 Date dt = (Date) criteria;
283 criteria = db.getDateString(dt);
284 }
285 else if (criteria instanceof DateKey)
286 {
287 Date dt = (Date) ((DateKey) criteria).getValue();
288 criteria = db.getDateString(dt);
289 }
290 else if (criteria instanceof Boolean)
291 {
292 criteria = db.getBooleanString((Boolean) criteria);
293 }
294 }
295
296 if (comparison.equals(Criteria.LIKE)
297 || comparison.equals(Criteria.NOT_LIKE))
298 {
299 buildLike(columnName, (String) criteria, comparison,
300 ignoreCase, db, whereClause);
301 }
302 else if (comparison.equals(Criteria.IN)
303 || comparison.equals(Criteria.NOT_IN))
304 {
305 buildIn(columnName, criteria, comparison,
306 ignoreCase, db, whereClause);
307 }
308 else
309 {
310 // Do not put the upper/lower keyword around IS NULL
311 // or IS NOT NULL
312 if (comparison.equals(Criteria.ISNULL)
313 || comparison.equals(Criteria.ISNOTNULL))
314 {
315 whereClause.append(columnName)
316 .append(comparison);
317 }
318 else
319 {
320 String columnValue = criteria.toString();
321 if (ignoreCase && db != null)
322 {
323 columnName = db.ignoreCase(columnName);
324 columnValue = db.ignoreCase(columnValue);
325 }
326 whereClause.append(columnName)
327 .append(comparison)
328 .append(columnValue);
329 }
330 }
331 }
332
333 /***
334 * Takes a columnName and criteria and builds an SQL phrase based
335 * on whether wildcards are present and the state of the
336 * ignoreCase flag. Multicharacter wildcards % and * may be used
337 * as well as single character wildcards, _ and ?. These
338 * characters can be escaped with \.
339 *
340 * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
341 * -> UPPER(columnName) LIKE UPPER('fre%')
342 * criteria = "50\%" -> columnName = '50%'
343 *
344 * @param columnName A column.
345 * @param criteria The value to compare the column against.
346 * @param comparison Whether to do a LIKE or a NOT LIKE
347 * @param ignoreCase If true and columns represent Strings, the
348 * appropriate function defined for the database will be used to
349 * ignore differences in case.
350 * @param db Represents the database in use, for vendor specific functions.
351 * @return An SQL expression.
352 */
353 static String buildLike(String columnName,
354 String criteria,
355 SqlEnum comparison,
356 boolean ignoreCase,
357 DB db)
358 {
359 StringBuffer whereClause = new StringBuffer();
360 buildLike(columnName, criteria, comparison, ignoreCase, db,
361 whereClause);
362 return whereClause.toString();
363 }
364
365 /***
366 * Takes a columnName and criteria and builds an SQL phrase based
367 * on whether wildcards are present and the state of the
368 * ignoreCase flag. Multicharacter wildcards % and * may be used
369 * as well as single character wildcards, _ and ?. These
370 * characters can be escaped with \.
371 *
372 * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
373 * -> UPPER(columnName) LIKE UPPER('fre%')
374 * criteria = "50\%" -> columnName = '50%'
375 *
376 * @param columnName A column name.
377 * @param criteria The value to compare the column against.
378 * @param comparison Whether to do a LIKE or a NOT LIKE
379 * @param ignoreCase If true and columns represent Strings, the
380 * appropriate function defined for the database will be used to
381 * ignore differences in case.
382 * @param db Represents the database in use, for vendor specific functions.
383 * @param whereClause A StringBuffer to which the sql expression
384 * will be appended.
385 */
386 static void buildLike(String columnName,
387 String criteria,
388 SqlEnum comparison,
389 boolean ignoreCase,
390 DB db,
391 StringBuffer whereClause)
392 {
393 // If selection is case insensitive use SQL UPPER() function
394 // on column name.
395 if (ignoreCase)
396 {
397 columnName = db.ignoreCase(columnName);
398 }
399 whereClause.append(columnName);
400
401 // If selection criteria contains wildcards use LIKE otherwise
402 // use = (equals). Wildcards can be escaped by prepending
403 // them with \ (backslash).
404 String equalsOrLike = " = ";
405 int position = 0;
406 StringBuffer sb = new StringBuffer();
407 while (position < criteria.length())
408 {
409 char checkWildcard = criteria.charAt(position);
410
411 switch (checkWildcard)
412 {
413 case BACKSLASH:
414 // Determine whether to skip over next character.
415 switch (criteria.charAt(position + 1))
416 {
417 case '%':
418 case '_':
419 case '*':
420 case '?':
421 case BACKSLASH:
422 position++;
423 break;
424 }
425 break;
426 case '%':
427 case '_':
428 equalsOrLike = comparison.toString();
429 break;
430 case '*':
431 equalsOrLike = comparison.toString();
432 checkWildcard = '%';
433 break;
434 case '?':
435 equalsOrLike = comparison.toString();
436 checkWildcard = '_';
437 break;
438 }
439
440 sb.append(checkWildcard);
441 position++;
442 }
443 whereClause.append(equalsOrLike);
444
445 // If selection is case insensitive use SQL UPPER() function
446 // on criteria.
447 String clauseItem = sb.toString();
448 if (ignoreCase)
449 {
450 clauseItem = db.ignoreCase(clauseItem);
451 }
452 whereClause.append(clauseItem);
453 }
454
455 /***
456 * Takes a columnName and criteria (which must be an array) and
457 * builds a SQL 'IN' expression taking into account the ignoreCase
458 * flag.
459 *
460 * @param columnName A column.
461 * @param criteria The value to compare the column against.
462 * @param comparison Either " IN " or " NOT IN ".
463 * @param ignoreCase If true and columns represent Strings, the
464 * appropriate function defined for the database will be used to
465 * ignore differences in case.
466 * @param db Represents the database in use, for vendor specific functions.
467 * @return An SQL expression.
468 */
469 static String buildIn(String columnName,
470 Object criteria,
471 SqlEnum comparison,
472 boolean ignoreCase,
473 DB db)
474 {
475 StringBuffer whereClause = new StringBuffer();
476 buildIn(columnName, criteria, comparison,
477 ignoreCase, db, whereClause);
478 return whereClause.toString();
479 }
480
481 /***
482 * Takes a columnName and criteria (which must be an array) and
483 * builds a SQL 'IN' expression taking into account the ignoreCase
484 * flag.
485 *
486 * @param columnName A column.
487 * @param criteria The value to compare the column against.
488 * @param comparison Either " IN " or " NOT IN ".
489 * @param ignoreCase If true and columns represent Strings, the
490 * appropriate function defined for the database will be used to
491 * ignore differences in case.
492 * @param db Represents the database in use, for vendor specific functions.
493 * @param whereClause A StringBuffer to which the sql expression
494 * will be appended.
495 */
496 static void buildIn(String columnName,
497 Object criteria,
498 SqlEnum comparison,
499 boolean ignoreCase,
500 DB db,
501 StringBuffer whereClause)
502 {
503 if (ignoreCase)
504 {
505 whereClause.append(db.ignoreCase(columnName));
506 }
507 else
508 {
509 whereClause.append(columnName);
510 }
511
512 whereClause.append(comparison);
513 HashSet inClause = new HashSet();
514 if (criteria instanceof List)
515 {
516 Iterator iter = ((List) criteria).iterator();
517 while (iter.hasNext())
518 {
519 Object value = iter.next();
520
521 // The method processInValue() quotes the string
522 // and/or wraps it in UPPER().
523 inClause.add(processInValue(value, ignoreCase, db));
524 }
525 }
526 else
527 {
528 // Assume array.
529 for (int i = 0; i < Array.getLength(criteria); i++)
530 {
531 Object value = Array.get(criteria, i);
532
533 // The method processInValue() quotes the string
534 // and/or wraps it in UPPER().
535 inClause.add(processInValue(value, ignoreCase, db));
536 }
537 }
538 whereClause.append('(')
539 .append(StringUtils.join(inClause.iterator(), ","))
540 .append(')');
541 }
542
543 /***
544 * Creates an appropriate string for an 'IN' clause from an
545 * object. Adds quoting and/or UPPER() as appropriate. This is
546 * broken out into a seperate method as it is used in two places
547 * in buildIn, depending on whether an array or List is being
548 * looped over.
549 *
550 * @param value The value to process.
551 * @param ignoreCase Coerce the value suitably for ignoring case.
552 * @param db Represents the database in use for vendor specific functions.
553 * @return Processed value as String.
554 */
555 static String processInValue(Object value,
556 boolean ignoreCase,
557 DB db)
558 {
559 String ret = null;
560 if (value instanceof String)
561 {
562 ret = quoteAndEscapeText((String) value, db);
563 }
564 else
565 {
566 ret = value.toString();
567 }
568 if (ignoreCase)
569 {
570 ret = db.ignoreCase(ret);
571 }
572 return ret;
573 }
574
575 /***
576 * Quotes and escapes raw text for placement in a SQL expression.
577 * For simplicity, the text is assumed to be neither quoted nor
578 * escaped.
579 *
580 * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
581 * @param db the db
582 * @return Quoted and escaped text.
583 */
584 public static String quoteAndEscapeText(String rawText, DB db)
585 {
586 StringBuffer buf = new StringBuffer((int) (rawText.length() * 1.1));
587
588 // Some databases do not need escaping.
589 String escapeString = new String();
590 if (db != null && !db.escapeText())
591 {
592 escapeString = String.valueOf(BACKSLASH);
593 }
594 else
595 {
596 escapeString = String.valueOf(BACKSLASH)
597 + String.valueOf(BACKSLASH);
598 }
599
600 char[] data = rawText.toCharArray();
601 buf.append(SINGLE_QUOTE);
602 for (int i = 0; i < data.length; i++)
603 {
604 switch (data[i])
605 {
606 case SINGLE_QUOTE:
607 buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
608 break;
609 case BACKSLASH:
610 buf.append(escapeString);
611 break;
612 default:
613 buf.append(data[i]);
614 }
615 }
616 buf.append(SINGLE_QUOTE);
617
618 return buf.toString();
619 }
620 }
This page was automatically generated by Maven