How to map JSON objects using generic Hibernate Types

(Last Updated On: January 29, 2018)

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:

JsonTypeDomainModel

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:

1
2
3
4
5
<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 static 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 static 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.

Subscribe to our Newsletter

* indicates required
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. 
Get the most out of your persistence layer!

Advertisements

22 thoughts on “How to map JSON objects using generic Hibernate Types

  1. 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?

      1. “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.

  2. 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”?

  3. Hi,
    I have scenario where I got JSON string/object how do i write that to JSONB data type in PostgreSQL?
    Which one of your approach is good?
    Thanks
    Adam

  4. Example perfect work on simple Json field. On Json field with inner Json i get error The given string value … cannot be transformed to Json object. My inner Json tested particilar. Have you work example with complex Json? Thanks!

      1. 2 questions before start…
        In this example used jsonb, but i have json field(Postgresql), not binary. It’s normally?
        Second, i’m right think to adapt example for my case:

        @Entity
        public class SomeObj{

        private int someInt;

        @Type(type = "json")
        @Column(columnDefinition = "json")
        private SomeJsonObj someJsonObj;

        }

        public class SomeJsonObj{

        … top level field definitions in json….

        @Type( type = “jsonb-node” )
        @Column(columnDefinition = “jsonb”)
        private JsonNode innerJsonObj;
        ? Great Thanks!

      2. You can use either json or jsonb, although jsonb is a much better choice.

        I don’t think you ever need to nest multiple JSON objects. Just use a single JSON. It’s hierarchical anyway.

  5. Hello,
    I would like to appreciate such a nice implementation which helped us a lot.
    Also, can we retrieve jsonb data from more than one table with complex joins with hibernate? Is this possible?
    Please let us know.

  6. Hi,

    First of all, thanks for the types. It has been a great help.

    I was planning to have a field of json array type, one that I could define in my entity class like

    class Entity{
        String name;
        @Type(type = "jsonb")
        @Column(columnDefinition = "jsonb")
        List<MyCustomClass> someObject;
    }
    

    This is failing as I am sure that this case array-objects is not handled. Or am I wrong and it is actually handled. I could create the helper classes thanks to the awesome guide written by you but would love to reuse existing code.

    N.B. Using Spring Boot 2.0R with Hibernate 5.2 and Postgresql. I would like the some_object field in the db to be jsonb.

    Thanks

      1. Ah! I forgot to implement Serializable. Thanks for the link to the other article. Got me on the right track!

  7. Hi Vlad,

    we are using hbm files instead of entity classes. So in my event.hbm.xml file I mentioned in the following way.

    com.vladmihalcea.hibernate.type.json.JsonStringType
    52

    But it is throwing following exception during run time

    Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET ‘binary’.

    Please help me here Vlad.

      1. Hi Vlad,

        in my table the column type is JSON only (mysql 5.7).
        Please can you help me how to keep this json type in event.hbm.xml file?

        I am using in this way.

        ?property name=”locationList” column=”location_list” optimistic-lock=”true” lazy=”false”?
        ?type name=”json”?
        ?param name=”classType”?com.vladmihalcea.hibernate.type.json.JsonStringType</param?
        ?param name=”type”>52</param?
        ?/type?
        ?/property?

        can you help me with this property Vlad?

        Here in the comment it is ignoring “<” so I replaced with “?”

      2. The code snippet is not properly formatted. Try to update your comment using Markdown or HTML <code> tags.

  8. Hi,

    I am getting following exception, do you know what could be the reason, I am using MySQL 5.7.
    java.lang.IllegalArgumentException: The given string value: {“name”: “someName”, “carrierId”: “someId”} cannot be transformed to Json object

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.