Search code examples
javajpa

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?

Solution

  • 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: https://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.