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

Question

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?

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

Oracle does not have a BOOLEAN data type in SQL. It does have a BOOLEAN data type in PL/SQL (but your query appears to be SQL and not PL/SQL). — MT0
– MT0, Commented Mar 14, 2022 at 13:07
The query works correctly in Oracle. It must map t.value to 0 or 1. It just doesn't work in H2. — Michael
– Michael, Commented Mar 14, 2022 at 13:08
My point is that when you say "This is due to a @Query comparing a boolean to an integer." cannot be correct as Oracle does not have BOOLEAN data type in SQL so, inside the database, you are probably comparing an integer to an integer. — MT0
– MT0, Commented Mar 14, 2022 at 13:09
In my object value is of type Boolean. In the database it will be stored as 0 or 1 but Hibernate must be mapping it based on the dialect. I believe my statement is correct as I'm not saying Oracle has a boolean type. I'm saying that my code is essentially running "TRUE" = 1 instead of mapping the values first. When executed in Oracle it runs 1 = 1 as expected. I've updated my question to make it clearer. — Michael
– Michael, Commented Mar 14, 2022 at 13:16

Peter Mortensen · Accepted Answer · 2023-11-08 01:14:17Z

1

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 the following:

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

edited Nov 8, 2023 at 1:14

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 14, 2022 at 14:15

Panagiotis Bougioukos

19.6k4 gold badges41 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Michael Over a year ago

This sounds like what I want although I'm having some trouble with it. If I use t.value = false I still get the same error. I've also tried setting the @Type for the variable. In the exception I can see it's converting the false to 0. E.g tableName.value=0

Panagiotis Bougioukos Over a year ago

@Michael do you get the same exception message with @Type(type= "org.hibernate.type.NumericBooleanType") ?

Michael Over a year ago

Yes the same message with @Type added. If I change it to t.value = false or t.value = 0 it throws the same exception in both cases.

Panagiotis Bougioukos Over a year ago

@Michael please share the properties that you use for test, in order to set h2 in Oracle mode

Michael Over a year ago

I upgraded to Hibernate 5.6.5 and the changes you mentioned solved it! I didn't need the @Type annotations but changing t.value=0 to t.value=false worked nicely.

|

Collectives™ on Stack Overflow

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

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related