Handling MySQL's date_format Function in H2 Database for Testing

Question

I am currently facing a challenge with testing a Spring Boot application that uses MySQL in production. Specifically, I'm having trouble with the date_format function, which works perfectly in MySQL but is not supported in the H2 database used for our tests.

Problem

Our application uses the date_format function extensively in repository queries to format dates. These queries work without issues in the production environment with MySQL. However, when running tests that use an H2 database, these queries fail because H2 does not recognize the date_format function.

Example Query

@Query("SELECT e FROM EmployeeEntity e WHERE DATE_FORMAT(e.joinedDate,'%Y%m') = :joinedMonth")
List<Employee> findByJoinedMonth(@Param("joinedMonth") String joinedMonth);

Answer 1

Worked Solution (tested with Hibernate 6.2.5.Final)

Using Custom H2 Dialect to add our custom function: date_format

1. Create my customer H2 dialect

package com.mycompany.myproject.dialect;

import org.hibernate.boot.model.FunctionContributions;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

/**
 * Custom H2 Dialect to add custom functions
 */
public class CustomH2Dialect extends H2Dialect {
    @Override
    public void initializeFunctionRegistry(FunctionContributions functionContributions) {
        super.initializeFunctionRegistry(functionContributions);

        // Registering the DATE_FORMAT function to use H2's FORMATDATETIME function
        functionContributions.getFunctionRegistry().register(
                "date_format",
                new StandardSQLFunction("FORMATDATETIME", StandardBasicTypes.STRING)
        );
    }
}

2. Config in /test/application.yaml to use my custom dialect when doing the UT

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.mycompany.myproject.dialect.CustomH2Dialect