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:
cat /etc/redhat-release
3. The code is tested on Mac and CentOS only, and can be downloaded from github
You can also try Maven plugin created exactly for this purpose: http://www.jcabi.com/jcabi-mysql-maven-plugin/index.html. It starts a local MySQL server on pre-integration-test phase and shuts it down on post-integration-test.
ReplyDeleteI looked into this approach but it seems that plugin isn't being supported all that well and that made me nervous. Also, I need an embedded-DB not only for integration-testing but also for unit-testing as well as specialized deployment situations. It wasn't clear to me how to use this plugin for those other situations. So I went with zhentao's solution above and it seems to be working quite well for me.
Deletejcabi-mysql-maven-plugin requires perl to be installed, it's a blocker for our team.
DeleteThis was a big help for me; thanks so much!
ReplyDeleteJust work, jcabi-mysql-maven-plugin from start not work as expected. Great thanks.
ReplyDeleteThis helped us a lot on our projects.
ReplyDeleteQuestion: How can I set the character set and collation of the database instance?
Thanks!
You can set it in your schema.sql. Here is the example:
Deletehttps://github.com/zhentao/maven-embedded-mysql/blob/master/src/test/resources/tag_schema.sql#L14
Great article. It was very helpful. Thank you. For me, local mysql is created and ddl is executed but I am seeing error while shut down of mysql after the unit test(java.util.MissingResourceException: Resource 'kill.exe' not found). Any suggestions is greatly appreciated!
ReplyDeletewhich import use at DataSourceConfig??? Can't resolve
ReplyDeleteYou can find the code here:
Deletehttps://github.com/zhentao/maven-embedded-mysql/blob/master/src/main/java/com/zhentao/embedded/mysql/config/DataSourceConfig.java
Works as a charm with some minor adjustments for Spring-Boots applications. Now I can say goodbye to the weird mappings between H2 and Mysql to have tests closer to reality.
ReplyDelete"InstaBuyJ is your one-stop shop for all things mobile repair! With an unbeatable selection of top-quality tools, fixing your device has never been easier. From screen replacements to battery swaps, they've got the tools you need to get the job done right. Plus, their user-friendly website and lightning-fast shipping make ordering a breeze. Don't let a broken phone slow you down – shop InstaBuyJ and get back to peak performance in no time!"
ReplyDeleteEnquiry Now -mobile repairing tools buy online