JPQL MySQLSyntaxErrorException: You have an error in your SQL syntax

Question

Hello,

I have two entities User and Group:

@Entity
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private String username;
    private String password;
    private String email;
    @ManyToMany
    @JoinTable(name="user_group", joinColumns={@JoinColumn(name="USERNAME")}, inverseJoinColumns={@JoinColumn(name="ID")})
    private List<Group> group;

    // getters and setters...
}

@Entity
public class Group implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private String id;

    // getters and setters...
}

It's ManyToMany unidirectional. It produces tables user and user_group. Now I have simple query:

Query q = em.createQuery("select g from Group g");
List<Group> usersGroup = q.getResultList();

which throws exception:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): 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 'GROUP' at line 1
Error Code: 1064
Call: SELECT ID FROM GROUP
Query: ReadAllQuery(referenceClass=Group sql="SELECT ID FROM GROUP")
... stack trace...

Answer 1

Don't call your user group entity 'Group'. It's be a reserved word in MySQL syntax for use in aggregation as GROUP BY.

Try calling it something else, like 'UserGroup'.

Answer 2

You CAN use Group or User for the database objects as long as you tell the JPA provider that these names have to be interpreted as delimited identifiers.

With JPA 2.0 and annotations, a name is specified as a delimited identifier by enclosing the name within double quotes, whereby the inner quotes are escaped:

@Entity
@Table(name="\"User\"")
public class User implements Serializable {
    ...
}

@Entity
@Table(name="\"Group\"")
public class Group implements Serializable {
    ...
}

Reference

• JPA 2.0 specification
  • Section 2.13 "Naming of Database Objects"