JPA Native Query set null parameter

Question

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?

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)? — Rohit Jain
– Rohit Jain, Commented Feb 12, 2014 at 15:04
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); — Alex Kartishev
– Alex Kartishev, Commented Feb 12, 2014 at 15:04
Also, can you try it with setTimestamp() instead of setParameter()? — Rohit Jain
– Rohit Jain, 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? — Alex Kartishev
– Alex Kartishev, Commented Feb 12, 2014 at 15:14

Suraj Kumar · Accepted Answer · 2019-01-24 09:24:28Z

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.

edited Jan 24, 2019 at 9:24

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

answered Jan 24, 2019 at 9:00

J. Doe

2112 silver badges3 bronze badges

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

2 Comments

Vishal Patel Over a year ago

Thanks. this helped me for Date field. query.setParameter(6, new TypedParameterValue(StandardBasicTypes.CALENDAR_DATE, null));

Prakhar Over a year ago

This worked for me when the column type was Number

Community · Accepted Answer · 2017-05-23 12:17:50Z

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.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Jul 15, 2014 at 5:56

Emmanuel Touzery

9,2533 gold badges69 silver badges86 bronze badges

2 Comments

Pwnstar Over a year ago

Cannot resolve method 'setParameter(java.lang.String, java.lang.Long, org.hibernate.type.LongType)'

Recigio Poffo Over a year ago

Works with dates too -> ..setParameter("date", date, TemporalType.TIMESTAMP)

Matteo · Accepted Answer · 2016-05-31 09:30:53Z

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.

Luigi Masiero · Accepted Answer · 2019-02-01 15:01:27Z

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, "");
}

edited Feb 1, 2019 at 15:01

answered Feb 1, 2019 at 14:29

Luigi Masiero

213 bronze badges

Comments

kgr · Accepted Answer · 2023-12-08 12:15:24Z

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

edited Dec 8, 2023 at 12:15

answered Dec 8, 2023 at 10:52

kgr

211 silver badge5 bronze badges

Collectives™ on Stack Overflow

JPA Native Query set null parameter

5 Answers 5

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related