Using @Table with schema name in Hibernate 3.3.1ga and HSQLDB

Question

How can I make this work in unit tests using Hibernate 3.3.1ga and HSQLDB:

@Entity
@Table(name="CATEGORY", schema="TEST")
public static class Category { ... }

The problem is that Hibernate expects the schema to exist. The second problem is that Hibernate issues the CREATE TABLE TEST.CATEGORY before any of my code runs (this happens deep inside Spring's test setup), so I can't get a connection to the DB before Hibernate and create the schema manually.

But I need the schema because I have to access different databases in the real code. What should I do?

Hibernate 3.3.1ga, HSQLDB, Spring 2.5

Answer 1

My current solution looks like this:

@Override
protected String[] getConfigLocations() {
    createHSQLDBSchemas ();

    return new String[]{
            "test-spring-config.xml"
    };
}

private static boolean hsqldbSchemasCreated = false;

public static void createHSQLDBSchemas ()
{
    if (hsqldbSchemasCreated)
        return;

    try
    {
        log.info ("createHSQLDBSchemas");

        Class.forName("org.hsqldb.jdbcDriver").newInstance();
        Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:test", "sa", "");
        Statement stmt = c.createStatement ();

        String sql;
        sql = "CREATE SCHEMA xxx AUTHORIZATION DBA";
        log.info (sql);
        stmt.execute (sql);

        stmt.close ();
        c.close ();
    }
    catch (Exception e)
    {
        throw new ShouldNotHappenException (e);
    }

    hsqldbSchemasCreated = true;
}

but that feels like a really ugly hack. Isn't there a better solution?

Answer 2

It looks to me like you have a reproducible bug in the Hibernate DDL creation code. You should report a bug - it's a long term solution but it's the way things are done in open source. Of course you might want to produce a patch, but I never found the Hibernate code base easy to hack.

Answer 3

You could write a class that implements InitializingBean:

public class SchemaCreator implements InitializingBean {

    private String schema;
    private DataSource dataSource;

    public String getSchema() {
        return schema;
    }

    public void setSchema(String schema) {
        this.schema = schema;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("CREATE SCHEMA " + schema + " AUTHORIZATION DBA");
    }

}

You then have to define a bean in your bean definition file of this class (I'm taking a shot in the dark as to what your existing bean definitions look like).

<bean id="dataSource" class="...">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
    <property name="url" value="jdbc:hsqldb:mem:test"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
</bean>

<bean id="sessionFactory" depends-on="schemaCreator" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    ...
</bean>

<bean id="schemaCreator" class="SchemaCreator">
    <property name="dataSource" ref="dataSource"/>
    <property name="schema" value="TEST"/>
</bean>

By using the depends-on attribute of Hibernate's bean, Spring will ensure that the schemaCreator bean will be initialized first, causing the schema to exist just in time. This should also make your intentions clearer.