Hibernate Query using Enum as Parameter

Question

I'm having no luck getting a hibernate (using HSQLDB) query to work. The query code looks like:

Query query = session.createQuery("from "+tableName+" where CURRENCY = :currency");
query.setParameter("currency",currency);
List<ExchangeRate> list = query.list();

I consistently get "Caused by: org.hsqldb.HsqlException: incompatible data type in conversion":

org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.loader.Loader.doList(Loader.java:2529)
    at org.hibernate.loader.Loader.doList(Loader.java:2512)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    at org.hibernate.loader.Loader.list(Loader.java:2337)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1275)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at com.rockymountaineer.wsapi.db.test.ExchangeRateDAOTest.getRate(ExchangeRateDAOTest.java:27)
    at com.rockymountaineer.wsapi.db.test.ExchangeRateDAOTest.main(ExchangeRateDAOTest.java:39)
Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setBytes(Unknown Source)
    at org.hibernate.type.descriptor.sql.VarbinaryTypeDescriptor$1.doBind(VarbinaryTypeDescriptor.java:57)
    at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:93)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:280)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:275)
    at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:66)
    at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:612)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1875)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    at org.hibernate.loader.Loader.doQuery(Loader.java:900)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    at org.hibernate.loader.Loader.doList(Loader.java:2526)
    ... 10 more
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.types.NumberType.convertToDefaultType(Unknown Source)
    ... 24 more

The class is annotated like so:

@Column(name="CURRENCY", nullable=false)
public CurrencyType getCurrency() {
    return currency;
}

...and the Enum type looks like:

public enum CurrencyType {
    CAD, AUD, EUR, GBP, USD;    
    /**
     * @param currency
     * @return
     */
    public static CurrencyType getByCurrency(String currency) {
        if(currency!=null) {
            for(CurrencyType type : CurrencyType.values()) {
                if(type.name().equals(currency)) return type;
            }
        }
        return null;
    }
}

From what I understand by the Hibernate documentation, this should totally work - not to mention that I am currently able to save, edit, delete, query (by id) objects using other hibernate methods - but the "createQuery" is proving stubborn.

If anyone can help I'd sincerely appreciate it! Cheers,

Alex

Answer 1

...ok - figured it out. In case anyone else is confused, here is what I discovered. It seems that by default, the table is created using the ordinal value (in my case CurrencyType.ordinal()) so the column ends up looking like CURRENCY INTEGER NOT NULL when the table is created.

This is less than ideal as if I change the Enum type (i.e. the order of the values), this will break everything. The good news is I can force it to save the String value (CurrencyType.name()) by adding a @Enumerated(EnumType.STRING) to the method, so it will look like:

    @Enumerated(EnumType.STRING)
    @Column(name="CURRENCY", nullable=false)
    public CurrencyType getCurrency() {
        return currency;
    }

...now to re-enter all the data.