How to store schema-less EAV (Entity-Attribute-Value) data using JSON and Hibernate

Last modified:

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

One of my Twitter followers has recently asked me about a way of storing EAV (Entity-Attribute-Value) data using JPA and Hibernate, and, because this is a very good question, I decided to turn into a blog post.

Kudos to @vlad_mihalcea, the Hibernate Types library saved me tons of work yesterday by automagically mapping the PostgreSQL jsonb column to a POJO. Brilliant work 💪
— Tomasz Knyziak (@TomaszKnyziak) September 18, 2019

In this article, you are going to see how you can use JSON types to store schema-less EAV data.

Domain Model

Let’s assume we have an online book store, and books are modeled via the Book entity:

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

    @Id
    @GeneratedValue
    private Long id;

    @NaturalId
    private String isbn;

    @Type( type = "jsonb-node" )
    @Column(columnDefinition = "jsonb")
    private JsonNode properties;

    //Getters and setters omitted for brevity
}

The Book has a unique isbn attribute, hence it’s mapped as a @NaturalId.

For more details about the @NaturalId annotation, check out this article.

The properties attribute is of type JsonNode which contains the EAV data structure.

Because Hibernate does not support JSON types natively, we need a JsonNodeBinaryType to handle the Jackson-specific JsonNode object type.

JsonNodeBinaryType

The JsonNodeBinaryType source code is simple since all the work is done by the Java and SQL Type descriptors:

public class JsonNodeBinaryType
    extends AbstractSingleColumnStandardBasicType<JsonNode> {

    public JsonNodeBinaryType() {
        super( 
            JsonBinarySqlTypeDescriptor.INSTANCE, 
            JsonNodeTypeDescriptor.INSTANCE 
        );
    }

    public String getName() {
        return "jsonb-node";
    }
}

To visualize the Java and SQL type descriptor object hierarchy, check out the following class diagram:

Luckily, we don’t need to implement all those types since Hibernate takes care of that on our behalf. However, we still need to implement the JsonBinarySqlTypeDescriptor and JsonNodeTypeDescriptor classes.

JsonBinarySqlTypeDescriptor

The JsonBinarySqlTypeDescriptor is responsible for binding the JsonNode to the JDBC PreparedStatement.

public class JsonBinarySqlTypeDescriptor 
    extends AbstractJsonSqlTypeDescriptor {

    public static final JsonBinarySqlTypeDescriptor INSTANCE = 
        new JsonBinarySqlTypeDescriptor();

    @Override
    public <X> ValueBinder<X> getBinder(
            final JavaTypeDescriptor<X> javaTypeDescriptor
        ) {
        return new BasicBinder<X>(javaTypeDescriptor, this) {
            @Override
            protected void doBind(
                    PreparedStatement st, 
                    X value, 
                    int index, 
                    WrapperOptions options
                ) throws SQLException {
                st.setObject(
                    index, 
                    javaTypeDescriptor.unwrap(
                        value, 
                        JsonNode.class, 
                        options
                    ), 
                    getSqlType()
                );
            }

            @Override
            protected void doBind(
                    CallableStatement st, 
                    X value, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                st.setObject(
                    name, 
                    javaTypeDescriptor.unwrap(
                        value, 
                        JsonNode.class, 
                        options
                    ), 
                    getSqlType()
                );
            }
        };
    }
}

You don't have to create all these types manually. You can simply get them via Maven Central using the following dependency:
<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>
For more info, check out the hibernate-types open-source project.

The AbstractJsonSqlTypeDescriptor source code can be visualized in this article.

JsonNodeTypeDescriptor

The JsonNodeTypeDescriptor is responsible for transforming the JsonNode into various representations which might be used by the underlying JDBC Driver during binding parameters or fetching from the JSON object from the underlying ResultSet.

public class JsonNodeTypeDescriptor
        extends AbstractTypeDescriptor<JsonNode> {

    public static final JsonNodeTypeDescriptor INSTANCE = 
        new JsonNodeTypeDescriptor();

    public JsonNodeTypeDescriptor() {
        super( 
            JsonNode.class, 
            new MutableMutabilityPlan<JsonNode>() {
                @Override
                protected JsonNode deepCopyNotNull(
                        JsonNode value
                    ) {
                    return JacksonUtil.clone(value);
                }
            }
        );
    }

    @Override
    public boolean areEqual(JsonNode one, JsonNode another) {
        if ( one == another ) {
            return true;
        }
        if ( one == null || another == null ) {
            return false;
        }
        return 
            JacksonUtil.toJsonNode(
                JacksonUtil.toString(one)
            ).equals(
                JacksonUtil.toJsonNode(
                    JacksonUtil.toString(another)
                )
            );
    }

    @Override
    public String toString(JsonNode value) {
        return JacksonUtil.toString(value);
    }

    @Override
    public JsonNode fromString(String string) {
        return JacksonUtil.toJsonNode(string);
    }

    @SuppressWarnings({ "unchecked" })
    @Override
    public <X> X unwrap(
            JsonNode value, 
            Class<X> type, 
            WrapperOptions options
        ) {
        if ( value == null ) {
            return null;
        }
        if ( String.class.isAssignableFrom( type ) ) {
            return (X) toString(value);
        }
        if ( JsonNode.class.isAssignableFrom( type ) ) {
            return (X) JacksonUtil.toJsonNode(toString(value));
        }
        throw unknownUnwrap( type );
    }

    @Override
    public <X> JsonNode wrap(X value, WrapperOptions options) {
        if ( value == null ) {
            return null;
        }
        return fromString(value.toString());
    }

}

That’s it!

Testing time

When persisting the following Book entity:

Book book = new Book();
book.setIsbn( "978-9730228236" );
book.setProperties(
    JacksonUtil.toJsonNode(
        "{" +
        "   \"title\": \"High-Performance Java Persistence\"," +
        "   \"author\": \"Vlad Mihalcea\"," +
        "   \"publisher\": \"Amazon\"," +
        "   \"price\": 44.99" +
        "}"
    )
);

entityManager.persist( book );

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
)

We can also fetch the Book entity and even modify it, and Hibernate will take care of all the persistence logic:

Session session = entityManager.unwrap( Session.class );

Book book = session
    .bySimpleNaturalId( Book.class )
    .load( "978-9730228236" );

LOGGER.info( "Book details: {}", book.getProperties() );

book.setProperties(
    JacksonUtil.toJsonNode(
        "{" +
        "   \"title\": \"High-Performance Java Persistence\"," +
        "   \"author\": \"Vlad Mihalcea\"," +
        "   \"publisher\": \"Amazon\"," +
        "   \"price\": 44.99," +
        "   \"url\": \"https://www.amazon.com/High-Performance-Java-Persistence-Vlad-Mihalcea/dp/973022823X/\"" +
        "}"
    )
);

When running the test case above, Hibernate generates the following SQL statements:

SELECT  b.id AS id1_0_
FROM    book b
WHERE   b.isbn = '978-9730228236'

SELECT  b.id AS id1_0_0_ ,
        b.isbn AS isbn2_0_0_ ,
        b.properties AS properti3_0_0_
FROM    book b
WHERE   b.id = 1

-- Book details: {"price":44.99,"title":"High-Performance Java Persistence","author":"Vlad Mihalcea","publisher":"Amazon"}

UPDATE 
    book 
SET 
    properties = '{"title":"High-Performance Java Persistence","author":"Vlad Mihalcea","publisher":"Amazon","price":44.99,"url":"https://www.amazon.com/High-Performance-Java-Persistence-Vlad-Mihalcea/dp/973022823X/"}' 
WHERE 
    id = 1

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Hibernate types are great! Even if Hibernate comes with a great variety of types, even Java 1.8 Date/Time, you can easily map custom types like ARRAY or JSON.

For JSON, not only that you can map JSON structures as Java objects as we saw in a previous article, but you benefit from schema-less structures as well.

Follow @vlad_mihalcea

FREE EBOOK