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.

Kudos to @vlad_mihalcea, the Hibernate Types library saved me tons of work yesterday by automagically mapping the PostgreSQL jsonb column to a POJO. Brilliant work 💪

— Tomasz Knyziak (@TomaszKnyziak) September 18, 2019

The Domain Model

Assuming we have the following 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.

FREE EBOOK

Insert details about how the information is going to be processed

DOWNLOAD NOW