Correct way to use Java Prepared Statement with Unsigned Integer

Question

I have a foreign key column in my table that holds an unsigned integer value. I want to add a new value to this column, but I don't know how java will deal with this unsigned value, since java doesn't have native unsigned values.

If I do:

preparedStatement.setInt(2, myInt);

Will the Database convert this signed int to it's unsigned value automatically? Or will it throw an error saying that those are incompatible types? Should I step up and use a long like:

preparedStatement.setLong(2, myLong);

Or will this throw an exception as well, because the Database is not using BIGINT?

I am using MySQL and I just want to avoid surprises in the future as my table records grow.

Answer 1

maybe MySQL behaves in one way and SQL Server in another

Leaving aside the fact that SQL Server does not have unsigned integers, your instincts are correct that signed ⇄ unsigned behaviour could very well depend on the implementation of the particular JDBC driver being used. Therefore, the general answer you seek is really "too broad" because it could potentially require a description of implementation-specific details for all of the JDBC drivers whose databases support unsigned integer columns.

So, as suggested in the comments to the question, your best bet for MySQL (or any other particular JDBC driver) would be to

1. see if the JDBC specification itself defines the required behaviour (unlikely),
2. check your JDBC driver documentation for their definitive answer, or
3. test your desired configuration to see if it behaves in a way that will suit your needs.