Using embedded mysql database for unit test with maven and spring

I used H2 in memory database to unit test my DAO.  However, my production database is MySQL.
Generally, H2 is compatible with MySQL.  However, H2 doesn't support ENUM type yet, and my schema uses ENUM for a column definition.  Inspired by this post and another, I successfully used embedded MySQL for unit test.  Here is what I did:

1.  Add mysql-connector-mxj dependency:

	<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-mxj</artifactId>
	<version>5.0.12</version>
	<scope>test</scope>
	</dependency>

view raw mxj-dependency.xml hosted with ❤ by GitHub

2. Create EmbeddedMysqlDatabase.java and EmbeddedMysqlDatabaseBuilder.java

	import java.io.IOException;
	import org.apache.commons.io.FileUtils;
	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;
	import org.springframework.jdbc.datasource.DriverManagerDataSource;
	import com.mysql.management.MysqldResource;
	public class EmbeddedMysqlDatabase extends DriverManagerDataSource {
	private final Logger logger = LoggerFactory.getLogger(EmbeddedMysqlDatabase.class);
	private final MysqldResource mysqldResource;
	public EmbeddedMysqlDatabase(MysqldResource mysqldResource) {
	this.mysqldResource = mysqldResource;
	}
	public void shutdown() {
	if (mysqldResource != null) {
	mysqldResource.shutdown();
	if (!mysqldResource.isRunning()) {
	logger.info(">>>>>>>>>> DELETING MYSQL BASE DIR [{}] <<<<<<<<<<", mysqldResource.getBaseDir());
	try {
	FileUtils.forceDelete(mysqldResource.getBaseDir());
	} catch (IOException e) {
	logger.error(e.getMessage(), e);
	}
	}
	}
	}
	}

view raw EmbeddedMysqlDatabase.java hosted with ❤ by GitHub

	import java.io.File;
	import java.util.HashMap;
	import java.util.Map;
	import java.util.Random;
	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;
	import org.springframework.core.io.DefaultResourceLoader;
	import org.springframework.core.io.ResourceLoader;
	import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;
	import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
	import com.mysql.management.MysqldResource;
	import com.mysql.management.MysqldResourceI;
	public class EmbeddedMysqlDatabaseBuilder {
	private static final Logger LOG = LoggerFactory.getLogger(EmbeddedMysqlDatabaseBuilder.class);
	private final String baseDatabaseDir = System.getProperty("java.io.tmpdir");
	private String databaseName = "test_db_" + System.nanoTime();
	private final int port = new Random().nextInt(10000) + 3306;
	private final String username = "root";
	private final String password = "";
	private boolean foreignKeyCheck;
	private final ResourceLoader resourceLoader;
	private final ResourceDatabasePopulator databasePopulator;
	public EmbeddedMysqlDatabaseBuilder() {
	resourceLoader = new DefaultResourceLoader();
	databasePopulator = new ResourceDatabasePopulator();
	foreignKeyCheck = true;
	}
	private EmbeddedMysqlDatabase createDatabase(MysqldResource mysqldResource) {
	if (!mysqldResource.isRunning()) {
	LOG.error("MySQL instance not found... Terminating");
	throw new RuntimeException("Cannot get Datasource, MySQL instance not started.");
	}
	EmbeddedMysqlDatabase database = new EmbeddedMysqlDatabase(mysqldResource);
	database.setDriverClassName("com.mysql.jdbc.Driver");
	database.setUsername(username);
	database.setPassword(password);
	String url = "jdbc:mysql://localhost:" + port + "/" + databaseName + "?" + "createDatabaseIfNotExist=true";
	if (!foreignKeyCheck) {
	url += "&sessionVariables=FOREIGN_KEY_CHECKS=0";
	}
	LOG.debug("database url: {}", url);
	database.setUrl(url);
	return database;
	}
	private MysqldResource createMysqldResource() {
	if (LOG.isDebugEnabled()) {
	LOG.debug("=============== Starting Embedded MySQL using these parameters ===============");
	LOG.debug("baseDatabaseDir : " + baseDatabaseDir);
	LOG.debug("databaseName : " + databaseName);
	LOG.debug("host : localhost (hardcoded)");
	LOG.debug("port : " + port);
	LOG.debug("username : root (hardcode)");
	LOG.debug("password : (no password)");
	LOG.debug("=============================================================================");
	}
	Map<String, String> databaseOptions = new HashMap<String, String>();
	databaseOptions.put(MysqldResourceI.PORT, Integer.toString(port));
	MysqldResource mysqldResource = new MysqldResource(new File(baseDatabaseDir, databaseName));
	mysqldResource.start("embedded-mysqld-thread-" + System.currentTimeMillis(), databaseOptions);
	if (!mysqldResource.isRunning()) {
	throw new RuntimeException("MySQL did not start.");
	}
	LOG.info("MySQL started successfully @ {}", System.currentTimeMillis());
	return mysqldResource;
	}
	private void populateScripts(EmbeddedMysqlDatabase database) {
	try {
	DatabasePopulatorUtils.execute(databasePopulator, database);
	} catch (Exception e) {
	LOG.error(e.getMessage(), e);
	database.shutdown();
	}
	}
	public EmbeddedMysqlDatabaseBuilder addSqlScript(String script) {
	databasePopulator.addScript(resourceLoader.getResource(script));
	return this;
	}
	/**
	* whether to enable mysql foreign key check
	*
	* @param foreignKeyCheck
	*/
	public EmbeddedMysqlDatabaseBuilder setForeignKeyCheck(boolean foreignKeyCheck) {
	this.foreignKeyCheck = foreignKeyCheck;
	return this;
	}
	/**
	* @param databaseName
	* the databaseName to set
	*/
	public final void setDatabaseName(String databaseName) {
	this.databaseName = databaseName;
	}
	public EmbeddedMysqlDatabase build() {
	MysqldResource mysqldResource = createMysqldResource();
	EmbeddedMysqlDatabase database = createDatabase(mysqldResource);
	populateScripts(database);
	return database;
	}
	}

view raw EmbeddedMysqlDatabaseBuilder.java hosted with ❤ by GitHub

3. Create spring bean datasource:

	import javax.sql.DataSource;
	import org.springframework.context.annotation.Bean;
	import org.springframework.context.annotation.Configuration;
	@Configuration
	public class EmbeddedDataSourceConfig implements DataSourceConfig {
	@Override
	@Bean(destroyMethod="shutdown")
	public DataSource dataSource() {
	return new EmbeddedMysqlDatabaseBuilder().addSqlScript("tag_schema.sql").addSqlScript("tag_init.sql").build();
	}
	}

view raw EmbeddedDataSourceConfig.java hosted with ❤ by GitHub

4. Run unit test with Spring:

	import static org.hamcrest.CoreMatchers.is;
	import static org.junit.Assert.assertNull;
	import static org.junit.Assert.assertThat;
	import javax.sql.DataSource;
	import org.junit.Before;
	import org.junit.Test;
	import org.junit.runner.RunWith;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.dao.EmptyResultDataAccessException;
	import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
	import org.springframework.test.annotation.DirtiesContext;
	import org.springframework.test.annotation.DirtiesContext.ClassMode;
	import org.springframework.test.context.ContextConfiguration;
	import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
	import com.zhentao.embedded.mysql.config.EmbeddedDataSourceConfig;
	import com.zhentao.embedded.mysql.dao.TagDaoJdbcImpl;
	import com.zhentao.embedded.mysql.model.Tag;
	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration(classes=EmbeddedDataSourceConfig.class)
	@DirtiesContext(classMode = ClassMode.AFTER_EACH_TEST_METHOD)
	public class TagDaoTest {
	@Autowired
	private DataSource dataSource;
	private TagDaoJdbcImpl tagDao;
	@Before
	public void setUp() {
	tagDao = new TagDaoJdbcImpl();
	tagDao.setDataSource(dataSource);
	tagDao.setJdbcInsert(new SimpleJdbcInsert(dataSource).withTableName("tag").usingGeneratedKeyColumns("id"));
	}
	@Test(expected = EmptyResultDataAccessException.class)
	public void testFindByIdNotExists() {
	tagDao.findById(10000);
	}
	@Test
	public void testInsert() {
	Tag tag = new Tag(3, 1, "lift", "source");
	tag = tagDao.insert(tag);
	Tag actual = tagDao.findById(tag.getId());
	assertThat(actual, is(tag));
	}
	@Test
	public void testUpdate() {
	String adSource = "2";
	Tag tag = new Tag(2, 2, "lift", adSource);
	tagDao.update(tag);
	Tag actual = tagDao.findByKey(tag.getTagId(), tag.getAdvertiserAccountId(), tag.getType());
	assertThat(actual.getAdSource(), is(adSource));
	}
	@Test
	public void testFindByKeyReturnNull() {
	assertNull(tagDao.findByKey(100l, 100l, "not exist"));
	}
	}

view raw TagDaoTest.java hosted with ❤ by GitHub

A couple things are worth mentioning:

1. The test uses Spring annotation @DirtiesContext(classMode = ClassMode.AFTER_EACH_TEST_METHOD) which reload the context after every test method.  It means every test method starts a new mysql instance.  You may group test methods to 2 different categories, one changes data, and another not.  For those methods don't change data, only need to create one instance of mysql to speed up testing.

2. If you see the following error message on Linux server:
/lib64/libc.so.6: version `GLIBC_2.7' not found (required by /tmp/test_db_2420035739165052/bin/mysqld)

You need to either downgrade mysql-connector-mxj to 5.0.11 or upgrade your linux OS.  I got the above error message on CentOS 5.4 with 5.0.12, but no problem with 5.0.11. However, 5.0.11 isn't in any public maven repo, but you can download it from here and install it to your local repo, or upload to your company's repo. Using the following command to check CentOS version:

cat /etc/redhat-release

3. The code is tested on Mac and CentOS only, and can be downloaded from github