SQLite dialect error with Hibernate

Question

I have been trying to use the hibernate dialect for SQLite from http://code.google.com/p/hibernate-sqlite/ in a project, but have hit a problem with something like the following...

 SQLQuery query = session
   .createSQLQuery("select id from collection where collection = (:collection_name);");

 query.setParameter("collection_name", collectionName);
 Integer collectionId = (Integer) query.uniqueResult();

What I expect is that if there is no matching row for my query, that collectionId would be set to null. What I actually get is an exception as follows...

Exception in thread "main" org.hibernate.MappingException: No Dialect mapping for JDBC type: 0
 at org.hibernate.dialect.TypeNames.get(TypeNames.java:79)
 at org.hibernate.dialect.TypeNames.get(TypeNames.java:104)
 at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:395)
 at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:582)
 at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:508)
 at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:524)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1821)
 at org.hibernate.loader.Loader.doQuery(Loader.java:697)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
 at org.hibernate.loader.Loader.doList(Loader.java:2232)
 at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
 at org.hibernate.loader.Loader.list(Loader.java:2124)
 at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
 at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1657)
 at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
 at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
 at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:835)
 at uniqueResult line of code above.

Having looked at the actual dialect, that's not totally surprising as it looks like JDBC type 0 is null, and the dialect contains the following function with the null mapping commented out...

public SQLiteDialect() {
 super();
 registerColumnType(Types.BIT, "integer");
                // ...lots more type registers...
 registerColumnType(Types.LONGVARBINARY, "blob");
 // registerColumnType(Types.NULL, "null");
 registerColumnType(Types.BLOB, "blob");
                // ...more column types and function registers...
}

Unfortunately, the obvious step of uncommenting the mapping for null does not seem to change the behavior.

Can any point out what I'm doing wrong, suggest how to fix the dialect, or recommend any good resources the read on writing a Hibernate dialect?

Answer 1

Interesting. The exception is thrown because Dialect can't locate an appropriate hibernate type rather than column type; you would normally use registerHibernateType() method to register it.

However, you can't do that with Types.NULL because there's no corresponding Hibernate type (NULL really signifies a "generic" type to begin with). And I don't think that's the source of the problem.

I've never worked with SQLite, but your query:

select id from collection where collection = (:collection_name)

looks rather curious; that's definitely not a standard SQL. For unmapped SQL queries Hibernate tries to auto-detect the type of result using metadata and in this case it can't (possibly because SQLite metadata doesn't provide correct type info?)

Possible work-arounds are:

1. Use SQLQuery.addScalar(alias, Type) to explicitly specify the type of result.
2. Rewrite your query as HQL if possible; your entity mappings will provide the necessary type information.