How to map JSON objects using generic Hibernate Types

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. 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-55</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 = JsonType.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 entity’s package.

MySQL

MySQL 5.7 adds support for JSON types, which, at the JDBC level, need to be materialized as String. However, the JsonType is smart knows how to handle each DB, so there’s nothing to worry about.

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, but the generic JsonType can handle this just fine.

PostgreSQL JSON column type

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

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

@Type(type = "json")
@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 since both the json and jsonb PostgreSQL column types are handled by the JsonType:

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

@Type(type = "json")
@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 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

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

  1. hi, Vlad,
    first of all, thinks for your work.

    Maybe a dumb doubt here:
    What should I do if instead a POJO I have a List?

  2. Thanks for your work.

    Why this package is still (end of 2021 … Java 17 LTS released … )
    not included in Hibernate-core ?

    it also contains the DTO mapping utility which is considered a best practise …

    • What’s the difference between having it in the core or using my library? It will work the same. So, you’re basically going to save 4 lines of XML in the pom.xml

  3. Great work.

    I was wondering, if there was a way to encrypt some the properties of the jsonb.
    Do you have an idea ?

    Elie

  4. Hi Vlad,

    Is there any way to map column to jsonb in postgres and varchar in oracle for same entity.

    Appreciate your help!

  5. Hi,
    in this way i can’t obtain a persistance of the various column values insert into the jsonb fields, there’s a way to do it?

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.