Workaround for "null primitives" in JDBC PreparedStatement?

Question

When using raw JDBC, you can parameterize a PreparedStatement like so:

PreparedStatement statement = connection.prepareStatement(someSQLString);
String someString = getSomeString();
Integer int = getSomeInteger();
statement.setString(1, someString);
statement.setLong(2, 5L);
statement.setInt(3, int);

...

Here, if someString is null, that's fine - strings are nullable. But if getSomeInteger() returns null, we have a problem.

PreparedStatement#setInt(int,int) sets a primitive int as the value, and therefore cannot be null.

However, it's perfectly plausible that I might want the value of the 3rd column above to be null for this particular record. After all, every RDBMS I've ever worked with allows numeric (INT, LONG, etc.) fields to be NULLABLE...

So what's the workaround?

score 29 · Accepted Answer · answered Jul 15 '13 at 14:49

29

Don't use any of those and use setObject instead, let the JDBC driver to manage the null values instead of you.

answered Jul 15 '13 at 14:49

Luiggi Mendoza

85,076
16
154
332

1

Thanks @Luiggi Mendoza (+1) - can you confirm that `setObject` handles all types, and handles when any type is NULL? Basically, does it cover all scenarios? Any caveats or pitfalls to watch out for? Thanks again! – IAmYourFaja Jul 15 '13 at 14:54
2

@IAmYourFaja this will depend on the JDBC driver. Note the last part of the `setObject` documentation: ***Note:** Not all databases allow for a non-typed Null to be sent to the backend. For maximum portability, the setNull or the setObject(int parameterIndex, Object x, int sqlType) method should be used instead of setObject(int parameterIndex, Object x)*. I recommend doing some tests and then use the best method for your case. – Luiggi Mendoza Jul 15 '13 at 14:55
OK - thanks again @Luiggi Mendoza (+1) - last followup: would `setObject(int,Object,int)` cover all types, null and non-null alike? It sound like that's the one I want...in other words: am I guaranteed that this method correctly handles all types and nulls in all/most JDBC drivers? – IAmYourFaja Jul 15 '13 at 14:59
@IAmYourFaja again, this depends on the JDBC driver. Do some tests (because you never stated which JDBC driver you use) and then decide if you should use `setObject` or `setNull` instead. – Luiggi Mendoza Jul 15 '13 at 14:59
1

if `setObject` is more suitable I don't understand why we let `setTYPE` methods in PreparedStatement. Should we expect them to be deprecated in the near future ? – soung Dec 15 '20 at 23:05
@soung those methods let you add primitives. In Java, primitive types must have a value, they can't be `null`. Thus you should use `setObject` or `setNull` – Luiggi Mendoza Dec 16 '20 at 01:19

score 17 · Answer 2 · answered Jul 15 '13 at 14:47

17

You can use the setNull(int parameterIndex, int sqlType) method of the PreparedStatement class.

Here's an example of how to use it:

  String query = 
     "insert into nullable_table(id,string_column, int_column) values(?, ?, ?)";

  // create PrepareStatement object
  PreparedStatement pstmt = connection.prepareStatement(query);
  pstmt.setString(1, id);
  pstmt.setNull(2, java.sql.Types.VARCHAR);
  pstmt.setNull(3, java.sql.Types.INTEGER);

Example taken from here.

answered Jul 15 '13 at 14:47

Konstantin Yovkov

62,134
8
100
147

Thanks @kocko (+1) - please read my question underneath Luiggi's answer - I have the same question for you! Thanks again! – IAmYourFaja Jul 15 '13 at 14:55
Thanks! I was googling this for 15 minutes and your post was the clearest and most concise! – user952342 Sep 20 '17 at 19:59
What is the correct SQL type for long? – Alex Dec 01 '21 at 14:16

score 3 · Answer 3 · answered Jul 15 '13 at 14:46

3

You need to use setNull() method. Based on parameter is null or not check you need to either call setNull() (or) setInt().

answered Jul 15 '13 at 14:46

kosa

65,990
13
130
167

Thanks @Nambari (+1) - please read my question underneath Luiggi's answer - I have the same question for you! Thanks again! – IAmYourFaja Jul 15 '13 at 14:56
1

@IAmYourFaja: As Luggin commented, if you use setObject, you are sticking to database driver implementation, if other doesn't support you will end up changing your code. If I have to decide, I wouldn't go with setObject() approach (assuming we don't know which databases we will use as we go). I would be happy to add few lines of code. – kosa Jul 15 '13 at 15:01

Workaround for "null primitives" in JDBC PreparedStatement?

3 Answers3

Linked

Related