How can I handle null values in an INSERT with JPA?

Question

I'm trying to execute an insert operation to an Oracle DB Table from Java with JPA.
The following is my code:

String coCode;
String coDescription;

/* some operations on data ... */

String queryStatement = 
  "insert into MANAGER_DATA (CO_CODE, CO_DESCRIPTION) values (?1, ?2)";

Query updateQuery = getEntityManager(context).createNativeQuery(queryStatement);

updateQuery.setParameter(1, coCode);
updateQuery.setParameter(2, coDescription.compareTo("") == 0 ? null : coDescription);

updateQuery.executeUpdate();

The problem is that coDescription can be null in case of an empty String and, in that case, I want the null value to be added in the table as well (coCode is primary key but coDescription is nullable). Apparently I cannot pass a null value to the setParameter function directly, as I am doing above.

How can I solve this and allow the null value to be added to my DB table?

ASFAIK It is possible to pass null values as second argument in `setParameter` method since Hibernate 5 (I tested it on 5.0.2), try upgrade your dependencies it should solve your problem. — Kamil W, Oct 25 '18 at 13:36
Why would you use JPA for doing basic inserts? you could have a class that maps to that table and insert an object if using JPA was so critical to you ... — , Oct 25 '18 at 13:56

score 0 · Answer 1 · answered Oct 25 '18 at 13:46

Oracle evaluates empty strings as null.

For example:

insert into MANAGER_DATA (CO_CODE, CO_DESCRIPTION) values ('my_code', '')

will insert this row: ('my_code', null).

Therefore you can leave it empty string.

score 0 · Accepted Answer · answered Oct 25 '18 at 13:47

Looks like you might be using old Hibernate version affected by HHH-9165 createNativeQuery's setParameter does not support null bug. Upgrade to Hibernate 5.0.2 or newer to resolve it. As per mentioned Jira:

Just tested it with Hibernate 5.0.2 and the bug seems to be resolved.

How can I handle null values in an INSERT with JPA?

2 Answers2