I've got a CLOB in my Oracle 10g database against which I'm doing a Restrictions.like(), which works fine. However, when I add a call to ignoreCase() to the resulting Criterion object, my search text is lower-cased, but nothing is done to the CLOB, which causes my query to fail unless the search text is present in the CLOB in all lower-case.
Looking at the code for SimpleExpression.toSqlString(), it becomes clear why nothing is done to the CLOB: the call to make it lower-case is done only when the type of the column in question is a VARCHAR or a CHAR. Looking at getTypedValues(), the value is lower-cased regardless of the type of the column, which seems like a worst-case scenario, since you won't even get results on an exact match of your search string if that string contains capital letters.
Code:
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException
{
String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);
StringBuffer fragment = new StringBuffer();
if (columns.length>1) fragment.append('(');
SessionFactoryImplementor factory = criteriaQuery.getFactory();
int[] sqlTypes = type.sqlTypes( factory );
for ( int i=0; i<columns.length; i++ ) {
boolean lower = ignoreCase && ( sqlTypes[i]==Types.VARCHAR || sqlTypes[i]==Types.CHAR );
if (lower) {
fragment.append( factory.getDialect().getLowercaseFunction() ) .append('(');
}
fragment.append( columns[i] ); if (lower) fragment.append(')');
fragment.append( getOp() ).append("?");
if ( i<columns.length-1 ) fragment.append(" and ");
}
if (columns.length>1) fragment.append(')');
return fragment.toString();
}
So the question is, why does the code not apply the lowercase function for CLOBs? If I force Hibernate to apply the function in my Oracle 10g database, it works properly (if I use the debugger to set the value of sqlTypes[i] to the value of Types.VARCHAR during the appropriate pass through the loop, it generates a query that runs fine and returns the expected results). Does it fail for some other database or for another version of Oracle such that it couldn't be applied universally? Is there some other reason why CLOBs can't be included in the list of types for which the lowercase function can be applied?
Thanks,
Tim
Login
FAQ
Search