Обсуждение: Mapping Hibernate boolean to smallint(Postgresql)
Good afternoon. I am the Chief Architect, with 19 years of experience in the software industry, for an enterprise software product. I am requesting your help to better understand or resolve the following issue. Our architecture is based on Spring and Hibernate. The product is required to support multiple databases (RDBMS neutral). Our product extensively utilizes "boolean" fields. In the database (DDL), we store these boolean fields into SmallInt, ANSI SQL data type for maximum portability. Hibernate maps these Java boolean types to DB SmallInt fields. 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). I understand you have your own reasons for this but this is a real architectural scenario anyone should expect at this age of RDBMS. I am new to PostgresQL but I also heard great things about PostgresQL. Your recent improvements towards High Availability (HA) especially Streaming Replication seems to be an impression direction. But it amazes me why such simple/flexible feature was not supported yet. I would greatly appreciate your insights. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2853280p2853280.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
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.
