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

Question

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.

Biagi

I've never had to do such a thing. What dialect did you have in your config? Instead of hacking the dialect, try 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 – Gev

Hi @Bohemian, the problem arises when one column is defined as boolean but Hibernate provides a 1/0 value instead of true/false. , for whatever reason, This issue had also been reported on H2 support group. Thanks. – Biagi

Equating 1/0 with true/false would be correct behaviour if H2 mode was set to MySQL. Try adding ;MODE=PostgreSQL (or whatever your DB is - many are supported) to your jdbc connection url, ie jdbc:h2:...;MODE=PostgreSQL. btw, what is your jdbc url? – Gev

The real DB is Oracle. The datasource is initialized programmatically because I need mode.limit=true hack to not get syntax error on the LIMIT clause : @Bean @Profile("test") public DataSource dataSource() { // Ref groups.google.com/g/h2-database/c/yxnv64Ak-u8/m/n-kqYV_yBQAJ org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE"); mode.limit = true; DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName("org.h2.Driver"); dataSource.setUrl("jdbc:h2:mem:vessaging;DB_CLOSE_DELAY=-1"); – Biagi

Staggeringly, and IMHO unacceptably, Oracle does't have a boolean datatype. Traditionally, char is used and Y and N values stored, but numeric storing 1 and 0 is also frequently used. Sounds like H2 picked that later. What datatype is your "boolean" column? – Gev

Indeed, it is char but the entity column type is Boolean. E.g. : @Column(name = "IS_BROADCAST") private Boolean isBroadcast; So H2 creates a boolean column. – Biagi

Answer 1

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.

Biagi

This did work for me. Spring Boot 2.7.1. Strange that I would have to do this when they upped the version of H2 to 2.x in Spring Boot 2.7.0. – Scaler

Answer 2

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.

Demarcate

Answer 3

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

Thearchy

Adding MODE=MySQL fixed it for me also! – Ailssa

This one worked also for me. Many thanks @Herry! – Mages

Answer 4

If you have error in type matching, you can use MODE=.... specifying it in spring.datasource.url=jdbc:h2:mem:test;MODE=MySQL But for some reason, when I specified MODE=PostgreSQL, it didn't solve my problem. Then I explicitly specified dialect for hibernate: spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

And the error in type matching disappeared.

Condom

Answer 5

I used this in my unit test which uses h2 and it worked

@BeforeAll
static void setUp() {
    org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
    mode.limit = true;
    mode.numericWithBooleKanComparison = true; //This fixes the issue
}

Hunfredo

Answer 6

Before any other solution, check your Hibernate version. Hibernate supports new versions of H2 since the version 5.6.5.Final. No dialect overriding or numericWithBooleKanComparison H2 config required.

Gravimeter

Answer 7

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).

Gev