Best way to integration-test a spring mysql application?

Question

I want to write integration tests for my application, which is using a MySQL db via jdbc. (FYI: The application has a user database and a wiki so far, so nothing sophisticated really)

I came across a lot of posts, mostly talking about HSQLDB or H2, as well as some exotics or discontinued (like mxj)

Sadly, most of these threads were made years ago and so many things have changed since then. I'm looking for people with recent experience (or even better having a similiar setup like I got - that means spring and the need to run these tests both locally and on jenkins)!

I understand that the best way to find the answer is to try it myself and see what works best, but maybe someone has recent experience and is willing to share :)

Technologies used:

MySQL
Spring
TestNG
Jenkins

I'd be very thankful if you can share your experience and advices with me.

not yet :( I started using h2, but I can't import MySQL dumps as it only works with SQL scripts - havn't found a good program to export MySQL->SQL (beside phpMyAdmin, which creates scripts that nevertheless don't work out-of-the-box) — Sebastian Saip, Jan 15 '13 at 08:55

score 0 · Accepted Answer · edited May 23 '17 at 12:15

So I decided to use h2, which gave me some problems with importing MySQL dumbs. The answer is here: Running h2 in MODE=MySQL doesn't support MySQL dumps

Basically, you have to remove quotes around table-names (they seem to work fine for fields nevertheless). You can use back-ticks ( ` ) or not use any ticks/quotes at all. Another thing that caused a problem was a "AUTO_INCREMENT = 1" at the end of the table definition. Since h2 uses 1 as default after the start, this command is not needed anyways.

Here's an example how to setup the embedded database:

public class InMemoryTest {
    @org.testng.annotations.Test // ofc this can be JUnit @Test as well
    public void test() throws Exception {
        Class.forName("org.h2.Driver");

        Connection conn = DriverManager.
                getConnection("jdbc:h2:mem:test;MODE=MySQL;IGNORECASE=TRUE;INIT=RUNSCRIPT FROM 'src/test/resources/test.sql'");


        Statement stat = conn.createStatement();
        stat.execute("INSERT INTO `usr_avatar` (\"usr_avatar_user_id\") VALUES (1)");
    }
}

and the test.sql would be:

DROP TABLE IF EXISTS usr_avatar;
CREATE TABLE IF NOT EXISTS usr_avatar (
  "usr_avatar_id" int(11) NOT NULL AUTO_INCREMENT,
  "usr_avatar_user_id" int(11) NOT NULL
)

score 0 · Answer 2 · answered Oct 09 '13 at 18:04

0

Try Maven plugin created exactly for this purpose: jcabi-mysql-maven-plugin. It starts a local MySQL server on pre-integration-test phase and shuts it down on post-integration-test.

answered Oct 09 '13 at 18:04

yegor256

102,010
123
446
597

Best way to integration-test a spring mysql application?

2 Answers2