PostgreSQL, JSON and Hibernate

December 08, 2016

NoSQL databases are hip these days. But good old relational databases can also be quite nice and even nicer databases systems like PostgreSQL have super-nice features, like JSON support, effectively allowing to store documents NoSQL-like without losing the advantages of relational databases.

In general, it is no problem to store JSON documents as plain text strings in databases. However, the functionality of the database management system, is restricted to operations based on strings. However, for some time now, PostgreSQL (and others, too) support JSON data types which allow more concise operations based on the JSON structure of a stored document, for example checking a property of a stored JSON document.

JPA itself does not support JSON types directly, but Hibernate allows to extend the type system with own types. This way, one can use the capabilities of the database management system with regard to JSON documents, and have the OR-mapper translate a JSON document into a Java object.

In the following, we're going to explain how to do the mapping part.

Goal

The idea is to have a database entity and let Hibernate do the translation between the object and the JSON representation which is actually stored. Like this:

import org.hibernate.annotations.Type;

@Entity
public final class MyDbClass extends AbstractEntity {
    @Type(type = JSONMAP_TYPE)
    private Map<String, Object> properties = new HashMap<>();
}

We have to define the JSONMAP_TYPE of course. A good place to do this, is the entity super-class:

@Access(AccessType.FIELD)
@MappedSuperclass
public abstract class AbstractEntity implements Serializable {
    public static final String JSONMAP_TYPE = "de.bytethefrog.hibernate.usertype.JsonMapUserType";
}

Custom Types

The main task is to implement a custom org.hibernate.usertype.UserType. This type is the connection between the database and Java objects.

import de.bytethefrog.hibernate.util.JsonHelper;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.internal.util.compare.EqualsHelper;
import org.hibernate.usertype.UserType;
import org.postgresql.util.PGobject;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

public class JsonMapUserType implements UserType {
    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }

    @Override
    public Class returnedClass() {
        return Map.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return EqualsHelper.equals(x, y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        String json = rs.getString(names[0]);

        if (StringUtils.isNotBlank(json)) {
            return JsonHelper.fromJson(json, Map.class);
        }

        return null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        if (value != null) {
            if (value instanceof Map && !((Map) value).isEmpty()) {
                String json = JsonHelper.toJson(value);
                PGobject pGobject = new PGobject();
                pGobject.setType("json");
                pGobject.setValue(json);
                st.setObject(index, pGobject, Types.OTHER);
                return;
            }
        }

        st.setObject(index, null);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value == null ? null : new HashMap((Map) value);
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) deepCopy(value);
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return deepCopy(cached);
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original);
    }
}

JSON Converter

The code above uses a small helper class with static methods in order to convert from and to JSON:

import com.fasterxml.jackson.annotation.JsonAutoDetect;
import com.fasterxml.jackson.annotation.PropertyAccessor;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;

public class JsonHelper {
    private static final Logger LOGGER = LoggerFactory.getLogger(JsonHelper.class);
    private static final ObjectMapper MAPPER = new ObjectMapper();

    static {
        MAPPER.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.NONE);
        MAPPER.setVisibility(PropertyAccessor.FIELD, JsonAutoDetect.Visibility.ANY);
    }

    private JsonHelper() {
        // hide constructor
    }

    public static String toJson(Object object) {
        try {
            return MAPPER.writeValueAsString(object);
        } catch (JsonProcessingException e) {
            LOGGER.error("toJson: could not convert object to json string", e);
            return null;
        }
    }

    public static <T> T fromJson(String jsonString, Class<T> clazz) {
        try {
            return MAPPER.readValue(jsonString, clazz);
        } catch (IOException e) {
            LOGGER.error("fromJson: could not convert json string to object", e);
            return null;
        }
    }
}

Hibernate Dialects

One last thing we need to do, in order to be able to use JSON type fields, is to provide a org.hibernate.dialect.Dialect and configure hibernate to use it:

import org.hibernate.dialect.PostgreSQL9Dialect;
import java.sql.Types;

public class JsonPostgreSQLDialect extends PostgreSQL9Dialect {
    public JsonPostgreSQLDialect() {
        this.registerColumnType(Types.JAVA_OBJECT, "json");
    }
}

Configuration

Finally, we have to set the the JsonPostgreSQLDialect. In Spring Boot, its enough to configure it in the application.properties, like this:

spring.jpa.database-platform=de.bytethefrog.hibernate.dialect.JsonPostgreSQLDialect

If you are using Hibernate directly, you have to add it to your hibernate.properties.