Most databases offer lots of proprietary features in addition to the known SQL standard. One example for it 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 (which is part of the SQL standard and supported by Hibernate), but 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.

Unfortunately, Hibernate does not support PostgreSQL’s JSONB data type, and you have to implement an UserType for it. I will show you how to do that 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 the entity for this table looks like this.

@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?

Implement a Hibernate UserType

The first thing you have to do is 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 the attribute from and when you write it to the database. 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, and I again use the Jackson ObjectMapper for it.

@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 which requires 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; ... }

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 create your own dialect to register it. But don’t worry, there isn’t much to do because you can simply extend an existing dialect and call the registerColumnType method in the constructor. I’m using a PostgreSQL database in version 9.4 in this example and therefore extend the existing 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

You can use the MyEntity entity and its MyJson attribute in the same way as any other entity and attribute. You can, for example, use 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 and cheat sheet

PostgreSQL offers different proprietary datatypes, like the JSONB type I used in this post, to store JSON documents in the database. These datatypes are not supported by Hibernate and you have to implement the mapping yourself. As you have seen during this post, this can be done by implementing the UserType interface, registering it with a @TypeDef annotation and creating a Hibernate dialect which registers the column type.

