How to solve H2 database `Values of types "BOOLEAN" and "INTEGER" are not compar-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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