Create tables in different databases programatically

Recently I came across a task that required to create tables manually in three different databases, but only if the database did not exist. The databases were accessed via JPA, but these tables contained some database specific optimalizations, which could not be generalized. The project had to be able to run on H2, MySQL and PostgreSQL databases. The main logic behind it was to create a database initializer, which upon the deploy of the application, creates the necessary table if it does not exist. The following SQL statements were created.

H2:

CREATE TABLE IF NOT EXISTS example(
 id_ IDENTITY PRIMARY KEY, 
 description VARCHAR(255), 
 amount BIGINT);

MySQL:

CREATE TABLE IF NOT EXISTS example (
 id_ BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 description VARCHAR(255),
 amount BIGINT
) ENGINE = INNODB;

Well this is not a big deal, they look pretty similar. The only difference is the handling of the auto increment definition, but so far so good, so 2 out of 3 is ready. Here comes PostgreSQL:

CREATE OR REPLACE FUNCTION build_example_table() RETURNS VOID AS $$
CREATE TABLE "example"
(
   "id_" SERIAL PRIMARY KEY, 
   "description" CHARACTER VARYING(255), 
   "amount" bigint
);
$$ LANGUAGE SQL;

SELECT CASE WHEN (SELECT COUNT(*) FROM information_schema.tables WHERE table_name='example')=0 THEN build_example_table() END;

DROP FUNCTION build_example_table();

This is quite different. Unfortunately PostgreSQL does not support "create table if not exist" syntax, but "create or replace function" is supported. So a function can be made for the creation of the table, and the PostgreSQL internal representation can be queried whether the desired table exist.

OK, so the create statements are ready, time to do the database initialization. This can be achieved via a spring or blueprint bean with an init method. The bean must have reference to the database connection (or it can receive the connection through the JNDI API.

The example function is as follows:

public void createDb() {
  Connection connection = null;
  try {
    connection = dataSource.getConnection();
    String databaseProductName = connection.getMetaData().getDatabaseProductName();
    Statement createStatement = connection.createStatement();
    if ("PostgreSQL".equals(databaseProductName)) {
      createStatement.execute(POSTGRESQL_CREATE);
    } else if ("H2".equals(databaseProductName)) {
      createStatement.execute(H2_CREATE);
    } else if ("MySQL".equals(databaseProductName)) {
      createStatement.execute(MYSQL_CREATE);
    } else {
      createStatement.close();
      throw new RuntimeException("Database type (" + databaseProductName + ") not supported!");
    }
    createStatement.close();
  } catch (SQLException e) {
    throw new RuntimeException("Cannot create tables!", e);
  } finally {
    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}