JPA Native Query set to zero

Question

Here is my piece of code:

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 can sometimes be null (an object of class Date). 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 can I make this code work and store a null value in the database?

Answer 1

You are using postgresql (the stack is already talking about this) and probably Hibernate and will almost certainly encounter this problem: PostgreSQL JDBC Null String, taken as bytea

I used this specific solution: https://stackoverflow.com/a/166778/

So this means moving to the Hibernate API so you can specify the type of expression.

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

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

shortValue is of type Short .

Answer 2

if you are not using Hibernate but using EclipseLink, you can use the query. Tip: https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints

Example request:

 String insert = "INSERT INTO mytable VALUES ( ?, ?, ?, ?)"; em = getEntityManager(); em.getTransaction().begin(); Query u = em.createNativeQuery(insert); u.setHint(QueryHints.BIND_PARAMETERS, HintValues.FALSE);//<--the hint u.setParameter(1, "value1"); u.setParameter(2, "value2"); u.setParameter(4, "value4");//just skipped the null element u.executeUpdate(); em.getTransaction().commit(); 

and the result will be an insert:

 mytable column1 column2 column3 column4 value1 value2 value4 

of course if "column3" is null in db ...

I do not know if it also works with Hibernate, but it is possible. I used PostgreSQL for my test.

Answer 3

I ran into the same problem when using EntityManager.createNamedQuery (guess the same problem with createNativeQuery).

In case you are going to pass a nullable parameter for the request then use TypedParameterValue, which allows you to pass a type.

For example:

 setParameter("paramName", new TypedParameterValue(StandardBasicTypes.LONG, paramValue)); 

Here you explicitly indicate the type of value to be transmitted, and when you pass a null value, since the processor knows the exact type.

Answer 4

In my case, using Oracle 12 and jboss 7.3, I decided to use an empty string as the parameter value. I don’t understand why, but it works. This is my code:

 String sql = "insert into customer (id,name,age) values (?1,?2,?3); em = getEntityManager(); Query query = em.createNativeQuery(sql); query.setParameter(1, getId()); query.setParameter(2, getName()); if (getAge() != null) {//getAge() return BigDecimal and map a NUMBER column type query.setParameter(3, getAge()); } else { query.setParameter(3, ""); }