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

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 5

I see no purpose for mocking the java.sql interfaces in DAOs. After all, when you're unit testing those you really do want them to connect to the database and bring something back.

I can see using HSQL or Derby or an in-memory database, as Skaffman suggests, but I see no point at all for mocking ResultSet or Connection.

Once you've unit tested the DAO, I can see where you'd mock the whole thing for your service test.

Using mocking for an interface in java.sql seems wrong to me.