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?

The SubQuestionsRepository should extend JpaRepository<SubQuestions,Integer> rather JpaRepository<Questions,Integer>. Isn't it? and if you are using an annotation-based configuration with Spring then you should annotate Model classes, repositories and Controllers as well with @Entity, @Repository, and @Controller respectively. and mark the answers field in SubQuestions Model class with respective column names in the DB. The format of saving the answers in the DB would be more helpful when providing an answer. — ThilankaD
– ThilankaD, Commented Jun 5, 2021 at 12:51

pcsutar · Accepted Answer · 2021-06-05 13:50:49Z

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

Collectives™ on Stack Overflow

Getting JSONArray from mysql database Using Spring boot JPA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related