How to use PostgreSQL’s JSONB data type with Hibernate

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

Most databases offer many proprietary features besides 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, store the JSON document in a text column. That column type is part of the SQL standard. Hibernate and all other JPA implementations support it out of the box. But you would then 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 read 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 JSON mappings, and you have to implement a UserType. I will show you both options in this post.

Database table and entity

Let’s quickly 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. It 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 easier than it might sound. You only need to implement the UserType interface and register your type mapping. I will show you how to do that later in this article.

Hibernate 6 makes all of this even easier. It provides a standard JSON mapping that you only need to activate. But if you want, you can still implement a UserType to provide your own mapping. 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.

If you don’t want to use Hibernate’s standard mapping, you can still implement a custom UserType. This can be useful if you previously used an UserType and its JSON mapping doesn’t match Hibernate’s standard mapping.

I prepared an example for both options.

Using Hibernate 6’s standard JSON mapping

As mentioned earlier, you only need to annotate your entity attribute with @JdbcTypeCode(SqlTypes.JSON) to activate Hibernate’s standard JSON mapping. Hibernate then uses an available JSON library to serialize and deserialize the attribute’s value.

And it uses the standard JSON column type defined by the RDBMS-specific dialect. For PostgreSQL databases, that’s the column type JSONB.

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

The @JdbcTypeCode annotation is a new annotation. Hibernate 6 introduces it as a part of its new type mapping. Starting with version 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 and queries. I prepared an example of that at the end of this article.

Implement a Hibernate UserType

If you don’t want to use Hibernate’s standard mapping, you need to implement a custom UserType. It defines the mapping between an entity attribute’s type and the type handled by the JDBC driver.

This was your only option with Hibernate 5. So, if you’re migrating an existing application to Hibernate 6, you might already have a UserType implementation. If you want to keep it, you have to adjust your implementation to the new UserType interface and change the type references in your entity mappings.

In contrast to the old UserType interface, the new one in Hibernate 6 uses generics and accesses the fields of the resultset by their index. That means updating your existing implementation isn’t too complex.

If you’re implementing a new UserType, you should be familiar with the following methods.

The methods returnedClass and getSqlType tell Hibernate which attribute type you want to map to which SQL type. In this example, I want to map objects of the MyJson class to SqlTypes.JSON. When working with a PostgreSQL database, Hibernate maps SqlTypes.JSON to a JSONB column.

Hibernate 6 not only slightly changed the signature of these 2 methods. It also improved the type handling. In previous versions, you had to extend the database-specific dialect to map the type returned by the getSqlType method to a JSONB column. Hibernate 6 does that automatically.

public class MyJsonType implements UserType<MyJson> {

	public static final ObjectMapper MAPPER = new ObjectMapper();
	
    @Override
    public int getSqlType() {
        return SqlTypes.JSON;
    }

    @Override
    public Class<MyJson> returnedClass() {
        return MyJson.class;
    }

    ...
}

Hibernate calls the nullSafeGet method to get the value of an attribute of type MyJson from the resultset. As you can see in the following code snippet, there isn’t anything special about that method.

The 2 most commonly used parameters are the ResultSet object and the position of the attribute’s value. In this case, that’s all I need to get the JSON document from the resultset and use Jackson’s ObjectMapper to parse it into a MyJson object.

public class MyJsonType implements UserType<MyJson> {

	public static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public MyJson nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session, Object owner)
            throws SQLException {
                final String cellContent = rs.getString(position);
                if (cellContent == null) {
                    return null;
                }
                try {
                    return MAPPER.readValue(cellContent.getBytes("UTF-8"), returnedClass());
                } catch (final Exception ex) {
                    throw new RuntimeException("Failed to convert String to MyJson: " + ex.getMessage(), ex);
                }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, MyJson value, int index, SharedSessionContractImplementor session)
            throws SQLException {
                if (value == null) {
                    st.setNull(index, Types.OTHER);
                    return;
                }
                try {
                    final StringWriter w = new StringWriter();
                    MAPPER.writeValue(w, value);
                    w.flush();
                    st.setObject(index, w.toString(), Types.OTHER);
                } catch (final Exception ex) {
                    throw new RuntimeException("Failed to convert MyJson to String: " + ex.getMessage(), ex);
                }
    }

    ...
}

Hibernate calls the nullSafeSet method to set a provided MyJson object as a bind parameter on the provided PreparedStatement. This method gets called when you insert or update an entity object that uses your UserType or when your query uses a bind parameter of the mapped type. That ensures that your UserType gets applied automatically every time you use an attribute that references it.

And the last method I want to describe in detail is the deepCopy method. Its implementation is simple, but many developers have a hard time figuring out how to implement it if they haven’t seen it before. That method aims to provide a deep copy of the provided MyJson object. The easiest way is to serialize and deserialize the provided object.

public class MyJsonType implements UserType<MyJson> {

    @Override
    public MyJson deepCopy(MyJson value) {
        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());
        	MyJson obj = (MyJson)new ObjectInputStream(bais).readObject();
        	bais.close();
        	return obj;
        } catch (ClassNotFoundException | IOException ex) {
            throw new HibernateException(ex);
        }
    }

    ...
}

After implementing your UserType, you need to tell Hibernate where you want to use it. Starting with Hibernate 6, you can annotate an entity attribute with a @Type annotation and provide a class reference to your UserType implementation.

@Entity
public class MyEntity {

	@Type(MyJsonType.class)
	private MyJson jsonProperty;

	...
}

After that, you can use the entity and its attribute in your business code. You can even include the content of the JSON document in your query. I prepared a few examples 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

First, you must 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 have to do a few important things if you want to implement your own UserType. 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 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 MyJson: " + 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 MyJson 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. You need to register it by extending an existing dialect and calling the registerColumnType method in its constructor. I use a PostgreSQL database in this example and extend Hibernate’s PostgreSQL94Dialect.

public class MyPostgreSQL94Dialect extends PostgreSQL94Dialect {
 
    public MyPostgreSQL94Dialect() {
        this.registerColumnType(Types.OTHER, "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. That’s also the case for the available query features but not for your business code that uses the entity or its attribute.

Use a JSON attribute in your business code

In your business code, 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);

Hibernate will detect this change during its next dirty check and execute the required SQL UPDATE statement.

Use a JSON attribute in a query with Hibernate 5, 6.0 and 6.1

The JPA standard doesn’t define any support in JPQL for database-specific JSON columns. If you want to select an entity based on some property values inside the JSON document, you need to use a native query. Within that query, you can use all features supported by your database, e.g., PostgreSQL’s JSON functions and operators.

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

Use a JSON attribute in a query with Hibernate 6.2

Hibernate 6.2 introduces proprietary support for JSON columns to its JPQL implementation. You can now navigate your JSON document like a mapped to-one association and reference parts of the document in your query.

MyEntity e = em.createQuery("SELECT e FROM MyEntity e WHERE e.jsonProperty.longProp = 456", MyEntity.class).getSingleResult();

This approach’s main advantages are gaining database portability and defining your query based on your entity instead of your table model. You can also constructor expressions to let Hibernate map your query result to DTO objects.

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. If you prefer providing your own mapping, you can do that by implementing the UserType interface and referencing your implementation in the @Type annotation.

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.