db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From se...@apache.org
Subject cvs commit: db-torque/src/java/org/apache/torque/util SqlEnum.java SqlExpression.java Criteria.java
Date Fri, 05 Dec 2003 05:04:37 GMT
seade       2003/12/04 21:04:37

  Modified:    xdocs    criteria-howto.xml changes.xml
               src/java/org/apache/torque/util SqlEnum.java
                        SqlExpression.java Criteria.java
  Log:
  Case insensitive LIKE comparisons now use ILIKE for PostgreSQL.
  Added example to criteria howto.
  
  Revision  Changes    Path
  1.3       +27 -0     db-torque/xdocs/criteria-howto.xml
  
  Index: criteria-howto.xml
  ===================================================================
  RCS file: /home/cvs/db-torque/xdocs/criteria-howto.xml,v
  retrieving revision 1.2
  retrieving revision 1.3
  diff -u -r1.2 -r1.3
  --- criteria-howto.xml	16 Jul 2002 10:32:31 -0000	1.2
  +++ criteria-howto.xml	5 Dec 2003 05:04:37 -0000	1.3
  @@ -5,6 +5,7 @@
    <properties>
     <title>Criteria Howto</title>
     <author email="criley@ekmail.com">Cameron Riley</author>
  +  <author email="seade@backstagetech.com.au">Scott Eade</author>
    </properties>
   
    <body>
  @@ -207,6 +208,32 @@
        greater than $1000. The other comparitors work similarly and can be used in
        the same manner though many of the comparators are present as methods in
        the Criteria Object already, such as the Joins.
  +   </p>
  +
  +  </section>
  +
  +  <section name="Case insensitive LIKE Comparator">
  +
  +   <p>
  +     A LIKE comparison is usually case sensitive (unless the underlying 
  +     database only provides case sensitive LIKE clauses - e.g. MySQL).  To get
  +     a case insensitive LIKE you need to tell the criteria that it should 
  +     ignore the case thus:
  +   </p>
  +
  +<source>
  +Criteria criteria = new Criteria();
  +criteria.add(InvoicePeer.TABLE_NAME, searchField, 
  +        (Object) ("%" + searchCriteria + "%"), Criteria.LIKE);
  +criteria.getCriterion(InvoicePeer.TABLE_NAME, searchField).setIgnoreCase(true);
  +
  +List invoices = InvoicePeer.doSelect(criteria);
  +</source>
  +
  +   <p>
  +     For PostgreSQL this will use ILIKE, for other databases it will use the 
  +     SQL upper() function on the column and search string (for Oracle you may
  +     want to define a function index to make this efficient).
      </p>
   
     </section>
  
  
  
  1.119     +3 -0      db-torque/xdocs/changes.xml
  
  Index: changes.xml
  ===================================================================
  RCS file: /home/cvs/db-torque/xdocs/changes.xml,v
  retrieving revision 1.118
  retrieving revision 1.119
  diff -u -r1.118 -r1.119
  --- changes.xml	25 Nov 2003 19:19:06 -0000	1.118
  +++ changes.xml	5 Dec 2003 05:04:37 -0000	1.119
  @@ -52,6 +52,9 @@
           TRQS149: For PostgreSQL the LONGVARCHAR datatype now maps to 
           <em>text</em> rather than <em>bytea</em>.
         </action>
  +      <action dev='seade' type='update'>
  +        Case insensitive LIKE comparisons now use ILIKE for PostgreSQL.
  +      </action>
       </release>
     </body>
   </document>
  
  
  
  1.6       +5 -1      db-torque/src/java/org/apache/torque/util/SqlEnum.java
  
  Index: SqlEnum.java
  ===================================================================
  RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/SqlEnum.java,v
  retrieving revision 1.5
  retrieving revision 1.6
  diff -u -r1.5 -r1.6
  --- SqlEnum.java	14 Oct 2002 01:44:08 -0000	1.5
  +++ SqlEnum.java	5 Dec 2003 05:04:37 -0000	1.6
  @@ -96,6 +96,10 @@
           new SqlEnum(" LIKE ");
       public static final SqlEnum NOT_LIKE =
           new SqlEnum(" NOT LIKE ");
  +    public static final SqlEnum ILIKE =
  +        new SqlEnum(" ILIKE ");
  +    public static final SqlEnum NOT_ILIKE =
  +        new SqlEnum(" NOT ILIKE ");
       public static final SqlEnum IN =
           new SqlEnum(" IN ");
       public static final SqlEnum NOT_IN =
  
  
  
  1.25      +23 -6     db-torque/src/java/org/apache/torque/util/SqlExpression.java
  
  Index: SqlExpression.java
  ===================================================================
  RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/SqlExpression.java,v
  retrieving revision 1.24
  retrieving revision 1.25
  diff -u -r1.24 -r1.25
  --- SqlExpression.java	27 Aug 2003 22:50:11 -0000	1.24
  +++ SqlExpression.java	5 Dec 2003 05:04:37 -0000	1.25
  @@ -63,6 +63,7 @@
   import org.apache.commons.lang.StringUtils;
   import org.apache.torque.TorqueException;
   import org.apache.torque.adapter.DB;
  +import org.apache.torque.adapter.DBPostgres;
   import org.apache.torque.om.DateKey;
   import org.apache.torque.om.ObjectKey;
   import org.apache.torque.om.StringKey;
  @@ -294,7 +295,9 @@
           }
   
           if (comparison.equals(Criteria.LIKE)
  -                || comparison.equals(Criteria.NOT_LIKE))
  +                || comparison.equals(Criteria.NOT_LIKE)
  +                || comparison.equals(Criteria.ILIKE)
  +                || comparison.equals(Criteria.NOT_ILIKE))
           {
               buildLike(columnName, (String) criteria, comparison,
                          ignoreCase, db, whereClause);
  @@ -390,11 +393,25 @@
                              DB db,
                              StringBuffer whereClause)
       {
  -        // If selection is case insensitive use SQL UPPER() function
  -        // on column name.
  +        // If selection is case insensitive use ILIKE for PostgreSQL or SQL 
  +        // UPPER() function on column name for other databases.
           if (ignoreCase)
           {
  -            columnName = db.ignoreCase(columnName);
  +            if (db instanceof DBPostgres)
  +            {
  +                if (comparison.equals(Criteria.LIKE))
  +                {
  +                    comparison = Criteria.ILIKE; 
  +                }
  +                else if (comparison.equals(Criteria.NOT_LIKE))
  +                {
  +                    comparison = Criteria.NOT_ILIKE; 
  +                }
  +            }
  +            else
  +            {
  +                columnName = db.ignoreCase(columnName);
  +            }
           }
           whereClause.append(columnName);
   
  @@ -445,7 +462,7 @@
           // If selection is case insensitive use SQL UPPER() function
           // on criteria.
           String clauseItem = sb.toString();
  -        if (ignoreCase)
  +        if (ignoreCase && !(db instanceof DBPostgres))
           {
               clauseItem = db.ignoreCase(clauseItem);
           }
  
  
  
  1.43      +7 -1      db-torque/src/java/org/apache/torque/util/Criteria.java
  
  Index: Criteria.java
  ===================================================================
  RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/Criteria.java,v
  retrieving revision 1.42
  retrieving revision 1.43
  diff -u -r1.42 -r1.43
  --- Criteria.java	24 Jun 2003 09:47:30 -0000	1.42
  +++ Criteria.java	5 Dec 2003 05:04:37 -0000	1.43
  @@ -124,6 +124,12 @@
       public static final SqlEnum NOT_LIKE = SqlEnum.NOT_LIKE;
   
       /** Comparison type. */
  +    public static final SqlEnum ILIKE = SqlEnum.ILIKE;
  +
  +    /** Comparison type. */
  +    public static final SqlEnum NOT_ILIKE = SqlEnum.NOT_ILIKE;
  +
  +    /** Comparison type. */
       public static final SqlEnum CUSTOM = SqlEnum.CUSTOM;
   
       /** Comparison type. */
  
  
  

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message