JPA Hibernate Postgres Error - operator does not exist: boolean = integer

2
votes

I am having problem with NamedQuery when project is deployed to tomcat server on Linux machine. I have environment as:

  1. CentOS 6.3
  2. Postgres 8.4
  3. JPA 2.1
  4. Hibernate 3.2.8

NamedQuery is 

Select dv FROM DocumentVersion dv where dv.isPublished=false

When this query is executed on windows machine, It works fine but when the project is deployed on server with above configuration, the pages display the exception with the following error:

ERROR 2015-01-04 22:30:33 org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146 - ERROR: operator does not exist: boolean = integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 649

When I change the query to :

Select dv FROM DocumentVersion dv where dv.isPublished =:isPublished

i.e. passing boolean as parameter to NamedQuery, it works fine on both platform.

I want to know if I need to add/update any configuration on linux machine or I am doing something wrong.

Table Schema

CREATE TABLE DocumentVersion (
  id SERIAL NOT NULL,
  uuid varchar(100) NOT NULL,
  displayName varchar(100) NOT NULL,
  fileName varchar(100) NOT NULL,
  fileDescription varchar(255) DEFAULT NULL,
  filePath varchar(2048) DEFAULT NULL,
  mimeType varchar(255) NOT NULL,
  version INTEGER NOT NULL,
  fileSize BIGINT NOT NULL,
  isPublished boolean NOT NULL DEFAULT false,
  PRIMARY KEY (id)
);
2
javahibernatepostgresqljpa
Do u mean dh.isPublished or dv.isPublished? – Juned Ahsan
Its "dv". Thanks. Just Corrected. – Muhammad Imran Saeed
Its a postgres issue look stackoverflow.com/questions/1948827/… .And always remember when there is problem in sql try running it in your database gui tool to find the error in sql rather than rerunning your project. – singhakash
It would have been correct if I would have written a native query with JPA Hibernate. It's JPQL query. The problem seems to be with dialect rather than Postgres itself. – Muhammad Imran Saeed
hibernate 3.2 will not support JPA 2.1. It is JPA 1.x IIRC – Neil Stockton

2 Answers

2
votes

try with this.

Select dv FROM DocumentVersion dv where dv.isPublished= "false"

or

Select dv FROM DocumentVersion dv where dv.isPublished is false

PostgreSQL likes "true" and "false" for boolean data types.

Refer documentation here for more.

-2
votes

First guess after looking at the documentation - you should use the word false in UPPERCASE, i.e. FALSE