Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use Postgres JSONB datatype with JPA?

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?

like image 408
justcode Avatar asked Oct 03 '16 11:10

justcode


People also ask

What is Jsonb data type in PostgreSQL?

The jsonb datatype is an advanced binary storage format with full processing, indexing and searching capabilities, and as such pre-processes the JSON data to an internal format, which does include a single value per key; and also isn't sensible to extra whitespace or indentation.

Does hibernate support Jsonb?

Hibernate dialectHibernate's PostgreSQL dialect does not support the JSONB datatype, and you need to register it.

How do I query Jsonb data?

Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.


1 Answers

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);     }   } } 
like image 59
justcode Avatar answered Oct 02 '22 04:10

justcode