1 package org.apache.torque.util;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.lang.reflect.Array;
23 import java.util.Date;
24 import java.util.HashSet;
25 import java.util.Iterator;
26 import java.util.List;
27
28 import org.apache.commons.lang.StringUtils;
29 import org.apache.torque.TorqueException;
30 import org.apache.torque.adapter.DB;
31 import org.apache.torque.om.DateKey;
32 import org.apache.torque.om.ObjectKey;
33 import org.apache.torque.om.StringKey;
34
35
36 /***
37 * This class represents a part of an SQL query found in the <code>WHERE</code>
38 * section. For example:
39 * <pre>
40 * table_a.column_a = table_b.column_a
41 * column LIKE 'F%'
42 * table.column < 3
43 * </pre>
44 * This class is used primarily by {@link org.apache.torque.util.BasePeer}.
45 *
46 * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
47 * @author <a href="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
48 * @author <a href="mailto:fedor@apache.org">Fedor Karpelevitch</a>
49 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
50 * @version $Id: SqlExpression.java 476550 2006-11-18 16:08:37Z tfischer $
51 */
52 public final class SqlExpression
53 {
54 /*** escaped single quote */
55 private static final char SINGLE_QUOTE = '\'';
56 /*** escaped backslash */
57 private static final char BACKSLASH = '//';
58
59 /***
60 * Private constructor to prevent instantiation.
61 *
62 * Class contains only static method ans should therefore not be
63 * instantiated.
64 */
65 private SqlExpression()
66 {
67 }
68
69 /***
70 * Used to specify a join on two columns.
71 *
72 * @param column A column in one of the tables to be joined.
73 * @param relatedColumn The column in the other table to be joined.
74 * @return A join expression, e.g. UPPER(table_a.column_a) =
75 * UPPER(table_b.column_b).
76 */
77 public static String buildInnerJoin(String column, String relatedColumn)
78 {
79
80 return buildInnerJoin(column, relatedColumn, false, null);
81 }
82
83 /***
84 * Used to specify a join on two columns.
85 *
86 * @param column A column in one of the tables to be joined.
87 * @param relatedColumn The column in the other table to be joined.
88 * @param ignoreCase If true and columns represent Strings, the appropriate
89 * function defined for the database will be used to ignore
90 * differences in case.
91 * @param db Represents the database in use for vendor-specific functions.
92 * @return A join expression, e.g. UPPER(table_a.column_a) =
93 * UPPER(table_b.column_b).
94 */
95 public static String buildInnerJoin(String column,
96 String relatedColumn,
97 boolean ignoreCase,
98 DB db)
99 {
100 int addlength = (ignoreCase) ? 25 : 1;
101 StringBuffer sb = new StringBuffer(column.length()
102 + relatedColumn.length() + addlength);
103 buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
104 return sb.toString();
105 }
106
107 /***
108 * Used to specify a join on two columns.
109 *
110 * @param column A column in one of the tables to be joined.
111 * @param relatedColumn The column in the other table to be joined.
112 * @param ignoreCase If true and columns represent Strings, the appropriate
113 * function defined for the database will be used to ignore
114 * differences in case.
115 * @param db Represents the database in use for vendor-specific functions.
116 * @param whereClause A StringBuffer to which the sql expression will be
117 * appended.
118 */
119 public static void buildInnerJoin(String column,
120 String relatedColumn,
121 boolean ignoreCase,
122 DB db,
123 StringBuffer whereClause)
124 {
125 if (ignoreCase)
126 {
127 whereClause.append(db.ignoreCase(column))
128 .append('=')
129 .append(db.ignoreCase(relatedColumn));
130 }
131 else
132 {
133 whereClause.append(column)
134 .append('=')
135 .append(relatedColumn);
136 }
137 }
138
139
140 /***
141 * Builds a simple SQL expression.
142 *
143 * @param columnName A column.
144 * @param criteria The value to compare the column against.
145 * @param comparison One of =, <, >, ^lt;=, >=, <>,
146 * !=, LIKE, etc.
147 * @return A simple SQL expression, e.g. UPPER(table_a.column_a)
148 * LIKE UPPER('ab%c').
149 * @throws TorqueException Any exceptions caught during processing will be
150 * rethrown wrapped into a TorqueException.
151 */
152 public static String build(String columnName,
153 Object criteria,
154 SqlEnum comparison)
155 throws TorqueException
156 {
157
158 return build(columnName, criteria, comparison, false, null);
159 }
160
161 /***
162 * Builds a simple SQL expression.
163 *
164 * @param columnName A column.
165 * @param criteria The value to compare the column against.
166 * @param comparison One of =, <, >, ^lt;=, >=, <>,
167 * !=, LIKE, etc.
168 * @param ignoreCase If true and columns represent Strings, the appropriate
169 * function defined for the database will be used to ignore
170 * differences in case.
171 * @param db Represents the database in use, for vendor specific functions.
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 boolean ignoreCase,
181 DB db)
182 throws TorqueException
183 {
184 int addlength = (ignoreCase ? 40 : 20);
185 StringBuffer sb = new StringBuffer(columnName.length() + addlength);
186 build(columnName, criteria, comparison, ignoreCase, db, sb);
187 return sb.toString();
188 }
189
190 /***
191 * Builds a simple SQL expression.
192 *
193 * @param columnName A column.
194 * @param criteria The value to compare the column against.
195 * @param comparison One of =, <, >, ^lt;=, >=, <>,
196 * !=, LIKE, etc.
197 * @param ignoreCase If true and columns represent Strings, the appropriate
198 * function defined for the database will be used to ignore
199 * differences in case.
200 * @param db Represents the database in use, for vendor specific functions.
201 * @param whereClause A StringBuffer to which the sql expression will be
202 * appended.
203 */
204 public static void build(String columnName,
205 Object criteria,
206 SqlEnum comparison,
207 boolean ignoreCase,
208 DB db,
209 StringBuffer whereClause)
210 throws TorqueException
211 {
212
213
214
215
216
217
218
219
220
221
222 if (criteria != null && criteria instanceof ObjectKey)
223 {
224 if (((ObjectKey) criteria).getValue() == null)
225 {
226 criteria = null;
227 }
228 }
229
230
231
232
233
234 if (criteria == null)
235 {
236 criteria = "null";
237 if (comparison.equals(Criteria.EQUAL))
238 {
239 comparison = Criteria.ISNULL;
240 }
241 else if (comparison.equals(Criteria.NOT_EQUAL))
242 {
243 comparison = Criteria.ISNOTNULL;
244 }
245 else if (comparison.equals(Criteria.ALT_NOT_EQUAL))
246 {
247 comparison = Criteria.ISNOTNULL;
248 }
249 }
250 else
251 {
252 if (criteria instanceof String || criteria instanceof StringKey)
253 {
254 criteria = quoteAndEscapeText(criteria.toString(), db);
255 }
256 else if (criteria instanceof Date)
257 {
258 Date dt = (Date) criteria;
259 criteria = db.getDateString(dt);
260 }
261 else if (criteria instanceof DateKey)
262 {
263 Date dt = (Date) ((DateKey) criteria).getValue();
264 criteria = db.getDateString(dt);
265 }
266 else if (criteria instanceof Boolean)
267 {
268 criteria = db.getBooleanString((Boolean) criteria);
269 }
270 else if (criteria instanceof Criteria)
271 {
272 Query subquery = SQLBuilder.buildQueryClause(
273 (Criteria) criteria,
274 null,
275 new SQLBuilder.QueryCallback() {
276 public String process(
277 Criteria.Criterion criterion,
278 List params)
279 {
280 return criterion.toString();
281 }
282 });
283 if (comparison.equals(Criteria.IN)
284 || comparison.equals(Criteria.NOT_IN))
285 {
286
287 criteria = subquery.toString();
288 }
289 else
290 {
291 criteria = "(" + subquery.toString() + ")";
292 }
293 }
294 }
295
296 if (comparison.equals(Criteria.LIKE)
297 || comparison.equals(Criteria.NOT_LIKE)
298 || comparison.equals(Criteria.ILIKE)
299 || comparison.equals(Criteria.NOT_ILIKE))
300 {
301 buildLike(columnName, (String) criteria, comparison,
302 ignoreCase, db, whereClause);
303 }
304 else if (comparison.equals(Criteria.IN)
305 || comparison.equals(Criteria.NOT_IN))
306 {
307 buildIn(columnName, criteria, comparison,
308 ignoreCase, db, whereClause);
309 }
310 else
311 {
312
313
314 if (comparison.equals(Criteria.ISNULL)
315 || comparison.equals(Criteria.ISNOTNULL))
316 {
317 whereClause.append(columnName)
318 .append(comparison);
319 }
320 else
321 {
322 String columnValue = criteria.toString();
323 if (ignoreCase && db != null)
324 {
325 columnName = db.ignoreCase(columnName);
326 columnValue = db.ignoreCase(columnValue);
327 }
328 whereClause.append(columnName)
329 .append(comparison)
330 .append(columnValue);
331 }
332 }
333 }
334
335 /***
336 * Takes a columnName and criteria and builds an SQL phrase based
337 * on whether wildcards are present and the state of the
338 * ignoreCase flag. Multicharacter wildcards % and * may be used
339 * as well as single character wildcards, _ and ?. These
340 * characters can be escaped with \.
341 *
342 * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
343 * -> UPPER(columnName) LIKE UPPER('fre%')
344 * criteria = "50\%" -> columnName = '50%'
345 *
346 * @param columnName A column.
347 * @param criteria The value to compare the column against.
348 * @param comparison Whether to do a LIKE or a NOT LIKE
349 * @param ignoreCase If true and columns represent Strings, the
350 * appropriate function defined for the database will be used to
351 * ignore differences in case.
352 * @param db Represents the database in use, for vendor specific functions.
353 * @return An SQL expression.
354 */
355 static String buildLike(String columnName,
356 String criteria,
357 SqlEnum comparison,
358 boolean ignoreCase,
359 DB db)
360 {
361 StringBuffer whereClause = new StringBuffer();
362 buildLike(columnName, criteria, comparison, ignoreCase, db,
363 whereClause);
364 return whereClause.toString();
365 }
366
367 /***
368 * Takes a columnName and criteria and builds an SQL phrase based
369 * on whether wildcards are present and the state of the
370 * ignoreCase flag. Multicharacter wildcards % and * may be used
371 * as well as single character wildcards, _ and ?. These
372 * characters can be escaped with \.
373 *
374 * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
375 * -> UPPER(columnName) LIKE UPPER('fre%')
376 * criteria = "50\%" -> columnName = '50%'
377 *
378 * @param columnName A column name.
379 * @param criteria The value to compare the column against.
380 * @param comparison Whether to do a LIKE or a NOT LIKE
381 * @param ignoreCase If true and columns represent Strings, the
382 * appropriate function defined for the database will be used to
383 * ignore differences in case.
384 * @param db Represents the database in use, for vendor specific functions.
385 * @param whereClause A StringBuffer to which the sql expression
386 * will be appended.
387 */
388 static void buildLike(String columnName,
389 String criteria,
390 SqlEnum comparison,
391 boolean ignoreCase,
392 DB db,
393 StringBuffer whereClause)
394 {
395
396
397
398
399
400
401
402
403 int position = 0;
404 StringBuffer sb = new StringBuffer();
405 boolean replaceWithEquals = true;
406 while (position < criteria.length())
407 {
408 char checkWildcard = criteria.charAt(position);
409
410 switch (checkWildcard)
411 {
412 case BACKSLASH:
413
414
415
416 int charsToProceed = db.escapeText() ? 2 : 1;
417 if (position + charsToProceed >= criteria.length())
418 {
419 charsToProceed = criteria.length() - position - 1;
420 }
421 else if (criteria.charAt(position + charsToProceed) == BACKSLASH
422 && db.escapeText())
423 {
424
425
426 charsToProceed += 1;
427 }
428 sb.append(criteria.substring(
429 position,
430 position + charsToProceed));
431 position += charsToProceed;
432
433 checkWildcard = criteria.charAt(position);
434 break;
435 case '%':
436 case '_':
437 replaceWithEquals = false;
438 break;
439 case '*':
440 replaceWithEquals = false;
441 checkWildcard = '%';
442 break;
443 case '?':
444 replaceWithEquals = false;
445 checkWildcard = '_';
446 break;
447 }
448
449 sb.append(checkWildcard);
450 position++;
451 }
452 criteria = sb.toString();
453
454 if (ignoreCase)
455 {
456 if (db.useIlike() && !replaceWithEquals)
457 {
458 if (SqlEnum.LIKE.equals(comparison))
459 {
460 comparison = SqlEnum.ILIKE;
461 }
462 else if (SqlEnum.NOT_LIKE.equals(comparison))
463 {
464 comparison = SqlEnum.NOT_ILIKE;
465 }
466 }
467 else
468 {
469
470
471
472 columnName = db.ignoreCase(columnName);
473 }
474 }
475 whereClause.append(columnName);
476
477 if (replaceWithEquals)
478 {
479 if (comparison.equals(Criteria.NOT_LIKE)
480 || comparison.equals(Criteria.NOT_ILIKE))
481 {
482 whereClause.append(" ").append(Criteria.NOT_EQUAL).append(" ");
483 }
484 else
485 {
486 whereClause.append(" ").append(Criteria.EQUAL).append(" ");
487 }
488
489
490 position = 0;
491 sb = new StringBuffer();
492 while (position < criteria.length())
493 {
494 char checkWildcard = criteria.charAt(position);
495
496 if (checkWildcard == BACKSLASH)
497 {
498
499
500
501 int charsToSkip = db.escapeText() ? 2 : 1;
502 if (position + charsToSkip >= criteria.length())
503 {
504 charsToSkip = criteria.length() - position - 1;
505 }
506 else if (criteria.charAt(position + charsToSkip)
507 == BACKSLASH
508 && db.escapeText())
509 {
510
511
512
513
514 sb.append(BACKSLASH);
515 charsToSkip += 1;
516 }
517 position += charsToSkip;
518
519 checkWildcard = criteria.charAt(position);
520 }
521 sb.append(checkWildcard);
522 position++;
523 }
524 criteria = sb.toString();
525 }
526 else
527 {
528 whereClause.append(comparison);
529 }
530
531
532
533 if (ignoreCase && (!(db.useIlike()) || replaceWithEquals))
534 {
535 criteria = db.ignoreCase(criteria);
536 }
537 whereClause.append(criteria);
538
539 if (!replaceWithEquals && db.useEscapeClauseForLike())
540 {
541 whereClause.append(SqlEnum.ESCAPE)
542 .append("'//'");
543 }
544 }
545
546 /***
547 * Takes a columnName and criteria (which must be an array) and
548 * builds a SQL 'IN' expression taking into account the ignoreCase
549 * flag.
550 *
551 * @param columnName A column.
552 * @param criteria The value to compare the column against.
553 * @param comparison Either " IN " or " NOT IN ".
554 * @param ignoreCase If true and columns represent Strings, the
555 * appropriate function defined for the database will be used to
556 * ignore differences in case.
557 * @param db Represents the database in use, for vendor specific functions.
558 * @return An SQL expression.
559 */
560 static String buildIn(String columnName,
561 Object criteria,
562 SqlEnum comparison,
563 boolean ignoreCase,
564 DB db)
565 {
566 StringBuffer whereClause = new StringBuffer();
567 buildIn(columnName, criteria, comparison,
568 ignoreCase, db, whereClause);
569 return whereClause.toString();
570 }
571
572 /***
573 * Takes a columnName and criteria (which must be an array) and
574 * builds a SQL 'IN' expression taking into account the ignoreCase
575 * flag.
576 *
577 * @param columnName A column.
578 * @param criteria The value to compare the column against.
579 * @param comparison Either " IN " or " NOT IN ".
580 * @param ignoreCase If true and columns represent Strings, the
581 * appropriate function defined for the database will be used to
582 * ignore differences in case.
583 * @param db Represents the database in use, for vendor specific functions.
584 * @param whereClause A StringBuffer to which the sql expression
585 * will be appended.
586 */
587 static void buildIn(String columnName,
588 Object criteria,
589 SqlEnum comparison,
590 boolean ignoreCase,
591 DB db,
592 StringBuffer whereClause)
593 {
594 if (ignoreCase)
595 {
596 whereClause.append(db.ignoreCase(columnName));
597 }
598 else
599 {
600 whereClause.append(columnName);
601 }
602
603 whereClause.append(comparison);
604 HashSet inClause = new HashSet();
605 if (criteria instanceof List)
606 {
607 Iterator iter = ((List) criteria).iterator();
608 while (iter.hasNext())
609 {
610 Object value = iter.next();
611
612
613
614 inClause.add(processInValue(value, ignoreCase, db));
615 }
616 }
617 else if (criteria instanceof String)
618 {
619
620 inClause.add(criteria);
621 }
622 else
623 {
624
625 for (int i = 0; i < Array.getLength(criteria); i++)
626 {
627 Object value = Array.get(criteria, i);
628
629
630
631 inClause.add(processInValue(value, ignoreCase, db));
632 }
633 }
634 whereClause.append('(')
635 .append(StringUtils.join(inClause.iterator(), ","))
636 .append(')');
637 }
638
639 /***
640 * Creates an appropriate string for an 'IN' clause from an
641 * object. Adds quoting and/or UPPER() as appropriate. This is
642 * broken out into a seperate method as it is used in two places
643 * in buildIn, depending on whether an array or List is being
644 * looped over.
645 *
646 * @param value The value to process.
647 * @param ignoreCase Coerce the value suitably for ignoring case.
648 * @param db Represents the database in use for vendor specific functions.
649 * @return Processed value as String.
650 */
651 static String processInValue(Object value,
652 boolean ignoreCase,
653 DB db)
654 {
655 String ret = null;
656 if (value instanceof String)
657 {
658 ret = quoteAndEscapeText((String) value, db);
659 }
660 else
661 {
662 ret = value.toString();
663 }
664 if (ignoreCase)
665 {
666 ret = db.ignoreCase(ret);
667 }
668 return ret;
669 }
670
671 /***
672 * Quotes and escapes raw text for placement in a SQL expression.
673 * For simplicity, the text is assumed to be neither quoted nor
674 * escaped.
675 *
676 * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
677 * @param db the db
678 * @return Quoted and escaped text.
679 */
680 public static String quoteAndEscapeText(String rawText, DB db)
681 {
682 StringBuffer buf = new StringBuffer((int) (rawText.length() * 1.1));
683
684
685 String escapeString;
686 if (db != null && !db.escapeText())
687 {
688 escapeString = String.valueOf(BACKSLASH);
689 }
690 else
691 {
692 escapeString = String.valueOf(BACKSLASH)
693 + String.valueOf(BACKSLASH);
694 }
695
696 char[] data = rawText.toCharArray();
697 buf.append(SINGLE_QUOTE);
698 for (int i = 0; i < data.length; i++)
699 {
700 switch (data[i])
701 {
702 case SINGLE_QUOTE:
703 buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
704 break;
705 case BACKSLASH:
706 buf.append(escapeString);
707 break;
708 default:
709 buf.append(data[i]);
710 }
711 }
712 buf.append(SINGLE_QUOTE);
713
714 return buf.toString();
715 }
716 }