How to map Oracle JSON columns using JPA and Hibernate

Last modified:

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see how to map the Oracle JSON column storage types when using JPA and Hibernate.

My hibernate-types project has long been supporting JSON for PostgreSQL, and MySQL. However, while developing my new and awesome High-Performance SQL training, I realized that the current implementation does not handle Oracle JSON column types properly.

Luckily, the fix was easy, and starting with the 2.7.0 version you can now persist and fetch JSON attributes on Oracle using JPA and Hibernate.

How to map Oracle JSON columns using JPA and Hibernate @vlad_mihalcea https://t.co/8uJwu34okC pic.twitter.com/JuxTgIXxkK
— Java (@java) September 20, 2019

Oracle JSON storage

When using Oracle, you have two options to persist JSON objects. You can either use a VARCHAR column or a LOB column storage type.

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.

For large JSON documents, BLOB is preferred over CLOB because the latter requires 2 bytes for storing each character, therefore doubling the storage requirements.

Storing JSON as VARCHAR

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 VARCHAR2(4000)
  CONSTRAINT ENSURE_JSON CHECK (properties IS JSON)
)

Notice that the properties column type is VARCHAR2(4000) and we defined the ENSURE_JSON custom constraint which validates whether the properties column is storing a proper JSON object.

To map the book table to a JPA entity, we also have to decide how to handle the JSON column. Since the properties column is of the type VARCHAR, we can map it as a String:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    name = "json", 
    typeClass = JsonStringType.class
)
public class Book {

    @Id
    private Long id;

    @NaturalId
    private String isbn;

    @Type(type = "json")
    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);
    }
}

Notice that we are using a Fluent-style API for the setters, which will allow us to simplify the process of building an entity.

For more details about using a Fluent-style API entity builders, check out this article.

The JsonStringType is the same Hibernate Type we previously used for MySQL, and it’s being offered by the Hibernate Types project.

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()
);

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 =  
    '{   
        "title": "High-Performance Java Persistence",   
        "author": "Vlad Mihalcea",   
        "publisher": "Amazon",   
        "price": 44.99,   
        "url": "https://amzn.com/973022823X"
    }' 
WHERE 
    id = 1

You are not limited to using a String entity attribute. You can use a POJO as well, considering that the POJO properties match the JSON attributes:

This time, the properties entity attribute will be mapped like this:

@Type(type = "json")
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 we are using a String or a POJO on the JPA side.

Storing JSON as BLOB

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 us 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 BLOB JSON column to either a String or a POJO. In both cases, we need to use the JsonBlobType offered by the Hibernate Types project.

So, when using a String JPA entity attribute, the mapping looks as follows:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(name = "jsonb", typeClass = JsonBlobType.class)
public class Book {

    @Id
    private Long id;

    @NaturalId
    private String isbn;

    @Type(type = "jsonb")
    private String properties;

    //Getters, setters and utility methods omitted for brevity 
}

And, when using the BookProperties POJO, the Book entity is mapped like this:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(name = "jsonb", typeClass = JsonBlobType.class)
public class Book {

    @Id
    private Long id;

    @NaturalId
    private String isbn;

    @Type(type = "jsonb")
    private BookProperties properties;

    //Getters, setters and utility methods omitted for brevity 
}

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 has been enhanced over the time to accommodate a wide variety of database-specific types, as well as to support multiple relational database systems.

With the 2.7.0 release, Hibernate Types can now map JSON columns to JPA entities on all the Top 4 database systems: Oracle, MySQL, SQL Server, and PostgreSQL.

Follow @vlad_mihalcea