JPA (Hibernate) Map String for postgres XML datatype
I'm trying to set up the following to work: Wildfly 8.1.0 (ships with Hibernate as a JPA implementation) Postgres Database 9.3
I want to match a Java string in a colum of type postgres xml, but I am getting an error as the column is of type "xml" and the expression is of type varchar.
Most (possibly) solutions I have found are where specific java classes for the database, hibernate specific workarounds, or just unanswered questions on Stackoverflow.
I want to use certain JPA Techniques to be able to switch to Mysql or SQLServer as I see fit, even replace Widlfly + hibernate with glassfish or tomcat + openJPA without touching the code.
Tweaking the database, however, would be fine.
I've of course done some research already and found this post: http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html
so I tried adding implicit casts for text and varchar:
create cast (varchar as xml) without function as implicit;
create cast (text as xml) without function as implicit;
but postgres just says that these casts already exist. I tested this by inserting random rows into the xml column which worked fine.
The exception is still happening.
in advance for tips and directions,
Billdoor
UPDATE:
I am using postgres 9.3.1102.jdbc41
hibernation operators:
[PreparedStatement] setLong(3, 20)
[PreparedStatement] setNull(2, -3)
[PreparedStatement] setNull(1, 12)
[Connection] prepareStatement(insert into testTable (xml, stuff, id) values (?, ?, ?))
fyi: stuff and xml are NULL, ID is primaryKey, and 20 is fetched from the sequence correctly.
source to share
After a recent discussion about pgsql hackers, I found out that there is an easier way to deal with this. In the JDBC url or property map passed to the driver at connection time, add:
stringtype=unspecified
This will cause PgJDBC to report the values assigned with setString(...)
as being of type unknown
to PostgreSQL, which will then infer that, since the target is the xml
string, it should be treated as having a type xml
.
source to share