Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

I got sick of the guesswork and hand-waving, so here's a test case
demonstrating that Hibernate works just fine with PostgreSQL when
mapping booleans as shorts or as booleans.

Executable:

http://www.postnewspapers.com.au/~craig/weblinks/pghiberboolean-1.0-SNAPSHOT-jar-with-dependencies.jar

Sources including build README and Maven pom:

http://www.postnewspapers.com.au/~craig/weblinks/pghiberboolean-1.0-SNAPSHOT-project.zip

Run the test case as:

java -jar target/pghiberboolean-1.0-SNAPSHOT-jar-with-dependencies.jar \
      jdbc:postgresql://localhost/DBNAME DBUSER DBPASSWORD

(all on one line, replace DBNAME, DBUSER, DBPASSWORD with settings for a
test database on your machine).



Now: please show your code so we can see what's actualling going wrong,
as this case demonstrates that Hibernate gets it right when left to its
own devices.

Are you using your own DDL to define your tables, so you're mapping
types as shorts in hibernate but using booleans in the database? Because
that's not how Hibernate expects it to be, it expects a short if you
tell it you're mapping a short.

I'm going to guess that what you're doing is equivalent to running the
attached test program to create the tables, then connecting to the
database and running:

  ALTER TABLE test_entity DROP COLUMN not_boolean;
  ALTER TABLE test_entity ADD COLUMN not_boolean boolean;

thus changing the "shortint" typed column to "boolean". This will cause
the test program to fail with:


SEVERE: ERROR: column "not_boolean" is of type boolean but expression is
of type smallint
  Hint: You will need to rewrite or cast the expression.
  Position: 67



If this is the case, you can:

- Let hibernate generate the DDL in a database-independent manner;

- Fix your SQL DDL so it declares booleans where Hibernate expects
  booleans;

- Fix your mappings so they're Boolean in java if they're boolean in
  the database, and let Hibernate take care of mapping them to shorts
  when the database doesn't understand booleans natively;

- Write a UserType to hack around this by using the short-typed get and
  set methods in JDBC rather than the boolean ones, overriding
  Hibernate's defaults.

Basically, I think you're throwing a spanner in the works by trying to
override Hibernate, but only doing it half-way.



--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/