Java common JDBC SQL Query strategy for Unit Test using HSQLDB and runtime using MySQL

I am developing Java Vert.x 3 application. I use HSQLDB for testing with in-memory DB and MySQL 8.0.20 for runtime. When the vertx verticle is deployed, it initializes the db and tables. Since this is a common code and there are differing SQL syntax between HSQLDB and MySQL and more ridiculously, the HSQLDB capitalizes all the property names and I have to double-quote the properties to use lower-case. I wonder how to achieve this. Here are my questions:

(1) HSQLDB uses “IDENTITY” keyword for creating the in-memory database table. This results in runtime error in MySQL DB as “IDENTITY” is not valid keyword. This poses a challenge that I am facing now.

(2) If it is not possible to have a common SQL syntax which satisfies both MySQL and HSQLDB, what’s the best approach to split this common execution path based on the java application runtime profile since this DB initialization is done in the start function of the verticle which is the core of the application?

Any advice and insight is appreciated.

Answer

Solution: Ditch HSQLDB and use H2 with database_to_upper=false option.