H2 Oracle Mode - Values of types "BOOLEAN" and "INTEGER" are not comparable-CodePudding

I'm using Spring Boot and Hibernate and when running my tests in H2 (2.1.210) I get an Values of types "BOOLEAN" and "INTEGER" are not comparable error. This is due to a @Query comparing a boolean to an integer. This works in Oracle correctly.

@Query("FROM TABLE_NAME t WHERE t.value= 0")
public List<Example> findExample();

t.value is of type Boolean.

I've found other questions with similar issues and the recommendation is to add this.

import org.hibernate.dialect.H2Dialect;

public class H2DialectExtended extends H2Dialect {

    @Override
    public String toBooleanValueString(boolean bool) {
        return bool ? "TRUE" : "FALSE";
    }
}

However this doesn't work.

How can I solve this?

Update

H2/Hibernate Settings - application.yml

spring:

  driver:
    class-name: org.h2.Driver

  datasource:
    platform:
      url: jdbc:h2:mem:project_name;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;
      username: username
      password:

  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect

CodePudding user response：

if this @Query("FROM TABLE_NAME t WHERE t.value= 0") is exactly what you have, then it means that this is JPQL language, not SQL language. The way that you compare it, it is like the entity related with this has a integer field, which should not be the case. The java field should have been boolean.

Then this should have been @Query("FROM TABLE_NAME t WHERE t.value= false") and it would be able to work both for Oracle and H2

By default when you use an ORM vendor like hibernate and you have an Oracle database, the entity field boolean value will be matched in database level with type of Number(1) where 0 == false and 1 == true on ORM layer.

One way for this to work would be to have your entity field as following

@Type(type= "org.hibernate.type.NumericBooleanType")
private Boolean value;