PostgreSQL JDBC Null String taken as a bytea
Solution 1
There's a simple fix: when you're building the query string, if a parameter is going to be null -- use "null" instead of "?".
As @araqnid says, Hibernate is incorrectly casting null values into type bytea because it doesn't know any better.
Solution 2
Since you're calling setParameter(int,Object)
with a null value, at a guess the entity manager has no idea which persistence type to use to bind the parameter. Istr Hibernate having a predilection for using SerializableType, which would equate to a bytea.
Can you use the setParameter method that takes a Parameter object instead, so you can specify the type? I haven't really used JPA, so can't give a detailed pointer.
(IMHO this is your just desserts for abusing EntityManager's native-sql query interface to do inserts, rather than actually mapping the entity, or just dropping through to JDBC)
Solution 3
Using Hibernate specific Session API works as a workaround:
String sql = "INSERT INTO person (id, name) VALUES (:id, :name)";
Session session = em.unwrap(Session.class);
SQLQuery insert = session.createSQLQuery(sql);
sql.setInteger("id", 123);
sql.setString("name", null);
insert.executeUpdate();
I've also filed HHH-9165 to report this issue, if it is indeed a bug.
Solution 4
Most of above answers are meaningful and helped me to narrow down issue.
I fixed issue for nulls like this:
setParameter(8, getDate(), TemporalType.DATE);
Solution 5
You can cast the parameter to a proper type in the query. I think this solution should work for other databases, too.
Before:
@Query("SELECT person FROM people person"
+ " WHERE (?1 IS NULL OR person.name = ?1)")
List<Person> getPeopleWithName(final String name);
After:
@Query("SELECT person FROM people person"
+ " WHERE (?1 IS NULL OR person.name = cast(?1 as string))")
List<Person> getPeopleWithName(final String name);
Also works for LIKE
statements:
person.name LIKE concat('%', cast(?1 as string), '%')
and with numbers (here of type Double
):
person.height >= cast(cast(?1 as string) as double)
laidlook
Updated on July 05, 2022Comments
-
laidlook almost 2 years
If entity.getHistory() is null following code snippet:
(getEntityManager() returns spring injected EntityManager, database field history type is: text or varchar2(2000)
Query query = getEntityManager().createNativeQuery("insert into table_name(..., history, ....) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") [...] .setParameter(6, entity.getHistory()) [...] query.executeUpdate();
Gives strange exception:
17/11/11 06:26:09:009 [pool-2-thread-1] WARN util.JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804 17/11/11 06:26:09:009 [pool-2-thread-1] ERROR util.JDBCExceptionReporter:101 - ERROR: **column "history" is of type text but expression is of type bytea**
Hint: You will need to rewrite or cast the expression.
Problem occurred only in this configuration:
OS: CentOS release 5.6 (Final)
Java: 1.6.0_26
DB: PostgreSQL 8.1
JDBC Driver: postgresql-9.1-901.jdbc4
Application Server: apache-tomcat-6.0.28Everything is working fine on few other configurations or when history is empty string. Same statement executed from pgAdmin works fine.
I guess problem is in PostgreSQL JDBC driver, is there some sensible reason to treat null string as a bytea value? Maybe some strange changes between Postgres 8 and 9?
-
Emmanuel Touzery almost 10 yearsnote I had this problem with a
Short
and there you can't usesetShort()
because it takes a non-nullableshort
. With hibernate 4.x the solution is to do:.setParameter("short", ShortValue, ShortType.INSTANCE);
(with 3.x I think it'sHibernate.SHORT
instead of using theShortType
). -
andrej about 6 yearsWas trying to escape JPA into hibernate for hours. Thanks!
-
Humppakäräjät over 4 yearsFor me .setParameter("yearLow",yearLow, IntegerType.INSTANCE) did the trick.
-
cdalxndr over 2 yearsNew issue for
update
statements: hibernate.atlassian.net/browse/HHH-14778 -
LisekKL almost 2 yearsThis doesn't work for me - when the date is null I get
expected type [null] passed with TemporalType; expecting Date or Calendar