hibernate.hbm2ddl.auto + custom sql-type in Oracle

Question

I'm trying to add the ability for JUnit tests to a legacy codebase that already uses Spring (2.5.6), Hibernate (3.2.6) and an Oracle 11g DBMS. I want Hibernate to generate all tables in an HSQLDB in-memory database. Unfortunately, the existing Hibernate mapping uses user-defined datatypes of the Oracle DB that prevent using a HSQLDB instead of the Oracle DB.

Here's my application-context-test.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:mydb" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.current_session_context_class">thread</prop>
            <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>

            <prop key="hibernate.cache.use_second_level_cache">false</prop>

            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">create</prop>
        </props>
    </property>

    <property name='mappingResources'>
        <list>
            <value>first.hbm.xml</value>
            <value>second.hbm.xml</value>
        </list>
    </property>
</bean>

That works great for most of my business objects. Unfortunately the table generator seems to be unable to handle user-defined types that some of my business objects use. This hbm.xml file won't work until I delete the "sql-type" - attribute for the varchar2:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="first"
    table="FIRST">

<!-- ... -->
<property name="someBoolean" type="yes_no">
    <column name="SOME_BOOLEAN" length="1" sql-type="varchar2"></column>
<!-- ... -->
</class>
</hibernate-mapping>

Also, default-values for these user-types won't work:

     <property name="someOtherBoolean" type="yes_no">
        <column name="SOME_OTHER_BOOLEAN" length="1" not-null="true" default="no"/>
     </property>

Deleting these user-types will cause the application to continue working but the Schema-Validation (validate) to fail with a

Wrong column type ... Found: varchar2, expected: char1(1 char)

How can I generate my tables while keeping the user-defined types? Thanks!

EDIT: I tried activating the support for Oracle-syntax by adding "sql.syntax_ora=true" to my application-context-test.xml:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:mydb;sql.syntax_ora=true" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

However, nothing changed. This option either doesn't support user-defined types or I failed to apply it correctly.

EDIT 2: I just used the latest snapshot from HSQLDB (2.3.0) instead of the old 1.8.1.3. That changed the mapping errors, but didn't solve them. For example, now I have

unexpected token: NO

at the following mapping:

     <property name="someOtherBoolean" type="yes_no">
        <column name="SOME_OTHER_BOOLEAN" length="1" not-null="true" default="no"/>
     </property>

In conclusion: The latest release of HSQLDB also doesn't have propper Oracle support.

EDIT 3: Just wanted to update to Hibernate 4.1.1 because the used 3.2.6 may be too old. Sadly, there are incompatibilities I can't resolve right now. Would be interesting if the upgrade would solve my problem - Can anyone generate the tables with the given Oracle attributes using a 4.x version of Hibernate?

Answer 1

To avoid validation message, columnDefinition = "varchar2(1)" was helpful for me:

@Column(name="BOOLEAN_COLUMN", columnDefinition = "varchar2(1)")
@Type(type = "yes_no")
private Boolean booCol;