// archives

MySQL Connector/MJX

This tag is associated with 1 posts

How do you unit test your Data Access Layer?

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"/>

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.