Archive

Posts Tagged ‘dbunit’
  • Database unit testing with DBUnit, Spring and TestNG

    I really like Spring, so I tend to use its features to the fullest. However, in some dark corners of its philosophy, I tend to disagree with some of its assumptions. One such assumption is the way database testing should work. In this article, I will explain how to configure your projects to make Spring Test and DBUnit play nice together in a multi-developers environment.

    Context

    My basic need is to be able to test some complex queries: before integration tests, I’ve to validate those queries get me the right results. These are not unit tests per se but let’s assilimate them as such. In order to achieve this, I use since a while a framework named DBUnit. Although not maintained since late 2010, I haven’t found yet a replacement (be my guest for proposals).

    I also have some constraints:

    • I want to use TestNG for all my test classes, so that new developers wouldn't think about which test framework to use
    • I want to be able to use Spring Test, so that I can inject my test dependencies directly into the test class
    • I want to be able to see for myself the database state at the end of any of my test, so that if something goes wrong, I can execute my own queries to discover why
    • I want every developer to have its own isolated database instance/schema

    Considering the last point, our organization let us benefit from a single Oracle schema per developer for those “unit-tests”.

    Basic set up

    Spring provides the AbstractTestNGSpringContextTests class out-of-the-box. In turn, this means we can apply TestNG annotations as well as @Autowired on children classes. It also means we have access to the underlying applicationContext, but I prefer not to (and don’t need to in any case).

    The structure of such a test would look like this:

    @ContextConfiguration(location = "classpath:persistence-beans.xml")
    public class MyDaoTest extends AbstractTestNGSpringContextTests {
    
        @Autowired
        private MyDao myDao;
    
        @Test
        public void whenXYZThenTUV() {
            ...
        }
    }
    

    Readers familiar with Spring and TestNG shouldn’t be surprised here.

    Bringing in DBunit

    DbUnit is a JUnit extension targeted at database-driven projects that, among other things, puts your database into a known state between test runs. [...] DbUnit has the ability to export and import your database data to and from XML datasets. Since version 2.0, DbUnit can also work with very large datasets when used in streaming mode. DbUnit can also help you to verify that your database data match an expected set of values.

    DBunit being a JUnit extension, it’s expected to extend the provided parent class org.dbunit.DBTestCase. In my context, I have to redefine some setup and teardown operation to use Spring inheritance hierarchy. Luckily, DBUnit developers thought about that and offer relevant documentation.

    Among the different strategies available, my tastes tend toward the CLEAN_INSERT and NONE operations respectively on setup and teardown. This way, I can check the database state directly if my test fails. This updates my test class like so:

    @ContextConfiguration(locations = {"classpath:persistence-beans.xml", "classpath:test-beans.xml"})
    public class MyDaoTest extends AbstractTestNGSpringContextTests {
    
        @Autowired
        private MyDao myDao;
    
        @Autowired
        private IDatabaseTester databaseTester;
    
        @BeforeMethod
        protected void setUp() throws Exception {
            // Get the XML and set it on the databaseTester
            // Optional: get the DTD and set it on the databaseTester
            databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
            databaseTester.setTearDownOperation(DatabaseOperation.NONE);
            databaseTester.onSetup();
        }
    
        @Test
        public void whenXYZThenTUV() {
            ...
        }
    }
    

    Per-user configuration with Spring

    Of course, we need to have a specific Spring configuration file to inject the databaseTester. As an example, here is one:

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
            <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
                <property name="location" value="${user.name}.database.properties" />
            </bean>
            <bean name="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
                 <property name="driverClass" value="oracle.jdbc.driver" />
                 <property name="username" value="${db.username}" />
                 <property name="password" value="${db.password}" />
                 <property name="url" value="jdbc:oracle:thin:@<server>:<port>/${db.schema}" />
            </bean>
            <bean name="databaseTester" class="org.dbunit.DataSourceDatabaseTester">
                <constructor-arg ref="dataSource" />
            </bean>
    </beans>
    

    However, there’s more than meets the eye. Notice the databaseTester has to be fed a datasource. Since a requirement is to have a database per developer, there are basically two options: either use a in-memory database or use the same database as in production and provide one such database schema per developer. I tend toward the latter solution (when possible) since it tends to decrease differences between the testing environment and the production environment.

    Thus, in order for each developer to use its own schema, I use Spring’s ability to replace Java system properties at runtime: each developer is characterized by a different user.name. Then, I configure a PlaceholderConfigurer that looks for {user.name}.database.properties file, that will look like so:

    db.password=mypassword1
    db.schema=myschema1
    

    This let me achieve my goal of each developer using its own instance of Oracle. If you want to use this strategy, do not forget to provide a specific database.properties for the Continuous Integration server.

    Huh oh?

    Finally, the whole testing chain is configured up to the database tier. Yet, when the previous test is run, everything is fine (or not), but when checking the database, it looks untouched. Strangely enough, if you did load some XML dataset and assert it during the test, it does behaves accordingly: this bears all symptoms of a transaction issue. In fact, when you closely look at Spring’s documentation, everything becomes clear. Spring’s vision is that the database should be left untouched by running tests, in complete contradiction to DBUnit’s. It’s achieved by simply rollbacking all changes at the end of the test by default.

    In order to change this behavior, the only thing to do is annotate the test class with @TransactionConfiguration(defaultRollback=false). Note this doesn’t prevent us from specifying specific methods that shouldn’t affect the database state on a case-by-case basis with the @Rollback annotation.

    The test class becomes:

    @ContextConfiguration(locations = {classpath:persistence-beans.xml", "classpath:test-beans.xml"})
    @TransactionConfiguration(defaultRollback=false)
    public class MyDaoTest extends AbstractTestNGSpringContextTests {
    
        @Autowired
        private MyDao myDao;
    
        @Autowired
        private IDatabaseTester databaseTester;
    
        @BeforeMethod
        protected void setUp() throws Exception {
            // Get the XML and set it on the databaseTester
            // Optional: get the DTD and set it on the databaseTester
            databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
            databaseTester.setTearDownOperation(DatabaseOperation.NONE);
            databaseTester.onSetup();
        }
    
        @Test
        public void whenXYZThenTUV() {
            ...
        }
    }
    

    Conclusion

    Though Spring and DBUnit views on database testing are opposed, Spring’s configuration versatility let us make it fit our needs (and benefits from DI). Of course, other improvements are possible: pushing up common code in a parent test class, etc.

    To go further:

    Categories: Java Tags: dbunitspringtesttestng
  • Speeding up your DBUnit tests with JavaDB

    Introduction to DBUnit

    DBUnit logoWith the developed use of JUnit, many additions were made to it. These additions take advantage of the pluggable nature of JUnit. One of these contribution is DBUnit. DBUnit enables your unit tests to:

    • preload the database with datas pertaining to your current test only,
    • run your unit test (as usual),
    • remove the data you preloaded in order to put the database in the same it was before your additions.

    In effect, you can now test your DAO classes. Let’s take a (very) simple example: a PersonDao class that manipulates Person objects.

    You create a test case that extends org.dbunit.JdbcBasedDBTestCase. It forces you to implement the following methods:

    • IDataSet getDataSet() throws Exception where IDataSet represents a collection of tables,
    • String getConnectionUrl() that returns the connection url of the database,
    • String getDriverClass() that returns the class name of the driver used for database access.

    This process enables you to test your DAO classes with various data sets. Moreover, these data sets can be initialized from various sources: I recently discovered one of these sources could be an Excel file ! Not very open-source, I agree, but very much more manageable than those cumbersome XML files.

    Yet, these tests are usually made on remote and shared databases. Drawbacks include:

    1. a sheer performance cost: remote databases use networks and, as such, increase the time to get the result, even if the databases themselves are very performant on their own,
    2. a risk of using the wrong datas: if using a shared database, you are taking chances that the data you want to use for your test has just been modified by another developer. If your DB uses a schema for each developer, well, that's just configuration overhead...

    The first point is a good way for developers not to write DB tests, since they can’t complete the build fast enough. The second point paves the road for correct tests to fail or more importantly, for incorrect tests to succeed. Both are bad.

    Using an alternate local DB

    The answer to these problems are the use of an alternate local DB. Yet, this solution has its own disadvantages including: license costs, configuration overhead for each developer’s machine and the ever-popular “It doesn’t work on my computer”. Well, let’s get back to the shared BD ? Clearly no: there’s a often overlloked option.

    Apache Derby logoSince version 6 of the JVM, Sun ships an embedded database with its JDK. This DB, called oh-so-originally JavaDB, is a reuse of the not-so-old Apache Derby database project. Apache Derby, is an open source relational database implemented entirely in Java.

    Main advantages are:

    • a small memory footprint,
    • the main SQL-99 Core / SQL-2003 mandatory features (see table for unsupported features),
    • an embedded driver.

    Quick how-to

    First of all, you should provide a mechanism to provide a connection in different ways, for example with dependency injection, probably the method you will choose for a real-world project (be it with Spring or Guice). For my proof-of-concept, I chose the method-overloading mechanism to keep it simple (KISS without the trailing S).

    Then, it’s all very simple:

    • the getConnectionUrl() should return "java:derby:[your_chosen_db_name];create=true",
    • the getDriverClass() should return "org.apache.derby.jdbc.EmbeddedDriver",
    • the getDataSet() should return very simply getConnection().createDataSet().

    Et voilà !

    The Maven artifact that contains an example is just here: JavaDB example sources

    Conclusion

    The use of JavaDB is not the golden hammer to your DB tests problems. According to me, there’s much to be done in order for it to be really full-SQL compliant. Yet, 90% of the common SQL requests used troughout prjects can be parsed by it. There’s even an Hibernate dialect available. In conclusion, it can save much anguish and much time for developers. So, why don’t use it ?

    Categories: Java Tags: dbunitderbyjavadbunit test