SQL and JPA error

Question

I have been trying to figure this out but I've been unable to find the problem that would have caused this error. Couldn't find any previous questions that used JPA and got the same error. This is the main class that is persisting the entity to the database:

package com.brandonemerson;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
public class Main {
 public static void main(String[] args) {
 ComicBook comicBook =new ComicBook("Moon Knight Meets Spiderman", "Moon Knight", "Marvel Comics", 1975);
 EntityManagerFactory emf =Persistence.createEntityManagerFactory("thePersistenceUnit");
 EntityManager em = emf.createEntityManager();
 EntityTransaction tx = em.getTransaction();
 tx.begin();
 em.persist(comicBook);
 tx.commit();
 em.close();
 emf.close();
 }
}

And this is the entity that is being persisted:

package com.brandonemerson;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
@Entity
public class ComicBook {
@Id @GeneratedValue
private Long id;
private String title;
private String character;
private String publisher;
private Integer year; 
public ComicBook(){
}
public ComicBook(String title, String character, String publisher, int year){
 setTitle(title);
 setCharacter(character);
 setPublisher(publisher);
 setYear(year);
}
public Long getId() {
 return id;
}
public void setId(Long id) {
 this.id = id;
}
public String getTitle() {
 return title;
}
public void setTitle(String title) {
 this.title = title;
}
public String getCharacter() {
 return character;
}
public void setCharacter(String character) {
 this.character = character;
}
public String getPublisher() {
 return publisher;
}
public void setPublisher(String publisher) {
 this.publisher = publisher;
}
public int getYear() {
 return year;
}
public void setYear(Integer year) {
 this.year = year;
}

}

I keep getting the below error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CHARACTER, PUBLISHER, TITLE, YEAR) VALUES (1651, 'Moon Knight', 'Marvel Comics',' at line 1

no matter what I change.

• Neil Stockton: Seems that your JPA provider doesn't auto-quote table/column identifiers that are also keywords. Some JPA providers do it for you so you don't have the hassle

Answer 1

Without having tried it out, I would assume that the SQL key word "character" is the problem here. You named a variable/field "character" - For this to work properly you will need to give the variable a different name, which is no SQL key word. Another option is to annotate the declaration with @Column(name="Other_than_character") private String character;

• Brandon Emerson: I just tried it and it worked, thanks, you're a life saver!

Answer 2

Issue is very simple you have named your table column as CHARACTER, you cannot use that as column name in your query as it is a reserved keyword in MySQL.

Solution is to rename the column to soemthing else which doesnt matches to any keywords in the MySQL keywords.

reference link: https://dev.mysql.com/doc/refman/5.5/en/keywords.html