As Published In July/August 2005

DEVELOPER: Testing

Testing 1, 2, 3 . . .

By Phil Zoio

Best practices for testing J2EE database applications

It wasn't too long ago that quality assurance (QA) teams played the leading (if not the only) role when it came to software testing. These days, however, developers are making an enormous contribution to software quality early in the development process, using automated unit-testing techniques. Most developers take for granted the need to use tools such as JUnit for comprehensive, automated testing, but there is little consensus on how to apply unit testing to the database operations associated with an application.

This article delineates best practices that can help you get the most out of your database testing environment and can ensure that your applications are robust and resilient. The primary focus of the article is on J2EE applications interfacing with Oracle Database through JDBC or Oracle TopLink, although the concepts also apply to applications written for other application environments, such as .NET. The scope of the article is restricted to functional testing and does not encompass performance or load testing.

Database Operations Tests: What They Are, Why They Are Needed

Testing J2EE applications is often difficult and time-consuming at best, but testing the database operations portion of a J2EE application is especially challenging. Database operations tests must be able to catch tricky logic errors—when a query returns the wrong data, for example, or when an update changes the database state incorrectly or in unexpected ways.

For example, say you have a PERSON class that represents a single PERSON table and that database operations on the PERSON table are encapsulated by a Data Access Object (DAO), PersonDAO , as follows: 

public interface PersonDAO 
{
        /**
         * Returns the list of persons 
     * with the given name 
         * or at least the minimum age
         */
        public List listPersons(
        String name, Integer minimumAge);
        /**
         * Returns all the persons 
         * in the database
         */
        public List listAllPersons();
}

In this simple PersonDAO interface, the listPersons() method should return all rows (from the PERSON table) that have the specified name or the specified value for minimum age. A test to determine whether you've correctly implemented this method in your own classes must take into consideration several questions: 

• Does the method call the correct SQL (for JDBC applications) or the correct query-filtering expression (for object role modeling [ORM]-based applications)?

• Is the SQL- or query- filtering expression correctly written, and does it return the correct number of rows?

• What happens if you supply invalid parameters? Does the method behave as expected? Does it handle all boundary conditions appropriately?

• Does the method correctly populate the persons list from the result set returned from the database?

Thus, even a simple DAO method has a host of possible outcomes and error conditions, each of which should be tested to ensure that an application works correctly. And in most cases, you'll want the tests to interact with the database and use real data—tests that operate purely at the individual class level or use mock objects to simulate database dependencies will not suffice. Database testing is equally important for read/write operations, particularly those that apply many changes to the database, as is often the case with PL/SQL stored procedures.

The bottom line: Only through a solid regime of database tests can you verify that these operations behave correctly.

The best practices in this article pertain specifically to designing tests that focus on these types of data access challenges. The tests must be able to raise nonobvious errors in data retrieval and modification that can occur in the data access abstraction layer. The article's focus is on database operations tests —tests that apply to the layer of the J2EE application responsible for persistent data access and manipulation. This layer is usually encapsulated in a DAO that hides the persistence mechanism from the rest of the application.

Listing Best Practices

Before stepping through the best practices, download the sample test harness (see the Installing and Using the Best-Practice Test Harness " sidebar) and follow along, using Oracle JDeveloper.

The following list enumerates the testing best practices covered in this article:

Practice 1: Start with a "testable" application architecture.
Practice 2: Use precise assertions.
Practice 3: Externalize assertion data.
Practice 4: Write comprehensive tests.
Practice 5: Create a stable, meaningful test data set.
Practice 6: Create a dedicated test library.
Practice 7: Isolate tests effectively.
Practice 8: Partition your test suite.
Practice 9: Use an appropriate framework, such as DbUnit, to facilitate the process.

With the example project set up, it's time to start examining these best practices.

Practice 1: Start with a "Testable" Application Architecture

Choose the right application architecture to make sure your application can be easily tested. In test-driven-development (TDD) circles, this is commonly called "design for testability." No matter how rigorous or sophisticated your test environment is, it can't do much good if the application is not amenable to testing. A database operation is easy to test when 

• It does not depend on the runtime environment (container) provided by an application server

• It has no unnecessary external dependencies

• External dependencies that do remain can easily be satisfied in a test environment

Traditional approaches to J2EE application development don't stack up well against these criteria. Current-generation EJB 2.0 and 2.1 applications with CMP Entity Beans are hard to test, because database operations must run within an EJB container. However, this situation is likely to improve significantly with EJB 3.0. Oracle's EJB 3.0 Preview provides an Entity Test Harness (see oracle.com/technetwork/articles/debu_testability_of_ejb.html) allowing database operations to be tested outside of the container.

Database access from within Servlets and JSPs is also hard to test, for much the same reason. Database operations are difficult to isolate from other actions performed within the context of the Web application.

Applying the practice. The easiest way to make your application testable is to use DAOs, which are particularly good at eliminating unnecessary dependencies. By definition, the only dependencies a DAO has are those specifically for data access, such as a Connection or DataSource instance, and it's easy to satisfy the dependencies in a test environment. In addition, DAOs hide data-access dependencies from the rest of the application, making other parts of it easier to test.

Our DAO implementation, PersonDAOImpl , has a simple setter method that satisfies the dependency on a Connection instance: 

public void 
setConnection(Connection conn) 
{
        this.connection = conn;
}

Note that the test, not the DAO, is responsible for obtaining the connection.

More typically, a real-world DAO is likely to be populated by a DataSource instance, rather than the Connection itself. In either case, there is no dependency on the lookup mechanism.

To create a testable architecture, it is advisable to configure your application to use a Dependency Injection (DI) container. A DI container will encourage you to write your application in a way that eliminates unnecessary dependencies.

Try it out. Install the JDBC and TopLink project. (See the "Testing Oracle TopLink Applications" sidebar for installation information.) To run the TopLink tests, set the system property useToplink . Open the Project Properties dialog box, select Run/Debug , and then enter -DuseToplink=true into the Java Options field, as shown in Figure 1.

Figure 1: Configuring the test suites to use TopLink instead of JDBC

Practice 2: Use Precise Assertions

Use precise assertions that describe exactly what state the system should be in when the test is run. A database test is precise when only one possible outcome satisfies the test. A database query that simply verifies that a SQL statement has executed successfully and returned some results is not precise, because even successful executions can return the wrong result set. Here's an example of an imprecise test: 

public class TestListPersons 
extends BaseDatabaseTest 
{
   ...
   /**
    * Simple imprecise test that 
    * checks that results are 
    * returned but does not
    * count the values returned
    */
   public void testListPersons() 
   {

      List results 
        = dao.listPersons(
          "Phil", new Integer(25));
      assertNotNull(results);
      assertTrue(results.size() > 0);
   }
}

As shown in the assertions in the code, the test proves only that the DAO implementation returns something (not null) and that the result is greater than 0—we don't know, however, whether the result list contains accurate data.

Applying the practice. Precise database tests specify the number and actual values of entries to be returned by query-based DAOs. For update operations, database tests should also verify that expected changes have been applied to a precisely defined set of tables and rows.

You can modify the preceding example with precise assertions, as shown in Listing 1.

Code Listing 1: Testing with precise assertions 

public void testPreciselyListPersons() 
{

        List results = dao.listPersons("Phil", new Integer(25));
        assertNotNull(results);
        assertEquals(2, results.size());

        for (Iterator iter = results.iterator(); iter.hasNext();) 
        {
                Person person = (Person) iter.next();
                assertNotNull(person.getId());
                assertEquals("Phil", person.getFirstName());
                assertNotNull(person.getAge());
                assertTrue(person.getAge().intValue() >= 25);
                assertNull(person.getSurName());
                assertNull(person.getGender());
        }
}

Our test now confirms that the DAO implementation obeys the listPersons() method contract, by verifying that for all the rows returned, the name of the person is Phil and the age is 25 or more (as defined by the method arguments).

Try it out. In Oracle JDeveloper, navigate to the test class TestListPersons . Also open the PersonDAOImpl class (under TestListPersons ). Compare the precision of the methods testListPersons() and testPreciselyListPersons() in testing the listPersons() implementation.

Practice 3: Externalize Assertion Data

Place your assertion data in an external repository, to make your tests easier to manage and maintain. Most developers agree that writing precise assertions is a great idea but might not like the way one of our precise assertions— assertEquals(2, results.size()) —is written, because the assertion value is hard-coded. If you're testing a large application with hundreds or even thousands of tests, you certainly don't want hundreds or thousands of hard-coded String or int values scattered throughout your test code, for two reasons. First, if your test data changes, you want to be able to easily find the assertion data that needs to change. Second, you will want to take advantage of mechanisms for sharing assertion data across different tests. The solution to the problem is to externalize your assertion data, as you would externalize String messages in your production code.

Applying the practice. Our example application includes a simple property-file-based solution to the problem of externalizing assertion data. Listing 2 shows an example at work.

Code Listing 2: Testing with precise assertions 

public class TestListAllPersonsExternal extends BaseDatabaseTest 
{

        private int ALL_RESULT;

        protected void setUp() throws Exception
        {
                ...
                //get the externalized assertion data
                ALL_RESULT = getAssertionDataInt("ALL_RESULT");
        }

        public void testPreciselyListAllPersons()
        {

                List results = dao.listAllPersons();
                assertNotNull(results);
                assertEquals(ALL_RESULT, results.size());
                ...
        }
        ...
}

An item of assertion data is stored in an instance field and loaded in setUp() , by use of the inherited getAssertionDataInt() method. In this example, you load the count of all the people in the database into the ALL_RESULT field. You can see how the test method assertion no longer relies on hard-coded data.

Our little assertion-data-loading framework attempts to load assertion values as follows:

1. It looks for a property called ALL_RESULT in the TestListAllPersonsExternal .properties file, located in the same package as the TestListAllPersonsExternal class.

2. It looks for the ALL_RESULT property in the assertions.properties file, also located in the TestListAllPersonsExternal class's package.

3. It looks for the property in the assertion.properties file in the class path's root package.

This is quite a convenient search scheme, because it allows you to group assertion data into items specific to a particular test class, shared among test classes in a package, and shared among all of the application's test classes.

Try it out. Compare the contents of TestListPersons and TestListPersonsExternal . Note that the two classes are virtually identical, except that TestListPersonsExternal uses externalized assertion data.

Practice 4: Write Comprehensive Tests

Your test suite should be comprehensive, covering all database operations and as many scenarios as practical constraints will allow. A set of tests is comprehensive if it covers all the scenarios you could reasonably expect to materialize in your application. Our tests need to verify that each method behaves correctly, given a complete range of preconditions and not simply the most obvious ones. In our listPersons() example, it's not difficult to think of some alternative preexecution states that may affect the way the method behaves: 

• No connection is supplied to the DAO implementation before listPersons() is called.

• listPersons() is called with one or both of the arguments set to null.

• listPersons() is called with different sets of data.

In each case, you'd want to verify that the correct data is returned. In the same way precise tests allow us to verify that a single operation behaves correctly under given preconditions, comprehensive tests determine that all operations behave correctly under all reasonably expected preconditions.

Applying the practice. The task of making your test environment comprehensive begins with the mental exercise involved in imagining possible scenarios. In the tradition of test-first development, this should be done during or even before implementation of the method under test. Second, a subjective judgment needs to be made about whether each scenario is sufficiently likely to occur to warrant incorporating it into the test suite. Extreme Programming (XP)-style pair programming is well suited to both of these tasks—two brains are better than one in imagining possible scenarios and smarter than one in judging which tests to actually implement.

Don't be tempted to use output from Java code coverage tools as proof that your database testing coverage is complete. Code coverage is necessary but not sufficient for database tests. Quite complex data retrieval logic can be embodied in a single SQL statement, each statement comprising several individually testable scenarios.

As you've probably experienced, making tests comprehensive is one of the hardest feats when writing tests. It's an ideal rather than a reality for virtually all projects. Developers need to find the right balance between covering the relevant test scenarios, on the one hand, and becoming overwhelmed with the burden of testing, on the other.

In the accompanying source code, the TestListPersons class contains an example of a fairly comprehensive set of tests for the listPersons() method, which interested readers can peruse at their leisure.

Try it out. In Oracle JDeveloper, open the implementation class PersonDAOImpl and the test class TestListPersons . Try to find a scenario in the implementation that isn't covered by any of the tests.

Practice 5: Create a Stable, Meaningful Test Data Set

Invest time and effort in creating a stable set of meaningful test data on which to base your tests. With good test data available, writing database tests becomes much easier and developers can confidently make precise assertions, knowing that the data they need will be present.

Creating test data is a cornerstone of database development in a TDD environment. For database operations, development should be test-data-driven. Before writing tests for a use case, construct a data set. The test data set does not always need to be large; it simply needs to be sufficient to cover the scenarios relevant to the operations being tested.

Creating test data may be a tedious job, but it's worth putting time and thought into creating data that is sensible and well structured. If you create good-quality data early in the project, you'll find it much easier to adapt, extend, or reuse your work as you add new features and tests to your application. Good test data is also extremely useful for user interface testing.

Applying the practice. You can easily create your test data, by either typing out SQL INSERT statements or using a tool to export data into this format. For test automation, however, you'll need a data insertion and removal mechanism that is easily accessible from within a JUnit test case. You can use any of the following: 

A SQL*Plus or SQL*Loader script launched from within JUnit. The accompanying source code uses this method. Interested readers can take a look at the implementation of SqlPlusRunner , which runs a SQL*Plus script via a separate Java process.

• The ANT SQL task.

• A custom test data loader, which reads SQL commands from a text file and executes them via JDBC.

• A library such as DbUnit, which is an extension of JUnit designed specifically to facilitate database testing. DbUnit allows test data to be imported into a database from an XML file and also allows data in the database to be exported into XML and other formats.

• The API of the application itself. Of course, you could not use this mechanism without a working implementation of the API.

In our example application, you use SQL*Plus launched from within Java to load our test data in the test class's setUp() method, as shown in Listing 3.

Code Listing 3: setup() method with SqlPlusRunner 

protected void setUp() throws Exception 
{
        super.setUp();
        SqlPlusRunner.runSQLPlusFile("truncate_data.sql");
        assertEquals(0,SqlPlusRunner.runSQLPlusFile("persons_data.sql"));
        ...
}

Note how the first call to SqlPlusRunner removes existing data (by calling the truncate_data.sql file) from the relevant tables before the test is run. The next call to SqlPlusRunner inserts data (by calling the persons_data.sql file) needed for the test itself.

Try it out. In the Applications Navigator view, open the Resources node. Several SQL files will be visible, as shown in Figure 2. These contain the test data for the project as well as scripts for truncating data and creating and dropping tables. The scripts are executed as part of the test environment and can also be run separately with SQL*Plus.

Figure 2: Resources for test data setup

If You Don't Apply It . . . . Without a commitment to creating meaningful data at an early stage of your project, you'll find yourself repeatedly, and often unnecessarily, having to create new data for new tests, resulting in a less manageable and robust test environment.

Practice 6: Create a Dedicated Test Library

Creating a library of classes dedicated to supporting database tests can greatly ease the burden of testing. As a developer writing database tests, you may find yourself writing quite a lot of code to support your tests. For example, you'll need to obtain and release database connections. You'll need to start and end transactions. You'll also often need to run queries on the state of your database after running tests. Sometimes you'll need to execute operations to modify the database state before running database tests, particularly if you are working in an environment in which cleaning and reinserting data before test runs is not an option.

In all of these situations, you'll benefit from the presence of a dedicated test library—a library of classes contained in a test source folder purely to facilitate test writing, never for production code. You don't need to create the test library in advance but can simply add to it over time. For example, say you write some test code that modifies or inspects the system's state—simply take this code out of the test class and move it to your test library, where it will be available for future use.

Applying the practice. You can write the code that goes into a test library by using quite different rules from those governing production code: 

• Test library code doesn't need to rigorously check for preconditions, such as null pointers, because failures in the tests catch problems of this kind.  

• Test library code does not need to throw checked exceptions. Any checked exception that is caught can simply be rethrown as a runtime exception and can be picked up by the test framework.  

• It is often preferable to use static methods. This distinguishes the objectives behind creating a test library. Your production code will use object instances exposed through interfaces to maximize flexibility and code reuse. Your test utilities, on the other hand, often need to perform very specific tasks and are closely coupled to the test environment itself. Appropriate use of static methods gives you an opportunity to reduce the volume of test support code, because you don't even need to worry about object instantiation.

The following shows our example application's test library in action: 

public void testPersonsWithIdLookup() 
{

        List results = dao.listPersons("Phil", null);
        assertNotNull(results);
        assertEquals(2, results.size());

        for (Iterator iter = results.iterator(); iter.hasNext();) 
        {
                Person person = (Person) iter.next();
                assertNotNull(person.getId());
                assertEquals("Phil", PersonUtils.getFirstName(person.getId(),
                                connection));
                doCommonAsserts(person);
        }
}

The first usage shows how you obtain a database connection. You hide the exact mechanism for getting the connection from the test. The mechanism used by ConnectionUtils for obtaining the connection, the static methods of DriverManager rather than a JDBC DataSource or connection pooling implementation, is specific to the test environment. The second usage shows how you can add application-specific functionality into the test library.

PersonUtils.getFirstName() is just a convenience method that allows you to retrieve an item of information from the database with the fewest keystrokes. The more of this kind of functionality you have in your test library, the more control you can exercise over your test environment.

Try it out. Use the Applications Navigator view to peruse the contents of the co.uk.realsolve.databasetest.library package and its subpackages for examples of test library methods. Check the usages of any individual method by selecting the method name and then entering CTRL + ALT-U.

Practice 7: Isolate Tests Effectively

Isolate tests so that one test's errors or failures don't affect other tests or prevent them from executing successfully. Any database test needs to run with the system preset to some stable known state. Your test environment should give you the freedom to run any set of tests in any sequence. If one test makes changes to the system and does not undo these changes, then unless you've made allowances for this possibility, a whole set of subsequent tests can end up failing. Tests need to be isolated in their effects to prevent this from happening.

Applying the practice. There are three techniques that facilitate test isolation: 

• Undo all changes made before or during test execution.

• Clear all test states prior to any test execution.

• Roll back (rather than commit) changes made during tests.

The first two techniques are all about how you manage test fixtures, the objects encapsulating the known system state and the runtime context at the start of a test. With the first technique, you typically use the JUnit tearDown() method to reset system state. There are some weaknesses to this approach. First, you need to write teardown code, which itself may contain bugs unless rigorously tested. Such bugs can be extremely difficult to find, because they may affect subsequent tests in unpredictable and untraceable ways.

The second technique involves clearing all test states prior to test execution. This is certainly preferable for new applications that don't interface with an existing database. The example below shows how you apply this technique in our test class's setUp() method: 

protected void setUp() throws Exception 
{
        super.setUp();
        SqlPlusRunner.runSQLPlusFile("truncate_data.sql");
        assertEquals(0, SqlPlusRunner.runSQLPlusFile("persons_data.sql"));

        //complete setup of the fixture by initializing DAO with connection to database
                dao = new PersonDAOImpl();
                dao.setConnection(connection = ConnectionUtils.getConnection());
}

Note how the truncate data script runs before the test data is loaded. With very little effort, this guarantees that the test will begin with the database in a predictable state. In spite of this clear advantage, it has its shortcomings: 

• Each test class can use its own test data, which raises the possibility of a veritable explosion of test datafiles. If test operations need to operate on complex sets of data that don't differ that much from test to test, some test data sharing can be beneficial, even if it means that the tearDown() method occasionally needs to be used to reset state. 

• Sometimes it simply isn't possible to reset the data state before every run. If you're developing an application that uses a shared legacy database instance managed by an external DBA team, this may well be the case.

The third technique involves rolling back rather than committing changes made during a test run. This can be quite effective, for two reasons: 

• A modern database such as Oracle Database provides excellent transaction support, so that you can easily undo all work done with the current transaction by simply using Connection.rollback(); . 

• All changes made during an Oracle Database transaction are visible within the transaction. In other words, it is easy to query any changes that would be committed if you were to proceed with the Connection.commit() call. The only restriction is that you need to use the same Connection instance to query changes made during the transaction. This strategy won't work for operations that need to commit more than once during execution.

Try it out. Examine the implementation of setup() in the TestListPersons class, where test data is loaded before each test. Run the class as a Java application. In Oracle JDeveloper's output log, you'll see the SQL*Plus commands executed as shown in Figure 3.

Figure 3: Console output from running TestListPersons as a Java application

Practice 8: Partition Your Test Suite

Divide your full set of tests into individual subsuites, to make your test environment more manageable and to share code and processes.

If you're testing a large database-driven system, you're likely to have hundreds or even thousands of database tests. You need to partition your tests into individual subsuites to keep your test environment manageable. You can choose from a variety of schemes to group your tests effectively: 

• Group tests that rely on the same base set of test data.

• Create separate suites for read-only versus read/write tests.

• Create separate suites for fine-grained tests (possibly operating directly with SQL and PreparedStatements) versus coarse-grained tests that operate at the DAO implementation level.

• Separate tests based on application functional area.

By partitioning your tests effectively, you can create a more coherent, manageable test environment. You'll get more opportunities to share test code, test data, and common setup and teardown operations.

Applying the practice. The JUnit suite() method provides a very convenient mechanism for partitioning test suites. Our example application has the full test suite divided into three subsuites: 

public class AllTests extends TestCase 
{

        public static void main(String[] args) 
        {
                junit.textui.TestRunner.run(suite());
        }

        public static Test suite() 
        {
                TestSuite suite = new TestSuite();
                suite.addTest(SqlPlusBasedTests.suite());
                suite.addTest(DbUnitTests.suite());
                suite.addTest(TestsOfTests.suite());
                return suite;
        }
}

The SQL*Plus-based tests are separated from those that use DbUnit, and an additional test suite for miniframeworks was created for this article. The setup and teardown operations for each of the suites is contained within the relevant suite() method. For example, the SqlPlusBasedTest implements the following suite() method: 

public static Test suite()
{

        TestSuite suite = new TestSuite();
        suite.addTestSuite(TestListPersons.class);
        ... add other tests

        //use test wrapper to drop and re-create tables
        TestSetup wrapper = new TestSetup(suite)
        {
                protected void setUp()
                {
                        SqlPlusRunner.runSQLPlusFile("drop_tables.sql");
                        System.out.println("Setting up all tables ...");
                        SqlPlusRunner.runSQLPlusFile("create_tables.sql");
                }

                protected void tearDown()
                {
                        //no implementation required
                }
        };
        return wrapper;
}

The TestSetup wrapper allows setup common to all tests in the suite, which, in this case, drops and then re-creates the database tables.

Try it out. From Oracle JDeveloper you can run any test class by opening the Java source file, right-clicking, and then selecting Run . With JUnit installed, you can run the class as a JUnit test. JDeveloper displays a JUnit output window that shows the progress of your test run, indicating which tests have failed or caused errors. Figure 4 shows the output of SqlPlusBasedTests.java after a couple of test methods have been modified to fail.

Figure 4: Example output for failing tests

Practice 9: Use an Appropriate Framework, such as DbUnit, to Facilitate the Process

Frameworks such as DbUnit can facilitate various aspects of your database unit testing effort. It's stretching the definition of this best practice a bit to suggest that as a best practice, you should use DbUnit. This is because DbUnit is not a best practice but, rather, a JUnit library extension that helps simplify testing of database applications. The advantage of using DbUnit is that it makes it very easy to apply many of the best practices this article has presented.

Applying the practice. You need a basic understanding of the main DbUnit classes and interfaces to apply the framework. The key interface in a DbUnit-based test is IDataSet . You can best think of IDataSet as representing one or more tables of data. It is possible to represent the entire content of a database schema as a single IDataSet instance. The tables themselves are represented by Itable instances.

There are many implementations of IDataSet , each corresponding to a different datasource or loading mechanism. Three of the most commonly used IDataSet implementations are 

• FlatXmlDataSet : a simple flat-file XML representation of data

• QueryDataSet : data obtained with a SQL query

• DatabaseDataSet : a representation of the contents of the database tables themselves

Here are two really useful DbUnit features: 

• You can read from one IDataSet implementation and write to another. This allows you to, for instance, populate a database from the flat XML file or create the flat XML file with data in a database table or retrieved with a query. This makes it very easy to manage your database test environment. 

• You can compare the contents of data contained in different data sets. Using this functionality, you can compare the contents of a database table or query with those of a flat XML file. This is a powerful feature, because it allows for very precise tests without your having to write much code to support them.

The example below shows the IDataSet comparison feature in action: 

public void testListPersons() throws Exception 
{
        FlatXmlDataSet loadedDataSet = new FlatXmlDataSet(DbUnitUtils
                        .getDbUnitFile("persons_data.xml"));
        QueryDataSet queryDataSet = new QueryDataSet(getConnection());
        queryDataSet.addTable("PERSON", "SELECT * FROM PERSON ORDER BY ID");
        Assertion.assertEquals(loadedDataSet, queryDataSet);
}

This capability allows you to externalize assertion data (in the above example, the assertion data is contained in the XML file). It can also be useful in testing the results of update operations; you can use a query to exclude irrelevant tables and rows from your comparison data.

Unfortunately, DbUnit is quite limited in the kinds of queries it can test. For example, to handle queries with parameters, you must extend the framework. Also, you won't find any support for testing queries run with object-relational mapping (ORM) libraries such as Oracle TopLink and Hibernate. See "Database Testing with DbUnit" for an introduction to DbUnit.

Try it out. Examine and run the TestUsingDbUnit class to get a feel for DbUnit in action. To run the DbUnit tests, you must add dbunit.jar to your JDeveloper project, as follows:

From the Project Properties dialog box, select Libraries -> Add Library -> New . The Create Library dialog box appears.

Navigate to dbUnit.jar, and add it to your new library classpath. Enter DbUnit as the library name, and add it to your project.

Summary

Database operations present challenges you need to meet in setting up and maintaining your test environment. This article has presented best practices that can help you meet these challenges. By following these best practices, you should be better able to effectively write database operations tests and execute them in a productive, coherent, and maintainable test environment.

public List listAllPersons()
{ 
    if (session == null)
 throw new IllegalStateException("Session must be set before business method is called"); 
    
    ReadAllQuery query = new ReadAllQuery(Person.class);
    List persons = (Vector) session.executeQuery(query);
    return persons;
}

public interface DAOFixtureStrategy
{

  /**
   * Called in test's setUp() method to set up connections, sessions, etc.
   */
    public void setUp(DAO dao);
    
  /**
   * Called in test's teardown() method to release connections, sessions, etc.
   */
    public void tearDown(DAO dao);
}

protected void setUp() throws Exception
{
    super.setUp();
    SqlPlusRunner.runSQLPlusFile("truncate_data.sql");
    assertEquals(0, SqlPlusRunner.runSQLPlusFile("persons_data.sql"));
    dao = getDAO();
    getDAOFixtureStrategy().setUp(dao);
}