Alex Kartishev Alex Kartishev - 6 months ago 264
Java Question

JPA Native Query set null parameter

Here is my code part:

Query q = em.createNativeQuery("insert into table_name (value_one, value_two, value_three) values (?,?,?)");
q.setParameter(1, value1);
q.setParameter(2, value2);
q.setParameter(3, value3);
q.executeUpdate();


value3
sometimes can be null (Date class object). And if it is null the following exception is thrown:

Caused by: org.postgresql.util.PSQLException: ERROR: column "value_three" is of type timestamp without time zone but expression is of type bytea
Hint: You will need to rewrite or cast the expression.
Position: 88
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:189)
... 11 more


How is it possible to get this code working and to persist null value into database?

Answer

You are using postgresql (already the stack is telling that), and likely Hibernate, and almost certainly hitting this problem: PostgreSQL JDBC Null String taken as a bytea

I used this particular solution: http://stackoverflow.com/a/23501509/516188

So that means escaping to the Hibernate API so you can give the type of the expression.

In my case it was a nullable Short so I used:

.setParameter("short", shortValue, ShortType.INSTANCE);

shortValue being of type Short.