Using Hibernate to map a JSON to PostgreSQL

* Map to PostgreSQL `jsonb`

Image that you have a table like this in the DB:

create table special
(
    id            bigint not null,
    json_property jsonb,
    names         text[],
    string_map    jsonb,
    primary key (id)
);

As you can see we have 2 columns in the table json_property and string_map with the type is jsonb. The question here is how to map this jsonb with Hibernate.

json_property is an Object in Java
string_map is a Map in Java

Define an Entity

@Entity
@Table( name = "special" )
public class Special  {

  private Long id;

  private String[] names;

  private Detail jsonProperty;

  private Map<String, String> stringMap;

  public Special() {
    // this form used by Hibernate
  }


  @Id
  @GeneratedValue(generator="increment")
  @GenericGenerator(name="increment", strategy = "increment")
  public Long getId() {
    return id;
  }

  private void setId(Long id) {
    this.id = id;
  }

  @Column(name = "names", columnDefinition = "text[]")
  @JdbcTypeCode( SqlTypes.ARRAY )
  public String[] getNames() {
    return names;
  }

  public void setNames(String[] names) {
    this.names = names;
  }

  @JdbcTypeCode( SqlTypes.JSON )
  @Column(columnDefinition = "jsonb", name = "json_property")
  public Detail getJsonProperty() {
    return jsonProperty;
  }

  public void setJsonProperty(Detail jsonProperty) {
    this.jsonProperty = jsonProperty;
  }


  @JdbcTypeCode( SqlTypes.JSON )
  @Column(columnDefinition = "jsonb", name = "string_map")
  public Map<String, String> getStringMap() {
    return stringMap;
  }

  public void setStringMap(Map<String, String> stringMap) {
    this.stringMap = stringMap;
  }

}

For Hibernate 6
- The important here is the annotation: @JdbcTypeCode( SqlTypes.JSON ) - Hibernate will only use the JSON type if explicitly configured through @JdbcTypeCode( SqlTypes.JSON ). The JSON library used for serialization/deserialization is detected automatically, but can be overridden by setting hibernate.type.json_format_mapper
- columnDefinition is the type in your DB -> this is optional
- Refer: https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#basic-mapping-json
For Hibernate 4 or 5
- We need a custom UserType to handle this. Please refer: https://thorben-janssen.com/persist-postgresqls-jsonb-data-type-hibernate/
About the Detail object we should make use that it is a Serializable implementation:

public class Detail implements Serializable {

  private static final long serialVersionUID = 12131312L;

  public String name;
  public Integer height;

  public Detail() {
  }

  public Detail(String name, Integer height) {
    this.name = name;
    this.height = height;
  }


  public Integer getHeight() {
    return height;
  }

  public void setHeight(Integer height) {
    this.height = height;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

}

Run the following test to check:

public class JsonTypeTest extends TestCase {
	private SessionFactory sessionFactory;

	@Override
	protected void setUp() throws Exception {
		// A SessionFactory is set up once for an application!
		final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
				.build();
		try {

			sessionFactory = new MetadataSources( registry )
					.addAnnotatedClass(Special.class)
					.buildMetadata()
					.buildSessionFactory();
		}
		catch (Exception e) {
			// The registry would be destroyed by the SessionFactory, but we had trouble building the SessionFactory
			// so destroy it manually.
			throw e;
//			StandardServiceRegistryBuilder.destroy( registry );
		}
	}

	@Override
	protected void tearDown() throws Exception {
		if ( sessionFactory != null ) {
			sessionFactory.close();
		}
	}


	public void testJsonType() {
		sessionFactory.inTransaction(session -> {
			
			String[] names = {"John", "Lisa"};

			Map<String, String> stringMap = new HashMap<>();
			stringMap.put("John", "He");
			stringMap.put("Lisa", "She");

			Special special = new Special();
			special.setNames(names);
			special.setJsonProperty(new Detail("John", 180));
			special.setStringMap(stringMap);
			
			session.persist(special);

		});


		sessionFactory.inTransaction(session -> {

			session.createNativeQuery("SELECT * FROM special e WHERE e.json_property->>'name' = 'John'", Special.class)
					.getResultList()
					.forEach(special ->
							{
								assertEquals(Integer.valueOf(180), special.getJsonProperty().getHeight());
							}
					);

			session.createNativeQuery("SELECT * FROM special e WHERE e.string_map->>'John' = 'He'", Special.class)
					.getResultList()
					.forEach(special -> {
								assertEquals(Integer.valueOf(180), special.getJsonProperty().getHeight());
							}
					);
		});

	}

}

Map to PostgreSQL `object types`

We have 3 object types like this in DB:

create type my_struct as
(
    a varchar,
    b varchar
);

create type child_struct as
(
    num int,
    str varchar
);

create type parent_struct as
(
    id    int,
    child child_struct
);

And the table likes this:

create table special_struct
(
    id            bigint not null,
    parent_struct parent_struct,
    my_struct     my_struct,
    primary key (id)
);

Define an Entity:

@Entity
@Table( name = "special_struct" )
public class SpecialStruct {

  private Long id;

  private MyStruct myStruct;

  private MyParentStruct myParentStruct;


  public SpecialStruct() {
    // this form used by Hibernate
  }


  @Id
  @GeneratedValue(generator="increment")
  @GenericGenerator(name="increment", strategy = "increment")
  public Long getId() {
    return id;
  }

  private void setId(Long id) {
    this.id = id;
  }

  @JdbcTypeCode( SqlTypes.JSON )
  @Column(name = "my_struct")
  // https://in.relation.to/2023/02/13/hibernate-orm-62-composite-aggregates/
  public MyStruct getMyStruct() {
    return myStruct;
  }

  public void setMyStruct(MyStruct myStruct) {
    this.myStruct = myStruct;
  }


  @JdbcTypeCode( SqlTypes.JSON )
  @Column(name = "parent_struct")
  public MyParentStruct getMyParentStruct() {
    return myParentStruct;
  }

  public void setMyParentStruct(MyParentStruct myParentStruct) {
    this.myParentStruct = myParentStruct;
  }

}

Define Structs:

In the DB have 3 object types so in the Hibernate we also define 3 structs:

@Embeddable
@Struct(name = "my_struct")
public record MyStruct (
    @Column(name = "a")
    String attr2,
    @Column(name = "b")
    String attr1
) {

}

@Embeddable
@Struct(name = "child_struct")
public record ChildStruct(
    Integer num,
    String str
) {

}

@Embeddable
@Struct(name = "parent_struct")
public record MyParentStruct(
    Integer id,
    ChildStruct child

) {

}

The annotation @Embeddable and @Struct are required:

More information please refer:
- https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#embeddable-mapping-aggregate
- https://in.relation.to/2023/02/13/hibernate-orm-62-composite-aggregates/

Run the following test to check:

public class JsonStructTest extends TestCase {
	private SessionFactory sessionFactory;

	@Override
	protected void setUp() throws Exception {
		// A SessionFactory is set up once for an application!
		final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
				.build();
		try {

			sessionFactory = new MetadataSources( registry )
					.addAnnotatedClass(SpecialStruct.class)
					.buildMetadata()
					.buildSessionFactory();
		}
		catch (Exception e) {
			// The registry would be destroyed by the SessionFactory, but we had trouble building the SessionFactory
			// so destroy it manually.
			throw e;
//			StandardServiceRegistryBuilder.destroy( registry );
		}
	}

	@Override
	protected void tearDown() throws Exception {
		if ( sessionFactory != null ) {
			sessionFactory.close();
		}
	}

	public void testSpecialType() {
		// create a couple of events...
		sessionFactory.inTransaction(session -> {

			SpecialStruct specialStruct = new SpecialStruct();

			MyStruct myStruct = new MyStruct("My struct attribute a", "My struct attribute b");
			specialStruct.setMyStruct(myStruct);

			ChildStruct childStruct = new ChildStruct(15, "Hi I'm a child");
			MyParentStruct myParentStruct = new MyParentStruct(100, childStruct);
			specialStruct.setMyParentStruct(myParentStruct);

			session.persist(specialStruct);

		});


		sessionFactory.inTransaction(session -> {
			session.createQuery("SELECT e FROM SpecialStruct e WHERE e.myParentStruct.child.str = 'Hi I''m a child'", SpecialStruct.class)
					.getResultList()
					.forEach(specialStruct -> {
						assertEquals(Integer.valueOf(100), specialStruct.getMyParentStruct().id());
						assertEquals(Integer.valueOf(15), specialStruct.getMyParentStruct().child().num());
					}
					);


			session.createQuery("SELECT e FROM SpecialStruct e WHERE e.myStruct.attr1 = 'My struct attribute b'", SpecialStruct.class)
					.getResultList()
					.forEach(specialStruct -> {
								assertEquals("My struct attribute a", specialStruct.getMyStruct().attr2());
							}
					);

		});

	}

}

NOTE: with Embeddable and Struct we can use the HQL/JPQL to query the result, but just with Hibernate 6.2 or higher else we can use the Native query.
My hibernate.properties file

# Database connection settings

hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://localhost:5432/hibernate-test
hibernate.connection.username=user
hibernate.connection.password=password

# don't need
#hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Echo all executed SQL to console
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.highlight_sql=true

# Automatically export the schema
hibernate.hbm2ddl.auto=update

Footer

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Using Hibernate to map a JSON to PostgreSQL

* Map to PostgreSQL `jsonb`

Map to PostgreSQL `object types`

Clone this wiki locally

Using Hibernate to map a JSON to PostgreSQL

* Map to PostgreSQL jsonb

Map to PostgreSQL object types

Clone this wiki locally

* Map to PostgreSQL `jsonb`

Map to PostgreSQL `object types`