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:
2. Create EmbeddedMysqlDatabase.java and EmbeddedMysqlDatabaseBuilder.java
3. Create spring bean datasource:
4. Run unit test with Spring:
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:
3. The code is tested on Mac and CentOS only, and can be downloaded from github