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.
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.