H2 Database, Junit and Sequence Problems.

Running my Junit tests against a disposable local database has saved me a lot of trouble but, it has given me a couple of problems as well.

I'm quite pleased with how I can bring up a fresh H2 database when I start an JUnit test. I have built a simple JUnit Abstract class which manages the H2 setup loading the DDL for my database followed by sql files that are specific to each unit test (The latter being called from an @BeforeClass method calls a parent method with the path of the unit tests sql inserts. This gives me the ability to seed the database with good test data against which I can run my tests. By doign this I ensure that the data in the database is always exactly what it should be while the test is running. If you have ever tried to run tests against a standing dev database you know it rapidly becomes populated with all sorts of junk from other developers testing or even your own code. With this approach your data is always in a known state, problem solved!

I like H2 and its very useful but,I have come across a couple of oddities using while using it as part of my testing structure. First, while H2 is Oracle compliant if accessed with mode=oracle on the JDBC URL, there are still some DDL and SQL commands which aren't supported, to_date() being a big one.

This is unfortunate since you also want to run your tests against a current database structure and if your in an agile environment with a new database in the works your DB is changing as often as your code! I got around that issue by building a Processor that simply converts all SQL into a compliant format discarding statements that are not usable. Thanks to this processor I can then dump DDL from Toad and simply load up the table export for testing. Nice!

The other notable issue I encountered when implementing this testing framework is encountered when processing the seed data sql. The following simple insert statements creates unexpected SQL errors:

INSERT INTO MY_TABLE (id, some_field, some_other_field) VALUES(MY_TABLE_SEQ.nextval, 'value', 'value');

Note the usage of the sequence call to nextval:  MY_TABLE_SEQ.nextval

For whatever reason this call simply doesn't work. You will receive a SQL error informing you of Primary Key violation requiring a unique id. Yea you SHOULD be inserting one from the sequence but, the H2 sequence is not incremented by the call and you get an old sequence number!

The solution is really quite simple but, sent me on a bit of a hunt trying to figure out what was amiss. In an effort to save someone else a chunk of time here is the solution:

SELECT MY_TABLE_SEQ.nextval from DUAL;
INSERT INTO MY_TABLE (id, some_field, some_other_field) VALUES(MY_TABLE_SEQ.currval, 'value', 'value');

Told you it was simple! I almost feel silly for having spent so much time trying to debug my code! As long as you give H2 a chance to commit a sequence increment, the problem is solved. This works great for testing and solves my problem.

It does imply that there could be issues in H2 if you are doing multiple user inserts and incrementing that sequence so if your in this sort of situation keep your eye out for issues. Fortunately it doesn't effect my local JUnits!