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

Craig Ringer wrote:

> It looks like "stagirus" may be right about the JDBC spec requiring
> conversion between all numeric types, and considering "boolean" to be a
> numeric special case. Page 217 of the JDBC4 spec (see
> http://java.sun.com/products/jdbc/download.html#corespec40) contains a
> table that suggests that setting a Java 'boolean' to a database
> 'smallint' (and vice versa) should be valid and permitted.

This is not correct. I covered this in a previous mail. This table
(table B-5) is specifically about the conversions that setObject() will
do on require. See page 192 at the start of appendix B:

> TABLE B-5 Conversions by setObject and setNull from Java Object Types to JDBC
> Types
> This table shows which JDBC types may be specified as the target JDBC type to the
> methods PreparedStatement.setObject, PreparedStatement.setNull, RowSet.setNull
> and RowSet.setObject.

Section 13.2.2.1, page 100:

> The data type specified in a PreparedStatement setter method is a data type in
> the Java programming language. The JDBC driver is responsible for mapping this to
> the corresponding JDBC type (one of the SQL types defined in java.sql.Types) so
> that it is the appropriate type to be sent to the data source. The default mapping is
> specified in Appendix B TABLE B-2.

(Table B-2 specifies that a Java boolean is mapped to JDBC datatypes BIT
or BOOLEAN)

And section 13.2.2.3, page 101:

> The method setObject can be used to convert an object in the Java programming
> language to a JDBC type.
> The conversion is explicit when setObject is passed a Java Object and a JDBC
> data type. [...]
> If setObject is called without a type parameter, the Java Object is implicitly
> mapped using the default mapping for that object type. [...]
> The default mapping is described in Appendix B TABLE B-4

(Table B-4 specifies that a Java boolean is mapped to JDBC datatypes BIT
or BOOLEAN)

So, in summary: If you explicitly ask for a Java boolean to be mapped to
a JDBC SMALLINT, by passing Types.SMALLINT to setObject(), then that
should be supported and the driver will do the conversion for you; if
that doesn't work, that's a bug! However, if you don't specify a type
when calling setObject() or setBoolean(), it is mapped to BOOLEAN. If
BOOLEAN is then not a suitable type for the actual query you're trying
to run, then you'll get a SQL error, but that's not the driver's fault,
it just did what you asked!

So the driver is working just fine here - the problem is that the
application (via Hibernate) is expecting that a BOOLEAN value should be
implicitly cast to a SMALLINT, which is really nonportable behaviour
which only happens to work with Oracle because Oracle doesn't have a SQL
boolean type in the first place and so the app is using SMALLINT in its
DDL ..

-O