Java persistence eclipselink mysql example: error in sql syntax

Question

I have this simple class Book:

package it.mysql.beginner;

import java.util.Date;


import javax.persistence.*;

@Entity
@Table(name = "books")
public class Book implements java.io.Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

@Column(unique = true)
private String isbn;

private String author;
private String title;
private boolean read;

/*@OneToOne(cascade = CascadeType.PERSIST)
private Kind kind;*/

private String type;


@Temporal(TemporalType.DATE)
private Date riconsegna;

public String getIsbn() {
    return isbn;
}

public void setIsbn(String isbn) {
    this.isbn = isbn;
}

public String getAuthor() {
    return author;
}

public void setAuthor(String author) {
    this.author = author;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public boolean isRead() {
    return read;
}

public void setRead(boolean read) {
    this.read = read;
}

public Date getRiconsegna() {
    return riconsegna;
}

public void setRiconsegna(Date riconsegna) {
    this.riconsegna = riconsegna;
}

public String getType() {
    return type;
}

public void setType(String type) {
    this.type = type;
}

/*public Kind getKind() {
    return kind;
}

public void setKind(Kind kind) {
    this.kind = kind;
}*/



}

and this is the Main class where I simply try to store a book in the mysql database:

package it.mysql.beginner;

import javax.persistence.*;

import java.util.ArrayList;
import java.util.List;
import java.util.Date;

public class Main {

public static void main(String argv[]) {

    EntityManagerFactory emf=Persistence.createEntityManagerFactory("mysqltest");

    EntityManager em = emf.createEntityManager();

        Date d = new Date();
    d.setMonth(11);
    d.setDate(25);
    d.setYear(1013);

    Book b1 = new Book();
    b1.setAuthor("Stephen King");
    b1.setIsbn("bg5dvc8");
    b1.setRead(true);
    b1.setRiconsegna(d);
    b1.setTitle("The green mile");
    b1.setType("Drammatico");

    em.getTransaction().begin();
    em.persist(b1);
    em.getTransaction().commit();

    em.close();
    emf.close();

}
}

The persistence.xml file is correctly set because it works if i try to save some other classes (for example users)...but this doesn't work for book. This is the exception I get:

[EL Info]: 2013-11-18 18:22:18.456--ServerSession(7486884)--EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b
[EL Info]: connection: 2013-11-18 18:22:18.714--ServerSession(7486884)--file:/home/lory/workspace/BeginnerMysql/build/classes/_mysqltest login successful
[EL Warning]: 2013-11-18 18:22:18.743--ServerSession(7486884)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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 'READ TINYINT(1) default 0, RICONSEGNA DATE, TITLE VARCHAR(255), TYPE VARCHAR(255' at line 1
Error Code: 1064
Call: CREATE TABLE books (ID INTEGER AUTO_INCREMENT NOT NULL, AUTHOR VARCHAR(255), ISBN VARCHAR(255) UNIQUE, READ TINYINT(1) default 0, RICONSEGNA DATE, TITLE VARCHAR(255), TYPE VARCHAR(255), PRIMARY KEY (ID))
Query: DataModifyQuery(sql="CREATE TABLE books (ID INTEGER AUTO_INCREMENT NOT NULL, AUTHOR VARCHAR(255), ISBN VARCHAR(255) UNIQUE, READ TINYINT(1) default 0, RICONSEGNA DATE, TITLE VARCHAR(255), TYPE VARCHAR(255), PRIMARY KEY (ID))")
[EL Warning]: 2013-11-18 18:22:18.804--UnitOfWork(27137311)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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 'READ, RICONSEGNA, TITLE, TYPE) VALUES ('Stephen King', 'bg5dvc8', 1, '2913-12-25' at line 1
Error Code: 1064
Call: INSERT INTO books (AUTHOR, ISBN, READ, RICONSEGNA, TITLE, TYPE) VALUES (?, ?, ?, ?, ?, ?)
    bind => [6 parameters bound]
Query: InsertObjectQuery(it.mysql.beginner.Book@85a5a5)
Exception in thread "main" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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 'READ, RICONSEGNA, TITLE, TYPE) VALUES ('Stephen King', 'bg5dvc8', 1, '2913-12-25' at line 1
Error Code: 1064
Call: INSERT INTO books (AUTHOR, ISBN, READ, RICONSEGNA, TITLE, TYPE) VALUES (?, ?, ?, ?, ?, ?)
    bind => [6 parameters bound]
Query: InsertObjectQuery(it.mysql.beginner.Book@85a5a5)
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:157)
    at it.mysql.beginner.Main.main(Main.java:60)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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 'READ, RICONSEGNA, TITLE, TYPE) VALUES ('Stephen King', 'bg5dvc8', 1, '2913-12-25' at line 1
Error Code: 1064
Call: INSERT INTO books (AUTHOR, ISBN, READ, RICONSEGNA, TITLE, TYPE) VALUES (?, ?, ?, ?, ?, ?)
    bind => [6 parameters bound]

but I've written no sql...why doesn't it work?

Answer 1

Instead of modifying your column name, you can use this :

@Column(name = "`read`")
private boolean read;

Answer 2

You have to change the column name of the field read in your entity class.

Try this:

@Column(name="isread")
private boolean read;

or change it directly in java;

private boolean isRead;

Unfortunately READ is a reserved word in mysql.

If you use any of the reserved words to name a field, the SQL generated by eclipseLink will have an invalid syntax (Error Code: 1064).

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Answer 3

For some reason I can't comment on this, so I am sorry if this is not the answer ahead of time... But it looks like you are trying to add in the value 1 in the line

VALUES ('Stephen King', 'bg5dvc8', 1, '2913-12-25'

MySQL won't add anything thing into your database that does not have a '' (quote) around it. So try adding this:

VALUES ('Stephen King', 'bg5dvc8', '1', '2913-12-25'

As you can see, all I did was place a pair of quotes around the 1, try adding that and see if it will change anything. Another cool part of MySQL is that if you have access to the database you are trying to add things into, you can manually put the values into the database and then look at the code to see reformat how you would put it inside of your Java code.