Mapping Postgresql JSON Column to a Hibernate Entity Property

Mapping PostgreSQL JSON column to a Hibernate entity property

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.

How to save json Object in postgresql using Hibernate in java?

Create a table in postgres

CREATE TABLE demoJson(
    id SERIAL NOT NULL PRIMARY KEY,
    jsonData JSONB NOT NULL
);

I created a hibernate entity class as DemoJsonEnitity and defined @TypeDefs

@Entity
@Table(name = "demoJson")
@TypeDefs({
    @TypeDef(name = "json", typeClass = JsonStringType.class)
    ,
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class DemoJsonEnitity implements Serializable {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id")
    private int dataId;

    @Type(type = "jsonb")
    @Column(name = "jsonData", columnDefinition = "jsonb")
    private String jsonData;

    public int getDataId() {
        return dataId;
    }

    public void setDataId(int dataId) {
        this.dataId = dataId;
    }

    public String getJsonData() {
        return jsonData;
    }

    public void setJsonData(String jsonData) {
        this.jsonData = jsonData;
    }

}

And thats it. So easy, I used this dependency

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.9.11</version>
</dependency>

To check I tried saving data as

public static void main(String arg[]) {
    try {
        DemoJsonEnitity obj = new DemoJsonEnitity();
        JSONObject jsonObj = new JSONObject();
        Map m1 = new LinkedHashMap(2);
        m1.put("oldValue", "Active");
        m1.put("newValue", "InActive");
        jsonObj.put("status", m1);
        Map m2 = new LinkedHashMap(2);
        m2.put("oldValue", "Test 6");
        m2.put("newValue", "Test 6 updated");
        jsonObj.put("taskDetails", m2);
        obj.setJsonData(jsonObj.toString());
        Session session = null;
        Transaction tx = null;
        try {
            session = sf.openSession();
            tx = session.beginTransaction();
            session.save(obj);
            tx.commit();
        } catch (Exception e) {
            if (tx != null) {
                tx.rollback();
            }
            throw new RuntimeException(e);
        } finally {
            session.close();
        }
    } catch (Exception e) {
        System.out.println("error: " + e.getMessage());
    }
}

Output

How to map a MySQL JSON column to a Java entity property using JPA and Hibernate

I prefer to do this way:

• Creating converter (attribute converter) from Map to String and vice versa.
• Using Map to map mysql JSON column type in domain (entity) class

The code is bellow.

JsonToMapConverted.java

@Converter
public class JsonToMapConverter 
                    implements AttributeConverter<String, Map<String, Object>> 
{
    private static final Logger LOGGER = LoggerFactory.getLogger(JsonToMapConverter.class);

    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Object> convertToDatabaseColumn(String attribute)
    {
        if (attribute == null) {
           return new HashMap<>();
        }
        try
        {
            ObjectMapper objectMapper = new ObjectMapper();
            return objectMapper.readValue(attribute, HashMap.class);
        }
        catch (IOException e) {
            LOGGER.error("Convert error while trying to convert string(JSON) to map data structure.");
        }
        return new HashMap<>();
    }

    @Override
    public String convertToEntityAttribute(Map<String, Object> dbData)
    {
        try
        {
            ObjectMapper objectMapper = new ObjectMapper();
            return objectMapper.writeValueAsString(dbData);
        }
        catch (JsonProcessingException e)
        {
            LOGGER.error("Could not convert map to json string.");
            return null;
        }
    }
}

Part of domain (entity-mapping) class

...

@Column(name = "meta_data", columnDefinition = "json")
@Convert(attributeName = "data", converter = JsonToMapConverter.class)
private Map<String, Object> metaData = new HashMap<>();

...

This solution perfectly works for me.

Writing to JSON column of Postgres database using Spring / JPA

You'll need to either use setObject at the JDBC level, or pass the PgJDBC parameter stringtype=unspecified to allow implicit casts from string types to json etc.

It's a problem with PostgreSQL being too strict about type casting.

How to map a JSON column with H2, JPA, and Hibernate

JSON support was added to H2 after the question was asked, with version 1.4.200 (2019-10-14).

However, you rarely need a JSON data type in a database. JSON essentially is just a potentially very long string, so you can use CLOB which is available on most databases.

You do need the JSON data type if you need an SQL function that operates on them, and then only if the database insists that its JSON functions operate on a JSON type instead of on a CLOB. Such functions tend to be database-dependent though.

---

Related Topics