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 hibernate-types
project provides a generic JsonType
that works with Oracle, SQL Server, PostgreSQL, and MySQL, and starting with the 2.16 version, the JsonType
can 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 hibernate-types
project.
If you’re using Hibernate 5 or 4, then your JPA entity mapping will look as follows:
@Entity(name = "Book") @Table(name = "book") public class Book { @Id private Long id; @NaturalId private String isbn; @Type(type = "com.vladmihalcea.hibernate.type.json.JsonType") private String properties; public Long getId() { return id; } public Book setId(Long id) { this.id = id; return this; } public String getIsbn() { return isbn; } public Book setIsbn(String isbn) { this.isbn = isbn; return this; } public String getProperties() { return properties; } public Book setProperties(String properties) { this.properties = properties; return this; } public JsonNode getJsonNodeProperties() { return JacksonUtil.toJsonNode(properties); } }
Or, if you are using Hibernate 6, the @Type
mapping will look as follows:
@Type(JsonType.class) 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 = "com.vladmihalcea.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 = "com.vladmihalcea.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(type = "com.vladmihalcea.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 Hibernate Types 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(type = "com.vladmihalcea.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(type = "com.vladmihalcea.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 hibernate-types
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 the Top 4 database systems: Oracle, MySQL, SQL Server, and PostgreSQL, but you can do that with the same JsonType
annotation.
Cool, right?
