PreparedStatement.setString for int / numeric columns in PostgreSQL JDBC
I'm working with some legacy code that does database operations in a generic way so that a user / developer can work with a different database by only modifying the JDBC driver.
I have a problem with the 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 the message: "cannot cast string to int ..."
Can't be used PreparedStatement.setString()
to set values that the database expects to be numeric? Should I expect the JDBC driver to automatically convert Java types to database types?
This test runs with other databases including H2 and MySQL. Does a bug in PostgreSQL give a bug in the JDBC driver? Is it possible for this case to work without changing the code?
source to share
The documentation for java.sql.PreparedStatement
has the following:
Note. The setter methods (setShort, setString, etc.) to set IN parameter values must specify types that are compatible with the specified SQL type of the input parameter. For example, if the IN parameter is of type SQL INTEGER, then the setInt method should be used.
Whether the particular database or JDBC driver you are careless about is its own business, but you fell short of expecting all drivers to allow such a retirement, even if some of them do.
source to share