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.