Creating TestNG casesI was having issues creating proper data sets for our test suite since the data model is so complex. I was manually instantiation each class’ entity, populating the object, persisting, then committing the transaction manually. It became obscene for certain objects that referenced up to ten different classes with multiple constraints each. We already have SQL scripts to pre-populate our local JBoss environment so it was a possible solution to use that same data for our tests. I posed the question of a better solution on the interweb and it was suggested I try another DBMS that supports DB2 used by our site and also operates in-memory to increase speed. We already were using Hypersonic but it doesn’t support SQL import from DB2. So I tried H2, using this JDBC connect string to enable the DB2 compatibility mode and trigger our create + populate scripts on the test suite’s startup.

jdbc:h2:mem:localDB;MODE=DB2;INIT=RUNSCRIPT FROM ‘~/workspace/database/create.sql’\;RUNSCRIPT FROM ‘~/workspace/database/populate.sql’

As the H2 features page indicates, “for certain features, this database can emulate the behavior of specific databases. Not all features or differences of those databases are implemented.” Enter what I found out was in fact DB2 incompatibility mode.

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

DB2 allows FROM as a column name. H2 does not. And no, I didn’t originally write this schema so don’t blame me for its poor naming.

org.h2.jdbc.JdbcSQLException: Column “CURRENT_ROOM.ROOM_NAME” not found

Aliases defined in an INNER JOIN clause cannot be referenced in the SELECT expression (isn’t that how Hibernate builds all its join queries?)

org.h2.jdbc.JdbcSQLException: Table “DATABASE” not found

Uh…

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement “…”; expected “AFTER”

Well it looks like a DB2 trigger doesn’t need that AFTER keyword.

So we’ve switched back to Hypersonic and I’m now creating an XML-based data set using DBUnit.