How to map JSON objects using generic Hibernate Types

(Last Updated On: January 22, 2019)
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

Since not all relational database systems support JSON types, Hibernate does not come with a built-in JSON Type mapper. Traditionally, all custom types have been supplied via a UserType implementation. However, a UserType is not very developer friendly so, in this post, I’m going to show how you can do a better job using AbstractSingleColumnStandardBasicType instead.

The Domain Model

Assuming we have the following Domain Model:

JsonType Domain Model

Location and Ticket are JSON Object(s), while Event and Participant are JPA entities. Our goal is to provide a Hibernate JSON Type that works for any type of JSON Java Object and on any relational database that supports JSON columns.

You don't have to create all these types manually. You can simply get them via Maven Central using the following dependency:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

For more info, check out the hibernate-types open-source project.

Generic JSON Types

First of all, we need a Jackson-based utility to allows us to easily transform JSON Object(s) to and from String:

public class JacksonUtil {

    public static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();

    public static <T> T fromString(String string, Class<T> clazz) {
        try {
            return OBJECT_MAPPER.readValue(string, clazz);
        } catch (IOException e) {
            throw new IllegalArgumentException("The given string value: " 
                + string + " cannot be transformed to Json object");
        }
    }

    public static String toString(Object value) {
        try {
            return OBJECT_MAPPER.writeValueAsString(value);
        } catch (JsonProcessingException e) {
            throw new IllegalArgumentException("The given Json object value: " 
                + value + " cannot be transformed to a String");
        }
    }

    public static JsonNode toJsonNode(String value) {
        try {
            return OBJECT_MAPPER.readTree(value);
        } catch (IOException e) {
            throw new IllegalArgumentException(e);
        }
    }

    public static <T> T clone(T value) {
        return fromString(toString(value), (Class<T>) value.getClass());
    }
}

There are two ways to send a JSON Object to the database:

  • using a String
  • using a binary representation of the actual JSON Object

For this reason, we are going to implement two types: JsonBinaryType and JsonStringType.

JsonBinaryType

The binary type looks as follows:

public class JsonBinaryType
    extends AbstractSingleColumnStandardBasicType<Object> 
    implements DynamicParameterizedType {

    public JsonBinaryType() {
        super( 
            JsonBinarySqlTypeDescriptor.INSTANCE, 
            new JsonTypeDescriptor()
        );
    }

    public String getName() {
        return "jsonb";
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((JsonTypeDescriptor) getJavaTypeDescriptor())
            .setParameterValues(parameters);
    }

}

JsonStringType

The String Type looks like this:

public class JsonStringType
        extends AbstractSingleColumnStandardBasicType<Object> 
        implements DynamicParameterizedType {

    public JsonStringType() {
        super( 
            JsonStringSqlTypeDescriptor.INSTANCE, 
            new JsonTypeDescriptor() 
        );
    }

    public String getName() {
        return "json";
    }

    @Override
    protected boolean registerUnderJavaType() {
        return true;
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((JsonTypeDescriptor) getJavaTypeDescriptor())
            .setParameterValues(parameters);
    }
}

Both types extend the AbstractSingleColumnStandardBasicType Hibernate built-in Type base class and any JSON Object is accepted. By implementing the DynamicParameterizedType interface, we can extract the underlying JSON Object Java Class, which is needed when reconstructing the JSON Object from its String representation.

The AbstractSingleColumnStandardBasicType constructor takes two parameters:

  • a sqlTypeDescriptor,
  • a javaTypeDescriptor

JsonTypeDescriptor

Both JsonBinaryType and JsonStringType use the same JsonTypeDescriptor which is responsible for transforming the JSON Object type used in the entity mapping classes to a format that is supported by the underlying database.

public class JsonTypeDescriptor
        extends AbstractTypeDescriptor<Object> 
        implements DynamicParameterizedType {

    private Class<?> jsonObjectClass;

    @Override
    public void setParameterValues(Properties parameters) {
        jsonObjectClass = ( (ParameterType) parameters.get( PARAMETER_TYPE ) )
            .getReturnedClass();

    }

    public JsonTypeDescriptor() {
        super( Object.class, new MutableMutabilityPlan<Object>() {
            @Override
            protected Object deepCopyNotNull(Object value) {
                return JacksonUtil.clone(value);
            }
        });
    }

    @Override
    public boolean areEqual(Object one, Object another) {
        if ( one == another ) {
            return true;
        }
        if ( one == null || another == null ) {
            return false;
        }
        return JacksonUtil.toJsonNode(JacksonUtil.toString(one)).equals(
                JacksonUtil.toJsonNode(JacksonUtil.toString(another)));
    }

    @Override
    public String toString(Object value) {
        return JacksonUtil.toString(value);
    }

    @Override
    public Object fromString(String string) {
        return JacksonUtil.fromString(string, jsonObjectClass);
    }

    @SuppressWarnings({ "unchecked" })
    @Override
    public <X> X unwrap(Object value, Class<X> type, WrapperOptions options) {
        if ( value == null ) {
            return null;
        }
        if ( String.class.isAssignableFrom( type ) ) {
            return (X) toString(value);
        }
        if ( Object.class.isAssignableFrom( type ) ) {
            return (X) JacksonUtil.toJsonNode(toString(value));
        }
        throw unknownUnwrap( type );
    }

    @Override
    public <X> Object wrap(X value, WrapperOptions options) {
        if ( value == null ) {
            return null;
        }
        return fromString(value.toString());
    }

}

The jsonObjectClass holds the underlying entity mapping JSON Object type. The toString and fromString methods allow Hibernate to converts a JSON Object to a String and to reconstruct a JSON Object from its String representation.

The unwrap method is used prior to materializing the JSON Object to a format that is expected by the currently used relational database. For this reason, we can unwrap a JSON Object to either a String or a JsonNode.

The MutableMutabilityPlan specifies that the JSON Object can be updated, and the areEqual method determines if two JSON Object(s) are equivalent.

AbstractJsonSqlTypeDescriptor

Both the JsonBinarySqlTypeDescriptor and the JsonStringSqlTypeDescriptor classes extend from a common AbstractJsonSqlTypeDescriptor which looks like this:

public abstract class AbstractJsonSqlTypeDescriptor 
    implements SqlTypeDescriptor {

    @Override
    public int getSqlType() {
        return Types.OTHER;
    }

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

    @Override
    public <X> ValueExtractor<X> getExtractor(
        final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>(javaTypeDescriptor, this) {
            @Override
            protected X doExtract(
                ResultSet rs, 
                String name, 
                WrapperOptions options) throws SQLException {
                return javaTypeDescriptor.wrap(
                    rs.getObject(name), options
                );
            }

            @Override
            protected X doExtract(
                CallableStatement statement, 
                int index, 
                WrapperOptions options) throws SQLException {
                return javaTypeDescriptor.wrap(
                    statement.getObject(index), options
                );
            }

            @Override
            protected X doExtract(
                CallableStatement statement, 
                String name, 
                WrapperOptions options) throws SQLException {
                return javaTypeDescriptor.wrap(
                    statement.getObject(name), options
                );
            }
        };
    }

}

The AbstractJsonSqlTypeDescriptor base class allows us to notify Hibernate that the expected JDBC type is not a SQL-standard column type, as well as the ResultSet extraction logic.

JsonBinarySqlTypeDescriptor

For relational databases that expect to materialize the JSON Object in a binary format, we need to use the JsonBinarySqlTypeDescriptor:

public class JsonBinarySqlTypeDescriptor
    extends AbstractJsonSqlTypeDescriptor {

    public static final JsonBinarySqlTypeDescriptor INSTANCE = 
        new JsonBinarySqlTypeDescriptor();

    @Override
    public <X> ValueBinder<X> getBinder(
        final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>(javaTypeDescriptor, this) {
            @Override
            protected void doBind(
                PreparedStatement st, 
                X value, 
                int index, 
                WrapperOptions options) throws SQLException {
                st.setObject(index, 
                    javaTypeDescriptor.unwrap(
                        value, JsonNode.class, options), getSqlType()
                );
            }

            @Override
            protected void doBind(
                CallableStatement st, 
                X value, 
                String name, 
                WrapperOptions options)
                    throws SQLException {
                st.setObject(name, 
                    javaTypeDescriptor.unwrap(
                        value, JsonNode.class, options), getSqlType()
                );
            }
        };
    }
}

As you can see, the JSON Object is sent as a JsonNode using the setObject method exposed by either PreparedStatement or CallableStatement.

JsonStringSqlTypeDescriptor

For relational databases that expect to materialize the JSON Object in a String-like format, we need to use the JsonStringSqlTypeDescriptor:

public class JsonStringSqlTypeDescriptor 
    extends AbstractJsonSqlTypeDescriptor {

    public static final JsonStringSqlTypeDescriptor INSTANCE = 
        new JsonStringSqlTypeDescriptor();

    @Override
    public <X> ValueBinder<X> getBinder(
        final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>(javaTypeDescriptor, this) {
            @Override
            protected void doBind(
                PreparedStatement st, 
                X value, 
                int index, 
                WrapperOptions options) throws SQLException {
                st.setString(index, 
                    javaTypeDescriptor.unwrap(value, String.class, options)
                );
            }

            @Override
            protected void doBind(
                CallableStatement st, 
                X value, 
                String name, 
                WrapperOptions options)
                    throws SQLException {
                st.setString(name, 
                    javaTypeDescriptor.unwrap(value, String.class, options
                ));
            }
        };
    }
}

As you can see, the JSON Object is sent as a String using the setString method exposed by either PreparedStatement or CallableStatement.

Testing time

These two generic JSON Types work on either PostgreSQL or MySQL. To make use of the newly defined types, we must declare them using the TypeDef annotation:

@TypeDefs({
    @TypeDef(name = "json", typeClass = JsonStringType.class),
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
@MappedSuperclass
public class BaseEntity { }

The `TypeDef` annotations can be applied to a base entity class or in the package-info.java file associated to our current entities package.

MySQL

MySQL 5.7 adds support for JSON types which, at the JDBC level, need to be materialized as String. For this reason we are going to use JsonStringType.

The entity mapping looks like this:

@Entity(name = "Event") 
@Table(name = "event")
public class Event extends BaseEntity {

    @Type(type = "json")
    @Column(columnDefinition = "json")
    private Location location;

    public Location getLocation() {
        return location;
    }

    public void setLocation(Location location) {
        this.location = location;
    }
}

@Entity(name = "Participant") 
@Table(name = "participant")
public class Participant extends BaseEntity {

    @Type(type = "json")
    @Column(columnDefinition = "json")
    private Ticket ticket;

    @ManyToOne
    private Event event;

    public Ticket getTicket() {
        return ticket;
    }

    public void setTicket(Ticket ticket) {
        this.ticket = ticket;
    }

    public Event getEvent() {
        return event;
    }

    public void setEvent(Event event) {
        this.event = event;
    }
}

When inserting the following entities:

final AtomicReference<Event> eventHolder = new AtomicReference<>();
final AtomicReference<Participant> participantHolder = new AtomicReference<>();

doInJPA(entityManager -> {
    Event nullEvent = new Event();
    nullEvent.setId(0L);
    entityManager.persist(nullEvent);

    Location location = new Location();
    location.setCountry("Romania");
    location.setCity("Cluj-Napoca");

    Event event = new Event();
    event.setId(1L);
    event.setLocation(location);
    entityManager.persist(event);

    Ticket ticket = new Ticket();
    ticket.setPrice(12.34d);
    ticket.setRegistrationCode("ABC123");

    Participant participant = new Participant();
    participant.setId(1L);
    participant.setTicket(ticket);
    participant.setEvent(event);

    entityManager.persist(participant);

    eventHolder.set(event);
    participantHolder.set(participant);
});

Hibernate generates the following statements:

INSERT INTO event (location, id) 
VALUES (NULL(OTHER), 0)

INSERT INTO event (location, id) 
VALUES ('{"country":"Romania","city":"Cluj-Napoca"}', 1)

INSERT INTO participant (event_id, ticket, id) 
VALUES (1, {"registrationCode":"ABC123","price":12.34}, 1)

The JSON Object(s) are properly materialized into their associated database columns.

Not only that JSON Object(s) are properly transformed from their database representation:

Event event = entityManager.find(
    Event.class, eventHolder.get().getId());
assertEquals("Cluj-Napoca", event.getLocation().getCity());

Participant participant = entityManager.find(
    Participant.class, participantHolder.get().getId());
assertEquals("ABC123", participant.getTicket().getRegistrationCode());

But we can even issue native JSON-based SQL queries:

List<String> participants = entityManager.createNativeQuery(
    "SELECT p.ticket -> \"$.registrationCode\" " +
    "FROM participant p " +
    "WHERE JSON_EXTRACT(p.ticket, \"$.price\") > 1 ")
.getResultList();

And JSON Object(s) can be modified:

event.getLocation().setCity("Constanța");
entityManager.flush();

Hibernate generating the proper UPDATE statement:

UPDATE event 
SET location = '{"country":"Romania","city":"Constanța"}'
WHERE id = 1

PostgreSQL

PostgreSQL has been supporting JSON types since version 9.2. There are two types that can be used:

  • json
  • jsonb

Both PostgreSQL JSON types need to be materialized using a binary data format, so we need to use the JsonBinaryType this time.

PostgreSQL JSON column type

For the JSON column type, the two JSON Object(s) mapping must be changed as follows:

@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Location location;

@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Ticket ticket;

The insert works just the same, as well as the entity update, and we can even query the JSON column as follows:

List<String> participants = entityManager.createNativeQuery(
    "SELECT p.ticket ->>'registrationCode' " +
    "FROM participant p " +
    "WHERE p.ticket ->> 'price' > '10'")
.getResultList();

PostgreSQL JSONB column type

For the JSONB column type, we only need to change the columnDefinition attribute:

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private Location location;

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private Ticket ticket;

The insert and the JSON Object update work the same, while the JSONB column type provides more advanced querying capabilities:

List<String> participants = entityManager.createNativeQuery(
    "SELECT jsonb_pretty(p.ticket) " +
    "FROM participant p " +
    "WHERE p.ticket ->> 'price' > '10'")
.getResultList();

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The generic JSON Types(s) are very useful when you need to map any JSON Object to either a String or a binary database representation.

Code available on GitHub.

Download free ebook sample

Newsletter logo
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence,
  • a 10% discount coupon for my book.

41 Comments on “How to map JSON objects using generic Hibernate Types

    • Thanks for your quick answer! I know that the best way is to test always against the DB which is also used in production.

      But is there any solution to add JSON support to H2?

      E.g. something like that:
      public class JsonH2Dialect extends H2Dialect {
      public JsonH2Dialect() {
      super();
      this.registerColumnType(Types.JAVA_OBJECT, “json”);
      }
      }

  1. I have json in the database – however the model has changed and even thought I use @jsonignoreproperties I still get the error Unrecognized field

    How to supply our own objectMapper to your class ObjectMapperWrapper

    Thanks

  2. Hello Vlad, appreciate your articles!

    Currently trying to break through a wall:

    How can I convert a @Procedure parameter (a JsonNode entity) to JsonBinaryType in order to use it with @Param?

    @Procedure(name = “schema.test_function”)
    T executeProcedure( @Param(“p_input”) JsonBinaryType var);

  3. HI,

    i added on my class
    @JsonInclude(JsonInclude.Include.NON_DEFAULT)

    but when i save the entity on database, the json column contains also the property that should be ignored (null, false for boolean, etc.)

    • Send me a replicating test case to the hibernate-types project using a Pull Request and I will take a look at it.

  4. Im my use case, i had:

    @Column(name = "params", nullable = false, updatable = false, columnDefinition = "json")
    @Type(type = "json")
    private Map<String, String> params;

    And I got on persist time:

    Caused by: org.postgresql.util.PSQLException: ERROR: column “params” is of type json but expression is of type character varying
    Hint: You will need to rewrite or cast the expression.
    Position: 158

    Have any way to use @Type(type = “json”) with a Map<>?

  5. Hi, Could you please provide jar for dependency java class. eg. AbstractTest.
    I was trying to call method doInJPA from dao class but unable to access. I already have entity manager object in my dao class. please advice how can I manage and call the doInJPA method for insert , update delete.

    • That’s only intended for testing. It makes no sense in a DAO since you would need to share the same EntityManager between all DAOs called within the same service method.

  6. Hi,
    I use MySql with Hibernate 5.2.11-Final.
    On database I created a column varchar(4000) to manage the JSON object.
    When I try to persist my entity I get NullPointerException in JsonTypeDescriptor.toString(JsonTypeDescriptor.java:83) because the objectMapperWrapper variable is null.

    How can I resolve this problem?

    Thanks

  7. Hi Vlad,

    Thanks for the excellent post. I have used this dependency in my project. I am using postgres db with spring-data-jpa in a spring-boot application. Insert works like a charm. But I am facing issue with querying table based jsonb column.
    The query used it as below.
    entityManager.createNativeQuery(“select * from myservice.mytable where jsonbpropertycolumn ->>’customer’=’John Doe’;”).getResultList();

    But i am getting an error like

    javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

    Could you please help.

    Thanks
    Nagarjun

    • Try adding @JsonIgnore to the property that you don’t want to save in the DB.

  8. Hello Vlad. Thanks for this excellent post. I am able to save my data in jsonb format in PostgreSQL . But I am having difficulty in reading the data and display in webpage. My jsonb column is given below:-

    @Type(type = “jsonb”)
    @Column(columnDefinition = “jsonb”,name=”charge_manager”)
    private ChargeManager chargeman;

    ChargeManager class is as below:-

    public class ChargeManager implements Serializable {
    private static final long serialVersionUID = 1L;

    private String name;

    public String getName() {
    return name;
    }

    public void setName(String name) {
    this.name = name;
    }

    In controller class, when I am retrieving all data of Projects, this column is also getting retrieved but I am not able to display it.

    ModelAndView mav = new ModelAndView();
    mav.addObject(“userProjects”, projectService.getAllProjects());

    In View,

    ‘name’}”>

    • If you can persist and fetch the JSON field, then everything works fine from the Hibernate part. You have an issue in the web layer, which beyond the scope of this article.

  9. Hi,

    Thanks for the article. I tried to use this with JPA auto generate in my spring boot (with hibernate-types-52), it didn’t work, got an error. Does it work with only predefined schema?

    Caused by: org.hibernate.MappingException: Could not determine type for: java.util.List, at table:

    Thanks

    • All the tests in the GitHub repository use user-generated schema and they work like a charm. Most likely, you didn’t register the JSON Type you are using as explained in the articles mentioned in the GitHub repository page.

  10. Hi,
    I’m trying to store a JsonNode Jackson data type in an Oracle database as data type BLOB.
    Is this possible with a small modification to your library? I notice that compatibility is only mentioned with MySQL and PostgreSQL.

    • Try either “jsonb” or “json” types and see how they work with Oracle. If they don’t work, try to build an Oracle-specific type based on the existing ones and send me a Pull Request.

      • In my Entity class, is that something like:

        @Type(type = “json”)
        @Column(columnDefinition = “blob”)
        private JsonNode data;

        and

        @Type(type = “jsonb”)
        @Column(columnDefinition = “blob”)
        private JsonNode data;

        or am mixing things up?

      • Have tested with Oracle. If your data type is set to CLOB (one of the other supported JSON types) then type=”json” works. Oracle gives the error: Invalid HEX number if the column type is BLOB – I assume this is because it’s trying to insert a string into a binary column.

  11. Hi,

    Very informative post. However, using your JsonBinarytype, how can you cater to the Collection of Json types? Does the type needs to be changed?

    For instance

    @Type(type = “jsonb”)
    @Column(columnDefinition = “jsonb”)
    private Set location;

  12. Hi,

    Very nice and informational post.

    I followed your method to make a custom type X which can be mapped to VARCHAR. So my SqlTypeDescriptor.getSqlType() return VARCHAR. Also my type descriptor class does not implement DynamicParameterizedType. Am I correct? Hiberate tries to make tables with fields of type X and obviously it fails in Postgre. Do you have any hint on this?

    This is what my entity class looks like:


    @TypeDefs({@TypeDef(name="x", typeClass=XType.class)})
    @Entity
    class Entity {
    @Type("x")
    @Column(columntDefinition="x")
    public X getX() {}
    }

      • Yes. I get it wrong. After changing columnDefinition to VARCHAR, everything works as expected.

        X is a custom class which I want store as VARCHAR in DB but I want have custom properties on it so I defined a custom type.

        Thank you!

      • You’re welcome. I’m glad I could help.

  13. Hi Vlad, thanks for the hibernate-types project. It works like magic. I was reading this blog article trying to understand how the custom types work without defining a custom hibernate dialect registering custom column types. I found other articles around about jsonb type they all need custom dialect with registered types. Which piece of the code shown in this article makes custom dialect unnecessary?

    • You only need that for native queries and some dialect already register JSON object type.

      • “some dialect already register JSON object type”

        That was the key! After you mentioned that, I dug down into hibernate builtin dialects and found that, specifically PostgreSQL92Dialect introduces the “json” type registration.

        thanks a lot.

  14. What if the bean to serialize/deserialize to JSON/JSONB contains LocalDate and/or LocalDateTime fields (java.time) and a custom datetime format is needed like for example “yyMMdd”?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want to run your data access layer at warp speed?