Java – How to Test the Data Access Layer

javaprogramming practicestddtesting

I have a DAO method that utilizes Spring for JDBC access. It calculates a seller's success rate of selling an item.

Here is the code:

public BigDecimal getSellingSuccessRate(long seller_id) {
    String sql = "SELECT SUM(IF(sold_price IS NOT NULL, 1, 0))/SUM(1) 
                  FROM transaction WHERE seller_id = ?";
    Object[] args = {seller_id};
    return getJdbcTemplate().queryForObject(sql, args, BigDecimal.class);
}

How should I go about testing this method or any DAO method with JUnit? What are some best practices to test the data access logic? I am thinking of testing it against an embeddable database loaded with some data, but shouldn't we do integration tests similar to a production environment in terms of RDBMS and the schema?

Best Answer

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

Related Topic