I am currently facing a challenge with unit 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 unit 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 unit 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);
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