Coverage report

  %line %branch
org.apache.torque.util.SqlExpression
61% 
89% 

 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  0
 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  1
         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  2
         int addlength = (ignoreCase) ? 25 : 1;
 89  2
         StringBuffer sb = new StringBuffer(column.length()
 90  
                 + relatedColumn.length() + addlength);
 91  2
         buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
 92  2
         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  2
         if (ignoreCase)
 114  
         {
 115  1
             whereClause.append(db.ignoreCase(column))
 116  
                     .append('=')
 117  
                     .append(db.ignoreCase(relatedColumn));
 118  
         }
 119  
         else
 120  
         {
 121  1
             whereClause.append(column)
 122  
                     .append('=')
 123  
                     .append(relatedColumn);
 124  
         }
 125  2
     }
 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  0
         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  0
         int addlength = (ignoreCase ? 40 : 20);
 173  0
         StringBuffer sb = new StringBuffer(columnName.length() + addlength);
 174  0
         build(columnName, criteria, comparison, ignoreCase, db, sb);
 175  0
         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  42
         if (criteria != null && criteria instanceof ObjectKey)
 210  
         {
 211  0
             if (((ObjectKey) criteria).getValue() == null)
 212  
             {
 213  0
                 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  42
         if (criteria == null)
 222  
         {
 223  0
             criteria = "null";
 224  0
             if (comparison.equals(Criteria.EQUAL))
 225  
             {
 226  0
                 comparison = Criteria.ISNULL;
 227  
             }
 228  0
             else if (comparison.equals(Criteria.NOT_EQUAL))
 229  
             {
 230  0
                 comparison = Criteria.ISNOTNULL;
 231  
             }
 232  0
             else if (comparison.equals(Criteria.ALT_NOT_EQUAL))
 233  
             {
 234  0
                 comparison = Criteria.ISNOTNULL;
 235  
             }
 236  
         }
 237  
         else
 238  
         {
 239  42
             if (criteria instanceof String || criteria instanceof StringKey)
 240  
             {
 241  24
                 criteria = quoteAndEscapeText(criteria.toString(), db);
 242  
             }
 243  18
             else if (criteria instanceof Date)
 244  
             {
 245  2
                 Date dt = (Date) criteria;
 246  2
                 criteria = db.getDateString(dt);
 247  
             }
 248  16
             else if (criteria instanceof DateKey)
 249  
             {
 250  0
                 Date dt = (Date) ((DateKey) criteria).getValue();
 251  0
                 criteria = db.getDateString(dt);
 252  
             }
 253  16
             else if (criteria instanceof Boolean)
 254  
             {
 255  2
                 criteria = db.getBooleanString((Boolean) criteria);
 256  
             }
 257  
         }
 258  
 
 259  42
         if (comparison.equals(Criteria.LIKE)
 260  
                 || comparison.equals(Criteria.NOT_LIKE)
 261  
                 || comparison.equals(Criteria.ILIKE)
 262  
                 || comparison.equals(Criteria.NOT_ILIKE))
 263  
         {
 264  4
             buildLike(columnName, (String) criteria, comparison,
 265  
                        ignoreCase, db, whereClause);
 266  
         }
 267  38
         else if (comparison.equals(Criteria.IN)
 268  
                 || comparison.equals(Criteria.NOT_IN))
 269  
         {
 270  0
             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  38
             if (comparison.equals(Criteria.ISNULL)
 278  
                     || comparison.equals(Criteria.ISNOTNULL))
 279  
             {
 280  0
                 whereClause.append(columnName)
 281  
                         .append(comparison);
 282  
             }
 283  
             else
 284  
             {
 285  38
                 String columnValue = criteria.toString();
 286  38
                 if (ignoreCase && db != null)
 287  
                 {
 288  0
                     columnName = db.ignoreCase(columnName);
 289  0
                     columnValue = db.ignoreCase(columnValue);
 290  
                 }
 291  38
                 whereClause.append(columnName)
 292  
                         .append(comparison)
 293  
                         .append(columnValue);
 294  
             }
 295  
         }
 296  42
     }
 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  0
         StringBuffer whereClause = new StringBuffer();
 325  0
         buildLike(columnName, criteria, comparison, ignoreCase, db,
 326  
                    whereClause);
 327  0
         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  4
         if (ignoreCase)
 361  
         {
 362  2
             if (db instanceof DBPostgres)
 363  
             {
 364  0
                 if (comparison.equals(Criteria.LIKE))
 365  
                 {
 366  0
                     comparison = Criteria.ILIKE;
 367  
                 }
 368  0
                 else if (comparison.equals(Criteria.NOT_LIKE))
 369  
                 {
 370  0
                     comparison = Criteria.NOT_ILIKE;
 371  
                 }
 372  
             }
 373  
             else
 374  
             {
 375  2
                 columnName = db.ignoreCase(columnName);
 376  
             }
 377  
         }
 378  4
         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  4
         String equalsOrLike = " = ";
 384  4
         if (comparison.equals(Criteria.NOT_LIKE))
 385  
         {
 386  1
             equalsOrLike = " " + Criteria.NOT_EQUAL + " ";
 387  
         }
 388  
 
 389  4
         int position = 0;
 390  4
         StringBuffer sb = new StringBuffer();
 391  48
         while (position < criteria.length())
 392  
         {
 393  44
             char checkWildcard = criteria.class="keyword">charAt(position);
 394  
 
 395  44
             switch (checkWildcard)
 396  
             {
 397  
             case BACKSLASH:
 398  
                 // Determine whether to skip over next character.
 399  0
                 switch (criteria.charAt(position + 1))
 400  
                 {
 401  
                 case '%':
 402  
                 case '_':
 403  
                 case '*':
 404  
                 case '?':
 405  
                 case BACKSLASH:
 406  0
                     position++;
 407  
                     break;
 408  
                 }
 409  0
                 break;
 410  
             case '%':
 411  
             case '_':
 412  0
                 equalsOrLike = comparison.toString();
 413  0
                 break;
 414  
             case '*':
 415  0
                 equalsOrLike = comparison.toString();
 416  0
                 checkWildcard = '%';
 417  0
                 break;
 418  
             case '?':
 419  0
                 equalsOrLike = comparison.toString();
 420  0
                 checkWildcard = '_';
 421  
                 break;
 422  
             }
 423  
 
 424  44
             sb.append(checkWildcard);
 425  44
             position++;
 426  
         }
 427  4
         whereClause.append(equalsOrLike);
 428  
 
 429  
         // If selection is case insensitive use SQL UPPER() function
 430  
         // on criteria.
 431  4
         String clauseItem = sb.toString();
 432  4
         if (ignoreCase && !(db instanceof DBPostgres))
 433  
         {
 434  2
             clauseItem = db.ignoreCase(clauseItem);
 435  
         }
 436  4
         whereClause.append(clauseItem);
 437  4
     }
 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  2
         StringBuffer whereClause = new StringBuffer();
 460  2
         buildIn(columnName, criteria, comparison,
 461  
                 ignoreCase, db, whereClause);
 462  2
         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  2
         if (ignoreCase)
 488  
         {
 489  2
             whereClause.append(db.ignoreCase(columnName));
 490  
         }
 491  
         else
 492  
         {
 493  0
             whereClause.append(columnName);
 494  
         }
 495  
 
 496  2
         whereClause.append(comparison);
 497  2
         HashSet inClause = new HashSet();
 498  2
         if (criteria instanceof List)
 499  
         {
 500  0
             Iterator iter = ((List) criteria).iterator();
 501  0
             while (iter.hasNext())
 502  
             {
 503  0
                 Object value = iter.next();
 504  
 
 505  
                 // The method processInValue() quotes the string
 506  
                 // and/or wraps it in UPPER().
 507  0
                 inClause.add(processInValue(value, ignoreCase, db));
 508  
             }
 509  
         }
 510  
         else
 511  
         {
 512  
             // Assume array.
 513  10005
             for (int i = 0; i < Array.getLength(criteria); i++)
 514  
             {
 515  10003
                 Object value = Array.get(criteria, i);
 516  
 
 517  
                 // The method processInValue() quotes the string
 518  
                 // and/or wraps it in UPPER().
 519  10003
                 inClause.add(processInValue(value, ignoreCase, db));
 520  
             }
 521  
         }
 522  2
         whereClause.append('(')
 523  
                    .append(StringUtils.join(inClause.iterator(), ","))
 524  
                    .append(')');
 525  2
     }
 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  10003
         String ret = null;
 544  10003
         if (value instanceof String)
 545  
         {
 546  10003
             ret = quoteAndEscapeText((String) value, db);
 547  
         }
 548  
         else
 549  
         {
 550  0
             ret = value.toString();
 551  
         }
 552  10003
         if (ignoreCase)
 553  
         {
 554  10003
             ret = db.ignoreCase(ret);
 555  
         }
 556  10003
         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  10027
         StringBuffer buf = new StringBuffer((int) (rawText.length() * 1.1));
 571  
 
 572  
         // Some databases do not need escaping.
 573  10027
         String escapeString = new String();
 574  10027
         if (db != null && !db.escapeText())
 575  
         {
 576  0
             escapeString = String.valueOf(BACKSLASH);
 577  
         }
 578  
         else
 579  
         {
 580  10027
             escapeString = String.valueOf(BACKSLASH)
 581  
                     + String.valueOf(BACKSLASH);
 582  
         }
 583  
 
 584  10027
         char[] data = rawText.toCharArray();
 585  10027
         buf.append(SINGLE_QUOTE);
 586  49103
         for (int i = 0; i < data.length; i++)
 587  
         {
 588  39076
             switch (data[i])
 589  
             {
 590  
             case SINGLE_QUOTE:
 591  0
                 buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
 592  0
                 break;
 593  
             case BACKSLASH:
 594  0
                 buf.append(escapeString);
 595  0
                 break;
 596  
             default:
 597  39076
                 buf.append(data[i]);
 598  
             }
 599  
         }
 600  10027
         buf.append(SINGLE_QUOTE);
 601  
 
 602  10027
         return buf.toString();
 603  
     }
 604  
 }

This report is generated by jcoverage, Maven and Maven JCoverage Plugin.