Tuesday, November 3, 2009

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>

Monday, November 2, 2009

DDL in Hibarenate Mapping File

If you set Hibernate property "hibernate.hbm2ddl.auto" to "create", Hibernate can automatically create the schema with the mapping files. Great! But what if you have things like triggers? It turns out you can add the specific DDL to element in the mapping file for this purpose.

<hibernate-mapping>
<class>
...
</class>

<database-object>
<create>
[CREATE statement]
</create>
<drop>
[DROP statement]
</drop>
<dialect-scope name="org.hibernate.dialect.HSQLDialect" />
</database-object>
</hibernate-mapping>


The elements restrict the custom CREATE or DROP statements to
a particular set of configured database dialects, which is useful if you’re deploying
on several systems and need different customizations.