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.