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

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.