JPA derived query methods failing when boolean mapping using YesNoConverter

Hibernate ORM
1

I have the below entity where the deleted column in database is of type character with expected values 'Y' or 'N' . I’m using the YesNoConverter to encode the boolean value as 'Y' or 'N' .

public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    .
    .
    
    @Column(name = "deleted")
    @Convert(converter= YesNoConverter.class)
    private boolean deleted;

}

But this code is giving error when I have a derived query method querying on the boolean property like below

public interface UserRepository extends JpaRepository<User,Long> {

    List<User> findByDeletedFalse();
}

This repository method throws below exception

2023-02-14T10:30:30.160Z  WARN 5796 --- [nio-9000-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42804
2023-02-14T10:30:30.160Z ERROR 5796 --- [nio-9000-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: argument of NOT must be type boolean, not type character
  Position: 128
2023-02-14T10:30:30.161Z ERROR 5796 --- [nio-9000-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select u1_0.id,u1_0.deleted,u1_0.first_name,u1_0.home_address_id,u1_0.last_name,u1_0.work_address_id from users u1_0 where not(u1_0.deleted)]; SQL [n/a]] with root cause

org.postgresql.util.PSQLException: ERROR: argument of NOT must be type boolean, not type character
  Position: 128
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql-42.5.1.jar:42.5.1]
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.0.1.jar:na]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:205) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:88) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:197) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]

Hibernate: select u1_0.id,u1_0.deleted,u1_0.first_name,u1_0.home_address_id,u1_0.last_name,u1_0.work_address_id from users u1_0 where not(u1_0.deleted)

Code with this issue: GitHub - chackomathew/spring-boot3-hibernate6 at boolean-converter-issue

Spring Boot v3.0.2

Hibernate v6.1.7.Final

If I provide JPQL query like below it’s working fine.

 @Query("select u from User u where u.deleted=false")
 List<User> findByDeletedFalse();
2

This is a bug. Thanks for reporting it. Please create an issue in the issue tracker(https://hibernate.atlassian.net) and link to this post.

3

Issue created [HHH-16182] - Hibernate JIRA

1 Like