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?

Insert details about how the information is going to be processed

DOWNLOAD NOW