Mapping Hibernate boolean to smallint(Postgresql)

stagirus <mamasa@stagirus.com> wrote:

JDBC driver does not convert boolean value (false/true) to
integers (0/1).

Can you mention which methods of which classes you would expect to
do this?

-Kevin

Thank you for your quick response. As I stated above, we directly do not call
any JDBC API, not any more. It is all done by Hibernate OR mapping. The
above solution (Hibernate mapping) worked fine with Oracle JDBC drivers, for
a long time. I believe Hibernate might be mapping, or calling the
appropriate JDBC API, for boolean fields. I guess it would be set/getBoolean
methods, right?

If I were to guess, Oracle JDBC driver happily takes Java true/false as
boolean values and maps to integer columns (0 or 1). Whereas PostgresQL
might be expecting 'f' or 't' for boolean values for obvious reasons. My
impression is it is the way PostgresQL (JDBC) team views the boolean values
should function. As all other religions - it is just a matter of
perspective. We are just your users, BTW, the users perspective is very
helpful it not powerful.

Am I making sense? If you need, I will get the exception report from my
developer.

Thank you again.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2853280p2853322.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

[Moving the discussion to the PostgreSQL JDBC list, with a blind copy
to bugs. This doesn't sound to me like a bug, per se, but an
extension to the JDBC driver which may be a convenience to some
users. We're more likely to get the attention of the right group of
people on the JDBC list.]

stagirus wrote:

I guess it would be set/getBoolean methods, right?

Maybe. I'd rather not guess at what it is you need though.

If you need, I will get the exception report from my developer.

Please do. That would allow us to make sure that if we try to help,
we're working on the right part of the code. It's so much harder to
hit a target when shooting in the dark. ;-)

-Kevin

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

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).

There isn't really enough information here to diagnose the problem,
but here's another guess - if it's just a problem with HQL generated
queries, perhaps you could try adding the following to your Hibernate
configuration:

<property name="hibernate.query.substitutions">true 1, false 0</property>

Regards,
Dean

As a fundamental protocol, I prefer to keep any "attitudes" and
"finger-pointing" off the table. Let us just focus on the real
problem-solving.

Though I am very versatile in speaking languages like C, C++ and Java, I
assumed we all can speak and understand "English." You can also clearly see
the issue on table is complicated. It spans Java, Hibernate, JDBC Drivers,
RDBMS Engines and and finally people.

Yes, the error is like - column "y" is of type integer but expression is of
type boolean. I just expressed it in natural language "type mismatch."
Below is the sample HBM file block.
<property name="deleted" type="boolean">
<column name="DELETED" />
</property>
In the DB, DELETED column is SMALLINT but in the Java(POJOs), it is boolean.

I like Dean's suggestion about "hibernate.query.substitutions." But does it
really solve the issue? The current code (HBM mapping) is fully functional
with Oracle (SMALLINT).

That leaves us only two variables in the equation:
1. PostgresQL JDBC Driver
2. PostgresQL DB Engine.

During my research on your JDBC discussion forum, I learned that JDBC team
is mapping Java boolean to BIT or chars. Please refer this discussion.
http://postgresql.1045698.n5.nabble.com/Wrong-SqlType-for-boolean-columns-td2256874.html#a2256874

Please refer to the following data type mapping between Java types and SQL
types.
Oracle:
http://download.oracle.com/javase/1.3/docs/guide/jdbc/getstart/mapping.anc1.gif
DB2:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvjdata.htm

Is there any similar mapping reference for PostgresQL data types? (Seems
this question was already raised and successfully ignored!)
http://postgresql.1045698.n5.nabble.com/PostgreSQL-types-and-Java-types-td2174117.html

ARE THERE ANY ALTERNATIVE (3rd-party) JDBC DRIVERs FOR POSTGRESQL? In this
day & age compatibility with Hibernate got to be a key goal for any RDBMS.

thanks.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2853280p2853928.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

[Sigh. Moving to the PostgreSQL JDBC list; I accidentally picked up
the Sun JDBC address in a previous attempt to move this. :-( ]

I wrote:

stagirus wrote:

I guess it would be set/getBoolean methods, right?

Maybe. I'd rather not guess at what it is you need though.

If you need, I will get the exception report from my developer.

Please do. That would allow us to make sure that if we try to
help, we're working on the right part of the code. It's so much
harder to hit a target when shooting in the dark. ;-)

Any progress on getting exception reports?

-Kevin

My developer sent me the following error report. It does not include stack
trace. But I can get it shortly.

ERROR: column "deleted" is of type smallint but expression is of type
boolean

It appears that the queries generated by Hibernate contains boolean
expressions that are not being accepted as valid values by Postgresql DB.
Does anybody have experience with the following Hibernate's query
substitutions will work (Dean's suggestion)?
<property name="hibernate.query.substitutions">true 1, false 0</property>

It seems to be promising but I want to be sure it works before I recommend
to my team. They are already burnt with this boolean related issues in
Postgresql.

Ideally DB Engine/JDBC Drivers should be flexible(smart?) enough to
recognize integer values for boolean values. It is unfortunate that RDMBS
vendors nor ANSI SQL standardized boolean values. Am I making sense?

Thanks.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855475.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

Ideally DB Engine/JDBC Drivers should be flexible(smart?) enough
to recognize integer values for boolean values. It is unfortunate
that RDMBS vendors nor ANSI SQL standardized boolean values. Am I
making sense?

Sure. I finally tracked down a current copy of the JDBC spec. For
some reason, this seems to have become more difficult to find in
recent months; if anyone else is looking, I found it here:

http://jcp.org/aboutJava/communityprocess/final/jsr221/index.html

The relevant portions are TABLE B-5 and TABLE B-6. What matters for
purposes of this discussion is that, when the driver is specifically
requested to convert between any of these Java classes:

String
java.math.BigDecimal
Boolean
Byte
Short
Integer
Long
Float
Double

and any of these database types:

TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT
DOUBLE
DECIMAL
NUMERIC
BIT
BOOLEAN
CHAR
VARCHAR
LONGVARCHAR

it should attempt to do so. Obviously that can sometimes fail due
to size or format issues. But getBoolean against a SMALLINT is
clearly supposed to be attempted on demand. I didn't happen across
the specification of how values map there, but in the absence of
evidence to the contrary I'd assume zero is false and anything else
is true; Likewise, setObject using a Boolean against a SMALLINT
target is supposed to work.

What kinds of values do you see in the SMALLINT column when you set
a TRUE into a SMALLINT column? 1? -1?

-Kevin

Kevin's response above seems to be very promising.

From your comments/observations, are you saying that there could be some

coding issues regarding these conversions between boolean and integers?

Reg your question of "What kinds of values do you see in the SMALLINT column
when you set a TRUE into a SMALLINT column? 1? -1?"
: Nothing. Because it fails before any inserts or updates happen. We
discussed the error above.

Because I have not worked with the internals of Hibernate/JDBC drivers, I
cannot definitively state if Hibernate is calling setters and getters
(setBoolean) using prepared statements or directly executes INSERT/UPDATE
statements. Anything is possible.

Thanks.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855614.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

Reg your question of "What kinds of values do you see in the
SMALLINT column when you set a TRUE into a SMALLINT column? 1?
-1?" : Nothing. Because it fails before any inserts or updates
happen. We discussed the error above.

I meant with other database products. Are they consistent?

-Kevin

Yes. It works without any issues on Oracle. We have not yet tested with other
databases except Postgresql. For the past 40 days, we have been trying to
get the product work with Postgresql. Ran into too many issues just related
to "Boolean" fields. We were hoping to deploy our Cloud System (SaaS) on
Postgreql -if we can get it to work.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855630.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

From your comments/observations, are you saying that there could
be some coding issues regarding these conversions between boolean
and integers?

I'm saying that while the code is probably behaving as the person
who contributed it to PostgreSQL intended, it appears not to comply
with the current standard. The PostgreSQL community generally makes
a pretty good effort toward conforming to standards. If it's
relatively easy to make it conform to the standard, someone will
probably just do it. (Heck, it could even be me.) If it's a lot of
work, someone who needs the feature may have to take on the burden
of the change, either by offering a patch to implement it or paying
someone to do so.

Because I have not worked with the internals of Hibernate/JDBC
drivers, I cannot definitively state if Hibernate is calling
setters and getters (setBoolean) using prepared statements or
directly executes INSERT/UPDATE statements. Anything is possible.

Hence the request for the stack trace. Most people have a pretty
full plate, and aren't eager to volunteer their time to work on
something which might not actually help anyone.

-Kevin

stagirus <mamasa@stagirus.com> wrote:

It works without any issues on Oracle.

The question was -- when you set a SMALLINT column with a Boolean
which is TRUE, what value is stored in the database column. You've
mentioned doing this on multiple databases; do they all set the same
value, and if so, what is it?

-Kevin

Point is well-taken. I will supply the stack trace. Thanks.

We are inserting 0 for false and 1 for true. Oracle and Hibernate has been
pretty happy so far. We just have to make Postgresql happy too.

I am impressed with your comment that Postgresql tries to comply with the
standards. If so do you have sufficient test cases for converting between
the data types you mentioned above. Any test cases for
Hibernate-JDBC-Postgresql scenarios? If I were (Postgres team), I would
include them to ensure the compatibility.

Thanks.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855656.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

If I were (Postgres team), I would include them to ensure the
compatibility.

Feel free to contribute a patch. The "PostgreSQL Team" is a pretty
loose and varied lot. I've read that the changes for the 9.0
release were contributed by over 100 different people, although some
people are obviously a lot more active than others. One great thing
about free, open source software is that "you can scratch your own
itch." Many contributions come from people who use the product in a
way others don't, and run into an issue which isn't bothering anyone
else -- much like you.

Welcome to the team! ;-)

-Kevin

Kevin Grittner wrote:

The relevant portions are TABLE B-5 and TABLE B-6. [...]

But getBoolean against a SMALLINT is
clearly supposed to be attempted on demand. I didn't happen across
the specification of how values map there, but in the absence of
evidence to the contrary I'd assume zero is false and anything else
is true; Likewise, setObject using a Boolean against a SMALLINT
target is supposed to work.

If I read the original report right, it's about setBoolean(), not
getBoolean() (the error is a type mismatch while executing the query,
not a problem processing the results)

Note that table B-5 is specifically about "What combinations of
java.sql.Types value and actual instance type are valid for
setObject()?". So, for example, if you called "setObject(column,
Boolean.TRUE, Types.SMALLINT)", that should work - you're explicitly
asking the driver to represent a Boolean as a SMALLINT. (As noted, the
mapping's not standard, so you may not get exactly the same as other
databases).

However, if you just call setBoolean() in a context where the database
is expecting a SMALLINT, then it's not going to work - how does the
driver know, in the general case, that it should apply a conversion
there? (Consider fun cases where the parameter isn't just directly
mapped to a column, it's part of an expression, etc).

The driver could in theory ask the backend to always infer a type for
positional parameters, then apply its own conversions, but this requires
an extra round trip per query and would actually break other cases where
valid queries with correct types would suddenly start complaining about
being unable to infer a type. And anyway, it's not the right place for
it - the backend already has a large set of type conversion
functionality, why are we suddenly trying to duplicate that in the driver?

You can think of setBoolean(column, boolValue) as essentially the same
as setObject(column, Boolean.valueOf(boolValue), Types.BOOLEAN) - if you
look at it that way, there's obviously no conversion required, because
the type you passed is already a BOOLEAN as you requested ..

I would suggest that the OP either:

* use a real boolean column in their schema (I mean, the data you're
representing IS a boolean value in this case, right?); or
* use setObject(column, value, Types.SMALLINT) instead of the implied
setBoolean() call when setting the column (this may be tricky to do
since you have a layer between you and the driver, but that's not really
the driver's fault..); or
* add a suitable implicit cast on the backend side from boolean ->
smallint (not sure if this works, haven't looked at the details)

-O

On Mon, Sep 27, 2010 at 1:43 PM, Oliver Jowett <oliver@opencloud.com> wrote:

I would suggest that the OP either:

* use a real boolean column in their schema (I mean, the data you're
representing IS a boolean value in this case, right?); or
* use setObject(column, value, Types.SMALLINT) instead of the implied
setBoolean() call when setting the column (this may be tricky to do
since you have a layer between you and the driver, but that's not really
the driver's fault..); or
* add a suitable implicit cast on the backend side from boolean ->
smallint (not sure if this works, haven't looked at the details)

I would actually suggest running your problem past the hibernate community.
It seems unlikely that you're the first team to encounter this problem.
How do you have the property mapped in the hibernate mapping? Is it
possible that explicitly setting the hibernate type of the property in the
mapping will solve your problem?

On 28/09/10 07:01, Samuel Gendler wrote:

I would actually suggest running your problem past the hibernate
community. It seems unlikely that you're the first team to encounter
this problem. How do you have the property mapped in the hibernate
mapping? Is it possible that explicitly setting the hibernate type of
the property in the mapping will solve your problem?

There's way too much guesswork happening here.

OP ("stagirus"), please supply *code*, *stack traces* and *exact error
messages*.

Please show at least one mapping that you are having problems with - the
Java class with annotations, or if you're using XML mapping the
associated XML configuration. Show the stack trace that results from the
failure, and what appears in the PostgreSQL error logs.

Even better, produce buildable source code that demonstrates your
problem without depending on other parts of your application. If you
don't use Hibernate's DDL generation to populate your database, include
a SQL script containing you would use to populate the database too. This
should be a twenty minute job for any competent developer on your team.

That way, instead of lots of hand-waving and guesswork, it'll be
possible to discuss concrete problems and possible solutions.

You should also specify:

- Your Hibernate version
- How you're mapping your entity classes
- Your PostgreSQL JDBC driver versions
- What dialect, if any, is configured for Hibernate
- Your JDK version
- Which version of PostgreSQL you are targeting

See:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

Personally, I suspect you're overriding Hibernate's DDL generation but
not its type mappings, so the PostgreSQL dialect can't do it's job. But
without actual code, it's nigh-on impossible to tell.

--
Craig Ringer

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