Tutorial: DbUnit with EJB3

Introduction

DbUnit is an open source add-on to JUnit. When programming with Java, JUnit allows comfortable execution of automated test cases that you specify; this is called test-driven development. The advantage is that you can always check whether something is broken in your project by running the tests.

With DbUnit, this principle is broadened to database use. It can be tedious to bring the database into the state (mostly table contents) which you need for testing. DbUnit can take a snapshot of an existing database and re-apply it before and/or after each testcase run.

EJB3 (Enterprise Java Beans 3) is a Java Enterprise method to handle Java Beans objects within an application server (such as JBoss). One of the most prominent advantages is the ability to persist (save/load) objects into a relational database (such as PostgreSQL), doing the object/relational mapping automatically. To do this, it uses a method named JPA (Java Persistence API), which is currently implemented by the Hibernate framework.

Apparently nobody used EJB3 and DbUnit before and documented it on the web; so I will give it a try. Maybe it is useful to you.

DbUnit Setup

Please verify the following steps before proceeding:

• dbunit.jar is in your classpath
• the JDBC driver (hsqldb.jar, pg80jdbc.jar, etc) is in the classpath
• you already have a JUnit TestCase and and would now like to restore database contents before and after each tests.

This article will only benefit you if these conditions are fulfilled.

Writing a TestCase

Open your test case class (mine is called DatabaseUnitTest). DbUnit requires one major thing from you: That you extend your test class from DBTestCase, instead from TestCase. There are ways around this, but they involve medium complexity.

DBTestCase is easiest to configure if you store/get information such as JDBC properties from system properties, so write a constructor. I use a PostgreSQL database; your information tokens may vary.

    public DatabaseUnitTest() {
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, "org.postgresql.Driver");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, "jdbc:postgresql://localhost/example_db");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, "postgres_user");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, "postgres_user_password");
    }

This way, you can draw the JDBC data out with System.getProperty(), which will get useful later on.

Getting the Database Content

DbUnit provides some facilities to get the content from a database and store it into a flat XML file. Later on, the content will be loaded from that XML file. Insert the following method into your class:

    protected void loadCurrentDatabase() throws Exception {
        String driverName = System.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS);
        String conName = System.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL);
        String user = System.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME);
        String pass = System.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD);
        Class.forName(driverName);
        Connection jdbcConnection = DriverManager.getConnection(conName, user, pass);
        IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

        // Add partial data set.
        // IDataSet fullDataSet = connection.createDataSet();
        QueryDataSet partialDataSet = new QueryDataSet(connection);
        String schema = "public";
        String[] dataTypes = new String[] { "TABLE" };
        ResultSet publicTables = jdbcConnection.getMetaData().getTables(null, schema, null, dataTypes);
        while (publicTables.next()) {
            String table = publicTables.getString(3);
            partialDataSet.addTable(table);
            System.out.println(table);
        }
        FlatXmlDataSet.write(partialDataSet, new FileOutputStream("docs/" + TEST_DB_FLAT_FILE));
    }

You see that I first tried to get a full data set instead of specifying the tables that I want. This had the disadvantage that DbUnit tried to copy a lot of system tables, which caused some problems. So I use the standard JDBC DatabaseMetaData to get all tables from schema "public", where my tables are.

TEST_DB_FLAT_FILE is set to "current_db.xml", but you can use any file name. I store it in a folder called "docs"; again, this is your choice. What is important is that you copy that file to the package of your test case, where it will be loaded in the next section.

Note: I tried the same approach with HSQLDB (an in-RAM database embedded with the application server); however, in embedded mode it allows only one connection (which is hogged by the application server). This would probably go away if I started it in server mode.

Loading the Data

DBTestCase demands that you override the method getDataSet() to determine which dataset to use. Specify as below. The flat XML file must be in the same package as your test class.

    @Override
    protected IDataSet getDataSet() throws Exception {
        InputStream flatFile = getClass().getResourceAsStream(TEST_DB_FLAT_FILE);
        IDataSet dataSet = new FlatXmlDataSet(flatFile);
        dataSet = new FilteredDataSet(new DatabaseSequenceFilter(getConnection()), dataSet);
        return dataSet;
    }

You see one special thing: Instead of returning the loaded database promptly, I use a FilteredDataSet. This is important if you have foreign keys; the DatabaseSequenceFilter ensures that the tables are re-created in the right order such that no foreign key violations occur.

Determining the Database Refresh Method

The last remaining thing to do is to determine how to refresh the database. For that, DbUnit provides two methods to be overridden:

    @Override
    protected DatabaseOperation getSetUpOperation() throws Exception {
        return DatabaseOperation.CLEAN_INSERT;
    }

    @Override
    protected DatabaseOperation getTearDownOperation() throws Exception {
        return DatabaseOperation.NONE;
    }


The CLEAN_INSERT method is the safest but also the slowest approach, rebuilding the database for each test to be run. The DbUnit components description page provides much more information on how to tune that.

Running the Tests

Now you can run your tests. Start your application server, specify a test method, and run the test case.

    public void testDatabaseUnitTest() throws Exception {
        // Insert some test code here.
        Connection connection = getConnection().getConnection();
        PreparedStatement stat = connection.prepareStatement("select count(*) from example_table");
        ResultSet resultSet = stat.executeQuery();
        int count = -1;
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        resultSet.close();
        stat.close();
        assertTrue("Must be 10 units, are " + count, 10 == count);
    }

Notice that the DbUnit connection gives us the JDBC connection. From here on, we can verify that the database is in a state that we expect.

Conclusion

So far, this document has not had any EJB3 specific issues (except the foreign keys, but those are used in any half-decent application). If this page helped you solve a problem, please drop me a short note. It is always nice to have feedback.

If you thought this article sucked, please also write me and propose how to make it better.

Thanks for leaving a part of your attention span here. Have a good time!