20

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?

Improve this question
7
  • what is your database? can you execute the query directly? Commented Feb 12, 2014 at 15:00
  • Did you check in the database whether the column actually allows NULL? Also, please check whether the field in entity is annotated with @Column(nullable=true)? Commented Feb 12, 2014 at 15:04
  • 1
    PostgreSQL 9.2. Yes, I can do it in such a way directly: insert into table_name (value_one, value_two, value_three) values ('test', 'test', NULL); Commented Feb 12, 2014 at 15:04
  • Also, can you try it with setTimestamp() instead of setParameter()? Commented Feb 12, 2014 at 15:09
  • Unfortunately, javax.persistence.Query doesn't have such a method. May be it is possible to cast it to org.hibernate.Query, that has such a method? Commented Feb 12, 2014 at 15:14

5 Answers 5

Reset to default
21

I have faced the same issue when use EntityManager.createNamedQuery(guess the same issue with createNativeQuery).

In case you are going to pass nullable parameter to Query then use TypedParameterValue which allows to pass the type.

For instance:

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

Here you explicitly set the type of passed value and when you pass null value as processor know the exact type.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks. this helped me for Date field. query.setParameter(6, new TypedParameterValue(StandardBasicTypes.CALENDAR_DATE, null));
This worked for me when the column type was Number
12

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.

Improve this answer

2 Comments

Cannot resolve method 'setParameter(java.lang.String, java.lang.Long, org.hibernate.type.LongType)'
Works with dates too -> ..setParameter("date", date, TemporalType.TIMESTAMP)
6

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

Example query:

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 insertion: 

mytable column1 column2 column3 column4 value1 value2 value4

of course if "column3" is nullable in the db...

I don't know if works also with Hibernate, but it could. I used PostgreSQL for my test.

Improve this answer

Comments

2

In my case, using Oracle 12 and jboss 7.3, I solved using an empty String as param 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, ""); }
Improve this answer

Comments

0

I had similar issue with Double nullable parameter when using javax.persistence.Query native query against Postgresql

The simplest solution I found:

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

Note the type casting inside insert statement.

Alternative option:

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

See: https://stackoverflow.com/a/62321877/13108438

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.