Alex Kartishev Alex Kartishev - 4 months ago 150x
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);

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(
at org.postgresql.core.v3.QueryExecutorImpl.processResults(
at org.postgresql.core.v3.QueryExecutorImpl.execute(
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(
... 11 more

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


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:

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.