In my Spring setup with DbSetUp + H2 database, queries with LIKE are not working

Question

As the title suggests, what I want to do is test a class with database access in a Spring Boot application.

In the target class, I am using Specification as follows:

TestSpecification specification = new TestSpecification();
Specification spec = Specification.where(specification.nameContain("John"))

List<TestEntity> user = target.findAll(spec);

The nameContain method in the above TestSpecification class uses LIKE and is as follows:

public Specification nameContain(String searchName) { 
 return searchName == null ? null : (root, query, builder) -> builder.like(root.get("name"),
searchName + "%"); 
}

In the test, I am using DbSetup and H2 database with the following H2 configuration:

spring: 
  datasource: 
    driverClassName: org.h2.Driver 
    url: jdbc:h2:mem:testoracledb;MODE=Oracle;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS TEST 
    username: username 
    password: password 
 h2.console.enabled: true

In DbSetup, data is inserted as follows:

private final Operation INSERT_DATA = Operations.insertInto("TEST.SAMPLE") 
                                .row() 
                                .column("MANAGE_NO", "1") 
                                .column("NAME","JohnDoe")

When executed with the actual Oracle DB instead of in unit tests, the LIKE query works without any problems. Additionally, if I use an EQUAL search instead of LIKE, it works without any issues.

public Specification nameContain(String searchName) { 
// this works without any problems
 return searchName == null ? null : (root, query, builder) -> builder.equal(root.get("name"),
searchName); 
}

I am very troubled because I do not understand why the LIKE query does not work only with H2. If anyone knows about this, I would appreciate your guidance.

Answer 1

There is a bug in Hibernate ORM: https://hibernate.atlassian.net/browse/HHH-16277

You can add an explicit escape character as a workaround:

builder.like(root.get("name"), searchName + "%", '\\')