[BUGS] 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
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. 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
