Getting JSONArray from mysql database Using Spring boot JPA

Question

In my mysql table has column that store 'JSONArray'

This is a part of model class in spring-boot project.

public class SubQuestions implements Serializable{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "sub_questionId", nullable = false,columnDefinition = "INT(11) UNSIGNED")
    private Integer sub_questionId;

    private JSONArray answers;

}

already model class having empty constructor, constructor with all fields, getters & setters.

This is my SubQuestionsRepository interface.

public interface SubQuestionsRepository extends Serializable,JpaRepository<Questions,Integer>{

}

This is a part of my controller class.

public class SubQuestionsController implements Serializable{
 private SubQuestionsRepository subquestionsrepository;


    public SubQuestionsController(SubQuestionsRepository subquestionsrepository) {
        super();
        this.SubQuestionsRepository = subquestionsrepository;
    }

    @GetMapping("/getall")
    public  Collection<SubQuestions> getallnestedques(){
        return subquestionsrepository.getactiveques();
    }
}

but when I'm calling "getallnestedques()" method it gives following error.

There was an unexpected error (type=Internal Server Error, status=500).
could not deserialize; nested exception is org.hibernate.type.SerializationException: could not deserialize
org.springframework.orm.jpa.JpaSystemException: could not deserialize; nested exception is org.hibernate.type.SerializationException: could not deserialize
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMeth

How I solve this?

Answer 1

Could you try below solution?

Need to declare answers column as Lob as shown in below example:

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;

import org.json.JSONArray;

@Entity
public class SubQuestions implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "sub_questionId", nullable = false,columnDefinition = "INT(11) UNSIGNED")
    private Integer sub_questionId;

    @Lob
    @Column
    @Convert(converter = JSONArrayConverter.class)
    private JSONArray answers;
}

Attribute converter JSONArrayConverter to covert JSONArray object to String before storing into database and convert to JSONArray after reading value from database:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

import org.json.JSONArray;
import org.slf4j.LoggerFactory;

import ch.qos.logback.classic.Logger;

@Converter(autoApply = true)
public class JSONArrayConverter implements AttributeConverter<JSONArray, String> {

    private static final Logger logger = (Logger) LoggerFactory.getLogger(JSONArrayConverter.class);

    @Override
    public String convertToDatabaseColumn(JSONArray array)
    {
        String data = null;
        try
        {
            data = array.toString();
        }
        catch (final Exception e)
        {
            logger.error("JSON writing error", e);
        }

        return data;
    }

    @Override
    public JSONArray convertToEntityAttribute(String data)
    {
        JSONArray array = null;

        try
        {
            array = new JSONArray(data);
        }
        catch (final Exception e)
        {
            logger.error("JSON reading error", e);
        }

        return array;
    }
}