How should I unit test Java that calls a MySQL stored procedure?

Question

I'm writing a simple application that will read some records and insert them in a database. I've written a stored procedure that handles the insertion logic, and plan to test that separately. Now I'd like to write a good unit test for the portion of the logic that takes a business object and passes it to the stored procedure call.

I think what I want to do is pass a mock of the database connection, then assert that the call is made with expected parameter values:

Connection dbConnection = makeMockConnection();  // how?
MyObjectWriter writer = new MyObjectWriter(dbConnection);
writer.write(someSampleObject);
// somehow assert that dbConnection called
// `sp_saveMyObject` with param values x, y, and z

However, it seems like a lot of work dig around inside java.sql.Connection, understand how it works, then mock all the results. Is there a test library that does all this for me? Am I coming at this the wrong way?

Answer 1

You could create an in-memory HSSQL database with a mock stored procedure. The mock sproc would insert a row into a table to show that it ran and what it's parameters were. Run the code under test and then look in the db to see what happened.