How to use Postgres JSONB datatype with JPA?

Question

Im not finding a way to map the JSON and JSONB datatypes from PostgreSQL using JPA (EclipseLink). Is some one using this datatypes with JPA and can give me some working examples?

Stepper

There is an example somewhere on SO, let me see if i can find... – Xochitlxp

Answer 1

All the answers helped me to reach the final solution that is ready for JPA and not EclipseLink or Hibernate specifically.

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.IOException;
import javax.json.Json;
import javax.json.JsonObject;
import javax.persistence.Converter;
import org.postgresql.util.PGobject;

@Converter(autoApply = true)
public class JsonConverter implements javax.persistence.AttributeConverter<JsonObject, Object> {

  private static final long serialVersionUID = 1L;
  private static ObjectMapper mapper = new ObjectMapper();

  @Override
  public Object convertToDatabaseColumn(JsonObject objectValue) {
    try {
      PGobject out = new PGobject();
      out.setType("json");
      out.setValue(objectValue.toString());
      return out;
    } catch (Exception e) {
      throw new IllegalArgumentException("Unable to serialize to json field ", e);
    }
  }

  @Override
  public JsonObject convertToEntityAttribute(Object dataValue) {
    try {
      if (dataValue instanceof PGobject && ((PGobject) dataValue).getType().equals("json")) {
        return mapper.reader(new TypeReference<JsonObject>() {
        }).readValue(((PGobject) dataValue).getValue());
      }
      return Json.createObjectBuilder().build();
    } catch (IOException e) {
      throw new IllegalArgumentException("Unable to deserialize to json field ", e);
    }
  }
}

Stepper

I have tried something like this for Hibernate, but it seems AttributeConverter that ends up with Object is broken with Hibernate. I just get an error saying "unknown jdbc type" or something like that. Shame really, since this approach is much prettier, with less boiler plate. – Xochitlxp

I didn't need this yesterday when the question was asked, I need it today and tried your solution, only to discover that the Postgres JDBC driver wants it through a special method setPGobject, which can only be called manually or if you use setObject you have to specify the type to java.sql.Types.OTHER, which I can't do. I modified the driver to send it there if unknown and the right class, only to run into ClassLoader conflicts. If I load the driver in the webapp I get conflict when loading, but not setting. If I load it in the tomcat/lib directory, I get the conflict when setting. – Singularize

@Singularize The class i posted isn´t working for you? I din´t understand your problem very well. If you see the method convertToDatabaseColumn it will return an Object but this object is already of type PGobject necessary for Postgres i dont think you need anything more than that. – Stepper

Technically, you're right that it should work. There is no good reason for the webapp and the server to be loading the class from different ClassLoaders, but they are doing it. – Singularize

PGobject is the class that is being loaded from different places? Try to see if you are not importing the packages two times one directly and one by maven. – Stepper

First I put the pgjdbc driver dependency in pom.xml as compile (include in war) and deployed on Tomcat 8.5 (older versions don't search the war for jdbc drivers) without having it in it's lib path, so it could only have found it in the war. The classloader conflict occurred when the pgjdbc driver tried to persist an entity. Then I put it in as provided scope, so it's not included in the war, and added the jar in tomcat/lib. The conflict was when I read an entity. The problem is from Tomcat. It might work right under Glassfish or another server. – Singularize

This solution doesn't work with Hibernate (current 5.2.10.Final). The error is org.hibernate.MappingException: No Dialect mapping for JDBC type: SOME_RANDOM_NUMBER and apparently developers are not going to fix it. The proposed by them solution is using Generic Hibernate Types (heavy stuff) vladmihalcea.com/2016/06/20/… – Qualitative

@SewerynNiemiec Are you using JPA right? This solution is for using with JPA and not with specific dialect HQL from Hibernate. Is that your case? – Stepper

Yes, I'm using JPA interface. All my custom AttributeConverters which use standard SQL types work without problems. The one for jsonb gave my the error. – Qualitative

You have to replace mapper.read by mapper.readFor as mapper.read is deprecated. – Irrefrangible

For me it doesn't work, I've the same problem that @Singularize when using Hibernate implementation. The problem is that when calling PGPreparedStatement.setObject with the PGObject Types.OTHER isn't used. Anyone had that problem and find any solution? – Gazetteer

@Gazetteer since then I have made a plug-and-play library for using these json object types. It will work for Hibernate 5.2 and above before 6.0. It will NOT work with Hibernate 6.0. Tested with postgresql and mysql databases. – Singularize

Answer 2

Edit: I see now that this is pretty much Hibernate dependent. But perhaps you can find something similar for EclipseLink.

I'll just add what I have as an answer, it originates from another SO answer but whatever. This will map jsonb to JsonObject of Google gson, but you can change it to something else if needed. To change to something else, change nullSafeGet, nullSafeSetand deepCopy methods.

public class JsonbType implements UserType {

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

    @Override
    public Class<JsonObject> returnedClass() {
        return JsonObject.class;
    }

    @Override
    public boolean equals(final Object x, final Object y) {
        if (x == y) {
            return true;
        }
        if (x == null || y == null) {
            return false;
        }
        return x.equals(y);
    }

    @Override
    public int hashCode(final Object x) {
        if (x == null) {
            return 0;
        }

        return x.hashCode();
    }

    @Nullable
    @Override
    public Object nullSafeGet(final ResultSet rs,
                              final String[] names,
                              final SessionImplementor session,
                              final Object owner) throws SQLException {
        final String json = rs.getString(names[0]);
        if (json == null) {
            return null;
        }

        final JsonParser jsonParser = new JsonParser();
        return jsonParser.parse(json).getAsJsonObject();
    }

    @Override
    public void nullSafeSet(final PreparedStatement st,
                            final Object value,
                            final int index,
                            final SessionImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
            return;
        }

        st.setObject(index, value.toString(), Types.OTHER);
    }

    @Nullable
    @Override
    public Object deepCopy(@Nullable final Object value) {
        if (value == null) {
            return null;
        }
        final JsonParser jsonParser = new JsonParser();
        return jsonParser.parse(value.toString()).getAsJsonObject();
    }

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

    @Override
    public Serializable disassemble(final Object value) {
        final Object deepCopy = deepCopy(value);

        if (!(deepCopy instanceof Serializable)) {
            throw new SerializationException(
                    String.format("deepCopy of %s is not serializable", value), null);
        }

        return (Serializable) deepCopy;
    }

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

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

To use this, do:

public class SomeEntity {

    @Column(name = "jsonobject")
    @Type(type = "com.myapp.JsonbType") 
    private JsonObject jsonObject;

In addition, you need to set your dialect to indicate that JAVA_OBJECT = jsonb:

registerColumnType(Types.JAVA_OBJECT, "jsonb");

Xochitlxp

Answer 3

I think I found an analogy to Hibernate's UserType for EclipseLink.

http://www.eclipse.org/eclipselink/documentation/2.6/jpa/extensions/annotations_ref.htm#CHDEHJEB

You have to make a class that implements org.eclipse.persistence.mappings.converters.Converter and does the conversion for you, then use the @Convert annotation on every field where you are using that type.

Singularize

Answer 4

For anyone looking for a Mysql solution with the JSON column type, here it is. FWIW I am using EclipseLink but this is a pure JPA solution.

@Column(name = "JSON_DATA", columnDefinition="JSON")
@Convert(converter=JsonAttributeConverter.class)
private Object jsonData;

and

@Converter
public class JsonAttributeConverter implements AttributeConverter <Object, String>
{

    private JsonbConfig cfg = new JsonbConfig().withFormatting(true);

    private Jsonb jsonb = JsonbBuilder.create(cfg);
    
    @Override
    public String convertToDatabaseColumn(Object object)
    {      
        if (object == null) return null;

        return jsonb.toJson(object);
    }

    @Override
    public Object convertToEntityAttribute(String value)
    {
        if (value == null) return null;

        return jsonb.fromJson(value, value.getClass());
    }

}

Lacey

Answer 5

@Column(columnDefinition = "jsonb")
@JdbcTypeCode(SqlTypes.JSON)
private String columnYouSetAsJsonb; // Replace "columnYouSetAsJsonb" with the appropriate field/column name

This is the simplest way I have found to set a field in the Entity class. It works well for me for saving and querying.

I don't have much time to investigate how it is implemented. It would be nice if someone else explain how it works.

Tufted