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