Using ignoreCase with number types

TheXFed · **Posted:** Fri Jan 20, 2006 10:33 am

Description of problem

[SimpleExpression.java]

Code:

    public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery)
    throws HibernateException {
        Object icvalue = ignoreCase ? value.toString().toLowerCase() : value;
        return new TypedValue[] { criteriaQuery.getTypedValue(criteria, propertyName, icvalue) };
    }

In my case, I'm working with a BigDecimal field. I also have the ignoreCase flag set on the SimpleExpression (it was set from the framework we built around hibernate criteria/expressions). So what happens in the first line of this routine, is that the BigDecimal value gets converted to a String. This ends up failing later on in BigDecimalType#set(PreparedStatement st, Object value, int index), since it tries to convert the value to a BigDecimal.

Now I was able to successfully modify our framework to fix the default setting for ignoreCase (we did have it default to true), which in the end solved the problem. My recommendation in coming across this, is that the getTypedValues should somehow only toString().toLowerCase() the 'value' if it's really a string type. That way all number types (and possibly others) won't be affected if ignoreCase happens to be set.

Also, when I bumped up the Hibernate logging, the only indication that there was a problem with the parameter was this:
09:13:33,562 DEBUG org.hibernate.type.BigDecimalType:79 - binding '3.00' to parameter: 1
09:13:41,171 INFO org.hibernate.type.BigDecimalType:89 - could not bind value '3.00' to parameter: 1
Maybe I'm just being picky, but I would think that would be at least a warning.

Hibernate version:
3.1

Mapping documents:

Code:

...
        <property
            name="originalCurrencyAmount"
            type="java.math.BigDecimal"
            update="true"
            insert="true"
            column="originalCurrencyAmount"
        />
...

Code between sessionFactory.openSession() and session.close():

Code:

Criteria criteria = getSession().createCriteria(Transaction.class);
criteria.add(Restrictions.ge("originalCurrencyAmount", new BigDecimal("3.00")).ignoreCase());
List results = criteria.list();

Full stack trace of any exception that occurs:

Code:

java.sql.SQLException: Parameter #1 has not been set.
   at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:500)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:664)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2150)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1492)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:298)
   at com.chessys.trecs.util.FedsTest.testFeds(FedsTest.java:27)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

Name and version of the database you are using:
SqlServer 8.00.194

The generated SQL (show_sql=true):
(I've shortened up this sql statement since there's so many columns involved)
select this_.tranId as tranId18_0_, ...... this_.originalCurrencyAmount as origina26_18_0_, ...... from Transactions this_ where this_.originalCurrencyAmount>=?

Code:

TheXFed · **Posted:** Tue Jan 24, 2006 12:46 pm

I guess what I'm looking for, is something similar to what's provided in the toSqlString(...) method of SimpleExpression.

Code:

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(')');

This code is checking to make sure we're dealing with string-type values before it adds the lowercase function. Is there a way getTypedValues(...) can check the type of the "value" field before calling value.toString().toLowerCase()?

Should this be added to JIRA?