What is good practice for java agile integration testing DAO -> database?

What is current good practice for agile integration testing of DAOs aga开发者_JAVA百科inst a real database schema in the Java landscape?

(By 'agile' I mean what is the most streamlined, automated & simple solution to this problem).

I hope to achieve automation of tests that prove the data access layer integrates seamlessly with real running instances of a database. It is important that we test our custom SQL against specific vendor DBs. I.E. if we write T-SQL we want to test against SQL Server.

Should a single database be dedicated to all running tests, but with no commits? Or should each test environment have a dedicated database?

How do people deal with setup and teardown? Is DBUnit still in popular use? How could Spring help?

It is important that we test our custom SQL against specific vendor DBs

So you should create a suite of test for each vendor DB

@RunWith(Suite.class)
@Suite.SuiteClasses({})
public class SQLServerTestSuite {

    @BeforeClass
    public static void setUpClass() throws Exception {

    }

    @AfterClass
    public static void tearDownClass() throws Exception {
        // clean database
    }

}

Should a single database be dedicated to all running tests, but with no commits ?

It is recommended you perform SQL commands but with rollback command because if using commit you can change the state of the current test as well as change the state of others tests. Otherwise, you can deal with unexpected behavior.

For each developer, it is recommended a database sandbox. This allows each user to modify the database in any way they see fit and to exercise the application with tests without worrying about any interactions between their tests and the tests of other users

How do people deal with setup and teardown ?

public class PersonTest {

    @Before
    public void setUp() {
        // set up state to run the test
    }

    @After
    public void teardown() {
       // Transaction rollback
    }

}

The nice thing about Transaction rollback pattern is that it leaves the database in exactly the same state as when we started the test regardless of what changes we made to the database contents

Is DBUnit still in popular use ?

DBUnit is used to compare the database tables against an XML file that contains the expected values. You should keep in mind DBUnit deal with hand-coded settings. However, although this approach is more thorough it is extremely tedious to develop and maintain these kinds of tests. In addition, the tests do not detect a missing mapping for a newly added field or property.

How could Spring help ?

Spring has built-in support to Transaction rollback pattern and supports named parameter (SqlParameterSource) which allows you to externalize plain jdbc queries in a multiline and readable xml file for each vendor database, for instance

<?xml version="1.0" encoding="UTF-8"?>
<queries>
    <query name="PERSON_BY_ID">
        <![CDATA[
            select 
                *
            from 
                PERSON
            where
                PERSON.ID = :integerId
        ]]>
    </query>
</queries>

Notice the query parameter. It follows JavaScript code style where you declare the kind of parameter as prefix. Now you can create a class where you define the set of named queries

public class SQLServerQuery {

    public static final String PERSON_BY_ID = "PERSON_BY_ID";

}

For dinamic queries, it is usually important to detect bugs in the logic of the query such as using < instead of <=. To catch these kinds of bugs, we need to write tests that populate the database with test data, execute the query, and verify that it returns the expected objects. Unfortunately, these kinds of tests are time consuming to both write and execute.

I would say that your DAO's integration test need only be concerned with ensuring that the ORM's QL e.g. HQL, or JDBC SQL, is valid. I think that it is reasonable to assume that the query languages themselves have been tested thoroughly against multiple drivers/databases. Following through with this, using an "in memory" database such as HSQLDB is reasonable.

"Spring Test" is aimed at integration testing and is great at setting up an IoC context, providing transactions, setting up a database session, creating a schema and then dropping it post test, rolling back transactions after a test... so much so that I'm sitting on the fence with regards to labelling some of my database tests as integration tests (I personally think that so long as the unit test does not cross process boundaries and that their set up is frictionless, they constitute a reasonable unit test).

A few good System Tests, on the other hand, should test your system end-to-end and with all of the target databases. There's always a great deal of effort in setting up system tests and, because you're testing the system itself, there's much help that is applicable in terms of frameworks and toolkits.

I think one database(per developer) should be fine as long as you make sure to tear down data you setup at the start of the test. If you are not dependent on any initial set of data, then you can simply clean the tables. DBUnit comes out to be a nice solution in this case.

Sometimes you are dependent on some initial set of data not for all tests but for few tests mainly for select queries. If you are dependent on some set of data, it becomes tricky. You can still manage with Dbunit by running seed data before start of each test and tear down at the end, but it makes tests run for longer duration.

In this case, what I have found better is to create a simple utility class to keep an id of whatever new data have been created at the start of test and just delete it in the end.

Maybe I don't understand the purpose of what you are trying to do or why this test approach was chosen, but have you considered using mock objects? You could write Junits tests using mock objects and integrate it with Hudson for automation. You could mock DAOs, preparedstatements, connections, resultsets and so on.

The DAO is a proven pattern. And so is JDBC or anything else you might use for interfacing. Are you trying to prove that it works? or do you really mean to test the queries or SQL within the methods of the DAO?