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="[EMAIL PROTECTED]">Cameron Riley</author>
+ <author email="[EMAIL PROTECTED]">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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
