How to map JSON data in PostGreSQL database to a Hibernate Entity column?

PostGreSQL database is one of the most popular open source databases . It has features which traditional relational databases lack , like storing a JSON as itself in the database.

JSON is a widely used communication data format and hence this feature comes quite handy.

The problem arises when you use an ORM tool like Hibernate to communicate with the database.

Hibernate does not support JSON types.

Hibernate probably will fix this in the coming versions.

Until then how can we map JSON types to a Hibernate entity attribute?

The solution is to create a custom Hibernate Type. This hibernate type can then be treated as a JSON data type.

Below is the algorithm to make this work:

STEP1:

Implement UserType inteface provided by Hibernate and override all the methods in it.

STEP2:

Register the user type thus created.

STEP3:

Update the column type in the hibernate entity class (corresponding to the JSON data type in the database) to the newly created type.

STEP4:

Create a new Dialect for the JSON type.

Let’s dive into the code now.

Let’s do the code set up first:

THE SETUP

Assume a JSON object corresponding to the details of a person , is passed by any UI client.

Something like this :

{

   "name":"Trump",
    "age":65,
    "address":"Newyork"

}

And you want to store this in a PostGreSQL table.

Let’s create the table first.

I created a table named MY_TABLE in postgresql using the below command:

CREATE TABLE MY_TABLE(ID SERIAL PRIMARY KEY NOT NULL, DATA JSON NOT NULL);

As you see , the second column “DATA” is of type JSON.

Let’s store the JSON object in this column.

Let’s map the JSON object to a Java POJO class named ‘Person’.

Here is the Person class:

package com.mapping.jsontype;

import java.io.Serializable;

public class Person implements Serializable {

	private String name;

	private int age;

	private String address;

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return the age
	 */
	public int getAge() {
		return age;
	}

	/**
	 * @param age the age to set
	 */
	public void setAge(int age) {
		this.age = age;
	}

	/**
	 * @return the address
	 */
	public String getAddress() {
		return address;
	}

	/**
	 * @param address the address to set
	 */
	public void setAddress(String address) {
		this.address = address;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "MyJson [name=" + name + ", age=" + age + ", address=" + address + "]";
	}

}

It just has the three attributes passed in the JSON.

Next , let’s create the hibernate entity class for the table

package com.mapping.jsontype;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Type;

@Entity
@Table(name = "MY_TABLE", schema = "public")
public class MyTable {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "data")
	private Person data;

	/**
	 * @return the id
	 */
	public Integer getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Integer id) {
		this.id = id;
	}

	/**
	 * @return the data
	 */
	public Person getData() {
		return data;
	}

	/**
	 * @param data the data to set
	 */
	public void setData(Person data) {
		this.data = data;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "MyTable [id=" + id + ", data=" + data + "]";
	}

}

The above entity mapping won’t work since ‘Person’ object holds a JSON data type and hibernate doesn’t know that.

Let’s implement the algorithm discussed earlier in the post to resolve this:

STEP1:

Create a custom User Type:

I created the below custom user type which implements the interface UserType:

package com.springboot.jsonmapping;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.Serializable;
import java.io.StringWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import com.fasterxml.jackson.databind.ObjectMapper;

public class JsonType implements UserType {

	@Override
	public int[] sqlTypes() {
		return new int[] { Types.JAVA_OBJECT };
	}

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

	@Override
	public boolean equals(Object x, Object y) throws HibernateException {

		if (x == null) {
			return y == null;
		}
		return x.equals(y);
	}

	@Override
	public int hashCode(Object x) throws HibernateException {
		return x.hashCode();
	}

	@Override
	public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, 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(PreparedStatement ps, Object value, int idx, SharedSessionContractImplementor 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);
		}

	}

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

	@Override
	public boolean isMutable() {
		return true;
	}

	@Override
	public Serializable disassemble(Object value) throws HibernateException {
		return (Serializable) this.deepCopy(value);
	}

	@Override
	public Object assemble(Serializable cached, Object owner) throws HibernateException {
		return this.deepCopy(cached);
	}

	@Override
	public Object replace(Object original, Object target, Object owner) throws HibernateException {
		return this.deepCopy(original);
	}
}

There are a few important methods here:

sqlTypes() – return a JAVA_OBJECT type here

returningClass() – return the class which corresponds to the JSON (Person.class)

nullSafeGet() & nullSafeSet()- tell hibernate how to get/set the data from/to database here

deepCopy() – tell hibernate how to create a deep copy of the JSON type.

Other methods like equals() , hashCode() etc can be implemented as above.

The first step is completed.

STEP2:

Register the User Type created:

Register the user type by adding a TypeDef annotation to the entity class :

package com.springboot.jsonmapping;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Type;

@Entity
@Table(name = "MY_TABLE", schema = "public")
@org.hibernate.annotations.TypeDef(name = "JsonType", typeClass = JsonType.class)
public class MyTable {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Integer id;

	@Column(name = "data")
	private Person data;

	/**
	 * @return the id
	 */
	public Integer getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Integer id) {
		this.id = id;
	}

	/**
	 * @return the data
	 */
	public Person getData() {
		return data;
	}

	/**
	 * @param data the data to set
	 */
	public void setData(Person data) {
		this.data = data;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "MyTable [id=" + id + ", data=" + data + "]";
	}

}

Notice the line :

@org.hibernate.annotations.TypeDef(name = "JsonType", typeClass = JsonType.class)

before the class declaration.

This registers the Json Type we created.

This completes the second step.

STEP3:

Update the column type to the new type .

Update the column type using @Type annotation like below:

	@Column(name = "data")
	@Type(type = "JsonType")
	private Person data;

Here is the complete entity mapping :

package com.springboot.jsonmapping;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Type;

@Entity
@Table(name = "MY_TABLE", schema = "public")
@org.hibernate.annotations.TypeDef(name = "JsonType", typeClass = JsonType.class)
public class MyTable {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Integer id;

	@Column(name = "data")
	@Type(type = "JsonType")
	private Person data;

	/**
	 * @return the id
	 */
	public Integer getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Integer id) {
		this.id = id;
	}

	/**
	 * @return the data
	 */
	public Person getData() {
		return data;
	}

	/**
	 * @param data the data to set
	 */
	public void setData(Person data) {
		this.data = data;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "MyTable [id=" + id + ", data=" + data + "]";
	}

}

This completes the third step.

STEP4:

Add a dialect for the new JSON type

To do this create a new class which extends PostGreSQL dialect provided by hibernate like this:

package com.mapping.jsontype;

import java.sql.Types;

import org.hibernate.dialect.PostgreSQL94Dialect;

public class PostGreSQLDialect extends PostgreSQL94Dialect {

	public PostGreSQLDialect() {

		this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
	}

}

Register the column type as “jsonb” in the above step.

That’s it!

Now you can treat the new entity attribute “data” as if it is a JSON type.

TESTING:

To test the above changes I created a new CRUD repository provided by Spring Data:

package com.mapping.jsontype;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyTableRepository extends CrudRepository<MyTable, Integer> {

}

and a REST controller to get and post data:

package com.mapping.jsontype;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;

@org.springframework.web.bind.annotation.RestController
public class RestController {

	@Autowired
	private MyTableRepository repository;

	@GetMapping("/getData")
	public Iterable<MyTable> getData() {

		return repository.findAll();
	}

	@PostMapping("/addData")
	public void addData(@RequestBody MyTable tableData) {

		repository.save(tableData);
	}

}

Here is the application.properties where I configured the database details:

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.show-sql=true
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=admin






Here is the pom xml with all the required dependencies:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.14.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.mapping</groupId>
	<artifactId>jsontype</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>jsontype</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

NOTE: The latest version of Spring Boot as of this writing 2.3.0 did not work properly. It didn’t have few classes required to implement the UserType interface (like HibernateException.class). So I used version 2.1.14 which works seamlessly.

Let me create a new entry in the database:

It returned 200 OK status and got saved successfully.

Let me retrieve all the records I created:

(The first two records were created earlier)

Here are the entries in postgresql database:

You can save and retrieve JSON data just like other data types now!

The complete code can be found here :

https://github.com/vijaysrj/jsonmappingpostgresql

Comments

6 responses to “How to map JSON data in PostGreSQL database to a Hibernate Entity column?”

  1. Ravi Avatar
    Ravi

    Getting compilation error when building the project,

    [ERROR] contextLoads(com.mapping.jsontype.JsontypeApplicationTests) Time elapsed: 0.016 s <<< ERROR!
    java.lang.IllegalStateException: Failed to load ApplicationContext
    Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000
    Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000
    Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000

    1. Vijay SRJ Avatar
      Vijay SRJ

      Try importing the github project I have provided and check if it compiles , you are probably missing some step

  2. faissal Avatar
    faissal

    could you please add an example with other objects image a json like this :
    {
    “data”:{
    “name”:”thomas”,
    “age”:90,
    “address”:”scotland”,
    “voitures” :[{“matricule”:”DF2500″,
    “type”:”ibiza”,
    “marque”:”seat”},
    {“matricule”:”GH3612″,
    “type”:”ibiza”,
    “marque”:”seat”}
    ]

    }
    }

    what we must add to store this structure in the database a whole json object

    1. Vijay SRJ Avatar
      Vijay SRJ

      Instead of the below code:
      @Column(name = “data”)
      @Type(type = “JsonType”)
      private Person data;

      you can use :

      @Column(name = “data”)
      @Type(type = “JsonType”)
      private Map data;

      In short , Map will support any type of json

      Please try this

  3. Asha S Avatar
    Asha S

    The data from the database are not displayed nor through postman or through html page

    1. Vijay SRJ Avatar
      Vijay SRJ

      What is the error that you are getting

Leave a Reply

Discover more from The Full Stack Developer

Subscribe now to keep reading and get access to the full archive.

Continue reading