Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly.
Hypersistence Optimizer is that tool!
Introduction
In this article, I want to show you how you can map a String JPA property to a JSON database column using the hibernate-types open-source project.
Although it’s probably more common to use a JsonNode or POJO (Plain Old Java Object) on the Java side, the hibernate-types framework is very flexible and allows you to use a String JPA property type to represent a JSON structure.
The properties JPA attribute encodes various book-related properties in a JSON String object. From the JPA @Column definition, we can see that the associated database column is of the type jsonb.
Now, since Hibernate does not provide a native Type to handle JSON database columns, we need to need to use the JsonBinaryType offered by the hibernate-types library.
To use the hibernate-types library in your project, just add the following Maven dependency:
If you’re using an older version of Hibernate, go to the hibernate-types GitHub repository and find the matching hibernate-types dependency for your current Hibernate version.
Hibernate generates the following SQL INSERT statement:
INSERT INTO book (
isbn,
properties,
id
)
VALUES (
'978-9730228236',
'{"title":"High-Performance Java Persistence","author":"Vlad Mihalcea","publisher":"Amazon","price":44.99}',
1
)
The JsonBinaryType binds the JSON String using the setObject method of the PreparedStatement object.
Notice the Fluent-style API used when creating the Book entity. For more details about building entities using a Fluent-style API, check out this article.
Now, when fetching the previously persisted Book entity:
Book book = entityManager
.unwrap(Session.class)
.bySimpleNaturalId(Book.class)
.load("978-9730228236");
assertTrue(book.getProperties().contains("\"price\": 44.99"));
We can see that the properties attribute is properly populated by the JsonBinaryType.
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Although creating a custom Hibernate Type is straightforward, it’s much more convenient to use the hibernate-types open-source project since you only need to add one dependency and specify which custom Type you want to use via the @TypeDef annotation.
16 Comments on “How to map a String JPA property to a JSON column using Hibernate”
I think this article is mistaken in its premise that the hibernate-types library is needed in order to convert a JSON column into a String. Both Postgres and MySQL JDBC drivers will automatically convert JSON columns to a String, meaning the @Type and columnDefinition=… are unnecessary. If additional conversion is needed, such as to a POJO or Jackson’s JsonNode structure, the @Convert JPA annotation can be used. I would be interested if there are other features the hibernate-types provides beyond that for JSON columns.
Mapping it as a String will just not work with Hibernate and PostgreSQL as it will call get/setString instead of get/setObject in the JDBC Driver.
Also, the Hibernate-Types project works even if you have a Pojo instead of String, and it does that for Oracle, SQL Server, PostgreSQL, and MySQL.
Being used by hundreds of thousands of developers, it provides better guarantees than most in-house converters. Also, most Types work as JPQL parameters and result-set mappings too, which is not the case for JPA Converters.
Thanks for the great tip! It indeed helps a lot. One thing I would like to ask, is it possible to have the json payload structured? For my project, I would like to have fixed structure for json, e.g. (title, isbn, date_issued), no other fields. Can we restrict the payload in some way?
I am getting an exception as given below when I try to retrieve the data by using the findById(id) method.
org.springframework.dao.InvalidDataAccessApiUsageException: The given string value: {“price”: 25, “title”: “ashar”, “author”: “Vlad Mihalcea”, “publisher”: “Amazon”} cannot be transformed to Json object;
I am able to add Postgres column type as jsonb. But it contains the escape character() also in the DB. Due to this not able to query through the json. The JSON data inside the table is as given below.
We achieved this by registering custom functions @json_extract@ and @json_extract_text@ and implemented custom dialects for PostgreSQL, MySQL and Oracle.
Registering the database-specific functions can be done. But there are JSON operators that won’t work. Eventually, yes, this should be supported by Hibernate. Meanwhile, you can use the hibernate-types library.
Yes, json operators are database specific so can’t be supported. That’s why we choose to create custom functions json_extract and json_extract_text which are actually not native functions (in some db they are) but maps to the equivalent native functions.
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
The SQL Master Class for Java Developers training is aimed to level up your SQL skills with techniques such as Window Functions, recursive queries, Pivoting, JSON processing, and many other database querying features supported by Oracle, SQL Server, MySQL, or PostgreSQL.
I think this article is mistaken in its premise that the hibernate-types library is needed in order to convert a JSON column into a String. Both Postgres and MySQL JDBC drivers will automatically convert JSON columns to a String, meaning the @Type and columnDefinition=… are unnecessary. If additional conversion is needed, such as to a POJO or Jackson’s JsonNode structure, the @Convert JPA annotation can be used. I would be interested if there are other features the hibernate-types provides beyond that for JSON columns.
Mapping it as a
Stringwill just not work with Hibernate and PostgreSQL as it will callget/setStringinstead ofget/setObjectin the JDBC Driver.Also, the
Hibernate-Typesproject works even if you have a Pojo instead of String, and it does that for Oracle, SQL Server, PostgreSQL, and MySQL.Being used by hundreds of thousands of developers, it provides better guarantees than most in-house converters. Also, most Types work as JPQL parameters and result-set mappings too, which is not the case for JPA Converters.
Thanks for the great tip! It indeed helps a lot. One thing I would like to ask, is it possible to have the json payload structured? For my project, I would like to have fixed structure for json, e.g. (title, isbn, date_issued), no other fields. Can we restrict the payload in some way?
Yes, you can. Check out this article for more details.
I am getting an exception as given below when I try to retrieve the data by using the findById(id) method.
org.springframework.dao.InvalidDataAccessApiUsageException: The given string value: {“price”: 25, “title”: “ashar”, “author”: “Vlad Mihalcea”, “publisher”: “Amazon”} cannot be transformed to Json object;
It works just fine on my High-Performance Java Persistence GitHub repository, so just compare your code with mine and see why yours does not work.
What you see in pgAdmin is the conversion that the UI tool makes it so that the binary format is humanly readable.
Thanks for the article,
I am able to add Postgres column type as jsonb. But it contains the escape character() also in the DB. Due to this not able to query through the json. The JSON data inside the table is as given below.
“{\”title\”:\”qwert\”,\”author\”:\”Vlad Mihalcea\”,\”publisher\”:\”Amazon\”,\”price\”:25}”
So in the jsonb column, it’s getting added as string only. am I missing something?
Jsonb stores the data in binary form, so there will be no escape character. Check out the Hibernate Types GitHub repository for more details.
hi, is your plugin available for gradle?
What plugin are you talking about? This is just a jar dependency you can get from Maven Central, which works just fine with Gradle too.
Great article.
I think it should also address the difficulty for searching (like queries based on the json columns), and how this will be RDBMS specific.
The only option is to use a native SQL query, and you can achieve database portability like this.
We achieved this by registering custom functions @json_extract@ and @json_extract_text@ and implemented custom dialects for PostgreSQL, MySQL and Oracle.
You can find the code here https://github.com/axelor/axelor-development-kit/tree/master/axelor-core/src/main/java/com/axelor/db/hibernate
I think hibernate should add support for json fields (hibernate specific feature).
Registering the database-specific functions can be done. But there are JSON operators that won’t work. Eventually, yes, this should be supported by Hibernate. Meanwhile, you can use the hibernate-types library.
Yes, json operators are database specific so can’t be supported. That’s why we choose to create custom functions json_extract and json_extract_text which are actually not native functions (in some db they are) but maps to the equivalent native functions.