How to map Oracle JSON columns using JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how to map the Oracle JSON column storage types when using JPA and Hibernate.
The Hypersistence Utils project provides a generic JsonType
that works with Oracle, SQL Server, PostgreSQL, and MySQL, and it can even use the columnDefinition
attribute of the JPA @Column
annotation to determine the underlying database column type.
How to map Oracle JSON columns using JPA and Hibernate @vlad_mihalceahttps://t.co/8uJwu34okC pic.twitter.com/JuxTgIXxkK
— Java (@java) September 20, 2019
Storing JSON objects in an Oracle database
When using Oracle, you have three options to persist JSON objects.
Starting with Oracle 21c, there is a native JSON
data type that stores JSON objects in a binary format that’s optimized for processing JSON objects using the SQL:2016 or the Oracle-specific JSON query methods.
The advantage of using the native JSON data type is that the JSON object is parsed only once when executing the INSERT
or UPDATE
operation, and SELECT
queries can execute faster since the JSON object is already optimized for querying.
Prior to Oracle 21c, you could only store the unparsed JSON value in VARCHAR2
or LOB
columns type.
Storing JSON in the Oracle 21 native JSON column
Let’s consider we are developing an online book store, and so we need to use the following book
database table:
To create the book
table, we can use the following DDL statement:
CREATE TABLE book ( id NUMBER(19, 0) NOT NULL PRIMARY KEY, isbn VARCHAR2(15 char), properties JSON )
To map the book
table to a JPA entity, we can use the JsonType
from the Hypersistence Utils project.
For Hibernate 6, the mapping will look as follows:
@Entity(name = "Book") @Table(name = "book") public class Book { @Id private Long id; @NaturalId private String isbn; @Type(JsonType.class) private String properties; }
And for Hibernate 5, like this:
@Entity(name = "Book") @Table(name = "book") public class Book { @Id private Long id; @NaturalId private String isbn; @Type(type = "io.hypersistence.utils.hibernate.type.json.JsonType") private String properties; }
Notice that the
Book
entity uses a Fluent-style API for the setters, which will allow us to simplify the process of building an entity.For more details about using Fluent-style API entity builders, check out this article.
Now, when persisting a Book
entity:
entityManager.persist( new Book() .setId(1L) .setIsbn("978-9730228236") .setProperties(""" { "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon", "price": 44.99 } """ ) );
Hibernate generates the proper SQL INSERT statement:
INSERT INTO book ( isbn, properties, id ) VALUES ( '978-9730228236', [ 123, 10, 32, 32, 32, 34, 116, 105, 116, 108, 101, 34, 58, 32, 34, 72, 105, 103, 104, 45, 80, 101, 114, 102, 111, 114, 109, 97, 110, 99, 101, 32, 74, 97, 118, 97, 32, 80, 101, 114, 115, 105, 115, 116, 101, 110, 99, 101, 34, 44, 10, 32, 32, 32, 34, 97, 117, 116, 104, 111, 114, 34, 58, 32, 34, 86, 108, 97, 100, 32, 77, 105, 104, 97, 108, 99, 101, 97, 34, 44, 10, 32, 32, 32, 34, 112, 117, 98, 108, 105, 115, 104, 101, 114, 34, 58, 32, 34, 65, 109, 97, 122, 111, 110, 34, 44, 10, 32, 32, 32, 34, 112, 114, 105, 99, 101, 34, 58, 32, 52, 52, 46, 57, 57, 10, 125, 10 ], 1 )
When fetching the Book
entity via its natural identifier, we can see that Hibernate fetches the entity just fine:
Book book = entityManager .unwrap(Session.class) .bySimpleNaturalId(Book.class) .load("978-9730228236"); assertEquals( "High-Performance Java Persistence", book.getJsonNodeProperties().get("title").asText() );
We can also change the JSON entity property:
book.setProperties(""" { "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon", "price": 44.99, "url": "https://amzn.com/973022823X" } """ );
And, Hibernate will issue the proper SQL UPDATE statement:
UPDATE book SET properties = [ 123, 10, 32, 32, 32, 34, 116, 105, 116, 108, 101, 34, 58, 32, 34, 72, 105, 103, 104, 45, 80, 101, 114, 102, 111, 114, 109, 97, 110, 99, 101, 32, 74, 97, 118, 97, 32, 80, 101, 114, 115, 105, 115, 116, 101, 110, 99, 101, 34, 44, 10, 32, 32, 32, 34, 97, 117, 116, 104, 111, 114, 34, 58, 32, 34, 86, 108, 97, 100, 32, 77, 105, 104, 97, 108, 99, 101, 97, 34, 44, 10, 32, 32, 32, 34, 112, 117, 98, 108, 105, 115, 104, 101, 114, 34, 58, 32, 34, 65, 109, 97, 122, 111, 110, 34, 44, 10, 32, 32, 32, 34, 112, 114, 105, 99, 101, 34, 58, 32, 52, 52, 46, 57, 57, 44, 10, 32, 32, 32, 34, 117, 114, 108, 34, 58, 32, 34, 104, 116, 116, 112, 115, 58, 47, 47, 97, 109, 122, 110, 46, 99, 111, 109, 47, 57, 55, 51, 48, 50, 50, 56, 50, 51, 88, 34, 10, 125, 10 ] WHERE id = 1
And, we are not limited to using a String
entity attribute. We can use a type-safe POJO, considering that the POJO properties match the JSON attributes:
This time, the properties
entity attribute will be mapped like this:
@Type(type = "io.hypersistence.utils.hibernate.type.json.JsonType") private BookProperties properties;
Using a POJO instead of a String-based JSON attribute allows us to simplify the read and write operations on the application side.
Notice how nicely we can build a Book
entity instance thanks to the Fluent-style API employed by both the entity and the POJO class:
entityManager.persist( new Book() .setId(1L) .setIsbn("978-9730228236") .setProperties( new BookProperties() .setTitle("High-Performance Java Persistence") .setAuthor("Vlad Mihalcea") .setPublisher("Amazon") .setPrice(44.99D) ) );
Changing the properties
entity attribute is also much simpler when using a POJO:
Book book = entityManager .unwrap(Session.class) .bySimpleNaturalId(Book.class) .load("978-9730228236"); book.getProperties().setUrl( "https://amzn.com/973022823X" );
The SQL statements are the same no matter whether we are using a String
or a POJO on the JPA side.
Storing JSON as VARCHAR
If the JSON document doesn’t exceed 4000 bytes, then it’s better to use a VARCHAR2(4000)
column type. If the JSON document size is between 4000 and 32767 bytes, you can use a VARCHAR2(32767)
column type instead.
The VARCHAR2(32767)
column storage is an extended data type and uses LOB
behind the scenes. The first 3500 bytes are stored inside the table row, so for JSON documents not exceeding 3500 bytes, there is a little performance impact for using VARCHAR2(32767)
instead of VARCHAR2(4000). However, for larger JSON documents, storing and fetching the document from the underlying LOB storage is going to be slower than reading and writing from the inlined table row storage.
When using a VARCHAR2
column type to store our JSON object, our book
database table is going to look as follows:
To create the book
table, we can use the following DDL statement:
CREATE TABLE book ( id NUMBER(19, 0) NOT NULL PRIMARY KEY, isbn VARCHAR2(15 char), properties VARCHAR2(4000) CONSTRAINT ENSURE_JSON CHECK (properties IS JSON) )
To map the properties
attribute to a VARCHAR2
column, we can use the JsonType
from the hibernate-types
project. While we could also use the JsonStringType
, the JsonType
is a more convenient choice due to its portability:
@Type(type = "io.hypersistence.utils.hibernate.type.json.JsonType") @Column( columnDefinition = "VARCHAR2(4000) CONSTRAINT ENSURE_JSON CHECK (properties IS JSON)" ) private String properties
Notice that we need to provide the @Column
annotation so that the JsonType
can use the columnDefinition
attribute to determine the underlying database column type. If you are using the JsonStringType
, then you don’t have to use the JPA @Column
annotation.
Or, if you are using Hibernate 6, the @Type
mapping will look as follows:
@Type(JsonType.class) @Column( columnDefinition = "VARCHAR2(4000) CONSTRAINT ENSURE_JSON CHECK (properties IS JSON)" ) private String properties;
Now, when persisting a Book
entity:
entityManager.persist( new Book() .setId(1L) .setIsbn("978-9730228236") .setProperties(""" { "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon", "price": 44.99 } """ ) );
Hibernate generates the proper 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 )
When fetching the Book
entity via its natural identifier, we can see that Hibernate fetches the entity just fine:
Book book = entityManager .unwrap(Session.class) .bySimpleNaturalId(Book.class) .load("978-9730228236"); assertEquals( "High-Performance Java Persistence", book.getJsonNodeProperties().get("title").asText() );
When changing the JSON entity property:
book.setProperties(""" { "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon", "price": 44.99, "url": "https://amzn.com/973022823X" } """ );
Hibernate will issue the proper SQL UPDATE statement:
UPDATE book SET properties = '{ "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon", "price": 44.99, "url": "https://amzn.com/973022823X" }' WHERE id = 1
And, just like in the previous JSON data type example, we can also use a type-safe POJO for the properties
entity attribute:
@Type(JsonType.class) //For Hibernate 5 use this instead //@Type(type = "io.hypersistence.utils.hibernate.type.json.JsonType") @Column( columnDefinition = "VARCHAR2(4000) CONSTRAINT ENSURE_JSON CHECK (properties IS JSON)" ) private BookProperties properties;
Storing JSON as BLOB
For large JSON documents, if you are using an Oracle version that’s older than 21c, then the BLOB
column type is preferred over CLOB because the latter requires 2 bytes for storing each character, therefore doubling the storage requirements.
If our book
database table needs to accommodate very large JSON objects, then we need to use a BLOB column type instead:
To create the book
table, we can use the following DDL statement:
CREATE TABLE book ( id NUMBER(19, 0) NOT NULL PRIMARY KEY, isbn VARCHAR2(15 char), properties BLOB CONSTRAINT ENSURE_JSON CHECK (properties IS JSON) ) LOB (properties) STORE AS (CACHE)
Notice that we are using the
STORE AS (CACHE)
directive, which tells Oracle to place the LOB pages in the buffer cache, so that reads and writes are executed faster.
Just like in the previous case when we were using VARCHAR
, we can map the “ JSON column to either a String
or a POJO
. In both cases, we need to use the JsonBlobType
offered by the Hypersistence Utils project.
To map the properties
attribute to a BLOB
column, we can use the generic JsonType
. While we could also use the JsonBlobType
, the JsonType
is more convenient since it works just fine with SQL Server, MySQL, PostgreSQL, or H2:
@Type(JsonType.class) //For Hibernate 5 use this instead //@Type(type = "io.hypersistence.utils.hibernate.type.json.JsonType") @Column( columnDefinition = "BLOB CONSTRAINT ENSURE_JSON CHECK (properties IS JSON)" )
Again, notice that we had to use the @Column
annotation to provide the database column type to the JsonType
instance. If you are using the JsonBlobType
, then you can skip the @Column
annotation altogether.
And, when using the BookProperties
POJO, the Book
entity is mapped like this:
@Type(JsonType.class) //For Hibernate 5 use this instead //@Type(type = "io.hypersistence.utils.hibernate.type.json.JsonType") @Column( columnDefinition = "BLOB CONSTRAINT ENSURE_JSON CHECK (properties IS JSON)" ) private BookProperties properties;
When inserting the same Book
entity, Hibernate is going to execute the following SQL INSERT statement:
INSERT INTO book ( isbn, properties, id ) VALUES ( '978-9730228236', org.hibernate.engine.jdbc.internal.BinaryStreamImpl@7d78f3d5, 1 )
When setting the BLOB column on Oracle, Hibernate uses the BinaryStreamImpl
object which implements the Java InputStream
interface.
When changing the Book
entity, Hibernate will use the BinaryStreamImpl
object to update the BLOB
column:
UPDATE book SET properties = org.hibernate.engine.jdbc.internal.BinaryStreamImpl@24d61e4 WHERE id = 1
Notice that the UPDATE statement sets only the
BLOB
column, instead of setting all columns as it’s the case with the default entity update mechanism.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
The Hypersistence Utils project provides a lot of value to any project using JPA and Hibernate.
Not only that, it allows you to map JSON column types on all 4 most popular database systems: Oracle, MySQL, SQL Server, and PostgreSQL, but you can do that with the same JsonType
annotation.
Cool, right?

Hi Vlad!
We were using JsonType with Postgresql till today without any problem (and the entity type as map of values). Now we have a requirement on making our application compatible with SqlServer and Oracle. Since in SqlServer we have to use a nvarchar column i was wondering if there were necessary to use this json type or only been a string will be enough.
Would we have any advantages using this type for the three databases?
I don’t see the way to set the check for the json type (“isjson” for sqlserver and “property is json” for oracle).
Thanks in advance!
The
JsonType
works with PostgreSQL, Oracle, and SQL Server, so you don’t have to change the Hibernate Type if you change the DB. Without theJsonType
, you’d have to use different Hibernate Types for each DB.The DDL schema needs to be configured using a tool like FlywayDB where you provide the DB-specific DDL constraints.