Getting org.hibernate.exception.SQLGrammarException while inserting objects through hibernate

Question

I have two tables 1) krs_question_bank 2) krs_options

     CREATE TABLE krs_question_bank (
        question_id INT(11) NOT NULL AUTO_INCREMENT,
        question VARCHAR(500) NOT NULL,
        course_id INT(11) NOT NULL,
        level_id INT(11) NOT NULL,
        PRIMARY KEY (question_id),
        INDEX fk_krs_course_id_course_idx (course_id),
        INDEX fk_krs_level_id_level_idx (level_id),
        CONSTRAINT fk_krs_course_id_course FOREIGN KEY (course_id) REFERENCES krs_course (course_id) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT fk_krs_level_id_level FOREIGN KEY (level_id) REFERENCES krs_level (level_id) ON UPDATE CASCADE ON DELETE CASCADE
     )
     COLLATE='utf8_general_ci'
     ENGINE=InnoDB 
     ;
     CREATE TABLE krs_options (
        options_id INT(11) NOT NULL AUTO_INCREMENT,
        option VARCHAR(100) NOT NULL,
        question_id INT(11) NOT NULL,
        answer INT(11) NOT NULL,
        PRIMARY KEY (options_id),
        INDEX fk_krs_question_bank_question_id_idx (question_id),
        CONSTRAINT fk_krs_question_bank_question_id FOREIGN KEY (question_id) REFERENCES krs_question_bank (question_id) ON UPDATE CASCADE ON DELETE CASCADE
     )
     COLLATE='utf8_general_ci'
     ENGINE=InnoDB
     ;

And I have two classes: 1. Question.java 2. Option.java

     package com.kr.vo;
     import java.util.List;
     import javax.persistence.CascadeType;
     import javax.persistence.Column;
     import javax.persistence.Embedded;
     import javax.persistence.Entity;
     import javax.persistence.FetchType;
     import javax.persistence.GeneratedValue;
     import javax.persistence.Id;
     import javax.persistence.JoinColumn;
     import javax.persistence.OneToMany;
     import javax.persistence.Table;     
     @Entity
     @Table(name="krs_question_bank")
     public class Question{
         @Id
         @GeneratedValue
         @Column(name = "question_id")
         private int questionId;         
         @Column(name = "question")
         private String questionText;         
         @Column(name = "course_id")
         private int course;         
         @Column(name = "level_id")
         private int level;         
         @OneToMany(cascade={CascadeType.ALL})
        @JoinColumn(name="question_id")
         private List<Option> options;     
     //getter and setter methods
    }

    package com.kr.vo;
    import java.util.List;
    import javax.persistence.Column;
    import javax.persistence.Embedded;
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import javax.persistence.OneToMany;
    import javax.persistence.Table;    
    @Entity
    @Table(name="krs_options")
    public class Option{
        @Id
        @GeneratedValue
        @Column(name ="options_id")
        private int optionId;        
        @Column(name = "option")
        private String optionText;        
        @Column(name = "answer")
        private int answer;            
        @ManyToOne
        @JoinColumn(name="question_id", 
                    insertable=false, updatable=false, 
                    nullable=false)
        private Question question;    
        //getter and setter methods
    }

While I am calling session.save(Question). Hibernate queries are generated but.. I am getting following error. Any idea how can i solve this error ?

 Hibernate: insert into krs_question_bank (course_id, level_id, question) values (?, ?, ?)
     Hibernate: insert into krs_options (answer, option) values (?, ?)
     Jun 10, 2017 3:54:07 PM org.apache.catalina.core.StandardWrapperValve invoke
     SEVERE: Servlet.service() for servlet [com.kr.servlets.AddQuestion] in context with path [/MyWebApp] threw exception
     org.hibernate.exception.SQLGrammarException: 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) values (1, 'james')' at line 1
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)

@Tahir Hussain Mir Query created through hibernate. I didn't write any query. When i called session.save(question); I am getting the above error. — santosh

Tahir Hussain Mir Tahir Hussain Mir · Accepted Answer · 2017-06-10T11:26:29

Option is the keyword in MYSQL server. Use different name for your table. Likewise, check all the names which are MYSQL keywords and rename them to some suitable names.

Getting org.hibernate.exception.SQLGrammarException while inserting objects through hibernate

1 Answers