Arshad Ali Arshad Ali - 5 months ago 51
Java Question

JPA mapping causes error in create table

I want to map a relation between Question and options.

@Entity
public class Question implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long questionId;
private String questionText;
private Integer questionChoices;
private Integer questionNumbers;

@ManyToOne
@JoinColumn(name = "quizId")
private Quiz quiz;

@OneToMany(mappedBy = "question", cascade = CascadeType.ALL)
private Set<Option> options = new HashSet<Option>();

@OneToOne
@JoinColumn(name = "answerId")
private Answer answer;
// omitting setters and getters hashcode equals methods
}


and

@Entity
public class Option implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long optionId;
private String optionText;
private static final long serialVersionUID = 1L;
@ManyToOne
@JoinColumn(name = "questionId")
private Question question;
// omitting setters and getters hashcode equals methods
}


but when I run the app I get console out put as

ERROR: 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 drop foreign key FK_sagu3nkb7af9pwcyqwdp6rggw' at line 1
Hibernate: alter table Question drop foreign key FK_reyly4qc111x1v426cvbyrvld
Hibernate: alter table Question drop foreign key FK_32arg10vsx8ch1qjngdd7mv0d
Hibernate: drop table if exists Option
Mar 03, 2016 7:54:07 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: HHH000389: Unsuccessful: drop table if exists Option
Hibernate: drop table if exists Question
Mar 03, 2016 7:54:07 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: 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' at line 1
Hibernate: create table Admin (adminId bigint not null auto_increment, adminEmail varchar(255), adminPassword varchar(255), primary key (adminId))
Hibernate: create table Answer (answerId bigint not null auto_increment, answerCorrect varchar(255), primary key (answerId))
Hibernate: create table History (historyId bigint not null auto_increment, historyCorrect integer, historyDate varchar(255), historyLevel integer, historyScore integer, historyWrong integer, primary key (historyId))
Hibernate: create table Option (optionId bigint not null auto_increment, optionText varchar(255), questionId bigint, primary key (optionId))
Mar 03, 2016 7:54:10 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: HHH000389: Unsuccessful: create table Option (optionId bigint not null auto_increment, optionText varchar(255), questionId bigint, primary key (optionId))
Hibernate: create table Question (questionId bigint not null auto_increment, questionChoices integer, questionNumbers integer, questionText varchar(255), answerId bigint, quizId bigint, primary key (questionId))
Mar 03, 2016 7:54:10 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: 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 (optionId bigint not null auto_increment, optionText varchar(255), questi' at line 1
Hibernate: create table Quiz (quizId bigint not null auto_increment, quizCorrectAnswers integer, quizDate varchar(255), quizTag varchar(255), quizTime varchar(255), quizTitle varchar(255), quizTotalQuestions integer, quizWrongAnswers integer, primary key (quizId))
Hibernate: create table Rank (rankId bigint not null auto_increment, time varchar(255), userScore integer, primary key (rankId))
Hibernate: create table User (userId bigint not null auto_increment, userEmail varchar(255), userGender varchar(255), userName varchar(255), userPassword varchar(255), rankId bigint, primary key (userId))
Hibernate: create table histories_quizs (history_id bigint not null, quiz_id bigint not null, primary key (history_id, quiz_id))
Hibernate: create table users_histories (user_id bigint not null, history_id bigint not null, primary key (user_id, history_id))
Hibernate: alter table Option add constraint FK_sagu3nkb7af9pwcyqwdp6rggw foreign key (questionId) references Question (questionId)
Mar 03, 2016 7:54:12 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: HHH000389: Unsuccessful: alter table Option add constraint FK_sagu3nkb7af9pwcyqwdp6rggw foreign key (questionId) references Question (questionId)
Mar 03, 2016 7:54:12 PM org.hibernate.tool.hbm2ddl.SchemaExport perform
ERROR: 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 add constraint FK_sagu3nkb7af9pwcyqwdp6rggw foreign key (questionId) refe' at line 1


is there any mistake in mapping Please.

Answer

Your mapping is perfectly alright. The problem is with the name of the entity: Option. The word Option is a reserved/keyword in MySQL, and hence the problem.

You can see the error statement in your logs: ERROR: HHH000389: Unsuccessful: create table Option (....

That indicates that the Option table creation is failing, and hence the other failures like addition of foreign key constraints. Give the Option table a different table name, for example:

@Entity @Table(name="ANSWER_OPTIONS")

That should solve your problem.