source : http://tech.shopzilla.com/2010/05/unit-tests-with-h2-and-ibatis/
Unit tests with H2 and iBATIS
Posted by Steve Corwin Friday, May 7th 2010
Since my previous post on Using HSQLDB for unit testing database access code I began work on a project that needed to use iBATIS instead of JPA or Hibernate for database access. Based on a recommendation from my previous post, I decided to try H2 instead of HSQLDB as the in-memory database. This project still uses Oracle and Spring.
H2 proved much easier when the production database is Oracle. I was able to copy DDL extracted from the Oracle database and use it (almost) unmodified in H2. The DDL ran without error in all cases I’ve tried, including “created_date date default sysdate not null”. There were a couple of issues:
1. there was a Java Boolean being stored in a 1-character varchar column. The Oracle JDBC driver translates to “0″ or “1″, which fits. The H2 JDBC driver translates to “true” or “false”. I just changed the column to “varchar2(5 char)” in the test code.
2. date columns in H2 don’t store the time, only the date (no hours, minutes, seconds). I just changed the column to a timestamp in the test code.
(I did try H2’s Oracle compatibility mode, but it didn’t make a difference.)
iBATIS was not so pleasant for me. Because of some complexities in this service, which needs to effectively combine operations on two different database connections into one transaction, I decided to use iBATIS manual transaction demarcation. This looks simple, but wasn’t so easy to get working. The iBATIS javadocs show manual transactions as a straightforward pattern:
view sourceprint?1 try {
2 sqlMap.startTransaction();
3 // do work
4 sqlMap.commitTransaction();
5 } finally {
6 sqlMap.endTransaction();
7 }
However there is a subtlety: depending on the SqlMapClient configuration, the commitTransaction() and endTransaction() methods may not do anything at all. This was the configuration we were getting from our existing Spring config files, and the result was that none of my changes were committed to the database. Apparently something was defaulting to having the transactions handled by com.ibatis.sqlmap.engine.transaction.external.ExternalTransaction. Looking at that source you can see that the commit() method has an empty body.
In order to have my transactions commit, I needed to use the class com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransactionConfig instead. In this code the commit() method calls commit() on the underlying JDBC connection.
Using the correct class is just a matter of setting the fully-qualified class name as the value of the “transactionConfigClass” property for the SqlMapClient instance. Here is an example from applicationContext.xml:
view sourceprint?01 <bean id="sqlMapClient"
02 class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
03 <qualifier value="account"/>
04 <property name="configLocation">
05 <value>classpath:P13NSqlMapConfig.xml</value>
06 </property>
07 <property name="dataSource" ref="masterDataSource" />
08 <!-- without this setting the commitTransaction() method does not
09 commit -->
10 <property name="transactionConfigClass" value=
11 "com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransactionConfig" />
12
13 </bean>
Note that you also need to have the connections set to not autocommit. For Oracle this seems to be the default.
Using this unit-testing technique requires completing three steps:
1. Create a class that will create the tables and (optionally) populate with test data.
2. Create a unit test that will use DbTestUtils to test a DAO implementation.
3. Supply a DataSource to the DAO/class under test.
Most of what follows is the same as it was for HSQLDB & Hibernate; the only real difference is the config file, which now supplies iBATIS objects instead of Hibernate/JPA. After some thought I decided to include the sections that haven’t changed so that this post could (hopefully) stand on its own.
1. Create a class that will create the tables and (optionally) populate with test data.
view sourceprint?01 public class DbTestUtils {
02 public static void setupTables(Connection connection) throws SQLException {
03
04 dropTable(connection, "activation_code_mapping");
05
06 executeSql(connection, "create table activation_code_mapping ("
07 + "activation_code varchar2(80 char) not null, "
08 + "email_address varchar2(500 char) not null, "
09 + "source varchar2(25 char) not null, " + "merchant_id number(38), "
10 + "historical_merchant_name varchar2(1020 char), "
11 // date columns in H2 don't store the time, only the date, so I changed it to
12 // timestamp
13 + "created_date timestamp default sysdate not null,"
14 + "constraint pk_activation_code_mapping primary key (activation_code)" + ")");
15 }
16
17 public static void dropSequence(Connection connection, String sequence) {
18 try {
19
20 executeSql(connection, "drop sequence " + sequence);
21 connection.commit();
22 } catch (SQLException se) {
23 // ignore exception
24 // System.out.println(se);
25 }
26 }
27
28 public static void dropTable(Connection connection, String table) {
29 try {
30
31 executeSql(connection, "drop table " + table);
32 connection.commit();
33 } catch (SQLException se) {
34 // ignore exception
35 // System.out.println(se);
36 }
37
38 }
39
40 public static void executeSql(Connection connection, String sql) throws SQLException {
41
42 Statement statement = connection.createStatement();
43 statement.execute(sql);
44 }
45 }
2. Create a unit test that will use DbTestUtils to test a DAO implementation.
view sourceprint?001 public class ActivationCodeMappingCacheStoreTest extends AbstractDependencyInjectionSpringContextTests {
002
003 private DataSource dataSource;
004 private ActivationCodeMappingCacheStore activationCodeMappingCacheStore;
005
006 @Override
007 protected String getConfigPath() {
008 return "unit-test-config.xml";
009 }
010
011 @Required
012 public void setDataSource(DataSource dataSource) {
013 this.dataSource = dataSource;
014 }
015
016 @Required
017 public void setActivationCodeMappingCacheStore(
018 ActivationCodeMappingCacheStore activationCodeMappingCacheStore) {
019 this.activationCodeMappingCacheStore = activationCodeMappingCacheStore;
020 }
021
022 @Override
023 protected void onSetUp() {
024
025 try {
026 Connection connection = dataSource.getConnection();
027 connection.setAutoCommit(false);
028
029 DbTestUtils.setupTables(connection);
030
031 insertTestData(connection);
032
033 // simple test to verify that the correct data was inserted into the database
034 Statement statement = connection.createStatement();
035 ResultSet resultSet = statement.executeQuery("select * from activation_code_mapping");
036
037 assertTrue(resultSet.next());
038 assertEquals("4fe801", resultSet.getString("activation_code"));
039
040 connection.commit();
041
042 connection.close();
043
044 } catch (SQLException se) {
045 throw new RuntimeException(se);
046 }
047 }
048
049 private void insertTestData(Connection connection) throws SQLException {
050
051 String sql = "insert into activation_code_mapping(activation_code, email_address, source, merchant_id, historical_merchant_name)"
052 + "values ('4fe801', 'arthur@hgttg.com', '0001', 1234567, 'Slartibartfast')";
053 DbTestUtils.executeSql(connection, sql);
054 }
055
056 public void testLoad() {
057
058 // test load
059 String key = "4fe801";
060 Object foundObject = activationCodeMappingCacheStore.load(key);
061 assertNotNull(foundObject);
062
063 ActivationCodeMapping activationCodeMapping = (ActivationCodeMapping) foundObject;
064
065 // verify columns including date
066 assertEquals("4fe801", activationCodeMapping.getActivationCode());
067 assertEquals("arthur@hgttg.com", activationCodeMapping.getEmailAddress());
068 assertEquals("0001", activationCodeMapping.getSource());
069 assertEquals(1234567L, activationCodeMapping.getMerchantId().longValue());
070 assertEquals("Slartibartfast", activationCodeMapping.getHistoricalMerchantName());
071
072 assertNotNull(activationCodeMapping.getCreatedDate());
073 assertEquals(1, activationCodeMapping.getImplVersion());
074 }
075
076 public void testStore() {
077
078 final String activationCode = "1234";
079
080 // test insert
081 ActivationCodeMapping activationCodeMappingForInsert = new ActivationCodeMapping();
082 activationCodeMappingForInsert.setActivationCode(activationCode);
083 activationCodeMappingForInsert.setEmailAddress("ford@hgttg.com");
084 activationCodeMappingForInsert.setSource("000A");
085 activationCodeMappingForInsert.setMerchantId(7890L);
086 activationCodeMappingForInsert.setHistoricalMerchantName("Milliways");
087
088 activationCodeMappingCacheStore.store(activationCode, activationCodeMappingForInsert);
089
090 // verify insert
091 Object foundObject = activationCodeMappingCacheStore.load(activationCode);
092 assertNotNull(foundObject);
093
094 ActivationCodeMapping foundActivationCodeMapping = (ActivationCodeMapping) foundObject;
095 assertEquals(activationCode, foundActivationCodeMapping.getActivationCode());
096 assertEquals("ford@hgttg.com", foundActivationCodeMapping.getEmailAddress());
097 assertEquals("000A", foundActivationCodeMapping.getSource());
098 assertEquals(7890L, foundActivationCodeMapping.getMerchantId().longValue());
099 assertEquals("Milliways", foundActivationCodeMapping.getHistoricalMerchantName());
100 assertNotNull(foundActivationCodeMapping.getCreatedDate());
101
102 // 
103 }
3. Supply a DataSource to the DAO/class under test.
Once again we’re using Spring to inject the needed instances. However the config file is now specifying an instance of an iBATIS SqlMapClient.
view sourceprint?01 <?xml version="1.0" encoding="UTF-8"?>
02 <beans xmlns="http://www.springframework.org/schema/beans"
03 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
04 xmlns:tx="http://www.springframework.org/schema/tx"
05 xsi:schemaLocation="http://www.springframework.org/schema/beans
06
07 http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
08
09 http://www.springframework.org/schema/tx
10
11 http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">
12
13 <!-- create data sources connected to an in-memory H2 (not HSQLDB) database. -->
14 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
15 <property name="driverClass" value="org.h2.Driver"/>
16 <property name="jdbcUrl" value="jdbc:h2:mem:test;AUTOCOMMIT=OFF"/>
17 <property name="user" value="sa" />
18 <property name="password" value="" />
19 </bean>
20
21 <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
22 <property name="configLocation">
23 <value>classpath:ActivationCodeMappingSqlMapConfig.xml</value>
24 </property>
25 <property name="dataSource" ref="dataSource" />
26 <!-- without this setting the commitTransaction() method does not commit -->
27 <property name="transactionConfigClass" value="com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransactionConfig" />
28 </bean>
29
30 <bean id="activationCodeMappingCacheStore" class="com.shopzilla.site.service.activationcode.cachestore.ActivationCodeMappingCacheStore" >
31 <property name="sqlMapClient" ref="sqlMapClient" />
32 </bean>
33 </beans>
After using both H2 & HSQLDB, I definitely prefer H2 and expect to use it going forward.