How to map JSON objects using generic Hibernate Types

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see how you can map a JSON column to a JPA entity attribute using the Hibernate Types open-source project.

While you can create your own custom Hibernate Types, for mapping JSON column types on Oracle, SQL Server, PostgreSQL, or MySQL, you don’t need to implement your own Hibernate Type since the Hibernate Types project already offers this functionality.

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.

Maven dependency

The first thing you need to do is to set up the following Maven dependency in your project pom.xml configuration file:

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

Declaring the Hibernate Types

To make use of the JSON Hibernate 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 {
    //Code omitted for brevity
}

The `TypeDef` annotations can be applied to a base entity class or in the package-info.java file associated with your 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 = "jsonb")
private Location location;

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
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();

I'm running an online workshopk on the 14th of May about The Best Way to Fetch Data with Java Persistence and Hibernate.

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

Conclusion

The Hibernate Types support for JSON column mapping is very useful, and you can use it to map entity attributes that are either POJO, String, or even JsonNode.

The best thing about the Hibernate Types project is that it offers support for Oracle, SQL Server, PostgreSQL, or MySQL JSON column types.

Transactions and Concurrency Control eBook

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

  1. The PostgreSQL JSON and JSONB column type examples have exactly the same column and type annotation (type = “jsonb”), could this be a typo?

    • No, there is no typo. Check out the test cases on GitHub. Here, the name of the Hibernate Type does not stand for the name of the PostgreSQL column type. It stands for what JDBC Statement method needs to be called to pass the JSON object.

  2. Hi,

    I used hibernate-types-52 in my project to convert a list of objects to the Postgresql jsob column. I noticed there is an additional update hibernate sql besides the hibernate insert sql. Is this update expected? Is there a way we can avoid it?

    • I’m having the same issue and I suppose that you also have a Set. The point is that the serialization is made twice: during the save and during the flush action. The elements could not have the same order, so it is interpreted as a change –> Insert, Update.

      The problem I’m having is that this increments the version number for Optimistic locking.
      Am I right?

      • Send me a Pull Request with a replicating test case, and I’ll check it out afterward.

  3. Currently, I can only filter JSON using native query. My use case requires using Spring Data Specification to filter data based on multiple columns. IS this possible?

  4. Thanks for the excellent post Vlad, it saves us a lot of time with JSONB column.

    I am just wondering, if this support array of user-defined-type(UDT),
    For ex: my another table has following UDT array as one column, how we can Map this in JPA ?

    create table Catalog {
    offers Offer_UDT[] — how to map this column ?

    }

    create type Offer_UDT as {
    offer-id text,
    offer-name text,
    }

    • It does bot support UDT arrays. But you can contribute the Type if you figured it out. That’s the spirit of open source software development.

    • Hi!

      Currently, deserializing Collection for the H2 for JSON type does not work. And because H2 added JSON type, are there any plans to add support for the H2?

      Thanks!

      • If you need H2 support for JSON, then send me a Pull Request with a fix. I’ll review it when it’s ready.

  5. 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

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.