Mock is not an option, I believe.
When I unit test my Data Access Layer (DAL), I really want to hit the database to ensure that I am executing correct SQL Queries against correct database schema. I have been using embedded (in-memory) databases like HSQL, H2 and Derby to unit test my Data Access Layer code.
Thanks to Spring 3, embedding databases in my unit tests is extremely straightforward. I just have to include the following lines in my application context:
<jdbc:embedded-database id="dataSource">
<jdbc:script location="classpath:schema.sql"/>
<jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>
There are couple of problems with this approach though:
- DDL supported by these embedded databases is not compatible with each other and with my production database (MySQL). I need to maintain 2 separate scripts, one for production and the other for my unit tests. Keeping these two scripts in sync is critical to make the unit test reliable.
- The embedded databases may not support all the SQL standards that my production database supports. For example HSQLDB does not support AUTO_INCREMENT. I have to use the IDENTITY column instead. Also, HSQLDB doesn’t support MEDIUMTEXT and TEXT columns supported by MySQL.
I can use Hibernate to generate and populate database specific schema automatically. But what if I am not using Hibernate (like in my recent project)? Ideally, I would like to solve these issues by using the same database that I have in my Production environment – MySQL. A Java utility called MySQL Connector/MXJ can be used to address these problems.
(more…)
