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.

With 19 GB of bandwidth served in just 20 days (thanks for listening to my podcast, everyone), I’m glad I’ve switched providers. I converted my site over to the new ISP at the beginning of the month, and things have gone quite well. I’ve got a new WordPress installation (that I absolutely LOVE), my old blog entries are all in (except a few from mid 2003 that I lost along the way), and I’ve switched from categories to tags. For more on the discussion of why tags are great, see this post and others by him. Still to come: a better look and feel, and integration with my Furl, del.icio.us, and Flickr stuff.

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.