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.

What is MySQL Connector/MXJ?

MySQL Connector/MXJ is a Java utility package for deploying and managing a MySQL database engine (mysqld) from within a java application. Connector/MXJ can be bundled with an existing Java application as a “plain old Java object” (POJO). I can use MySQL JDBC driver to start up MySQL database by appropriately configuring the JDBC url (explained here) or use the com.mysql.management.MysqldResource class to programmatically control the MySQL server instance (explained here). MySQL Connector/MXJ launches appropriate binary by detecting the platform it is running on. This post describes how I use Connector/MXJ component to unit test my DAL.

Where do I begin?

I use Maven-2 to manage component dependencies in my projects. Unfortunately, MySQL Connector/MXJ jars are not available in any central Maven-2 repositories. I manually installed required jar files in my local maven repository (~/.m2) as explained below:

1. Download MySQL Connector/MXJ (link). I am currently using Connector/MXJ version 5.0.12. According to the documentation, 5.0.12 version on Connector/MXJ supports the 5.5.9 version of MySQL.
2. Unzip downloaded file in a temporary directory. I got this after exploding mysql-connector-mxj-gpl-5-0-12.zip:
   
3. Install necessary Connector/MXJ jars in Maven. I had to manually install the following two files in my local maven repository:
   • mysql-connector-mxj-gpl-5-0-12.jar
   • mysql-connector-mxj-gpl-5-0-12-db-files.jar – This jar contains platform specific binaries.

   Run mvn install:install-file to install these file locally.

   			mvn install:install-file -Dfile=mysql-connector-mxj-gpl-5-0-12.jar -DgroupId=com.mysql -DartifactId=connector.mxj -Dversion=5.0.12 -Dpackaging=jar -DgeneratePom=true -DupdateReleaseInfo=true
   		
   			mvn install:install-file -Dfile=mysql-connector-mxj-gpl-5-0-12-db-files.jar -DgroupId=com.mysql -DartifactId=connector.mxj.dbfiles -Dversion=5.0.12 -Dpackaging=jar -DgeneratePom=true -DupdateReleaseInfo=true
   		

Now I am ready to embed MySQL in my project.

Configure pom.xml

First I need to add Connector/MXJ dependencies to the pom.xml file.

	<dependency>
    	<groupId>com.mysql</groupId>
    	<artifactId>connector.mxj</artifactId>
    	<version>5.0.12</version>
	</dependency>
	<dependency>
    	<groupId>com.mysql</groupId>
    	<artifactId>connector.mxj.dbfiles</artifactId>
    	<version>5.0.12</version>
	</dependency>

I also need to include MySQL Connector/J JDBC driver in my pom.xml file.

	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.13</version>
		<type>jar</type>
	</dependency>

Implement a Manager to manage Embedded MySQL Database

EmbeddedMysqlManager uses com.mysql.management.MysqldResource to embed MySQL database in my unit tests. The MysqldResource provides methods to control the lifecycle of the MySQL database.

The following code snippet explains how MySQL instance can be started programatically. new MysqldResource(databaseDir) constructs a MysqldResource instance by setting the base database directory where MySQL files are deployed. The standard output and errors are directed to System.out and System.err.

mysqldResource.start("embedded-mysqld-thread-" + System.currentTimeMillis(), database_options) call deploys and starts the MySQL database. The first parameter specifies the name of the thread that runs the MySQL executable. The second parameter specifies a map of command line arguments passed to the MySQL executable.

mysqldResource.isRunning() returns true if the MySQL is running.

File databaseDir = new File(new File(baseDatabaseDir), databaseName);

mysqldResource = new MysqldResource(databaseDir);

Map<String, String> database_options = new HashMap<String, String>();
database_options.put(MysqldResourceI.PORT, Integer.toString(port));
database_options.put(MysqldResourceI.INITIALIZE_USER, "true");
database_options.put(MysqldResourceI.INITIALIZE_USER_NAME, username);
database_options.put(MysqldResourceI.INITIALIZE_PASSWORD, password);

mysqldResource.start("embedded-mysqld-thread-" + System.currentTimeMillis(), 
					 database_options);

if (!mysqldResource.isRunning()) {
    throw new RuntimeException("MySQL did not start.");
}

The complete implementation of EmbeddedMysqlManager is shown below:

 
package com.rajandesai.example.testing.util;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.jdbc.SimpleJdbcTestUtils;

import com.mysql.management.MysqldResource;
import com.mysql.management.MysqldResourceI;

/**
 * Manages the life cycle of the embedded MySQL database. 
 * This manager uses the MySQL Connector/MXJ and Mysql Connector/J JDBC driver to manage the
 * embedded MySQL DB instance.
 * 
 * @see http://dev.mysql.com/doc/refman/5.1/en/connector-mxj.html for more details
 */
public class EmbeddedMysqlManager {
	protected Logger logger = LoggerFactory.getLogger(this.getClass());
	
	private MysqldResource mysqldResource;
	private String baseDatabaseDir = System.getProperty("java.io.tmpdir");
	private String databaseName = "test_db_" + System.nanoTime();
	private int port = 13306;
	private String username = "root";
	private String password = "password";
	private List<String> sqlScripts = new ArrayList<String>();
	private DriverManagerDataSource datasource;
	
	//-------------------------------------------------------------------------
	/**
	 * Starts the mysql database
	 * @return
	 */
    public void startDatabase() {
        if (logger.isDebugEnabled()) {
        	logger.debug("=============== Starting Embedded MySQL using these parameters ===============");
        	logger.debug("baseDatabaseDir : " + baseDatabaseDir);
        	logger.debug("databaseName : " + databaseName);
        	logger.debug("host : localhost (hardcoded)");
        	logger.debug("port : " + port);
        	logger.debug("username : " + username);
        	logger.debug("password : " + password);
        	logger.debug("=============================================================================");
        }
        
        File databaseDir = new File(new File(baseDatabaseDir), databaseName);
        
        mysqldResource = new MysqldResource(databaseDir);

        Map<String, String> database_options = new HashMap<String, String>();
        database_options.put(MysqldResourceI.PORT, Integer.toString(port));
        database_options.put(MysqldResourceI.INITIALIZE_USER, "true");
        database_options.put(MysqldResourceI.INITIALIZE_USER_NAME, username);
        database_options.put(MysqldResourceI.INITIALIZE_PASSWORD, password);

        mysqldResource.start("embedded-mysqld-thread-" + System.currentTimeMillis(), 
        					 database_options);

        if (!mysqldResource.isRunning()) {
            throw new RuntimeException("MySQL did not start.");
        }

        logger.info("MySQL started successfully @ " + System.currentTimeMillis());
        
        try {
	        if (!sqlScripts.isEmpty()) {
	        	SimpleJdbcTemplate simpleJdbcTemp = new SimpleJdbcTemplate(getDatasource());
	        	logger.info("Executing scripts...");
	        
		        for (String script : sqlScripts) {
		        	logger.info("Executing script [" + script + "]");
		        	SimpleJdbcTestUtils.executeSqlScript(simpleJdbcTemp, new ClassPathResource(script), false);
		        }
	        } else {
	        	logger.info("No scripts to load...");
	        }
        } catch (Exception e) {
        	logger.error(e.getMessage(), e);
        	if ((mysqldResource != null) && (mysqldResource.isRunning())) {
        		shutdownDatabase();
        	}
        }
    }

	public void shutdownDatabase() {
		mysqldResource.shutdown();
		if (mysqldResource.isRunning() == false) {
	        logger.info(">>>>>>>>>> DELETING MYSQL BASE DIR [" + mysqldResource.getBaseDir() + "] <<<<<<<<<<");
	        try {
				FileUtils.forceDelete(mysqldResource.getBaseDir());
			} catch (IOException e) {
				logger.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * Gets a {@link DataSource} for the embedded DB managed by this manager
	 * @return
	 */
	public DataSource getDatasource() {
		if (!mysqldResource.isRunning()) {
			logger.error("MySQL instance not found... Terminating");
			throw new RuntimeException("Cannot get Datasource, MySQL instance not started.");
		}
		
		if (datasource == null) {
			datasource = new DriverManagerDataSource();
			datasource.setDriverClassName("com.mysql.jdbc.Driver");
	        String url = "jdbc:mysql://localhost:" + port + "/" + databaseName + 
	        			 "?" + "createDatabaseIfNotExist=true&sessionVariables=FOREIGN_KEY_CHECKS=0";
	
	        datasource.setUrl(url);
	        datasource.setUsername(username);
	        datasource.setPassword(password);
		}        
        return datasource;
	}
	
	//-------------------------------------------------------------------------
	/**
	 * @return the baseDatabaseDir
	 */
	public final String getBaseDatabaseDir() {
		return baseDatabaseDir;
	}

	/**
	 * @param baseDatabaseDir the baseDatabaseDir to set
	 */
	public final void setBaseDatabaseDir(String baseDatabaseDir) {
		this.baseDatabaseDir = baseDatabaseDir;
	}

	/**
	 * @return the databaseName
	 */
	public final String getDatabaseName() {
		return databaseName;
	}

	/**
	 * @param databaseName the databaseName to set
	 */
	public final void setDatabaseName(String databaseName) {
		this.databaseName = databaseName;
	}

	/**
	 * @return the port
	 */
	public final int getPort() {
		return port;
	}

	/**
	 * @param port the port to set
	 */
	public final void setPort(int port) {
		this.port = port;
	}

	/**
	 * @return the username
	 */
	public final String getUsername() {
		return username;
	}

	/**
	 * @param username the username to set
	 */
	public final void setUsername(String username) {
		this.username = username;
	}

	/**
	 * @return the password
	 */
	public final String getPassword() {
		return password;
	}

	/**
	 * @param password the password to set
	 */
	public final void setPassword(String password) {
		this.password = password;
	}

	/**
	 * @return the sqlScripts
	 */
	public final List<String> getSqlScripts() {
		return sqlScripts;
	}

	/**
	 * @param sqlScripts the sqlScripts to set
	 */
	public final void setSqlScripts(List<String> sqlScripts) {
		this.sqlScripts = sqlScripts;
	}		
}

Wire everything together using Spring

Define EmbeddedMysqlManager bean in the Spring application context as shown below:

<!-- ====================================================================== -->
<!-- Test DB setup using Embedded MySQL							-->
<!-- Note :  DO NOT include "-" in the DB name. 							-->
<!-- For example, test-db is INVALID, use test_db instead 					-->
<!-- ====================================================================== -->
<bean id="embeddedMysqlManager" 
	  class="com.rajandesai.example.testing.util.EmbeddedMysqlManager"
	  p:baseDatabaseDir="#{systemProperties['java.io.tmpdir']}"		
	  p:databaseName="test_db"  	
	  p:username="root"
	  p:password="password"
	  p:port="13306"
	  init-method="startDatabase"
	  destroy-method="shutdownDatabase">
	<property name="sqlScripts">
		<list>
			<value>schema.sql</value>
			<value>data.sql</value>
		</list>
	</property>
</bean>	

This setup ensures that the test database (test_db) is initialized correctly as the Spring Framework loads the application context. The schema.sql and data.sql scripts are executed as part of the manager initialization process. As names suggest, the schema.sql script creates the database schema and the data.sql script inserts
sample data in the database.

Test the setup

Finally, the following unit test illustrates how everything works together. This simple test validates the count of employees from the embedded database.

package com.rajandesai.example.testing.dal;

import static org.junit.Assert.assertTrue;
import junit.framework.TestCase;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext-test.xml"})
public class AppTest extends TestCase{
	@Autowired
	private SimpleJdbcTemplate jdbcTemplate;

	@Test
	public void testApp() {
        int rowCount = jdbcTemplate.queryForInt("select count(*) from employees");
        assertTrue(rowCount == 10);
    }
}

Running the above unit test from command prompt produces this output:

[testing.dal]> mvn test
[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] Building testing.dal
[INFO]    task-segment: [test]
[INFO] ------------------------------------------------------------------------
[INFO] [resources:resources {execution: default-resources}]
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory testing.dal/src/main/resources
[INFO] [compiler:compile {execution: default-compile}]
[INFO] Nothing to compile - all classes are up to date
[INFO] [resources:testResources {execution: default-testResources}]
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 4 resources
[INFO] [compiler:testCompile {execution: default-testCompile}]
[INFO] Nothing to compile - all classes are up to date
[INFO] [surefire:test {execution: default-test}]
[INFO] Surefire report directory: testing.dal/target/surefire-reports

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running com.rajandesai.example.testing.dal.AppTest
DEBUG [main] EmbeddedMysqlManager.startDatabase(136) | =============== Starting Embedded MySQL using these parameters ===============
DEBUG [main] EmbeddedMysqlManager.startDatabase(137) | baseDatabaseDir : /var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/
DEBUG [main] EmbeddedMysqlManager.startDatabase(138) | databaseName : test_db
DEBUG [main] EmbeddedMysqlManager.startDatabase(139) | host : localhost (hardcoded)
DEBUG [main] EmbeddedMysqlManager.startDatabase(140) | port : 13306
DEBUG [main] EmbeddedMysqlManager.startDatabase(141) | username : root
DEBUG [main] EmbeddedMysqlManager.startDatabase(142) | password : password
DEBUG [main] EmbeddedMysqlManager.startDatabase(143) | =============================================================================
[/private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/bin/mysqld][--no-defaults][--port=13306][--socket=mysql.sock][--basedir=/private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db][--datadir=/private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/data][--pid-file=/private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/data/MysqldResource.pid]
[MysqldResource] launching mysqld (embedded-mysqld-thread-1318513795413)
111013  9:49:56 [Warning] Setting lower_case_table_names=2 because file system for /private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/data/ is case insensitive
111013  9:49:56 [Note] Plugin 'FEDERATED' is disabled.
111013  9:49:56 InnoDB: The InnoDB memory heap is disabled
111013  9:49:56 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
111013  9:49:56 InnoDB: Compressed tables use zlib 1.2.3
111013  9:49:56 InnoDB: Initializing buffer pool, size = 128.0M
111013  9:49:56 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
111013  9:49:56  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
111013  9:49:57  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
111013  9:49:57  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
111013  9:49:57  InnoDB: Waiting for the background threads to start
111013  9:49:58 InnoDB: 1.1.5 started; log sequence number 0
111013  9:49:58 [Note] Event Scheduler: Loaded 0 events
111013  9:49:58 [Note] /private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/bin/mysqld: ready for connections.
Version: '5.5.9'  socket: 'mysql.sock'  port: 13306  MySQL Community Server (GPL)
[MysqldResource] mysqld running as process: 2555
INFO [main] EmbeddedMysqlManager.startDatabase(163) | MySQL started successfully @ 1318513803579
INFO [main] EmbeddedMysqlManager.startDatabase(168) | Executing scripts...
INFO [main] EmbeddedMysqlManager.startDatabase(171) | Executing script [schema.sql]
INFO [main] EmbeddedMysqlManager.startDatabase(171) | Executing script [data.sql]
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 11.616 sec

Results :

Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

[MysqldResource] stopping mysqld (process: 2555)
111013  9:50:06 [Note] /private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/bin/mysqld: Normal shutdown

111013  9:50:06 [Note] Event Scheduler: Purging the queue. 0 events
111013  9:50:06  InnoDB: Starting shutdown...
111013  9:50:07  InnoDB: Shutdown completed; log sequence number 1740505
111013  9:50:07 [Note] /private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db/bin/mysqld: Shutdown complete

[MysqldResource] clearing options
[MysqldResource] shutdown complete
INFO [Thread-3] EmbeddedMysqlManager.shutdownDatabase(188) | >>>>>>>>>> DELETING MYSQL BASE DIR [/private/var/folders/b8/_yd9n9kj56z30jx6xp2gqjj80000gp/T/test_db] <<<<<<<<<<
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 17 seconds
[INFO] Finished at: Thu Oct 13 09:50:08 EDT 2011
[INFO] Final Memory: 10M/265M
[INFO] ------------------------------------------------------------------------

Conclusion

MySQL Connector/MXJ is a little known secret that really simplified my unit testing of Data Access Layer.

Source for this project can be downloaded here. This zip can be directly imported in Eclipse IDE.