Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Check out DBUnit. It's made specifically to solve your problem. – Sergio Nov 24 '13 at 5:35
up vote 8 down vote accepted

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

share|improve this answer
I didn't know about the part that HSQL can mock another db vendor's dialect. Thank you. – Dog Nov 25 '13 at 7:31
@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

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.
share|improve this answer
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. – Dog 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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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