problem with BIT in Criteria API

Bazi · **Posted:** Thu Aug 25, 2005 5:19 am

Greetings,
I am trying to use criteria API, but I am not able to construct a condition on a Boolean value, represented by a BIT(1) in the database, can someone help me with this?
I was trying to search forum and Hibernate site too, but BIT is a little bit too frequent word to find something useful.
I tried org.hibernate.type.BooleanType, but get a ClassCastException.
Thanks in advance for any advice.

Hibernate version:
hibernate-3.1beta1

Mapping documents:
in DB:

Code:

show_on_web bit(1) NOT NULL

Java class (generated by Hibernate tools):

Code:

@Entity
@Table(name="pricelist"
    ,schema="public"
)
public class Pricel  implements java.io.Serializable {
    
    private Integer priceId;
    private Boolean showOnWeb;
    
    /** default constructor */
    public Pricel() {
    }
    
    /** constructor with id */
    public Pricel(Integer priceId) {
        this.priceId = priceId;
    }
    
    @Id(generate = GeneratorType.AUTO)
    @Column(name = "price_id")
    public Integer getPriceId() {
        return this.priceId;
    }
    public void setPriceId(Integer priceId) {
        this.priceId = priceId;
    }
    
    @Column(name = "show_on_web")
    public Boolean getShowOnWeb() {
        return this.showOnWeb;
    }
    public void setShowOnWeb(Boolean showOnWeb) {
        this.showOnWeb = showOnWeb;
    }
}

Code between sessionFactory.openSession() and session.close():

Code:

Criteria crit = sess.createCriteria(Pricel.class);
crit.add(Expression.eq("showOnWeb", true));
l = crit.list();

Full stack trace of any exception that occurs:

Code:

org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1962)
   at org.hibernate.loader.Loader.list(Loader.java:1943)
   at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1314)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:298)
   at cz.active24.central.Test.main(Test.java:66)
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bit = boolean
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   ... 5 more

Name and version of the database you are using:
PostgreSQL 7.4.7

The generated SQL (show_sql=true):
Hibernate: select this_.price_id as price1_15_0_, this_.show_on_web as show14_15_0_ from public.pricelist this_ where this_.show_on_web=?

Debug level Hibernate log excerpt:
25.08.05 10:58:44,343 WARN org.hibernate.util.JDBCExceptionReporter SQL Error: 0, SQLState: 42883
25.08.05 10:58:44,343 ERROR org.hibernate.util.JDBCExceptionReporter ERROR: operator does not exist: bit = boolean

Bazi · **Posted:** Thu Aug 25, 2005 7:11 am

heh, didnt post full DEBUG info, so there it is

Code:

08.05 13:04:16,549 INFO  org.hibernate.cfg.Environment Hibernate 3.1 beta 1
08.05 13:04:16,565 INFO  org.hibernate.cfg.Environment hibernate.properties not found
08.05 13:04:16,565 INFO  org.hibernate.cfg.Environment using CGLIB reflection optimizer
08.05 13:04:16,581 INFO  org.hibernate.cfg.Environment using JDK 1.4 java.sql.Timestamp handling
08.05 13:04:16,706 DEBUG org.hibernate.cfg.Configuration Preparing to build session factory with filters : {}
08.05 13:04:16,706 DEBUG org.hibernate.cfg.AnnotationConfiguration Executing first pass of annotated classes
08.05 13:04:17,174 DEBUG org.hibernate.cfg.Ejb3Column Binding column TYPE unique false
08.05 13:04:17,206 DEBUG org.hibernate.cfg.annotations.EntityBinder Import with entity name=Pricel
08.05 13:04:17,237 DEBUG org.hibernate.cfg.AnnotationBinder Processing cz.active24.central.Pricel per property access
08.05 13:04:17,315 DEBUG org.hibernate.cfg.AnnotationBinder Processing annotations of cz.active24.central.Pricel.priceId
08.05 13:04:17,315 DEBUG org.hibernate.cfg.Ejb3Column Binding column price_id unique false
08.05 13:04:17,315 DEBUG org.hibernate.cfg.AnnotationBinder priceId is an id
08.05 13:04:17,346 DEBUG org.hibernate.cfg.annotations.SimpleValueBinder building SimpleValue for priceId
08.05 13:04:17,346 DEBUG org.hibernate.cfg.annotations.PropertyBinder Building property priceId
08.05 13:04:17,346 DEBUG org.hibernate.cfg.annotations.PropertyBinder Cascading priceId with null
08.05 13:04:17,346 DEBUG org.hibernate.cfg.AnnotationBinder Bind @EmbeddedId on priceId
08.05 13:04:17,346 DEBUG org.hibernate.cfg.AnnotationBinder Processing annotations of cz.active24.central.Pricel.showOnWeb
08.05 13:04:17,346 DEBUG org.hibernate.cfg.Ejb3Column Binding column show_on_web unique false
08.05 13:04:17,362 DEBUG org.hibernate.cfg.annotations.PropertyBinder binding property showOnWeb with lazy=false
08.05 13:04:17,362 DEBUG org.hibernate.cfg.annotations.SimpleValueBinder building SimpleValue for showOnWeb
08.05 13:04:17,362 DEBUG org.hibernate.cfg.annotations.PropertyBinder Building property showOnWeb
08.05 13:04:17,362 DEBUG org.hibernate.cfg.annotations.PropertyBinder Cascading showOnWeb with null
08.05 13:04:17,362 DEBUG org.hibernate.cfg.AnnotationConfiguration processing manytoone fk mappings
08.05 13:04:17,362 INFO  org.hibernate.cfg.Configuration processing extends queue
08.05 13:04:17,378 INFO  org.hibernate.cfg.Configuration processing collection mappings
08.05 13:04:17,378 INFO  org.hibernate.cfg.Configuration processing association property references
08.05 13:04:17,378 INFO  org.hibernate.cfg.Configuration processing foreign key constraints
08.05 13:04:17,549 INFO  org.hibernate.connection.DriverManagerConnectionProvider Using Hibernate built-in connection pool (not for production use!)
08.05 13:04:17,549 INFO  org.hibernate.connection.DriverManagerConnectionProvider Hibernate connection pool size: 5
08.05 13:04:17,549 INFO  org.hibernate.connection.DriverManagerConnectionProvider autocommit mode: false
08.05 13:04:17,565 INFO  org.hibernate.connection.DriverManagerConnectionProvider using driver: org.postgresql.Driver at URL: jdbc:postgresql:*****
08.05 13:04:17,565 INFO  org.hibernate.connection.DriverManagerConnectionProvider connection properties: {*****}
08.05 13:04:17,565 DEBUG org.hibernate.connection.DriverManagerConnectionProvider total checked-out connections: 0
08.05 13:04:17,565 DEBUG org.hibernate.connection.DriverManagerConnectionProvider opening new JDBC connection
08.05 13:04:17,799 DEBUG org.hibernate.connection.DriverManagerConnectionProvider created connection to: jdbc:postgresql:*****, Isolation Level: 2
08.05 13:04:17,815 INFO  org.hibernate.cfg.SettingsFactory RDBMS: PostgreSQL, version: 7.4.7
08.05 13:04:17,815 INFO  org.hibernate.cfg.SettingsFactory JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.1devel JDBC3 with SSL (build 400)
08.05 13:04:17,815 DEBUG org.hibernate.connection.DriverManagerConnectionProvider returning connection to pool, pool size: 1
08.05 13:04:17,862 INFO  org.hibernate.dialect.Dialect Using dialect: org.hibernate.dialect.PostgreSQLDialect
08.05 13:04:17,878 INFO  org.hibernate.transaction.TransactionFactoryFactory Using default transaction strategy (direct JDBC transactions)
08.05 13:04:17,878 INFO  org.hibernate.transaction.TransactionManagerLookupFactory No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory Automatic flush during beforeCompletion(): disabled
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory Automatic session close at end of transaction: disabled
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory JDBC batch size: 15
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory JDBC batch updates for versioned data: disabled
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory Scrollable result sets: enabled
08.05 13:04:17,878 DEBUG org.hibernate.cfg.SettingsFactory Wrap result sets: disabled
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory JDBC3 getGeneratedKeys(): disabled
08.05 13:04:17,878 INFO  org.hibernate.cfg.SettingsFactory Connection release mode: null
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Default batch fetch size: 1
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Generate SQL with comments: disabled
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Order SQL updates by primary key: disabled
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
08.05 13:04:17,893 INFO  org.hibernate.hql.ast.ASTQueryTranslatorFactory Using ASTQueryTranslatorFactory
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Query language substitutions: {}
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Second-level cache: enabled
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Query cache: disabled
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Cache provider: org.hibernate.cache.EhCacheProvider
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Optimize cache for minimal puts: disabled
08.05 13:04:17,893 INFO  org.hibernate.cfg.SettingsFactory Structured second-level cache entries: disabled
08.05 13:04:17,956 DEBUG org.hibernate.exception.SQLExceptionConverterFactory Using dialect defined converter
08.05 13:04:17,971 INFO  org.hibernate.cfg.SettingsFactory Echoing all SQL to stdout
08.05 13:04:17,971 INFO  org.hibernate.cfg.SettingsFactory Statistics: disabled
08.05 13:04:17,971 INFO  org.hibernate.cfg.SettingsFactory Deleted entity synthetic identifier rollback: disabled
08.05 13:04:17,971 INFO  org.hibernate.cfg.SettingsFactory Default entity-mode: pojo
08.05 13:04:18,174 INFO  org.hibernate.impl.SessionFactoryImpl building session factory
08.05 13:04:18,174 DEBUG org.hibernate.impl.SessionFactoryImpl Session factory constructed with filter configurations : {}
08.05 13:04:18,174 DEBUG org.hibernate.impl.SessionFactoryImpl instantiating session factory with properties: {*****}
08.05 13:04:18,237 WARN  net.sf.ehcache.config.Configurator No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/....../lib/ehcache-1.1.jar!/ehcache-failsafe.xml
08.05 13:04:18,706 DEBUG org.hibernate.persister.entity.BasicEntityPersister Static SQL for entity: cz.active24.central.Pricel
08.05 13:04:18,706 DEBUG org.hibernate.persister.entity.BasicEntityPersister  Version select: select price_id from public.pricelist where price_id =?
08.05 13:04:18,706 DEBUG org.hibernate.persister.entity.BasicEntityPersister  Snapshot select: select pricel_.price_id, pricel_.show_on_web as show2_0_ from public.pricelist pricel_ where pricel_.price_id=?
08.05 13:04:18,706 DEBUG org.hibernate.persister.entity.BasicEntityPersister  Insert 0: insert into public.pricelist (show_on_web, price_id) values (?, ?)
08.05 13:04:18,706 DEBUG org.hibernate.persister.entity.BasicEntityPersister  Update 0: update public.pricelist set show_on_web=? where price_id=?
08.05 13:04:18,706 DEBUG org.hibernate.persister.entity.BasicEntityPersister  Delete 0: delete from public.pricelist where price_id=?
08.05 13:04:18,753 DEBUG org.hibernate.loader.entity.EntityLoader Static select for entity cz.active24.central.Pricel: select pricel0_.price_id as price1_0_0_, pricel0_.show_on_web as show2_0_0_ from public.pricelist pricel0_ where pricel0_.price_id=?
08.05 13:04:18,753 DEBUG org.hibernate.loader.entity.EntityLoader Static select for entity cz.active24.central.Pricel: select pricel0_.price_id as price1_0_0_, pricel0_.show_on_web as show2_0_0_ from public.pricelist pricel0_ where pricel0_.price_id=?
08.05 13:04:18,753 DEBUG org.hibernate.loader.entity.EntityLoader Static select for entity cz.active24.central.Pricel: select pricel0_.price_id as price1_0_0_, pricel0_.show_on_web as show2_0_0_ from public.pricelist pricel0_ where pricel0_.price_id=? for update
08.05 13:04:18,753 DEBUG org.hibernate.loader.entity.EntityLoader Static select for entity cz.active24.central.Pricel: select pricel0_.price_id as price1_0_0_, pricel0_.show_on_web as show2_0_0_ from public.pricelist pricel0_ where pricel0_.price_id=? for update
08.05 13:04:18,753 DEBUG org.hibernate.impl.SessionFactoryObjectFactory initializing class SessionFactoryObjectFactory
08.05 13:04:18,768 DEBUG org.hibernate.impl.SessionFactoryObjectFactory registered: d1dfe83205ed4f3f0105ed4f42410000 (unnamed)
08.05 13:04:18,768 INFO  org.hibernate.impl.SessionFactoryObjectFactory Not binding factory to JNDI, no JNDI name configured
08.05 13:04:18,768 DEBUG org.hibernate.impl.SessionFactoryImpl instantiated session factory
08.05 13:04:18,768 INFO  org.hibernate.impl.SessionFactoryImpl Checking 0 named queries
08.05 13:04:18,831 DEBUG org.hibernate.impl.SessionImpl opened session at timestamp: 4607868349513728
08.05 13:04:18,831 DEBUG org.hibernate.transaction.JDBCTransaction begin
08.05 13:04:18,831 DEBUG org.hibernate.jdbc.ConnectionManager opening JDBC connection
08.05 13:04:18,831 DEBUG org.hibernate.connection.DriverManagerConnectionProvider total checked-out connections: 0
08.05 13:04:18,831 DEBUG org.hibernate.connection.DriverManagerConnectionProvider using pooled JDBC connection, pool size: 0
08.05 13:04:18,831 DEBUG org.hibernate.transaction.JDBCTransaction current autocommit status: false
08.05 13:04:18,878 DEBUG org.hibernate.jdbc.AbstractBatcher about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
08.05 13:04:18,878 DEBUG org.hibernate.SQL select this_.price_id as price1_0_0_, this_.show_on_web as show2_0_0_ from public.pricelist this_ where this_.show_on_web=?
Hibernate: select this_.price_id as price1_0_0_, this_.show_on_web as show2_0_0_ from public.pricelist this_ where this_.show_on_web=?
08.05 13:04:18,878 DEBUG org.hibernate.jdbc.AbstractBatcher preparing statement
08.05 13:04:18,878 DEBUG org.hibernate.type.BooleanType binding 'true' to parameter: 1
08.05 13:04:18,909 DEBUG org.hibernate.jdbc.AbstractBatcher about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
08.05 13:04:18,909 DEBUG org.hibernate.jdbc.AbstractBatcher closing statement
08.05 13:04:18,909 DEBUG org.hibernate.util.JDBCExceptionReporter could not execute query [select this_.price_id as price1_0_0_, this_.show_on_web as show2_0_0_ from public.pricelist this_ where this_.show_on_web=?]
org.postgresql.util.PSQLException: ERROR: operator does not exist: bit = boolean
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1618)
   at org.hibernate.loader.Loader.doQuery(Loader.java:639)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:221)
   at org.hibernate.loader.Loader.doList(Loader.java:1959)
   at org.hibernate.loader.Loader.list(Loader.java:1943)
   at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1314)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:298)
   at cz.active24.central.Test.main(Test.java:64)
08.05 13:04:18,924 WARN  org.hibernate.util.JDBCExceptionReporter SQL Error: 0, SQLState: 42883
08.05 13:04:18,924 ERROR org.hibernate.util.JDBCExceptionReporter ERROR: operator does not exist: bit = boolean
08.05 13:04:18,924 DEBUG org.hibernate.transaction.JDBCTransaction rollback
08.05 13:04:18,924 DEBUG org.hibernate.jdbc.JDBCContext before transaction completion
08.05 13:04:18,924 DEBUG org.hibernate.impl.SessionImpl before transaction completion
08.05 13:04:18,924 DEBUG org.hibernate.transaction.JDBCTransaction rolled back JDBC Connection
08.05 13:04:18,924 DEBUG org.hibernate.jdbc.JDBCContext after transaction completion
08.05 13:04:18,924 DEBUG org.hibernate.impl.SessionImpl after transaction completion
08.05 13:04:18,924 DEBUG org.hibernate.impl.SessionImpl closing session
08.05 13:04:18,924 DEBUG org.hibernate.jdbc.ConnectionManager closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
08.05 13:04:18,924 DEBUG org.hibernate.connection.DriverManagerConnectionProvider returning connection to pool, pool size: 1
08.05 13:04:18,924 DEBUG org.hibernate.jdbc.JDBCContext after transaction completion
08.05 13:04:18,924 DEBUG org.hibernate.impl.SessionImpl after transaction completion

xobo · **Posted:** Thu Aug 25, 2005 9:48 am

try to add type for the column to notice hibernate about BIT type in the database
@Column(name = "show_on_web", type="bit" [check the type in PostgreSQL ])

Bazi · **Posted:** Fri Aug 26, 2005 3:53 am

xobo wrote:

try to add type for the column to notice hibernate about BIT type in the database
@Column(name = "show_on_web", type="bit" [check the type in PostgreSQL ])

Well, the @Column property do not have a type attribute, so how can I notice hibernate about that column type? I tried columnDefinition attribute without success.
I am able to read from DB correctly, and if I make a querry like where pricel.showOnWeb = '1' it works OK. But I need to create quite a complicated querry, so I want to use Criteria API...

Bazi · **Posted:** Tue Aug 30, 2005 9:22 am

Should anyone encounter the same problem, here is a workaround:
just replace the

Code:

crit.add(Expression.eq("showOnWeb", true));

line with this one

Code:

crit.add(Expression.sql("{alias}.show_on_web = '1'"));

- it has to be the column name as it appears in DB instead of using the property name

Anyway, this workaround is not nice, could someone tell me, why is the original code not working?
Is that 'operator does not exist: bit = boolean' thing a BUG in Hibernate or am I just overlooking something?