Test-driven development is something wonderful! Once you’ve established it in your organisation, you will start to:
- Greatly improve your quality (things break less often)
- Greatly improve your processes (things can be changed more easily)
- Greatly improve your developer atmosphere (things are more fun to do)
The importance of doing the
right test-driven development is to find a good ratio of what kind of code is to be covered…
- by automated unit tests
- by automated integration tests
- by manual “smoke tests”
- by manual “acceptance tests”
- not at all
Finding that ratio can be grounds for heated, religious discussions. I will soon blog about my own opinion on that subject. In this post, however, we will focus on the first kind of test:
unit tests.
Unit testing your data access
When databases are involved, people will probably quickly jump to writing integration tests, because all they have to do is create a little Derby, H2 or HSQLDB (or other) test database, and run a couple of data-setup queries prior to the actual test. Their code module will then hopefully not notice the difference to a productive environment, and the whole system can be tested as a blackbox. The advantage of this is that your tests can be written in a way to verify your business requirements, your user stories, or whatever you call them. So far, the theory.
When these database integration tests pile up, it starts to become increasingly difficult to shield them off one another. Avoiding inter-dependencies and at the same time, avoiding costly database setups is hard. You won’t be able to run the whole test-suite immediately after building / committing. You need nightly builds, weekly builds. But unit testing the data access layer isn’t that much easier! Because JDBC is an awful API to mock. There are so many different ways of configuring and executing queries through this highly
stateful API, your unit tests quickly become unmanageable.
There are a few libraries that help you with database testing. Just to name a few:
- MockRunner: This one has some JDBC-specific extensions that allow for simulating JDBC ResultSets, as well as for checking whether actual queries are executed
- jMock: An “ordinary” Java mocking library
- mockito: An “ordinary” Java mocking library
- DBUnit: This one doesn’t mock your database, it’s good for testing your database. Another use-case, but still worth mentioning here
Some of the above libraries will not get you around the fact that JDBC is an awkward API to mock, specifically if you need to support several (incompatible!) versions of JDBC at the same time. Some examples can be seen here:
Mocking the database with jOOQ
When you’re using
jOOQ in your application, mocking your database just became really easy in jOOQ 3.0. jOOQ now also ships with a
Mock JDBC Connection. Unlike with other frameworks, however, you only have to implement a single functional interface with jOOQ, and provide that implementation to your MockConnection: The
MockDataProvider. Here’s a simple implementation 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(configuration);
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());
The above implementation acts as a callback for JDBC’s various executeXXX() methods. Through a very simple
MockExecuteContext API, you can thus:
- Get access to the executed SQL and bind values (Use general jOOQ API to inline bind values into the SQL statement)
- Distinguish between regular SQL statements and both single-statement/multi-bind-value and multi-statement/no-bind-value batch executions
- Return one or several results using jOOQ’s org.jooq.Result objects (which you can easily import from CSV, XML, JSON, TEXT formats)
- Return “generated keys” results through the same API
- Let jOOQ’s MockStatement take care of the serialisation of your mock data through the JDBC API
There is also an experimental implementation of a
MockFileDatabase, a text-based mock database that uses the following format:
# 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
MockFileDatabase implements MockDataProvider, so it’s dead-simple to provide your unit tests with sample data. Future versions of jOOQ will allow for:
- Regex pattern-matching SQL statements to provide mock results
- Load these results from other formats, such as jOOQ’s supported export formats
- Specify the behaviour of batch statements, multi-result statements, etc.
Using jOOQ’s MockConnection in other contexts
Things don’t stop here. As jOOQ’s MockConnection is the entry point for this mocking sub-API of jOOQ, you can also use it in other environments, such as when running JPA queries, Hibernate queries, iBatis or just your plain old legacy JDBC queries.
jOOQ has just become your preferred JDBC mock framework! ;-)
Like this:
Like Loading...
In my experience, unit testing tends to become very difficult to reliably implement when database interactions are involved. Tools like DBunit can help but they seem to require a fair amount of extra work and maintenance.
You mentioned a future blog post on the subject, I’d be very interested to read more about what you think is worth testing or not. I guess it’s not possible to cover everything without unlimited time and money, so I am still looking for what I’d call “the right trade-off”..
Best
I actually don’t have too much experience with unit testing and databases. This feature was requested by a jOOQ user on the user group, and it actually makes a lot of sense. jOOQ, however, mostly runs integration tests, as it would be mad not to test jOOQ’s internal complexity against an actual database – or 14 different ones, as a matter of fact. An interesting discussion was also started on the DZone repost of this article, if you want to hear more opinions:
http://architects.dzone.com/articles/easy-mocking-your-database-0
I hope I’ll find some time soon to write more about unit / integration testing. I can already give you the summary of my opinion (which might not match yours, of course). Integration testing beats unit testing most of the time, as
And then, there’s the important 80/20 rule. Spend 20% of your time achieving 80% of coverage. Or so. But don’t be dogmatic about coverage. It never pays off to be dogmatic.
It’ll be a controversial article :-)
I have read the discussion on dZone and found it quite interesting. (I love reading opinions from real people on this topic rather than articles written by some agile consultant or book writer who assume we are all living in a wonderful OO world where everything occurs in ram and persistence is…. ehhh.. “taken care of somewhere else” ™.)
It seems like many people are actually using a test database and testing real interactions. My only concern about it is the set-up and additional maintenance cost, but even so I guess it probably pays back in the long term because such tests, while automated, are really close to reality. It makes sense to me.
And even concerning the mocking API described here, I think I’d still choose to fill 2-3 tables with sample data rather that manually providing data for 2, 3, or maybe 10 resultsets…
Still many things to decide, use an in-memory database or not? Use dbunit or not? how to minimize dependencies so that every developer in the team can easily run the tests, I’ll try to learn more about these during the next weeks…
Thanks
Would be interesting to hear your findings about which tool(s) to use for database testing!
a colleague of mine just told me about DEEP_STUBS in Mockito[0] that can be used to really easily mock your database. I’ve created a small gist explaining how we are mocking our database calls now at [1].
We basically take our existing jooq code/query, wrap it into a Mockito.when() call and then specify whatever we wish to be returned.
[0]: http://docs.mockito.googlecode.com/hg/org/mockito/Mockito.html#RETURNS_DEEP_STUBS
[1]: https://gist.github.com/sebhoss/fb41b560672ce3cdd341
Oh wow, that’s very interesting! Thanks for sharing – would be very interesting topic for a full blog post.
Of course, you will need to be mocking a lot of potential API calls. How many of these matchers did you define, then?
That’s the best part: We didn’t have to write any new code – no new matchers or anything. The only thing to add was this ‘Mockito.RETURNS_DEEP_STUBS’ as the second parameter to ‘Mockito.mock()’. Without that, we had to mock/stub every step in the query construction. With that second parameter, we can just keep on using jOOQs DSL to construct the query. For every step we take in the query construction, Mockito will return us with a valid mock-object that we can use to create the rest of the query.
As you can see in the runnable example[0], there is no magic going on and no special setup or anything required :-)
If you want to turn the good news into a blog post, i’m happy to help!
[0]: https://gist.github.com/sebhoss/fb41b560672ce3cdd341#gistcomment-1548131
Oh yeah, I ran that example myself, and I’m thrilled :-)
Yet, you still have to mock each query exactly the way you write it, or am I missing something? Also, if a method returns, for instance,
Integer, mocking didn’t work for me. Is that a known limitation of Mockito?Yeah you have to mock the query exactly as it is. Otherwise you’ll most likely get an NPE or some similar exception from Mockito. So i guess you can’t really compare this to the approach in the initial blog post.
In our case, we have a flyway + jooq based setup that already requires a database during the build process. So we don’t have any problems running our integration tests against real databases as well. However we do have some tests, that require some specific data to be returned from our database calls. Instead of setting up a bunch of insert statements first, nowadays (since monday!) i just mock those calls away and specify what i need directly.
I might have reproduces that Integer problem in another update[0], but i couldn’t find any (open) ticket for Mockito. However several questions on SO[1][2][3] that complain about Integer related problems as well…
[0]: https://gist.github.com/sebhoss/fb41b560672ce3cdd341#gistcomment-1549303
[1]: https://stackoverflow.com/questions/19155369/set-mock-return-value-for-any-integer-input-parameter
[2]: https://stackoverflow.com/questions/21980728/mockito-for-int-primitive
[3]: https://stackoverflow.com/questions/21441551/mockito-not-allowing-matchers-any-with-integer-class
Very interesting, thanks for the follow-up. I can see the use-case for the occasional mocking away of specific database calls. Really nice to see that this works so well with jOOQ’s interface-based approach.
I’m definitely going to publish another blog post around that topic pretty soon, and I’ll reference your work. This is really useful!
Please clarify the line in the example implementation, “DSLContext create = DSL.using(…);” What is intended to go in the “…”? Seems recursive at first look.
At the moment I’m using
DSLContext dslContext = DSL.using(new MockConnection(this));on that line and it “works,” however seems wrong given thatConnection connection = new MockConnection(provider);appears below that block (providerbelow being equivalent tothisabove)I see that
DSLContext create = DSL.using(SQLDialect.MYSQL)is acceptable, and that makes sense.Yes, that’s one possibility.
The
using()method is overloaded several times with a variety of convenience constructors. It depends on your actual way of using jOOQ, which overload you’ll pick. This is why the blog post omits the details here.It’s probably better to put
configurationthere to avoid the confusion.