The problem with using a ‘real’ database for unit testing is the setup, take down, and isolation of the tests. You don’t want to have to spin up an entirely new MySQL database and create tables and data just for one unit test. The problems with this have to do with the external nature of the database and your test database is down, your unit tests fail. There are also issues with making sure you have a unique database for testing with. They can be overcome, but there is a simpler answer.

Mocking the database is one option however it doesn’t test the actual queries that are run. It can be used as a much simpler solution when you want to make sure the data from the DAO goes through the system properly. But for testing the DAO itself you need something behind the DAO has the data and the queries run properly.

The first thing to do is use a in memory database. HyperSQL is an excellent choice for this because it has the ability to emulate the dialect of another database – so that the minor differences between databases stays the same (data types, functions and the like). hsqldb also has some nice features for unit testing.

db.url=jdbc:hsqldb:file:src/test/resources/testData;shutdown=true;

This loads the state of the database (the tables, initial data) from the testData file. shutdown=true will automatically shut down the database when the last connection closes.

Using dependency injection, have the unit tests select a different database than what the production (or test, or local) builds use.

Your DAO then uses the injected database for which you can launch tests against the database.

The unit tests will then look something like (bunch of boring stuff not included for brevity):

    @Before
    public void setUpDB() {
        DBConnection connection = new DBConnection();
        try {
            conn = connection.getDBConnection();
            insert = conn.prepareStatement("INSERT INTO data (txt, ts, active) VALUES (?, ?, ?)");
        } catch (SQLException e) {
            e.printStackTrace();
            fail("Error instantiating database table: " + e.getMessage());
        }
    }

    @After
    public void tearDown() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void addData(String txt, Timestamp ts, boolean active) throws Exception {
        insert.setString(1, txt);
        insert.setTimestamp(2, ts);
        insert.setBoolean(3, active);
        insert.execute();
    }

    @Test
    public void testGetData() throws Exception {
        // load data
        Calendar time = Calendar.getInstance();
        long now = time.getTimeInMillis();
        long then1h = now - (60 * 60 * 1000);  // one hour ago
        long then2m = now - (60 * 1000 * 2);   // two minutes ago
        addData("active_foo", new Timestamp(then1h), true);     // active but old
        addData("inactive_bar", new Timestamp(then1h), false);  // inactive and old
        addData("active_quz", new Timestamp(then2m), true);     // active and new
        addData("inactive_baz", new Timestamp(then2m), false);  // inactive and new

        DataAccess dao = new DataAccess();
        int count = 0;
        for (Data data : dao.getData()) {
            count++;
            assertTrue(data.getTxt().startsWith("active"));
        }

        assertEquals("got back " + count + " rows instead of 1", count, 1);
    }

And thus, you’ve got a unit test that calls the DAO and is using the data that was set up in an on the fly database that exists for the duration of the test. You don’t have to worry about external resources or the state of the database before the run, or restoring to a known state (well, the ‘known state’ is ‘does not exist’ which is trivial to revert to).

DBUnit can make much of what I described a simpler process in setting up the database, creating the tables and loading the data. If you were going to need to use the actual database for some reason, this is by far the better tool to use.

^{The above code is part of a maven project I wrote for proof of concept TestingWithHsqldb on github}

First, you should never do testing in a production environment. You should have a test environment that mirrors your production environment and do integration tests there.

If you do that, then you can do a number of things.

• Write unit tests that test to see if the appropriate SQL is being submitted to a mock item using a mocking framework such as Mockito. This will ensure that your method is doing what it is supposed to do and takes integration out of the picture.
• Write test SQL scripts demonstrating the appropriateness of the SQL you tested for in your unit tests. This can help with any tuning issues you may come upon, as you can also run explains and such based on your test scripts.
• Use DBUnit, as mentioned by @Sergio.

On our project, each developer is running an empty database, its structure is the same as the production database.

In each unit tests TestInitialize, we create a connection & transaction to the database plus some default objects we need for each test. And everything gets rolled back after the end of each method or class.

In this way, it’s possible to test the sql layer. In fact, every query or database call has to be tested in this way.

The downside is that it’s slow, so we put it in a separate project from our regular unit tests. It’s possible to speed this up by using an in memory database but the idea remains the same.