7
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

Gopinath
  • 121
  • 1
  • 2
  • 10
  • 1
    Yes, there is a way. How have you tried to implement this logic? What problems have you encountered implementing this logic? – rgettman Mar 21 '16 at 16:52

1 Answers1

13

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());
Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • Instead of using above logic i have changed the data type as Integer from int and setting null if the value is empty. – Gopinath Mar 22 '16 at 09:35
  • Be aware that the first example code contains a concurrency bug if the `pimDataVo`-Object changes between the check and the set. Please use this only if you can assert that no other thread can access the underlying object! (else use a local variable) – Qw3ry Jul 08 '19 at 08:05
  • I am wondering, is there some sort of preparedstatement wrapper that would do this for us? – Tamas Rev Mar 13 '20 at 10:45