1

MySql column type smallint, it is mapped to java type java.lang.Integer. Should it be java.lang.Short? 

resultSet.getObject(index, Short.class)

java.sql.SQLException: Conversion not supported for type java.lang.Short

Why JDBC does not support such basic conversion?

Improve this question
2

1 Answer 1

Reset to default
2

JDBC is only a set of interfaces and requirements, the behavior must be explicitly implemented in a driver (in your case, MySQL Connector/J). For getObject(int/String, Class), the API specifies:

Retrieves the value of the designated column in the current row of this ResultSet object and will convert from the SQL type of the column to the requested Java data type, if the conversion is supported. If the conversion is not supported or null is specified for the type, a SQLException is thrown.

At a minimum, an implementation must support the conversions defined in Appendix B, Table B-3 and conversion of appropriate user defined SQL types to a Java type which implements SQLData, or Struct. Additional conversions may be supported and are vendor defined.

Table B-3 specifies the following conversion for smallint:

SMALLINT | Integer

In other words, the behavior of the MySQL Connector/J driver complies with the JDBC specification, although MySQL would be allowed to support more extensive conversions for getObject(String/int, Class).

As far as I'm aware, a lot of JDBC drivers do support more extensive conversions, and will also support the reverse of the mapping specified for setObject in table B-4:

Short | SMALLINT

This mapping means that setObject(int, <Short value>) should set SQL type SMALLINT for the parameter (for databases/drivers that support dynamic (or need explicit) typing of the parameter value).

And, some also support the additional setObject conversions from B-5 for getObject:

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

The disconnect between getObject using Integer and setObject supporting Short (and others) is largely historical. Until JDBC 4.1 (Java 7), there was only one mapping for getObject (there was no Class parameter), and the initial version of JDBC mapped all integral types except BIGINT to java.lang.Integer. This is explicitly mentioned in the specification:

Note – The JDBC 1.0 specification defined the Java object mapping for the SMALLINT and TINYINT JDBC types to be Integer. The Java language did not include the Byte and Short data types when the JDBC 1.0 specification was finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to preserve backwards compatibility.

So requiring getObject(String/int, Class) to at least support java.lang.Integer for smallint retains the backwards compatibility and the symmetry with getObject(String/int) returning a java.lang.Integer for a smallint. Not requiring (but allowing) the full set of conversions that are mandated for setObject makes the addition/transition easier for driver implementations.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Basically can not depend on JDBC doing conversion.
Is there a way to check if a conversion to a java type is supported?
@Sunnyday No, but in this case, if you really need a short why not just use getShort?
getShort(...) will convert any Number to short? Actually the java type is dynamic in our case.
@Sunnyday Yes, see table B-6 in the specification, however exact behavior can vary, some drivers will throw an exception if the value is out of range, others will register a warning and apply the same conversion you get when casting.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.