View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2005 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
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          // 'db' can be null because 'ignoreCase' is false.
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;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
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         // 'db' can be null because 'ignoreCase' is null
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;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
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;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
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         // Allow null criteria
200         // This will result in queries like
201         // insert into table (name, parent) values ('x', null);
202         //
203 
204         /* Check to see if the criteria is an ObjectKey
205          * and if the value of that ObjectKey is null.
206          * In that case, criteria should be null.
207          */
208 
209         if (criteria != null && criteria instanceof ObjectKey)
210         {
211             if (((ObjectKey) criteria).getValue() == null)
212             {
213                 criteria = null;
214             }
215         }
216         /*  If the criteria is null, check to see comparison
217          *  is an =, <>, or !=.  If so, replace the comparison
218          *  with the proper IS or IS NOT.
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             // Do not put the upper/lower keyword around IS NULL
276             //  or IS NOT NULL
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         // If selection is case insensitive use ILIKE for PostgreSQL or SQL
359         // UPPER() function on column name for other databases.
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         // If selection criteria contains wildcards use LIKE otherwise
381         // use = (equals).  Wildcards can be escaped by prepending
382         // them with \ (backslash).
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                 // Determine whether to skip over next character.
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         // If selection is case insensitive use SQL UPPER() function
430         // on criteria.
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                 // The method processInValue() quotes the string
506                 // and/or wraps it in UPPER().
507                 inClause.add(processInValue(value, ignoreCase, db));
508             }
509         }
510         else
511         {
512             // Assume array.
513             for (int i = 0; i < Array.getLength(criteria); i++)
514             {
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('(')
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         // Some databases do not need escaping.
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 }