CODEWITHSUNDEEP
javajpaspring-data-jpa
Arshad Ali
Arshad Ali

Reputation: 3274

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.

Upvotes: 1

Views: 1426

Answers (2)

MooMoo
MooMoo

Reputation: 83

I found the same issue occurs for 'Group'. Renamed it to 'Groups' and it works. Thanks for the tip Mubin.

Upvotes: 0

Mubin
Mubin

Reputation: 4239

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.

Upvotes: 5

Related Questions