How to solve H2 database `Values of types "BOOLEAN" and "INTEGER" are not comparable` syntax error?

user_1431979 0
Viewed 3856

I have an H2 column of type Boolean but Hibernate query it using a 1/0 instead of TRUE/FALSE values, which leads to the Values of types "BOOLEAN" and "INTEGER" are not comparable syntax error.

For instance, Hibernate 5 will write

WHERE myBooleanColumn = 1

instead of

WHERE myBooleanColumn = TRUE

How can this be solved?

My H2 database version is 2.0.206 and I'm using Spring Boot 2.5.6.

h2 hibernate
4 Answers

You can you can create a class that will override the Hibenate's Dialect toBooleanValueString method :

package com.myCorp;

import org.hibernate.dialect.H2Dialect;

public class H2DialectExtended extends H2Dialect {

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

}

And load it in your Spring Boot testing application-test.properties :

spring.jpa.properties.hibernate.dialect=com.myCorp.H2DialectExtended

This way, Hibernate will write :

WHERE myBooleanColumn = TRUE

instead of

WHERE myBooleanColumn =  1

Which will solve the problem as the myBooleanColumn is of type H2 Boolean.

user_14319790

This works for me (in case you are using Oracle mode) :

@TestConfiguration
public class H2WithOracleModeTestConfiguration {

    @Bean
    public DataSource h2DataSource() {
        EmbeddedDatabase embeddedDatabase = new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .setName(UUID.randomUUID() + ";Mode=Oracle;DEFAULT_NULL_ORDERING=HIGH")
                .build();
        Mode mode = Mode.getInstance("ORACLE");
        mode.limit = true;
        // Here is the trick
        mode.numericWithBooleanComparison = true;
        return embeddedDatabase;
    }
}

I am using Spring Boot 2.7.X and H2 2.1.214.

user_197154290

I know this is an old question, but I ran into the same issue and solved it differently, so I state my solution here if anyone in the future runs into this problem.

It is based on the comment of Bohemian (I tried to cite you, but I couldn't find a way, sorry.

He suggested changing the connection mode in the JDBC string. My connection string was: spring.datasource.url: jdbc:h2:mem:testdb;NON_KEYWORDS=USER; (I have a table 'user', so I needed to disable H2's keyword checking for 'user').

I had to specifically add the database mode 'MODE=MySQL' for it to work with my boolean field.

This resulted in the final connection string: spring.datasource.url: jdbc:h2:mem:testdb;NON_KEYWORDS=USER;MODE=MySQL

user_90809740

Configure these properties:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

or if you use yaml:

spring:
  datasource:
    url: jdbc:h2:mem:mydb
    username: sa
    password: password
    driverClassName: org.h2.Driver
  jpa:
    spring.jpa.database-platform: org.hibernate.dialect.H2Dialect

Consider upgrading to the latest version of spring-boot (currently 2.6.2).

user_2561960
Related