Implementing Oracle functions in H2 for Spring Boot integration testing

We all know that having integration tests helps catch potential issues before they reach production. When a test environment needs to mirror the production database behaviour but Testcontainers aren’t an option due to infrastructure limitations, running these tests becomes challenging. The H2 in-memory database provides a good alternative – it’s lightweight, fast, and easy to configure. However, Oracle-specific SQL functions create a compatibility gap that needs to be handled.

The challenge

When using H2 for testing Oracle-based applications, the first challenge arises when the code uses specific SQL functions. A common example is the TO_DATE function. If you try to run it with H2 you will get an exception such as:

Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement [Function "TO_DATE" not found;... 

Implementation example

The following Appointment entity shows an example of a use case where TO_DATE is needed for dynamic date formatting:

@Entity
@Table(name = "APPOINTMENT")
public class Appointment {
    @Id
    @GeneratedValue
    private Long id;

    private Integer day;
    private Integer month;
    private Integer year;
    private Status status;

    @Formula("CASE WHEN year IS NOT NULL THEN " +
            "    CASE WHEN month IS NOT NULL THEN " +
            "        CASE WHEN day IS NOT NULL THEN " +
            "            TO_DATE(year || '-' || month || '-' || day, 'yyyy-MM-dd') " +
            "        ELSE " +
            "            TO_DATE(year || '-' || month || '-' || '01', 'yyyy-MM-dd') " +
            "            END " +
            "    ELSE " +
            "        TO_DATE(year || '-' || '01-01', 'yyyy-MM-dd') " +
            "        END " +
            "ELSE " +
            "    TO_DATE(NULL, NULL) " +
            "    END")
    private Timestamp formattedAppointmentDate;
    
    <constructors>
    <getter/setter>
....

Also, the repository layer may require these functions in query definitions:

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Long> {
    @Query(value = "SELECT a FROM Appointment a WHERE TO_DATE(a.year || '-' || TO_CHAR(a.month, '00') || '-' || TO_CHAR(a.day, '00'), 'yyyy-MM-DD') BETWEEN TO_DATE(:#{#startDate}, 'yyyy-MM-DD') AND TO_DATE(:#{#endDate}, 'yyyy-MM-DD')")
    List<Appointment> findAppointmentsBetweenGivenDates(@Param("startDate") String startDate, @Param("endDate") String endDate);
}

The solution

The solution is straightforward, as H2 allows the registration of custom functions through Java implementations. This capability enables the creation of Oracle-compatible functions within the H2 environment. Here’s how to do it:

Function definition

First, create a file e.g. register_compatibility_functions.sql with this code:

DROP ALIAS IF EXISTS TO_DATE;
CREATE ALIAS TO_DATE AS '
import java.text.*;
@CODE
java.util.Date toDate(String s, String dateFormat) throws Exception {
    if (s == null || dateFormat == null) return null;
    return new SimpleDateFormat(dateFormat).parse(s);
}
';

and save it in your test resources folder, e.g. src/test/resources/compatibility/register_compatibility_functions.sql.

Usage

Use Spring’s @Sql annotation to load this function before test execution:

@Test
    @Sql(scripts = "classpath:compatibility/register_compatibility_functions.sql")
    public void shouldFormatTimestampCorrectlyWithFullDate() {

        Appointment appointment = new Appointment(15, 6, 2024, Status.APPROVED);

        appointmentService.addNewAppointment(appointment);

        Appointment saved = appointmentService.findAllAppointments().getFirst();
        assertNotNull(saved.formattedAppointmentDate());
        assertEquals("2024-06-15 00:00:00.0", saved.formattedAppointmentDate().toString());
    }

Key considerations

A few important things to keep in mind when using this approach:

• 1. It’s not exactly the same as using a real Oracle database
• 2. You’ll need to create similar functions for any other Oracle-specific functions you use
• 3. It’s wise to occasionally  run tests against a real Oracle database
• 4. Remember that, whenever possible, Testcontainers remains the most reliable option
  

Want to learn more? Check out H2’s compatibility features in the documentation:

https://www.h2database.com/html/features.html#compatibility

Do you have any tips or suggestions?