JPA(Hibernate) Map String to postgres XML datatype

Question

I'm trying to get the following setup to work: Wildfly 8.1.0 (comes with Hibernate as JPA implementation) Postgres 9.3 database

I want to map a Java String to a colum of postgres' xml-type, but i get the error thet the column is of type "xml" while the expression is of type varchar.

Most of the (maybe) solutions i found where database specific java classes, hibernate specific workarounds or just unanswered questions on Stackoverflow.

I want to use the defined JPA-Methods to be able to switch to Mysql or SQLServer at will, even replace Widlfly+hibernate with glassfish or tomcat+openJPA without touching the code.

Tweaking something in the Database however would be fine.

I of course already did some research 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 verified this by inserting random Strings into the xml column, which worked fine.

The Exception still occurs.

thanks in advance for hints and directions,

BillDoor

UPDATE:

I use postgres 9.3.1102.jdbc41

the statements hibernate issues:

[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 nullable, ID is primaryKey and the 20 is retrieved from a sequence correctly.

Answer 1

After recent discussion on pgsql-hackers I've found out that there's an easier way to deal with this. In your JDBC URL, or in the properties map passed to the driver at connect-time, add:

stringtype=unspecified

This will cause PgJDBC to report values assigned with setString(...) as being of unknown type to PostgreSQL, which will then infer that since the destination field is xml, the string should be treated as being of type xml.