Is there a set of stubs/mocks for JDBC available anywhere?

Question

For the past few years I've continuously struggled with unit testing database code and all the pain that comes with it. I found this existing thread which I found very enlightening:

• What's the best strategy for unit testing databases?

The author of the accepted answer suggests that it might be useful to mock the entire database layer in order to validate the generated SQL. I didn't think much of it when I first read the answer a few months ago, but recently I have observed several bugs caused by incorrectly generated SQL, wrongly assigned fields, and so on. I do realize that JDBC is rather bloated and error prone to use, but it isn't an option to switch to something different at this point.

The application in question is a batch processor of data feeds, and uses JDBC directly rather than an ORM. All JDBC code is separated into distinct DAO objects where each object has its own interface and stub, besides the actual implementations. This has allowed me to achieve good test coverage of the business layer, but the testing of the database layer is virtually non-existant.

Is there an existing stub implementation of the JDBC (java.sql) interfaces that can be injected into DAO classes and used to validate the generated SQL and possibly send back some preprogrammed results?

Answer 1

I don't know if you have seen it or not but there's MockRunner. It provides many classes that implement the interfaces of JDBC (as well as other J2EEclasses). Here's the JDBC mock objects. There are also quite a few examples.

Answer 2

It sounds like you're having issues in the DAO code itself? Otherwise, the DAO layer is the obvious place to do your mocking, but if you're trying to test the DAO, then you'll need to mock that which comes beneath.

Personally, I tend to stay away from mocking large, complex libraries; if you really need to test the DAO layer directly and the DAO works directly with JDBC, you've got three obvious choices:

1. Run an integrated test that includes the DAO and JDBC along with a Database
2. Add a layer above JDBC with a thinner interface, better suited for mocking.
3. Use JDBC mocks either of your own writing, or some of the items listed above.

I would almost always choose #1 or #2. Because there's a host of possibilities of errors in malformed SQL syntax and the like I tend to lean towards #1. I realize, however, that that's not what you're asking for. ;)

Answer 3

You could test the database directly with dbunit.

Answer 4

jOOQ ships with a MockConnection that can be provided with a MockDataProvider, which is much easier to implement than the complete JDBC API. This blog post shows how to use the MockConnection: http://blog.jooq.org/2013/02/20/easy-mocking-of-your-database/

An example:

MockDataProvider provider = new MockDataProvider() {

    // Your contract is to return execution results, given a context
    // object, which contains SQL statement(s), bind values, and some
    // other context values
    @Override
    public MockResult[] execute(MockExecuteContext context) 
    throws SQLException {

        // Use ordinary jOOQ API to create an org.jooq.Result object.
        // You can also use ordinary jOOQ API to load CSV files or
        // other formats, here!
        DSLContext create = DSL.using(...);
        Result<MyTableRecord> result = create.newResult(MY_TABLE);
        result.add(create.newRecord(MY_TABLE));

        // Now, return 1-many results, depending on whether this is
        // a batch/multi-result context
        return new MockResult[] {
            new MockResult(1, result)
        };
    }
};

// Put your provider into a MockConnection and use that connection
// in your application. In this case, with a jOOQ DSLContext:
Connection connection = new MockConnection(provider);
DSLContext create = DSL.using(connection, dialect);

// Done! just use regular jOOQ API. It will return the values
// that you've specified in your MockDataProvider
assertEquals(1, create.selectOne().fetch().size());

There is also the MockFileDatabase, which helps you matching dummy results with SQL strings by writing a text file like this:

# This is a sample test database for MockFileDatabase
# Its syntax is inspired from H2's test script files

# When this query is executed...
select 'A' from dual;
# ... then, return the following result
> A
> -
> A
@ rows: 1

# Just list all possible query / result combinations
select 'A', 'B' from dual;
> A B
> - -
> A B
@ rows: 1

select "TABLE1"."ID1", "TABLE1"."NAME1" from "TABLE1";
> ID1 NAME1
> --- -----
> 1   X
> 2   Y
@ rows: 2

Answer 5

While I'm a huge fan of unit testing in general I've found it to be of limited value with DAOs.

What I've seen is while it is entirely possible to write the tests (using any of the mocking APIs - JMock, EasyMock, etc), they typically work straight-off (the logic is so basic how couldn't they) only breaking when you change the code (adding a value for example) and that just makes them a burden on the code base.

I think this is because my DAOs typically follow the form:

• get connection.
• create statement.
• set values.
• read values (for load operations).
• clean-up.

You then make assumptions about how the JDBC driver will/is work(ing) and you get a test that's really doing nothing more than testing some simple code gets called in the order it is declared.

Errors originating from DAOs typically occur within the database (key violations, bugs in stored procs, etc) and unless you are running the system as a whole you aren't going to see these errors.

These days I tend to let the higher levels of testing - integration and the like - exercise the DAO code hitting the actual database in doing so and hopefully catching the sort of errors I mentioned sooner rather than later.

Answer 6

If you want to test persistence layer (ORM, DAO, ...) is acting as expected according various JDBC cases (e.g. when it gets such result set/update count, then it should do this and that), then Acolyte framework must be considered.

It allow to build JDBC connection you manage throught handler, so you choose what is returned for each query/update: https://github.com/cchantep/acolyte

Disclosure: this is my framework.

Answer 7

Specmatic JDBC stub does exactly this. Here is a video showcasing the power of JDBC stubbing in Specmatic. https://www.youtube.com/watch?v=2-zqzBvFjnc

Specmatic allows you to define / record and replay expectations. Expectations are mappings of queries and results, I am guessing that is what you are looking for when you mentioned this.

validate the generated SQL and possibly send back some preprogrammed results

Here is an example.

{
  "query": "select product0_.id as id1_0_0_, product0_.cost as cost2_0_0_, product0_.name as name3_0_0_ from product product0_ where product0_.id=1",
  "response": {
    "rows": [
      {
        "id1_0_0_": 1,
        "cost2_0_0_": 100,
        "name3_0_0_": "Artificial Lamp"
      }
    ]
  }
}

These expectations can be written by hand or recorded based on actual interactions between the API and a real database. This allows you to verify that your application is only interacting with the database as intended by you.

Here is a detailed writeup.

Hope this helps.

Disclosure: I am co-founder and CTO at Specmatic