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

(Last Updated On: January 16, 2018)

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 answer it using a blog post.

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

You don't have to create all these types manually. You can simply get them via Maven Central using the following dependency:

1
2
3
4
5
<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.

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

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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

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

  1. Hey i am getting following error on saving the JsonNode property into json type column in mysql db.

    “”2018-10-02 05:33:14 [http-nio-8080-exec-3] DEBUG org.hibernate.SQL –
    /* update
    com.models.Employee */ update
    employees
    set
    properties=?
    where
    id=?
    “”2018-10-02 05:33:14 [http-nio-8080-exec-3] TRACE o.h.type.descriptor.sql.BasicBinder – binding parameter [19] as [OTHER] – [{“title”:”High-Performance Java Persistence”}]
    “”2018-10-02 05:33:14 [http-nio-8080-exec-3] INFO o.h.e.j.b.internal.AbstractBatchImpl – HHH000010: On release of batch it still contained JDBC statements
    “”2018-10-02 05:33:14 [http-nio-8080-exec-3] WARN o.h.e.jdbc.spi.SqlExceptionHelper – SQL Error: 0, SQLState: S1009
    “”2018-10-02 05:33:14 [http-nio-8080-exec-3] ERROR o.h.e.jdbc.spi.SqlExceptionHelper – Invalid argument value: java.io.NotSerializableException
    “”2018-10-02 05:33:18 [http-nio-8080-exec-3] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] – Servlet.service() for servlet [dispatcherServlet] in context with path [/api] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not update: [com.hr.models.Employee#171]; nested exception is org.hibernate.exception.GenericJDBCException: could not update: [com.hr.models.Employee#171]] with root cause
    “java.io.NotSerializableException: com.fasterxml.jackson.databind.node.ObjectNode
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1184)
    at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
    at com.mysql.jdbc.PreparedStatement.setSerializableObject(PreparedStatement.java:3824)
    at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3748)
    at com.mysql.jdbc.JDBC42PreparedStatement.setObject(JDBC42PreparedStatement.java:99)
    at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3580)
    at com.mysql.jdbc.JDBC42PreparedStatement.setObject(JDBC42PreparedStatement.java:83)

    i am using following dependency with hibernate 5.0

    <dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-5</artifactId>
    <version>2.3.0</version>
    </dependency>

    1. Send a Pull Request to the hibernate-types project that demonstrates your issue. Use one of the existing test cases as a template.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.