Search code examples
javajdbcresultset

Why doesn't get() in ResultSet throw exception when 'getting' wrong type of data?

Say I have table cities with 2 columns. One is ID (integer primary key), other is NAME (text not null). When I do something like:

resultSet = statement.executeQuery("select * from cities");
while (resultSet.next()){
System.out.println("ID and Name: " + resultSet.getInt(1) + "," + resultSet.getString(2));
}

I get nice result of:

ID and Name: 1,london
ID and Name: 2,madrid
ID and Name: 3,paris

But! Watch what happens when I change print statement to this. Notice how get() methods have incorrect data type from actual data in database.

System.out.println("ID and Name: " + resultSet.getString(1) + "," + resultSet.getInt(2));

What surprised me is that program didn't crash. Instead it got:

ID and Name: 1,0
ID and Name: 2,0
ID and Name: 3,0

Why is it designed this way? Shouldn't I get some sort of castException? And why this result? Also how it can convert primary key to String (when I didn't do any explicit casting)?

Solution

  • As documented in the ResultSet javadoc:

    For the getter methods, a JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value. The JDBC specification has a table showing the allowable mappings from SQL types to Java types that can be used by the ResultSet getter methods.

    So, being able to perform a getString(..) on an integer column is perfectly acceptable, and the driver will do the conversion for you (eg using String.valueOf(int) or something similar).

    The JDBC 4.3 specification, appendix B, contains tables of mappings that the JDBC specification expects drivers to support, but be aware that some drivers might support more conversions than listed (and - unfortunately - some do not support all conversions listed).

    The use of getInt(..) on a string-type column (eg VARCHAR) is also allowed, but comes with more limitations: the value must be parseable to an int. The example you show seems to be from a non-compliant driver that converts non-parseable values to 0 instead of throwing an exception.

    On this, the JDBC 1.20 specification says:

    If you attempt an illegal conversion, or if a data conversion fails (for example if you did a getInt on a SQL VARCHAR value of “foo”), then a SQLException will be raised.

    Although this is no longer explicitly mentioned in recent JDBC specifications, it is still expected behaviour. You should report a bug to the maintainer of your JDBC driver on this.