Saturday, May 28, 2016

JPA Json Serialization/Deserialization to/from MySQL

I'm using spring boot with JPA and a MySQL backend database with several JSON columns.  The following is the JSON converter class used to save and retrieve data to the JSON column.  This article helped a lot.

This worked with:
Windows 7
Oracle Java 1.8.0_66
MySql 5.7.10.0
Spring Boot 1.3.1.RELEASE

Steps:
  1. Create a POJO to represent the JSON object that will be saved to the column.
    package com.cherryshoe.model;
    
    /*
     * Represents a "documents" database column that holds json document data
     */
    public class JsonDocuments {
        private String docId;
        private String docName;
    
        public String getDocId() {
            return docId;
        }
    
        public void setDocId(String docId) {
            this.docId = docId;
        }
    
        public String getDocName() {
            return docName;
        }
    
        public void setDocName(String docName) {
            this.docName = docName;
        }
    
        public JsonDocuments() {
            super();
        }
    
        public JsonDocuments(String docId, String docName) {
            super();
            this.docId = docId;
            this.docName = docName;
        }
    
        @Override
         public int hashCode() {
          ...
         }
    
        @Override
         public boolean equals(Object obj) {
          ...
         }
    
        @Override
        public String toString() {
            return "JsonDocuments [docId=" + docId + ", docName=" + docName + "]";
        }
    
    }
  2. Create a Converter Class that implements AttributerConverter.

    • Annotate the class with JPA converter annotation
    • I need to save a list of documents so the type argument to AttributeConverter is List<JsonDocuments> vs JsonDocuments
    • We are using the Jackson JSON library to serialize/deserialize POJO's to JSON and vice versa.  ObjectMapper is thread safe so it is declared as a static variable in the JpaJsonDocumentsConverter class.
    • Override convertToDatabaseColumn and convertToEntityAttribute methods with objectMapper writeValue and readValue calls
    package com.cherryshoe.utils;
    
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.List;
    
    import javax.persistence.AttributeConverter;
    import javax.persistence.Converter;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.cherryshoe.model.JsonDocuments;
    
    import com.fasterxml.jackson.core.JsonProcessingException;
    import com.fasterxml.jackson.databind.ObjectMapper;
    
    @Converter
    public class JpaJsonDocumentsConverter implements
            AttributeConverter<List<JsonDocuments>, String> {
    
        // ObjectMapper is thread safe
        private final static ObjectMapper objectMapper = new ObjectMapper();
    
        private Logger log = LoggerFactory.getLogger(getClass());
    
        @Override
        public String convertToDatabaseColumn(List<JsonDocuments> meta) {
            String jsonString = "";
            try {
                log.debug("Start convertToDatabaseColumn");
    
                // convert list of POJO to json
                jsonString = objectMapper.writeValueAsString(meta);
                log.debug("convertToDatabaseColumn" + jsonString);
    
            } catch (JsonProcessingException ex) {
                log.error(ex.getMessage());
            }
            return jsonString;
        }
    
        @Override
        public List<JsonDocuments> convertToEntityAttribute(String dbData) {
            List<JsonDocuments> list = new ArrayList<JsonDocuments>();
            try {
                log.debug("Start convertToEntityAttribute");
    
                // convert json to list of POJO
                list = Arrays.asList(objectMapper.readValue(dbData,
                        JsonDocuments[].class));
                log.debug("JsonDocumentsConverter.convertToDatabaseColumn" + list);
    
            } catch (IOException ex) {
                log.error(ex.getMessage());
            }
            return list;
        }
    }
    
  3. The JPA Entity class that contains the JSON column needs to have the @Convert annotation for the documents column.  NOTE:  You could also have in the JpaJsonDocumentsConverter class added the autoapply attribute to the @Converter annotation and set that to true to have JPA apply this converter to all entity attributes of type List<JsonDocuments>.
    @Entity
    @Table(name = "TABLE_WITH_DOCUMENTS")
    public class TableWithDocuments implements Serializable
    {
    
         /**
         *
         */
         private static final long serialVersionUID = 3781459465416706159L;
        
         ... entity attributes
        
         @Convert(converter = JpaJsonDocumentsConverter.class)
         private List<JsonDocuments> documents;
        
         ... entity attributes
    
     }

  4. Verified with an integration test on the Spring Data JPA Repository class that uses this entity that these methods worked.  I did notice that the documents column could not be null, so when you create the new TableWithDocuments record, the documents column had to be set as an empty JsonArray if there were no documents.  Adding in null checks in the JpaJsonDocumentsConverter  convertToDatabaseColumn and convertToEntityAttribute  did not help, it was inside the JPA code that would error out.

2 comments:

I appreciate your time in leaving a comment!