Unit Testing is an integral part of any software development cycle. A large project involves many database systems, each separated by its role in business systems. Also many services are spawned relying on the database to provide refined results based on client input. As each database may reside on different machines, they require multiple connections. Further schema for each business operation may vary and would have highly normalized set of tables. Normalization though good for reducing redundancy, unfolds a new hurdle of inter-dependency between the tables increasing the effort to insert dummy records for testing purposes. Such case gets worse when the records are referenced between tables, across multiple databases. Inserting records for test purposes would suffice to resolve such issues, but in a large co-operations with numerous cross-continent teams working in collaboration requiring to access the same database, makes it hard to ensure records stay untouched. One work around would be to insert and delete the records for each test, but it not only keeps the connection occupied for unit tests but makes the database fragmented with sequences running out of their range. The basic need for a database for a unit test is to ensure the logic of insertion and fetching of records work as expected along with the queries. The content of the records though need not vary for the assertions in the Junit to work. In such circumstances there is nothing more helpful than to setup an in-memory database.
HSQLDB is an ideal choice for in-memory embeddable database, especially for java applications as its written in Java and it integrates well with Spring, Hibernate and JPA. Also it support most of the SQL-2008 standards and provides a fast lightweight database alternative for in-memory usage. The default username is "SA" and default password is empty. In case a new user id and password is provided, hibernate creates the new user but does not provide the necessary privileges to insert in the created tables. In such case, privileges must be granted by executing the HQLDB query such as "GRANT ALL ON SCHEMA_NAME.TABLE_NAME TO PUBLIC". Hence it is preferred to use super admin account for all the in-memory database operations.
The jdbc url for in-memory database starts with "jdbc:hsqldb:mem:" were "mem" signifies in-memory database protocol identifier. HSQLDB also has other protocol identifiers as follows:
- mem: stored entirely in RAM - without any persistence beyond the JVM process's life
- file: stored in filesystem files
- res: stored in a Java resource, such as a Jar and always read-only
- hsql and hsqls: connects to a local or in network database server. The host and port specify the IP address or host name of the server and an optional port number. The database to connect to is specified by an alias which is a lowercase string defined in the server.properties file .
- http and https: connects to a remote database server based on network domain similar as above.
In the hibernate configuration of HqlDB, the hibernate.hbm2ddl.auto property automatically validates or exports schema DDL to the database when the SessionFactory is created. With create-drop option, the database schema will be created when SessionFactory is initialized and dropped when the SessionFactory is closed explicitly. Hibernate uses the hbm mappings generated from the database to create the tables for the in-memory database schema. This feature makes it easier to load the database without generating complex sql scripts. Here are the other possible options for the hibernate.hbm2ddl.auto property:
- validate: validates the schema, makes no changes to the database.
- update: updates the schema.
- create: creates the schema, destroying the previous data.
- create-drop: creates schema at the start of session and drops the schema at the end of the session.
The hibernate.cache.use_query_cache property is used to enable caching of query result sets. This is only useful for queries that run frequently with the same parameters. The query cache doesn't cache the state of the actual entities in the result set; but only caches the identifier values and results of value type.
In hibernate world, a session is a transaction-level cache of persistent data which is also known as second-level cache. The second-level cache exists as long as the session is alive and holds all the data for properties and associations for individual entities which are marked to be cached. The hibernate.cache.use_second_level_cache property can be used to enable and disable second level caching which is enabled by default. The hibernate.cache.provider_class property tells the hibernate the caching implementation to use, which implements the org.hibernate.cache.CacheProvider class. Hibernate is bundled with a number of built-in integrations with the open-source cache providers (including provider.org.hibernate.cache.EhCacheProvider) which can be used as the hibernate cache providers. The hibernate.cache.region.factory_class property specifies the implementation to be used to build the second level cache regions. Ehcache is a widely used open source java cache used for general purpose caching and in light weight containers. Since Hibernate 2.1, Hibernate has included an Ehcache CacheProvider and it is periodically synced up with the provider in the Ehcache Core distribution. It is required to specify both the EHCache Provider and the RegionFactory in order to avoid exceptions such as "org.hibernate.cache.NoCachingEnabledException: Second-level cache is not enabled". Below is the sample hibernate spring configuration for in-memory database for HQLDB:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbc.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" depends-on="hsqlSchemaCreator">
<property name="configLocation">
<value>classpath:measurementapicore-hibernate-mapping/hibernate.cfg.xml</value>
</property>
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.format_sql">false</prop>
<prop key="hibernate.lazy">false</prop>
<prop key="hibernate.pretty">true</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">net.sf.ehcache.hibernate.EhCacheProvider</prop>
<prop key="hibernate.cache.region.factory_class">net.sf.ehcache.hibernate.EhCacheRegionFactory</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.hbm2ddl.auto">create-drop</prop>
<prop key="hibernate.connection.autocommit">true</prop>
</props>
</property>
</bean>
<bean id="hsqlSchemaCreator" class="com.emprovise.configuration.HSQLSchemaCreator">
<property name="dataSource" ref="dataSource" />
<property name="schema" value="CONFIG, SHRDM" />
</bean>
<jdbc:embedded-database id="embedded" type="HSQL"/>
<jdbc:initialize-database data-source="dataSource">
<jdbc:script location="classpath:dbschema/shrdm_data_setup.sql"/>
<jdbc:script location="classpath:dbschema/config_data_setup.sql"/>
</jdbc:initialize-database>
Although the above configuration (except the hsqlSchemaCreator Bean) is sufficient for the Database were all tables reside in single schema, it does bring some challenges when trying to load tables from multiple schemas. Hibernate allows to configure the default schema by setting the hibernate.default_schema property which is used by the HQLDB In-Memory database. But if we refer tables from different schemas, we add "schema" attribute to the hibernate-mapping for criteria queries to work. The named queries also use the schema name followed by the table name to refer to corresponding table in the schema. The hibernate create-drop feature uses the hibernate default_schema name as the schema for all the tables created if none of the tables have schema attribute set in the hibernate mapping files. When the schema attribute is set for all the tables in the hibernate mapping files, the default_schema property is overridden. While creating the HQL database in such case hibernate expects the all the schemas to be already present and won't create the schema before creating the tables. As a result it throws errors such as table does not exists, invalid schema name etc when trying to create the tables in non existing schema and fails in any query. The solution for this problem is provided by one of the fellow blogger. A new class called schemaCreator is created a below along with the corresponding bean, and is referred in the "depends-on" attribute of the sessionFactory bean in order for the spring container to create the dependent bean before the sessionFactory bean. The schemaCreator takes schema values as comma separated parameters and creates the schema using plain old spring jdbc template.
public final class HSQLSchemaCreator implements InitializingBean {
private String schema;
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;
}
public void afterPropertiesSet() throws Exception {
if(schema != null) {
StringTokenizer stringTokenizer = new StringTokenizer(schema, ",");
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
while(stringTokenizer.hasMoreTokens()) {
String nextToken = stringTokenizer.nextToken();
jdbcTemplate.execute("CREATE SCHEMA " + nextToken + " AUTHORIZATION DBA");
}
}
}
}
No comments:
Post a Comment