Trouble with Integration Testing MySQL Syntax on an H2 Database in Spring

Question

I am working on a project which uses different MySQL schemas for customer management, e.G. every customer has his own database schema, which is created with a skeleton template.

For compatibility issues, I have to create new MySQL schemas from Java, which was before done in PHP. I created a small service class, which works perfectly on a MySQL database by repeatedly using CREATE TABLE 'a' LIKE 'b';, however this does not work with H2db since H2 does not support the LIKE-part when creating tables. I then created a MySQL and modified the file to be easily processed with Java (Files.readAllLines, which is fed into Statement.executeBatch). However, this dump also fails on a H2db because of Statements like COLLATION=UTF8. Sadly, these are important to have, since we often have special chars which need to be encoded correctly, so simply removing said statements from the sql file is not recommended.

I initialize the DataSource like this:

public DataSource dataSource(ResourceLoader resourceLoader) {
    return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .setScriptEncoding("UTF-8")
            .addScript("data.sql").build();
}

So, my configuration possibilities are scarce. We use gradle to build our application, so the suggested maven-plugin won't help either - at least I have not found out ow to use it with gradle, if that is possible at all.

So my problem here is, that I'd need to check if the Service actually persists the data correctly, but I cannot do it in-memory since H2 does not support the syntax, and I am not allowed to use the "real" MySQL database, since all our tests must be detached from "real" connections and shall only use in-memory stores for databases, if necessary.

Answer 1

I ended up using https://github.com/vorburger/MariaDB4j as an embedded db, since we use MariaDB anyway, so this will definitely eliminate all dialect issues. Since this effectively works the same way as an embedded MongoDB, the extra cost for testing is acceptable.

A caveat though: this NEEDS a 64bit OS to work.

Answer 2

You use MySQL. You have several possibilities.

A couple of which I use:

1.Create in gradle with use groove (plugin) You create new database for mysql test and you create schema the same way.

Some config in gradle to help you.

def dbUser = 'root'
def dbPasswd = 'root'
def dbHost = 'localhost'
def dbName = 'testDB'
def dbPort = '3306'
def dbUrl = 'jdbc:mysql:////localhost:3306/testDB?
useUnicode=yes&characterEncoding=UTF-8'

dependencies { 
        testCompile files('libs/junit-4.12.jar')
        testCompile files('libs/hamcrest-core-1.3.jar')
        testCompile files('libs/mysql-connector-java-5.1.29.jar')
       }

//create DB for test
task loadDriver {
    URLClassLoader loader = GroovyObject.class.classLoader
    loader.addURL(file('libs/mysql-connector-java-5.1.29.jar').toURL())
    java.sql.DriverManager.registerDriver(loader.loadClass('com.mysql.jdbc.Driver').newInstance())
}

task createTestData(dependsOn: loadDriver) {

//  println 'Connecting to database ...'
//  def sql = groovy.sql.Sql.newInstance('jdbc:mysql://localhost:3306/?useUnicode=yes&characterEncoding=UTF-8', dbUser, dbPasswd, 'com.mysql.jdbc.Driver')
//  println '... connected'

//    println 'Drop test database testDB...'
//    sql.execute 'drop database IF exists testDB;'
//    println 'database testDB is droped'

//    println 'Create test database testDB...'
//    sql.execute 'create database if not exists testDB'
//    println 'Database testDB is created'

}

2.You may in similar way in java create base class you use in test to create testDB mysql for example:

public class TestDAO {
@Before
public void setUp() throws ClassNotFoundException, SQLException {

    java.util.Date date = new java.util.Date();
    LOG.info("Set up database for test "+date.getTime());
    database = database + date.getTime();
    LOG.info("DATABASE NAME:"+database);


    LOG.info("Create database:"+database);
    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(URL+":"+PORT, USER, PASS);
        stmt = conn.createStatement();
        String sql = "CREATE DATABASE " + database;
        stmt.executeUpdate(sql);
    } finally {
        if (stmt != null)
            stmt.close();
        if (conn != null)
            conn.close();
    }

There are some other ways one may invent.

Answer 3

Database migration tools

Build, and re-build, your database schema with a database migration tool such as:

• Flyway
• Liquibase

Instead of directly editing your database, write SQL scripts and/or Java JDBC classes to define your database schema. Create tables, define columns, import data, insert expected rows, all in SQL or Java as a collection of SQL scripts and/or Java classes. When testing, either of these tools will create and load a fresh database by executing your growing collection of scripts/classes as instructions. After testing, delete the database.

Your Java classes for this purpose can use alternate syntax to be specific to MySQL or H2 as appropriate.

Use H2 in production

Also, you could skip the MySQL altogether, and use H2 in production.

You could resolve your multi-tenancy problem by giving each customer their own separate H2 database, which means each customer has their own physical data file.

The database migration tools mentioned above work by creating an extra table in your database, with versioning information. When deploying your app, the app first runs the migration tool to update the database structure and data first, always keeping it up to date. This makes it easy to have many separate databases in production, each being automatically updated live in production on next usage.

You would need enough memory on your database server to support as many multiple databases that might simultaneously be open and in use by H2. For a very large number of tenants, you could run multiple database servers, each with a subset of your tenants’ databases.

For more info, see Multiple independent H2 databases within one JVM.