Mapping PostgreSQL JSON column to a Hibernate entity property
hibernate, java, jpa, json, postgresql
Solution
See PgJDBC bug #265.
PostgreSQL is excessively, annoyingly strict about data type conversions. It won't implicitly cast `text` even to text-like values such as `xml` and `json`.
The strictly correct way to solve this problem is to write a custom Hibernate mapping type that uses the JDBC `setObject` method. This can be a fair bit of hassle, so you might just want to make PostgreSQL less strict by creating a weaker cast.
As noted by @markdsievers in the comments and this blog post, the original solution in this answer bypasses JSON validation. So it's not really what you want. It's safer to write:
CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in($1::cstring);
$$ LANGUAGE SQL IMMUTABLE;
CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;
`AS IMPLICIT` tells PostgreSQL it can convert without being explicitly told to, allowing things like this to work:
regress=# CREATE TABLE jsontext(x json);
CREATE TABLE
regress=# PREPARE test(text) AS INSERT INTO jsontext(x) VALUES ($1);
PREPARE
regress=# EXECUTE test('{}')
INSERT 0 1
Thanks to @markdsievers for pointing out the issue.
Problem
I have a table with a column of type JSON in my PostgreSQL DB (9.2). I have a hard time to map this column to a JPA2 Entity field type. I tried to use String but when I save the entity I get an exception that it can't convert character varying to JSON. What is the correct value type to use when dealing with a JSON column? ``` @Entity public class MyEntity { private String jsonPayload; // this maps to a json column public MyEntity() { } } ``` A simple workaround would be to define a text column.