how to set null in prepared statement if the value is zero [duplicate]

Question

preparedStatement.setInt(1, pimDataVo.getItemFlashMessageId());
preparedStatement.setInt(2, pimDataVo.getErpImprintCodeId());
preparedStatement.setInt(3, pimDataVo.getPublisherCodeId());
preparedStatement.setInt(4, pimDataVo.getGlClassId());

Is there any way to set these values null, if the get values are zero.?? All are Number columns

Answer 1

Yes, you need to use setNull method. So, in your case it would be:

if (pimDataVo.getItemFlashMessageId() != 0) {
    preparedStatement.setInt(1, pimDataVo.getItemFlashMessageId());
} else {
    // use setNull
    preparedStatement.setNull(1, java.sql.Types.INTEGER);
}

And you use a similar approach for the other values. You could also write a HELPER CLASS to perform this if for you (so you don't repeat a lot of code). Something like this:

public static void setIntOrNull(PreparedStatement pstmt, int column, int value)
{
    if (value != 0) {
        pstmt.setInt(column, value);
    } else {
        pstmt.setNull(column, java.sql.Types.INTEGER);
    }
}

Then you use your code like this:

Helper.setIntOrNull(preparedStatement, 1, pimDataVo.getItemFlashMessageId());
Helper.setIntOrNull(preparedStatement, 2, pimDataVo.getErpImprintCodeId());
Helper.setIntOrNull(preparedStatement, 3, pimDataVo.getPublisherCodeId());
Helper.setIntOrNull(preparedStatement, 4, pimDataVo.getGlClassId());