Phil Webb's Blog

Random thoughts from a software developer

Database testing using DBUnit, Spring and Annotations

with 31 comments

If you have ever tried writing database tests in Java you might have come across DBUnit. DBUnit allows you to setup and teardown your database so that it contains consistent rows that you can write tests against. You usually specify the rows that you want DBUnit to insert by writing a simple XML document, for example:

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
	<Person id="0" title="Mr" firstName="Dave" lastName="Smith"/>
	<Person id="1" title="Mrs" firstName="Jane" lastName="Doe"/>
</dataset>

You can also use the same format XML files to assert that a database contains specific rows.

DBUnit works especially well using in-memory databases, and if you work with Spring, setting them up is pretty straightforward. Here is a good article describing how to get started.

Working directly with DBUnit is fine, but after a while it can become apparent how many of your tests are following the same pattern of setting-up the database then testing the result. To cut down on this duplication you can use the spring-test-dbunit project. This project is hosted on GitHub and provides a new set of annotations that can be added to your test methods. Version 1.0.0 has just been released and is now available in the maven central repository:

<dependency>
  <groupId>com.github.springtestdbunit</groupId>
  <artifactId>spring-test-dbunit</artifactId>
  <version>1.0.0</version>
  <scope>test</scope>
</dependency>

Once installed three new annotations are available for use in your tests: @DatabaseSetup, @DatabaseTearDown and @ExpectedDatabase. All three can either be used on the test class or individual test methods.

The @DatabaseSetup and @DatabaseTearDown annotations are used to put your database into a consistent state, either before the test runs or after it has finished. You specify the dataset to use as the annotation value, for example:

@Test
@DatabaseSetup("sampleData.xml")
public void testFind() throws Exception {
  // test code
}

The @ExpectedDatabase annotation is used to verify the state of the database after the test has finished. As with the previous annotations you must specify the dataset to use.

@Test
@DatabaseSetup("sampleData.xml")
@ExpectedDatabase("expectedData.xml")
public void testRemove() throws Exception {
  // test code
}

You can use @ExpectedDatabase in a couple of different modes depending on how strict the verification should be (see the JavaDocs for details).

For the annotations to be processed you need to make sure that your test is using the DbUnitTestExecutionListener. See the project readme for full details. If you want to learn more there is an example project on GitHub and some walk-though instructions available here.

About these ads

Written by Phillip Webb

April 23, 2012 at 12:52 am

Posted in DBUnit, Spring

31 Responses

Subscribe to comments with RSS.

  1. Can you use spring-test-dbunit also with TestNG?

    tomekkaczanowski

    April 24, 2012 at 2:09 pm

    • I have not tried TestNG. The code builds on the Spring test framework and I believe that that does support TestNG so I see no reason why this could not work. Feel free to raise an issue if it fails.

      Phillip Webb

      April 24, 2012 at 2:37 pm

  2. If you have DAO tests, why do you need this ??
    Testing Database, really !!

    0Bama

    April 24, 2012 at 2:20 pm

    • The aim is to help you write your DAO tests, perhaps I did not make that clear enough. If you are writing a test to ensure a query in your DAO returns expected results you probably want some specific data to drive that. I personally like writing these kinds of tests against an in-memory database.

      Phillip Webb

      April 24, 2012 at 2:27 pm

      • Hi

        I tried using DB unit some time ago and IMHO it is simply unmaintainable. Dealing with setup/expected XML files soon becomes hell for any non trivial database, i.e database with many referential integrities:
        to load Person you need to load Team he belongs to, to load team you need team address and team coach, to load address you need… to load coach …

        Moreover, if it happened that your schema can be changed quite often during project lifecycle and/or the schema changes are performed by “other authorities” (like DB team) it is nearly impossible to have your DB in synch with your xml files. Obviously you could keep the whole DML in a single xml file to fit all possible tests but working with such huge, “cover-all cases” xml file is cumbersome and has its own problems.
        Regarding in memory database i try to avoid them as much as i can and always try to test against real, in-production like DB engine. For this we have a separate schema for each developer (copied from staging env each night). Working with a real database has many advantages over working with in-memory database, just to give a few:
        – you can test your code that uses specific DB functionality (like Oracle hierarchical queries, query hints),
        – there are subtle differences among DB regarding transaction isolation levels, locking and so on.
        – even if you use ORM like solution, some features like: mapping to java.util.Date, java.util.Calendar works different depending on DB ( and in case of ORACLE it also depends on jdbc driver version),db-dependent key generation strategy.
        I think that testing against in-memory DB and deploying live app to work with no-toy DB is like developing on Tomcat and deploying live to Websphere.

        IHMO If you use Spring test context framework with production-live DB engine, blessed schema (copied to for developer) and rollback after each test you got a really nice environment to test you DAO. And pls don’t tell me that these kind of tests are slow: on my PC i run suite of about 2000 “DB infected” tests against Oracle (installed in our LAN) with rollback after each test in about 5 minutes. Even if you could run these tests a liitle faster aganst in-memory db, a maintenance burden and db incompatibility gap is too high to me.

        jmilkiewicz

        April 24, 2012 at 3:48 pm

  3. @jmilkiewicz I certainly agree with you that the maintenance of DBUnit XML files can become a problem and that running integration tests against a real database helps to catch a lot of the subtle differences that you describe.

    Having said that, setting up the infrastructure to support that can be quite an endeavor, and it does end up forcing you work in a specific environment. One aspect of the in-memory database tests that I like is that developers don’t need to setup anything specific. Just check-out the project and run the tests.

    It is certainly not a magic bullet, but it can help to catch a lot of the silly errors that can occur when ORM entities get refactored and the queries get missed.

    At least having projects like DBUnit gives you another option if you don’t have time to create a real test database.

    Phillip Webb

    April 24, 2012 at 4:27 pm

    • Maybe i am a little bit biased after working with Oracle and we never had a problems with infrastructure. We have a dedicated person who is responsible for preparing “fresh” schema. Using built-in oracle commands you can do it mostly automatically so it is no a big deal. It required some initial investments but now it is deadly simple. We were lucky to have a person with db administration experience so it all took us much faster than we thought…
      Moreover, you have an option to produce the virtual image with fresh DB schema and data and then even run it from usb. In this way you can work against real database in train with no internet access.

      jmilkiewicz

      April 25, 2012 at 10:54 am

  4. Phillips,

    What do you think of Unitils ? Do you use it ?

    Rudy

    April 25, 2012 at 4:57 am

    • I am rather embarrassed to say that I missed Unitils before I wrote this code and I still have not used it in earnest. It certainly looks like a good option, especially if you are not using Spring.

      Phillip Webb

      April 25, 2012 at 9:25 am

  5. I tried all examples and they work wonderful.
    But there is an issue :
    1. I have an HSQLDB database
    2. I have 2 test methods, both annotated with @DatabaseSetup(value = “/dbUnit/initialData.xml”, type = DatabaseOperation.CLEAN_INSERT)
    3. initialData.xml file contains data for 2 database records (= 2 hibernate entities)
    4. First test method adds an entity to the database
    5. Second test method also adds an entity to the database.
    6. The entity has @Id @GeneratedValue private Long id;
    My problem is that I expect the entity added in second test to have the same id with the entity added in the first test, because the CLEAN_INSERT should drop the old database tables and create new ones.
    But in reality the entity added in the second test has the id greater with 1 compared to the id of the entity added by the first test.
    So, it seems that entity IDs are remembered between tests executions, and for a lot of tests this can be a really big issue.
    Is there a way to “reset” the IDs between tests, so that each test to start counting them from the beginning?

    Ciprian Mihalache

    October 7, 2012 at 8:14 am

    • Hi Ciprian,

      I am glad that the examples work for you. I don’t know of any standard way to solve your particular case, it seems like you are not the only person who has asked for such functionality in DBUnit [1]. I have three ideas that might solve your problem:

      1) It might be possible to bypass key generation all-together. You could try defining the IDs in your xml setup so that the generation is not needed.

      2) Use transactions. If you use transaction based tests (see the readme on the github project) no specific tests will commit to the underlying database. I am unsure how sequences fit into this picture but it might be worth trying this approach to see if the sequence generated ID is also rolled back.

      3) Use @After to clean-up the sequence. You could try using a JUnit @After annotated method that runs specific SQL to reset the underlying sequence. The SQL is something like “ALTER SEQUENCE RESTART WITH “.

      Hope that helps,
      If you find a better approach that needs modification to the underlying code please let me know. Also feel free to fork the code and submit a pull request if you need to.

      Cheers,
      Phil.

      [1] http://stackoverflow.com/questions/6307604/dbunit-doesnt-reset-sequences

      Phillip Webb

      October 7, 2012 at 10:43 am

      • I do not believe that option 2 could work, since all dbs i know sequences have non transactional sequences. So no matter if commit/rollback has been issued sequence.nextVal will always get current sequence value +1.

        jmilkewicz

        October 8, 2012 at 8:12 am

      • Thank you Phillip for your quick and complete answer.
        You gave me some ideas that finally solved my problem.
        First option sounds wonderful from a testing point of view, but the test runs on a production code, and I need @Generated in production.
        I have tried second option, but I doesn’t work. Someone explained this behavior very well in [1] that indexes are not affected (decreased) by a roll-back.
        I have fighted with third option a whole day, but, from the logs I noticed that the order of execution is the following:
        @DatabaseSetup
        @Before
        @Test
        @After
        @ExpectedDatabase
        I used TRUNCATE SCHEMA PUBLIC RESTART IDENTITY AND COMMIT NO CHECK as explained in [2]
        With the above order, the problem is that the cleanup in @After destroys database objects before @ExpectedDatabase, thus tests are failing, and the cleanup in @Before destroys objects created by @DatabaseSetup
        So, my problem would not exist if the order would be @Before-@DatabaseSetup-@Test-@ExpectedDatabase-@After
        So all these were dead ends.
        But your suggestion with ALTER SEQUENCE worked, but under some conditions, because when DBUnit adds the objects to the database, the sequence is not aware of their insertion (so if there is an object in xml with id=0 and test adds a new object to database, the addition fails, because the new object is tried to be persisted also with id=0).
        Solutions:
        1) Either define objects in xml with very large IDs and be careful in tests that default objects are the last ones. In @After reset sequence index to 0
        2) The objects in xml have indexes 0…n and reset sequence index in @Before with the value n+1.
        I used the second approach, it seems more natural.
        Of course, who is not interested in database indexes, ca use something like: @ExpectedDatabase(value = “path_to_xml”, assertionMode = DatabaseAssertionMode.NON_STRICT), but I nedded to test all aspects of using your library before deciding to integrate it in our tests. Except the fact that @Before and @After are between @DatabaseSetup and @ExpectedDatabase, it is a very good library. It is fantastic that through a single annotation the database is populated and the entire database records can be checked also by using a simple annotation. This will replace lots of java code.
        Thank you once again for your support.

        BR,
        Ciprian

        [1] http://stackoverflow.com/questions/8834697/autoincrement-with-hsql-hibernate-and-spring-and-tests
        [2] http://stackoverflow.com/questions/4990410/how-can-i-wipe-data-from-my-hsqldb-after-every-test

        Ciprian Mihalache

        October 8, 2012 at 8:53 am

      • @Ciprian maybe if you are to configure the library via junit rule (DbUnitRule) you could be able to specify RuleChain and define the @YourBefore-@DatabaseSetup-@Test-@ExpectedDatabase-@YourAfter order

        jmilkiewicz

        October 8, 2012 at 1:02 pm

      • @Ciprian i just read that using Junit @Rule is not working in the library. Nevertheless i would experiment with Spring @BeforeTransaction/@AfterTransaction. I think that if you follow the project’s configuration guideline (TransactionalTestExecutionListener before DbUnitTestExecutionListener on TestExecutionListeners list) @BeforeTransaction should be fired before dbunit setup. Since the code within @BeforeTransaction is be fired outside transaction it can happen you would have to issue your “reinitialize squences” SQLs with pure JDBC since

        jmilkiewicz

        October 8, 2012 at 2:00 pm

  6. The class test is like this:

    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = { "/spring/applicationContext.xml" })
    @TransactionConfiguration(transactionManager = "business1TransactionManager", defaultRollback = false)
    @Transactional
    @TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, DbUnitTestExecutionListener.class,
    TransactionalTestExecutionListener.class })
    public class TestPeopleServiceWithPredefinedDatabase {
    @Autowired
    private PeopleService peopleService;

    /**
    * Just test that the database contains all the data defined in initialData.xml file
    */
    @Test
    @DatabaseSetup("/dbUnit/initialData.xml")
    public void test1() {
    List allPersons = peopleService.getAllPersons();
    assertEquals(2, allPersons.size());

    Person p1 = allPersons.get(0);
    assertEquals(Long.valueOf(0), p1.getId());
    assertEquals("P1", p1.getName());

    Person p2 = allPersons.get(1);
    assertEquals(Long.valueOf(1), p2.getId());
    assertEquals("P2", p2.getName());

    }

    /**
    * Test what happens when we add a new record into the database
    */
    @Test
    @DatabaseSetup("/dbUnit/initialData.xml")
    public void test2() {
    Person newPerson = new Person();
    newPerson.setName("newPerson");
    peopleService.savePerson(newPerson);

    List allPersons = peopleService.getAllPersons();
    assertEquals(3, allPersons.size());

    Person p1 = allPersons.get(0);
    assertEquals(Long.valueOf(0), p1.getId());
    assertEquals("P1", p1.getName());

    Person p2 = allPersons.get(1);
    assertEquals(Long.valueOf(1), p2.getId());
    assertEquals("P2", p2.getName());

    Person p3 = allPersons.get(2);
    assertEquals(Long.valueOf(2), p3.getId());
    assertEquals("newPerson", p3.getName());
    }

    /**
    * Test exactly the same as {@link #test2()} but in a different manner.
    */
    @Test
    @DatabaseSetup(value = "/dbUnit/initialData.xml", type = DatabaseOperation.CLEAN_INSERT)
    @ExpectedDatabase("/dbUnit/newPerson_normalIndex.xml")
    public void test3() {
    Person newPerson = new Person();
    newPerson.setName("newPerson");
    peopleService.savePerson(newPerson);
    }

    /**
    * Test exactly the same as {@link #test3()} but skip databse index checking
    */
    @Test
    @DatabaseSetup("/dbUnit/initialData.xml")
    @ExpectedDatabase(value = "/dbUnit/newPerson_noIndexes.xml", assertionMode = DatabaseAssertionMode.NON_STRICT)
    public void test4() {
    Person newPerson = new Person();
    newPerson.setName("newPerson");
    peopleService.savePerson(newPerson);
    }

    /**
    * Utility method for reseting the database indexes after each test. It needs the @Transactional
    * annotation on the class level to work. Inspired from:
    * http://stackoverflow.com/questions/4990410/how-can-i-wipe-data-from-my-hsqldb-after-every-test
    * http://coderphil.wordpress.com/2012/04/23/database-testing-using-dbunit-spring-and-annotations/
    */
    @Before
    public void resetDatabaseIndexes() throws Exception {
    Connection connection = null;
    try {
    SessionImpl session = (SessionImpl) entityManager.getDelegate();
    connection = session.connection();
    try {
    Statement stmt = connection.createStatement();
    try {
    // stmt.execute("TRUNCATE SCHEMA PUBLIC RESTART IDENTITY AND COMMIT NO CHECK");
    stmt.execute("ALTER SEQUENCE PERSON_SEQ RESTART WITH 2");
    connection.commit();
    } finally {
    stmt.close();
    }
    } catch (SQLException e) {
    connection.rollback();
    throw new Exception(e);
    }
    } catch (HibernateException e) {
    throw new Exception(e);
    } finally {
    if (connection != null) {
    connection.close();
    }
    }
    }
    </code<
    This way it works perfectly, but in I change @Before with @BeforeTransaction or @AfterTransaction, hibernate throws an Exception that session is closed

    Ciprian Mihalache

    October 10, 2012 at 9:36 am

    • Hi

      I am not using pure JPA, still stick to Hibernate API, but nevertheless the problem is (I think ) easily solvable. As i wrote in my previous comment you would have to deal with a fact that within @AfterTransaction/@BeforeTransaction no transaction is active (which is quite obvious ) but it also implies that no session/jdbc conntection/jms session (depending on PlatformTransactionManager implementation you are using) is bound (or is bound in a “not usable state” ,i.e.closed).It means that if you are outside transaction you can not use the “constructions” in a way as if you were in transcation. Some of these constructions are spring injected entityManager, SessionFactory.getCurrentSession. So i think that you could employ one of 2 approaches:
      – inject DataSource and within @Before/AfterTransaction create JdbcTemplate(datasource) and then call jdbcTemplate.update(“ALTER SEQUENCE PERSON_SEQ RESTART WITH 2″). Since non-transactional connection retrieved internally should be in autocommit mode so no commit/close on connection is necessary. Look at sample code http://wklej.org/id/845230/
      – inject PlatformTransactionManager and within @Before/AfterTransaction create TransactionTemplate(platformTransactionManager), then call transcationTemplate.execute. For code look at http://wklej.org/id/845236/ . Again, you do not need to close/commit anything since Spring transactionTemplate will take care of everything.

      I hope Phillip Webb is not upset because of the problem solving conversation on his blog …

      jmilkewicz

      October 11, 2012 at 2:14 am

      • Thank you very much for the detailed explanation and for the code snippets. I have tried both approaches, both are working perfectly, but I really like the first one because of its simplicity, so I will use it further.
        Thank you once again for teaching me how to deal with DB changes outside transactions used by tests.
        BR,
        Ciprian

        Ciprian Mihalache

        October 13, 2012 at 5:41 am

      • Hi

        Glad it works and i could help.

        jmilkiewicz

        October 13, 2012 at 6:03 am

    • Ciprian,
      The reason you have different PK could come from the fact that Spring is creating only ONE context for any test class configured using same config.xml files.
      E.g.: having 2 different test classes which are using same config files for loading the Spring context, than, Spring will create only one ApplicationContext used by both classes:
      @ContextConfiguration(locations = { “app-config1.xml”, “app-config2.xml” })
      @RunWith(SpringJUnit4ClassRunner.class)
      public class FirstClassTest{}

      @ContextConfiguration(locations = { “app-config1.xml”, “app-config2.xml” })
      @RunWith(SpringJUnit4ClassRunner.class)
      public class SecondClassTest{}

      I suppose that, in your case, the problem is that Spring is using same context for every test method from your class/classes, so it’s normal the sequence to be incremented.

      Try to annotate your test classes or method with @DirtiesContext. Using this, Spring will recreate the context for every method.

      Hope this will solve your problem,
      Ciprian :)

      Ciprian Pataca

      December 16, 2012 at 11:53 am

  7. Hi Phil,

    Love this project! I have a question for which I seem unable to find an answer.

    When I run my tests, I get a message stating:

    The configured data type factory ‘class org.dbunit.dataset.datatype.DefaultDataTypeFactory’ might cause problems with the current database ‘HSQL Database Engine’

    Is there a way to configure the DataTypeFactory?

    bturner

    October 25, 2012 at 8:44 am

    • Thanks! I guess you are following the information in the DbUnit FAQ (http://dbunit.sourceforge.net/faq.html#typefactory). You should be able to configure custom dataTypeFactories (see the “Custom IDatabaseConnections” section of the readme). Probably something like this:

      Thanks! I guess you are following the information in the DbUnit FAQ (http://dbunit.sourceforge.net/faq.html#typefactory). You should be able to configure this (see the “Custom IDatabaseConnections” section of the readme). Probably something like this:

      <bean id="dbUnitDatabaseConfig" class="com.github.springtestdbunit.bean.DatabaseConfigBean">
      <property name="dataTypeFactory" ref="yourSpecificFactoryBean"/>
      </bean>

      <bean id="dbUnitDatabaseConnection" class="com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean">
      <property name="databaseConfig" ref="dbUnitDatabaseConfig"/>
      </bean>

      Phillip Webb

      October 25, 2012 at 8:57 am

  8. Thanks, Phil. That really helped. There was a little more to it hat what you have above, but it was easily discoverable. For others coming here, this is what I ended up with:

    bturner

    October 26, 2012 at 7:32 am

  9. Darn! Your site does not allow me to paste in code!

    Well, I just had to add another bean specifying the datatype factory, to which you allude. And, I had to add a dataSource property reference to the dbUnitDatabaseConnection bean.

    bturner

    October 26, 2012 at 7:34 am

  10. But, I have a new problem. Others have discussed the insert_identity problem here. DbUnit solves this quite nicely with InsertIdentityOperation class. See http://www.dbunit.org/apidocs/org/dbunit/ext/mssql/InsertIdentityOperation.html. Unfortunately, in my environment, I recently started this gig, no one had local DBs and did everything in one shared DB, which is also the same DB to which the CI server points. So, having InsertIdentityOperation.REFRESH is very valuable to us. I am guessing we can work around the problem by using DatabaseOperation.REFRESH and using data that we, the entire staff, not just our team, are “untouchable” entries in the common DB. That sounds like a recipe for disaster.

    bturner

    October 26, 2012 at 7:44 am

  11. Nice article…I Implemented it and worked fine for normal web applications. But when i used dbunit with Coherence based applications, it does not work. I see that DBUnit populates the (in memory) database, but when coherence starts, the whole application context is initilized again and this time dbUnit does not pouplate the dataset in memory db and my tests fail. Anyone used dbUnit with oracle coherence?

    Sunil

    November 7, 2012 at 11:02 am

  12. How can I used Oracle Sequences to auto-generate primary keys for my tables while exporting data into Oracle using DBUnit ?

    Jagadeesh

    May 23, 2013 at 9:10 am

  13. I am using Oracle database. I am able to run my unit tests using dbUnit (DataSourceDatabaseTester). I would like to use springtestdbunit. Is there a way to set a particular database schema? User specified in my datasource has access to multiple schemas, I want to run my tests on a particular schema. Please advice.

    Sasha

    November 7, 2013 at 10:25 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: