How to use PostgreSQL’s JSONB data type with Hibernate



Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.

Join the Persistence Hub!


Most databases offer lots of proprietary features in addition to the known SQL standard. One example is PostgreSQL’s JSONB data type which allows you to store JSON documents efficiently in a database column.

You could, of course, also store the JSON document in a text column. That is part of the SQL standard and supported by Hibernate and all other JPA implementations. Still, then you would miss out on PostgreSQL-specific features like JSON validation and a list of interesting JSON Functions and Operators. But you are probably already aware of that if you are reading this post.

If you want to use a JSONB column with Hibernate 6, I have great news for you. Hibernate 6 provides a standard mapping for entity attributes to JSON columns; you only need to activate it. Unfortunately, Hibernate 4 and 5 do not support any JSON mappings, and you have to implement a UserType. I will show you both options in this post.

Database table and entity

Let’s have a quick look at the database table and entity before we get into the details of the UserType.
As you can see in the following code snippet, the definition of the database table is very simple and consists of only 2 columns: the primary key column id and the column jsonproperty of type JSONB.

CREATE TABLE myentity
(
  id bigint NOT NULL,
  jsonproperty jsonb,
  CONSTRAINT myentity_pkey PRIMARY KEY (id)
)

And you can see the entity that maps the table in the following code snippet.

@Entity
public class MyEntity {
 
    @Id
    @GeneratedValue
    private Long id;
 
    private MyJson jsonProperty;
     
    ...
}

As you can see, there is nothing JSON specific on this entity, only an attribute of type MyJson. MyJson is a simple POJO with 2 properties which you can see in the next code snippet.

public class MyJson implements Serializable {
 
    private String stringProp;
     
    private Long longProp;
 
    public String getStringProp() {
        return stringProp;
    }
 
    public void setStringProp(String stringProp) {
        this.stringProp = stringProp;
    }
 
    public Long getLongProp() {
        return longProp;
    }
 
    public void setLongProp(Long longProp) {
        this.longProp = longProp;
    }
}

So what do you have to do if you want to store the MyJson property in a JSONB database column? The answer to that depends on the Hibernate version you’re using.

In Hibernate 4 and 5, you need to implement a custom type mapping. Don’t worry. That’s not as complicated as it might sound. You only need to implement the UserType interface and register your type mapping. I will show you how to do that in this article.

Hibernate 6 makes all of this even easier. It provides a standard JSON mapping that you need to activate. Let’s take a look at this first.

JSONB mapping in Hibernate 6

Thanks to the JSON mapping introduced in Hibernate 6, you only need to annotate your entity attribute with a @JdbcTypeCode annotation and set the type to SqlTypes.JSON. Hibernate then detects a JSON library on your classpath and uses it to serialize and deserialize the attribute’s value.

@Entity
public class MyEntity {
 
    @Id
    @GeneratedValue
    private Long id;
 
    @JdbcTypeCode(SqlTypes.JSON)
    private MyJson jsonProperty;
     
    ...
}

The @JdbcTypeCode annotation is a new annotation that was introduced as part of Hibernate’s new type mapping. Starting with Hibernate 6, you can define the Java and the JDBC mapping separately by annotating your entity attribute with a @JdbcTypeCode or @JavaType annotation. Using these annotations, you can reference one of Hibernate’s standard mappings or your own implementations of the JavaTypeDescriptor or JdbcTypeDescriptor interfaces. I will explain the implementation of those interfaces in another tutorial. We only need to activate Hibernate’s standard mapping.

After you annotate your entity attribute to activate Hibernate’s JSON mapping, you can use the entity and its attribute in your business code. I prepared an example of that at the end of this article.

JSONB mapping in Hibernate 4 and 5

As I mentioned earlier, you need to implement a custom mapping if you want to use PostgreSQL’s JSONB type with Hibernate 4 or 5. The best way to do that is to implement Hibernate’s UserType interface and register the mapping in a custom dialect.

Implement a Hibernate UserType

You first have to create a Hibernate UserType, which maps the MyJson object into a JSON document and defines the mapping to an SQL type. I call the UserType MyJsonType and show only the most important methods in the following code snippets. You can have a look at the entire class in the GitHub repository.

There are a few important things you have to do if you want to implement your own UserType. First of all, you have to implement the methods sqlTypes and returnedClass, which tell Hibernate the SQL type and the Java class it shall use for this mapping. In this case, I use the generic Type.JAVA_OBJECT as the SQL type and, of course, the MyJson class as the Java class.

public class MyJsonType implements UserType {
 
    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }
 
    @Override
    public Class<MyJson> returnedClass() {
        return MyJson.class;
    }
     
    ...
}

Then you have to implement the methods nullSafeGet and nullSafeSet, which Hibernate will call when you read or write the attribute

The nullSafeGet method gets called to map the value from the database into the Java class. So we have to parse the JSON document into a MyJson class. I use the Jackson ObjectMapper here, but you can also use any other JSON parser. 

The nullSafeSet method implements the mapping of the MyJson class into the JSON document. Using the Jackson library, you can do that using the same ObjectMapper as in the nullSafeGet method.

@Override
public Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor session,
                          final Object owner) throws HibernateException, SQLException {
    final String cellContent = rs.getString(names[0]);
    if (cellContent == null) {
        return null;
    }
    try {
        final ObjectMapper mapper = new ObjectMapper();
        return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());
    } catch (final Exception ex) {
        throw new RuntimeException("Failed to convert String to Invoice: " + ex.getMessage(), ex);
    }
}
 
@Override
public void nullSafeSet(final PreparedStatement ps, final Object value, final int idx,
                        final SessionImplementor session) throws HibernateException, SQLException {
    if (value == null) {
        ps.setNull(idx, Types.OTHER);
        return;
    }
    try {
        final ObjectMapper mapper = new ObjectMapper();
        final StringWriter w = new StringWriter();
        mapper.writeValue(w, value);
        w.flush();
        ps.setObject(idx, w.toString(), Types.OTHER);
    } catch (final Exception ex) {
        throw new RuntimeException("Failed to convert Invoice to String: " + ex.getMessage(), ex);
    }
}

Another important method you need to implement is the deepCopy method which has to create a deep copy of a MyJson object. One of the easiest ways to do that is to serialize and deserialize the MyJson object. This forces the JVM to create a deep copy of the object.

@Override
public Object deepCopy(final Object value) throws HibernateException {
    try {
        // use serialization to create a deep copy
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        ObjectOutputStream oos = new ObjectOutputStream(bos);
        oos.writeObject(value);
        oos.flush();
        oos.close();
        bos.close();
         
        ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());
        Object obj = new ObjectInputStream(bais).readObject();
        bais.close();
        return obj;
    } catch (ClassNotFoundException | IOException ex) {
        throw new HibernateException(ex);
    }
}

Register the UserType

In the next step, you need to register your custom UserType. You can do this with a @TypeDef annotation in the package-info.java file. As you can see in the following code snippet, I set the name and the typeClass property of the @TypeDef annotation.

@org.hibernate.annotations.TypeDef(name = "MyJsonType", typeClass = MyJsonType.class)
 
package org.thoughts.on.java.model;

This links the UserType MyJsonType to the name “MyJsonType” which I can then use with a @Type annotation in the entity mapping.

@Entity
public class MyEntity {
 
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;
 
    @Column
    @Type(type = "MyJsonType")
    private MyJson jsonProperty;
     
    ...
 
}

And we’re almost done. Hibernate will now use the UserType MyJsonType to persist the jsonproperty attribute in the database. But there is still one step left.

Hibernate dialect

Hibernate’s PostgreSQL dialect does not support the JSONB datatype, and you need to register it. You do that by extending an existing dialect and calling the registerColumnType method in the constructor. I use a PostgreSQL database in this example and extend Hibernate’s PostgreSQL94Dialect.

public class MyPostgreSQL94Dialect extends PostgreSQL94Dialect {
 
    public MyPostgreSQL94Dialect() {
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }
}

Now you can finally store the MyJson object in a JSONB column.

How to use an entity with a JSONB mapping

As you saw in this article, the things you need to do to map an entity attribute to a JSONB column depend on the Hibernate version you’re using. But that’s not the case for your business code that uses the entity or its attribute. You can use the MyEntity entity and its MyJson attribute in the same way as any other entity. That also enables you to replace your UserType implementation with Hibernate’s standard handling when you migrate your application to Hibernate 6.

The following code snippet shows a simple example that uses the EntityManager.find method to get an entity from the database and then change the attribute values of the MyJson object.

MyEntity e = em.find(MyEntity.class, 10000L);
e.getJsonProperty().setStringProp("changed");
e.getJsonProperty().setLongProp(789L);

And if you want to select an entity based on some property values inside the JSON document, you can use PostgreSQL’s JSON functions and operators with a native query.

MyEntity e = (MyEntity) em.createNativeQuery("SELECT * FROM myentity e WHERE e.jsonproperty->'longProp' = '456'", MyEntity.class).getSingleResult();

Summary

PostgreSQL offers different proprietary datatypes, like the JSONB type I used in this post, to store JSON documents in the database. 

Hibernate 6 provides a standard JSON mapping. You only need to activate it by annotating your entity attribute with a @JdbcTypeCode annotation and setting the type to SqlTypes.JSON.

Hibernate 4 and 5 don’t support these data types. You have to implement the mapping yourself. As you have seen in this post, you can do this by implementing the UserType interface, registering it with a @TypeDef annotation, and creating a Hibernate dialect that registers the column type.

Related Articles

Responses

Your email address will not be published.

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

  1. Hi,

    Thanks for your great articles.

    Here, Instead of using MyJson and limiting what my JSON document can contain, why don’t I declare it as a Map and do whatever you’re doing?

    1. Hi,

      You could do that, and the Hibernate mapping wouldn’t be much different. But you, of course, lose the strong typing defined by the MyJson class.

      Regards,
      Thorben

  2. Hi, greate post.

    One question, why do not you close ByteArrayOutputStream and other Closable?

    1. Hi,

      I’m closing the ByteArrayOutputStream and the ObjectOutputStream. But you’re right, the code snippet was missing the closing of the ByteArrayInputStream. I fixed it.

      Regards,
      Thorben

  3. By creating MyJson class, this makes sure that JSON type is already known.
    How about custom_attributes, where you are unaware of what JSON can contain?

    1. It’s up to you how you implement the UserType. How do you want to handle the unknown structure of the document?
      You could skip the JSON processing step (the part with the ObjectMapper) and just map it to a String.

  4. Really appreciate your article. Good job!

    I have a question. Will this implementation work for persisting a collection or a map as JSONB (e.g: instead of MyJson, the attribute is of type List or Map) ?

    1. You can apply this principle to any JSON document. The only important thing is that you change the JSON mapping part of the user type.

  5. Good post, thanks for it

  6. Hi! I try to implement your code but i get this error when i deploy it. I’m usin Wildfly 10.

    ‘Caused by: java.lang.ClassNotFoundException: Could not load requested class : MyJsonType’

    Is the persistence.xml missing something?

    1. I just added the typedef annotation in the entity class and it’s solved

      1. Good idea. I was thinking the same thing. I have not used package-info.java before, so this seems like a good solution.

        Also, with MyPostgreSQL94Dialect definition, I assume the persistence.xml should update its Dialect, right?

        e.g.

      2. I had the same problem and adding typedef in the entity class worked, but does that mean my application is not able to get it from package-info.java file.
        PS : I am working on a spring boot application

  7. A great article.
    Impressive how things like CDI and JPA become JEE pleasure.
    Your article “fit like a velvet glove”, in my projects.
    Thank you

    1. Thanks!
      Happy to hear that it helps you with your current project

  8. Great post!

    But would it be better using JPA 2.1 converters instead? They’re simpler!

    What do you think?

    1. An AttributeConverter is much simpler. Unfortunately, you can only use it with Hibernate to convert any Java type to a different Java type that’s already supported by Hibernate. So, you can’t map the class to PostgreSQL’s JSONB type. You would need to map it to, e.g. a String.

  9. Hi Thorben, another fascinating article, thank you for sharing it. I have 2 questions: would this same method work with the Json support in newer versions of mysql? And do you think hibernate itself will add Json support in a future version? Cheers, martin

    1. Hi Martin,

      I didn’t use mysql for quite some time but you should be able to use the same approach to support their json format.

      I don’t know the Hibernate roadmap but I would expect that they will support these data types at some point.

      Regards,
      Thorben

  10. Please cache the ObjectMapper! Always.

    1. Good point. That would be a preferable optimization of the UserType.
      Thanks!