TimShadel.com

You’ve got database code that uses both Hibernate and Spring. You need to test it. In this podcast I talk about how you can use DBUnit and HSQLDB along with some Spring test classes to create a setup that will let you test database code fast, and in isolation. Just what good unit tests should do.

Check out some great references on database testing, including the ones I mentioned in this show.

DBUnit and thier best practices for database testing.
The HSQLDB home page.
Scott Ambler’s excellent discussion of database refactoring.
An article from TheServerSide.com about unit testing with HSQLDB.
Spring’s AbstractTransactionalDataSourceSpringContextTests class that helps make some of your database testing easier. Check the other classes in the hierarchy for specifics on what they do as well.

I decided to try out adding a GarageBand.com song today, like the SlashdotReview podcast does. The song is Calendar by Eugenia and The Boys. Let me know if you like it. Also feel free to revolt and tell me to pull the music and keep it to tech stuff.

Listen now with the Flash player.

Download Unit Testing Database Code.

I use DBUnit to setup my database for test cases. Right now, I’m running HSQL in its “in memory” mode as the database. I’m using Hibernate mappings to connect my objects to the database, and I’ve got my Spring/Hibernate configuration setup to drop and create the tables at the beginning of my test run (and only once for all tests). I kept running into a problem with foreign keys, though. I couldn’t get past it, and no amount of wrestling with my mapping file eliminated my "Integrity constraint violation".

I tracked it down eventually to my DBUnit file. I have a Composite pattern in one of my objects, so items in the Chart table have a parent that is also a Chart; but the root element has no parent. The first record in my XML file referred to a parent that was later in the file. I swapped the records around so that they were all created in the right order. No problem. Run the tests, no error, but my test fails. NullPointerException. Hmm.

Another while of head-banging and incantations leads nowhere. So I decided to swap from in-memory DB to an actual DB I can look at. Thankfully, that’s pretty easy. I fire up an HSQLDB server instance inside Eclipse, and swap the comment on this line in my Spring properties file… (More about my environment another time, this is a bug hunt!)

dataSource.url=jdbc:hsqldb:mem:account
#dataSource.url=jdbc:hsqldb:hsql://localhost:1701/

Now I can easily see that none of the PARENT_ID colums are being populated. That’s really odd. So I fiddle a bit, and decide to add a parent reference to my root node to point to itself, et voilà. All of my parent references suddenly appear. Make the root one null again; they’re all gone. So I scratch my head for a bit, and then I make a guess.

DBUnit’s FlatXmlDataset requires you to eliminate a column if you want it left null. But it appears that it uses the first record’s data to fully define the schema it’ll use when creating its eventual insert sequence. I need that column to be null. And I need that record to be first. What to do? I decided to embed a DTD right in my xml dataset. I ran over to the right entry in the FAQ and quickly got a DTD for my database schema. Then I modified my xml dataset to look like this:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE dataset
    [

<!ELEMENT dataset (
    CHART*)>
<!ELEMENT CHART EMPTY>
<!ATTLIST CHART
    ID CDATA #REQUIRED
    PARENT_ID CDATA #IMPLIED
    CHART_NUMBER CDATA #REQUIRED
    DESCRIPTION CDATA #REQUIRED
    OPEN_DATE CDATA #REQUIRED
    CLOSE_DATE CDATA #IMPLIED
>
    ]>

<dataset>
  <chart ID=’12′ CHART_NUMBER=’12000′ DESCRIPTION=’Simple description’ OPEN_DATE=’2004-01-01 00:00:00.0′/>
  <chart ID=’15′ CHART_NUMBER=’12100′ PARENT_ID=’12′ DESCRIPTION=’Sub chart’ OPEN_DATE=’2004-01-01 00:00:00.0′/>
  <chart ID=’16′ CHART_NUMBER=’12110′ PARENT_ID=’15′ DESCRIPTION=’Sub sub chart’ OPEN_DATE=’2004-01-01 00:00:00.0′/>
  <chart ID=’17′ CHART_NUMBER=’12200′ PARENT_ID=’12′ DESCRIPTION=’Sub chart 2′ OPEN_DATE=’2004-01-01 00:00:00.0′/>
  <chart ID=’18′ CHART_NUMBER=’12210′ PARENT_ID=’17′ DESCRIPTION=’Sub sub chart 2′ OPEN_DATE=’2004-01-01 00:00:00.0′/>
</dataset>

I ran the tests, and bingo! Green bar. I love tests that run.

About a year ago, we were using jWebUnit, which is an API built on HttpUnit — which in turn is very similar to HtmlUnit. We used it in conjunction with DbUnit to load the database tables before the test and verify them after. It worked really well. We had tests that could verify the operation properly manipulated the database, and could check the structure of the HTML returned.

We went away from it mainly because functional tests could only be written by programmers with that method, and with JMeter we could have someone a systems analyst or a tester write the functional tests. Using the proxy recorder available in JMeter let a user click through the test and replay it. JMeter could load the DB and check it based on CSV files, instead of XML. In the end I think the flexibility is a little less, but the number of people who can help get the work done has increased.