PreparedStatement.setString on int/numeric columns in PostgreSQL JDBC

Question

I am working with some legacy code that performs database operations in a generic way, so that the User/developer can work with a different database by changing only the JDBC driver.

I have a problem with PostgreSQL JDBC driver. My test case:

 //ddl
 CREATE TABLE test
 (
    id numeric,
    name text,
 )

 //java code
 String sqlCmd = "INSERT INTO test values (?, ?)";

 PreparedStatement ps = connection.prepareStatement( sqlCmd );
 ps.setString( 1, "1" );
 ps.setString( 1, "name1" );
 ps.executeUpdate();

With Postgres, the result of this case is an exception with message: "can't cast string to int..."

Is it inappropriate to use PreparedStatement.setString() to set values that database expects to be numeric? Should I expect the JDBC driver to automatically convert java types to database types?

This test passes with other databases, including H2 and MySQL. Does the failure with PostgreSQL reflect a bug in the JDBC driver? Is it possible to make this case work without changing code?

Answer 1

The documentation for java.sql.PreparedStatement has this to say:

Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.

Whether a particular database or JDBC driver allows you to be sloppy about that is its own affair, but you are not justified in expecting that all drivers will allow such slop, even if certain ones do.