Best way to integration-test a spring mysql application?

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.

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)