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
Bookentity 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
BLOBcolumn, 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?
