How to use Oracle and PostgreSQL scripts and functions in H2 Database using Java

In this use case we have a production server that is Oracle and we use H2 as development server / tests for a Spring Boot project.

We are using H2 as local or memory database to quickly develop and test functionalities but we want to use the same data that is present in the Oracle instance.

In our case we exported the data from Oracle using IntelliJ. IntelliJ generated some scripts that are compatible with Oracle but not with H2, in particular the dates and timestamps used 'TO_DATE' and 'TO_TIMESTAMP' functions, these are specific to Oracle.

How to use TO_DATE and TO_TIMESTAMP in H2

This solution can be applied to other functions of Oracle but not present in H2.

In H2 you can create aliases using Java, here the ALIAS documentation.

The Oracle SQL script contained something like:

INSERT INTO H2_TABLE(POST_DATE, COMMENT_TIMESTAMP) 
VALUES(TO_DATE('2021-12-31', "YYYY-MM-DD HH24:MI:SS"), TO_TIMESTAMP('2022-12-31 22:23:24.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')); 

The date/time converters contain the value and the pattern of the format used to insert in Oracle.

The DBException in H2

If you run this script in H2 you will receive an exception like:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "TO_DATE" not found; SQL Statement: 
INSERT ... 
    at org.h2.message.DBException.getJdbcSQLException(DbException.java.632) [h2...jar:...] 

Create TO_DATE for H2 Database using Java

As we said, H2 Database is very flexible and we can create functions using Java.

In our data.sql script (we are using Spring Boot and Spring Data), we add:

drop ALIAS if exists TO_DATE; 
CREATE ALIAS TO_DATE as ' 
import java.text.*; 
@CODE 
java.util.Date toDate(String originalDate, String dateFormat) throws Exception { 
    return new SimpleDateFormat("yyyy-MM-dd").parse(originalDate); 
} 
'; 

In our example we did very simple and we hard-coded the pattern of the date, the dateFormat is specific to Oracle and not very interesting for us.
You can adapt the script as it's better for your configuration.

Create TO_TIMESTAMP for H2 Database using Java

The code for TO_TIMESTAMP is similar to the code used for TO_DATE:

drop ALIAS if exists TO_TIMESTAMP; 
CREATE ALIAS TO_TIMESTAMP as ' 
import java.text.*; 
@CODE 
java.sql.Timestamp toTimestamp(String originalTime, String dateFormat) throws Exception { 
    return new java.sql.Timestamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(originalTime).getTime()); 
} 
'; 

Declaring these scripts before the INSERT INTO in your data.sql allow to simulate the TO_DATE and TO_TIMESTAMP functions of Oracle in H2 and reuse the same scripts.

Further customization

If you have errors with other functions you can simply implement them with Java as for our examples.

Note that H2 has an Oracle mode: jdbc:h2:~/test;MODE=Oracle or in the SQL SET MODE Oracle that increases the compatibility with Oracle.
Here you can find the documentation of the Oracle mode in H2

Random UUID of PostgreSQL in H2

The second step of our migration was to use Postgres in production. We are storing UUIDs as IDs in PostgreSQL:

create table SONG 
( 
    ID  UUID not null default gen_random_uuid() 
) 

gen_random_uuid() is a function specific to Postgres: uuid documentation.

This function is equivalent to random_uuid() in H2. A small difference break the compatibility of the scripts.

Update: this feature has been added to H2 v. 2.2.220 and will be integrated in Spring Boot 3.2. This change applies only for SB < 3.2

To avoid to change the schema scripts between Postgres (Test, Prod etc.) and H2 (local development and tests), we created a function in H2 using Java:

drop alias if exists gen_random_uuid; 
CREATE ALIAS gen_random_uuid as ' 
    import java.util.UUID; 
    @CODE 
    java.util.UUID getRandomUuid() throws Exception { 
      return UUID.randomUUID(); 
    } 
' 

With this script added to schema.sql, H2 generates a KEY with an UUID for each new entry. The Postgres configuration doesn't use this file.

schema.sql should be loaded by default by Spring. In case it's not working you can explicitly declare the schema file in application.properties:

spring.sql.init.schema-locations=classpath:/your-db-script-dir/schema.sql