Menu
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?
520
1. Define JPA native queries. Named SQL queries are defined using the @NamedNativeQuery annotation. This annotation may be placed on any entity and defines the name of the query as well as the query text. Like JPQL named queries, the name of the query must be unique within the persistence unit. Named SQL native queries are defined like this:
But the use of named parameter bindings for native queries is not defined by the JPA specification. Positional parameters are referenced as “ ?” in your native Query and their numbering starts at 1. Query q = em.createNativeQuery ("SELECT a.firstname, a.lastname FROM Author a WHERE a.id = ?");
JPQL and native SQL queries use the same Query interface which provides a setParameter method for positional and named parameter bindings. But the use of named parameter bindings for native queries is not defined by the JPA specification. Positional parameters are referenced as “ ?” in your native Query and their numbering starts at 1.
The JPA setParameter Query method is very useful for basic entity properties that can be mapped using the default Hibernate ORM types.
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.
125
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.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
