Hibernate Mapping Error: Could not write JSON: could not extract ResultSet

Viewed 522

Hey guys I have problem mapping my three table together using Hibernate. Not sure which part I mess up. Thanks

I have create three table:

CREATE TABLE `topic` (
   `topic_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(100) default null,
   PRIMARY KEY (`topic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `question` (
  `question_id` int unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int unsigned NOT NULL,
  `question` varchar(100) NOT NULL,
  `answer` varchar(100) NOT NULL,
  PRIMARY KEY (`question_id`),
  KEY `topic_id` (`topic_id`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`topic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `option` (
  `option_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(11) unsigned NOT NULL,
  `option` varchar(100) not null,
  PRIMARY KEY (`option_id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `question` (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The three entity: ---- Topic

@Entity
@Table(name="topic")
public class Topic {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="topic_id")
    private int id;
    
    @Column(name="name")
    private String name;
    
    @OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL)
    @JoinColumn(name="topic_id")
    private List<Question> questions;
    
    // Define Constructors
    public Topic() {
    }

    public Topic(String name) {
        this.name = name;
    }

    // Define Getter/Setter
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Question> getQuestions() {
        return questions;
    }

    public void setQuestions(List<Question> questions) {
        this.questions = questions;
    }   

}

--- Question

@Entity
@Table(name="question")
public class Question {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="question_id")
    private int id;
    
    @Column(name="question")
    private String question;
    
    @Column(name="answer")
    private String answer;
    
    @Column(name="topic_id")
    private int topic_id;
    
    @OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL)
    @JoinColumn(name="question_Id")
    private List<Option> options;
    
    // Define Constructors
    public Question() {}

    public Question(String question, String answer, int topic_id) {
        this.question = question;
        this.answer = answer;
        this.topic_id = topic_id;
    }

    // Define Getter/Setter
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getQuestion() {
        return question;
    }

    public void setQuestion(String question) {
        this.question = question;
    }

    public String getAnswer() {
        return answer;
    }

    public void setAnswer(String answer) {
        this.answer = answer;
    }

    public int getTopic_id() {
        return topic_id;
    }

    public void setTopic_id(int topic_id) {
        this.topic_id = topic_id;
    }

    public List<Option> getOptions() {
        return options;
    }

    public void setOptions(List<Option> options) {
        this.options = options;
    }
}

--- Option

@Entity
@Table(name="option")
public class Option {
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="option_id")
    private int id;
    
    @Column(name="option")
    private String option;
    
    @Column(name="question_id")
    private int question_id;
    
    // Define Constructors
    public Option() {}

    public Option(String option, int question_id) {
        this.option = option;
        this.question_id = question_id;
    }

    // Define Getter/Setter
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getOption() {
        return option;
    }

    public void setOption(String option) {
        this.option = option;
    }

    public int getQuestion_id() {
        return question_id;
    }

    public void setQuestion_id(int question_id) {
        this.question_id = question_id;
    }
}

-- I test in Postman: GET http://localhost:8080/api/topic/1 the message show

"message": "Could not write JSON: could not extract ResultSet; nested exception is com.fasterxml.jackson.databind.JsonMappingException: could not extract ResultSet (through reference chain: com.generator.ExamMaker.entity.Topic["questions"]->org.hibernate.collection.internal.PersistentBag[0]->com.generator.ExamMaker.entity.Question["options"])",

-- Eclipse console message show

2021-05-02 20:51:42.825  WARN 2452 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2021-05-02 20:51:42.825 ERROR 2452 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option options0_ where options0_.question_id=1' at line 1
2021-05-02 20:51:42.830  WARN 2452 --- [nio-8080-exec-2] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: could not extract ResultSet; nested exception is com.fasterxml.jackson.databind.JsonMappingException: could not extract ResultSet (through reference chain: com.generator.ExamMaker.entity.Topic["questions"]->org.hibernate.collection.internal.PersistentBag[0]->com.generator.ExamMaker.entity.Question["options"])]
2021-05-02 20:54:44.578  WARN 2452 --- [l-2 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-2 - Retrograde clock change detected (housekeeper delta=29s614ms), soft-evicting connections from pool.
0 Answers
Related