Schema Creator for HSQLDB in Unit Testing Hibernate

I am working on an application with Spring, Hibernate and MySQL database. There are several options I can unit test Hibernate DAOs. One is to use EasyMock, but it won't really test the queries. Another one is to Spring AbstractTransactionalJUnit4SpringContextTests, connecting to a test instance of MySQL. But then the tests rely on the external resource. As an alternative, I want to use a in-memory HSQLDB during unit testing to cut the dependency. The database will be created and dropped during the tests with Hibernate property "hibernate.hbm2ddl.auto=create-drop".

Sounds great. However, here is the catch. Basically, when using in-memory HSQLDB to test Hibernate with hibernate.hbm2ddl.auto=create-drop option, Hibernate does not know to create the schema BEFORE creating tables. Thus it leads to "invalid schema name" error in unit tests.

There is an ingenious solution. It created a schema creator and define it as a "depends-on" for Hibernate session factory in Spring configuration file. It thus forces the schema creation.

public final class HSQLSchemaCreator implements InitializingBean {

    /**
     * schema name.
     */
    private String schema;

    /**
     * data source.
     */
    private DataSource dataSource;

    // setters and getters
    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;
    }

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

}

    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"
        depends-on="hsqlSchemaCreator">
              ...
    </bean>

    <bean id="hsqlSchemaCreator" class="com.guident.skills.util.HSQLSchemaCreator">
        <property name="dataSource" ref="dataSource" />
        <property name="schema" value="MY_SCHEMA" />
    </bean>