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.

Craig Ringer · Accepted Answer · 2014-09-09 08:05:30Z

4

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.

answered Sep 9, 2014 at 8:05

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

4 Comments

billdoor Over a year ago

changed the connectionURL to: jdbc:postgresql://localhost:5432/testDb?stringtype=unspecified - the error remains the same, do i need to put the property somewhere else?

Craig Ringer Over a year ago

@billdoor Can you extract the underlying SQL query from the logs? (You might need to change settings).

Craig Ringer Over a year ago

@billdoor What PgJDBC version are you using?

billdoor Over a year ago

updated both answers in my question, thanks for your effort by the way

Collectives™ on Stack Overflow

JPA(Hibernate) Map String to postgres XML datatype

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related