Re: Mapping Hibernate boolean to smallint(Postgresql)

On 09/25/2010 05:33 AM, stagirus wrote:

> Good afternoon. I am the Chief Architect, with 19 years of experience
> in the software industry

Without meaning to sound excessively grumpy, I'm surprised that those 19
years didn't teach you to provide detailed error messages, in-depth
description, and preferably test cases with "bug" reports.

The very broad description you've provided forces everybody to guess
what you are talking about. A few excerpts of mapping code, a stack
trace, and some log messages would've made a world of difference.

> Now comes the "religious" discussion. The above design works well for
> Oracle, DB2 and MySQL, etc. But PostgresQL seems to choke. It complains
> about the data type mismatch. By reading various discussions on your forum,
> there seems to some issues with the data type mapping at the JDBC driver.
> JDBC driver does not convert boolean value (false/true) to integers (0/1).

You're correct, it doesn't. PostgreSQL has a native boolean data type.
The JDBC driver uses this type when passed a boolean value.

It might be nice for the JDBC driver to detect that the target column is
a 'short' or 'integer' column and do an implicit boolean->integer
conversion. To do that might require more database traffic (to discover
column types) though. Right now, it doesn't, so you're probably getting
this error message:

ERROR: column "y" is of type integer but expression is of type boolean

As far as I know there's no JDBC driver property to tell it to send
booleans as integers, nor is there any PostgreSQL server configuration
option that lets the server convert boolean values to integer.
Personally I'm pretty surprised that booleans aren't implicitly castable
to 'int', which would resolve the issue you're encountering.

You have a couple of options here. You can:

- Map your boolean fields as booleans in the database when using
PostgreSQL. Hibernate should do this automatically if you let it create
your DDL and it's connected to a PostgreSQL database. This is the best
option, and should be what's happening unless you're overriding
Hibernate's DDL generation.

- Change your mapped objects so the fields are shorts in java, if you
want them as shorts in the database

- Write a Hibernate UserType (this is **trivial** to do) that translates
boolean to/from database short using the setInt() and getInt() JDBC
driver methods. Then use this on your boolean mappings.

- Write a custom PostgreSQL dialect for Hibernate that ignores
PostgreSQL's boolean support and converts booleans to/from integer

--
Craig Ringer