Automated Unit Testing with DBUnit, Hsqldb in Spring & Hibernate projects

June 7th, 2008 | by Priyatam |

If your project has a lot of unit testing in Java and you use continuous builds (CruiseControl) to automate, it is often difficult to automate tests which connect to Database (Dao and integration tests). For most companies, having a dedicated Oracle schema for testing is expensive. Also, sometimes, developers need their own instances of schemas to test on, which again is not possible with a corporate DBA on Oracle. Obviously writing unit tests connecting to the same instance where dev environment is setup is extremely unpleasant.

What do you do then?

Welcome to Schemamule. It’s a tiny jar file and has a single ant task which copies the entire Oracle Schema and generates a Hsqldb database. YES. It replicates an entire Oracle Schema into a hsqldb in seconds! You can either use it as an in memory database for your automated unit testing or as dev only local database for unit testing. For more details, read the original blog here.

Let me iterate the advantages:-

– Can write database dependent test cases
– Need not connect to a network for running tests
– Database tests run at blazing speed, as now they’re on hsqldb

I worked with one of the developers — Rhett Sutphin, in my earlier project. He’s a brilliant programmer. I’ve also reused it in one of my projects at a financial firm, it works great and is amazingly productive once you have setup a framework based on dbunit. Wait, what is dbunit? I’m not going into the details but it’s a no-brainer that if you use hibernate and junit, you better get started on using dbunit for unit testing backed by xml datasets instead of the cumbersome setup/teardown methods to populate data for each junit test case

We have a sample project checked into RTC’s sourceforge (We call it helloworld2), with custom classes on top of DBUnit (most of it has been written by the author of Schemamule).

My example considers of an entity, Foo with FooDao, mapped to a table Foo. For simplicity, I assume one field called name. As a step by step tutorial on extending DBUnit Unit Testing , I’ll show how simple it is to write a Dao based unit test case using Dbunit, Spring and Hibernate (of course on our generated hsqldb)

Step 1:Create an Hsqldb database from Oracle

Download Schemamule and run the following ant task

<schemacopy xmlns="http://bioinformatics.northwestern.edu/schemamule">
    <to uri="jdbc:hsqldb:file:${basedir}/hsqldb/mydb" username="sa">
    <from uri="jdbc:oracle:thin:mydb" username="scott" password="tiger">
</from>
</to></schemacopy>

Step 2: Setup DBUnit
Download the latest version of dbunit (jdk1.4 or jdk5)

Step 3: Download the svn repository (0r copy by browsing svn) Revert To Console’s sample test utility classes and example DBUnit tutorial from sourceforge here.

- Checkout under projects/unit-test.
- I’m not providing a complete working example with spring/hibernate configuration, as I presume you are running under an environment where you already have a this configured with an Oracle database.

- the utility classes include the following classes
- CoreTestCase — An extension on JUnit with much more useful assert statements
- ApplicationTestCase — All the unit tests in your Spring/Hibernate project should extend this class
- DaoTestCase — All your Dao Tests should extend this class
- ContextTools - The class which reads your applicationContext-xyz.xml file, where hibernate session is configured
- Sample Dao and entity

Configure these files in your project, which can read the applicationContext from the classpath (for the tests to work!)

Step 4:With all this configured, let’s write a simple Dao Test

public class FooDAOTest extends DaoTestCase {
    FooDao dao = (FooDao)getApplicationContext().getBean(&quot;fooDao&quot;);
         // assuming that your spring config has a bean-id 'fooDao'
	 public void testGetById() throws Exception {
            Foo foo = (Foo)dao.getById(1);
            assertEquals(&quot;revert to console&quot;, foo.getName());
	 }
}

By Default, DBUnit assumes a test data xml file in the same testdata/FooDaoTest.xml (you can override this). Now, lets see the dataset

<dataset>
<table name="FOO"><column>name</column>
        <row>
            <value>revert to console</value>
         </row></table>
</dataset>

That’s it! For all the configuration that I’ve showed you (which will take some time to setup), all you need to do to write another test is, to write another method with testdata! This setup will create,drop the testdata on the generated HsqlDb schema before/after each test. What more, because, it is in memory, you can even run, Dao Tests and complete round trip unit tests automated in your nightly builds, without coupling to an existing Oracle Schema.

There are some limitations to Schemamule though. I had to customize it for my project as we were using DB Views. I finally got it working after modifying the sourcecode. Unfortunately I could never put that back into Schemamule as a patch but I recommend downloading the source and try it for yourself. It’s a great example on understanding how Spring’s Jdbc template works and can be extended.

Post a Comment