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?

  • Check out DBUnit. It's made specifically to solve your problem. – Sergio Nov 24 '13 at 5:35

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.


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):

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

    public void tearDown() {
        try {
        } catch (SQLException e) {

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

    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()) {

        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

  • 2
    I didn't know about the part that HSQL can mock another db vendor's dialect. Thank you. – Michael Nov 25 '13 at 7:31
  • 1
    @Dog this can be done via the database properties such as sql.syntax_mys=true which changes the way hsqldb works: "This property, when set true, enables support for TEXT and AUTO_INCREMENT types and also allow compatibility with some other aspects of this dialect." while sql.syntax_ora=true does "This property, when set true, enables support for non-standard types. It also enables DUAL, ROWNUM, NEXTVAL and CURRVAL syntax and and also allow compatibility with some other aspects of this dialect." – user40980 Nov 25 '13 at 15:07
  • DBUnit is the way :) – Silviu Burcea Nov 27 '13 at 8:51
  • @SilviuBurcea DBUnit certainly makes much of the 'plumbing' of setting up a complex database testing environment much easier than doing it by hand. Its still sometimes useful to know how to do it by hand if you need to (the 'by hand' approach mentioned above could be migrated to other languages where DBUnit isn't an option). – user40980 Nov 27 '13 at 16:23
  • You can have a look at Acolyte – cchantep Jul 29 '16 at 8:22

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.
  • Woops when I said production environment really I meant a simulation of it. Thank you for your reply, I will take a look at Mockito because that is something I have been wanting to learn too. – Michael Nov 24 '13 at 15:35

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.

  • If using an in-memory database, then a drop-create approach before all test suites run could be used in place of roll-back transaction right, which is just a lot faster. – Downhillski Mar 29 '18 at 18:37
  • Never thought to do it that way before. In our tests, most tests create a user 'x' though which is unique. Creating a db once would mean changing the tests to reuse those objects. – Carra Apr 3 '18 at 14:27
  • I know, we are on the same page and I like your approach. your approach guarantees each test cases can be independently run regardless of the order, and every time before it is run the state of the data table is the same. – Downhillski Apr 3 '18 at 18:43
  • That is correct, the order doesn't matter then. We've seen tests fail before because the unit test run order is different on our build pc and our local machine. – Carra Apr 5 '18 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.