MySQLSyntaxErrorException though everything seems fine (Hibernate)

Question

I have a Project entity and a Group entity. I can get a Project by name using the following getter method from DAO:

public Project getProject(String name){
    Project project = null;

    DetachedCriteria criteria = DetachedCriteria.forClass(Project.class);
    criteria.add(Restrictions.eq("name", name));
    List<Project> projects = getHibernateTemplate().findByCriteria(criteria);
    if ((projects != null) && (projects.size() > 0)) {
        project = (Project)projects.get(0);
    }
    return project;
}

Whereas this very same build doesn't work for the group DAO:

public Group getGroup(String name){
    Group group = null;

    DetachedCriteria criteria = DetachedCriteria.forClass(Group.class);
    criteria.add(Restrictions.eq("name", name));
    List<Group> groups = getHibernateTemplate().findByCriteria(criteria);
    if ((groups != null) && (groups.size() > 0)) {
        group = (Group)groups.get(0);
    }
    return group;
}

Here's the stack trace that goes (this appears when I try to create a new group; a group with supplied name is fetched to check if such a group already exists and throw an exception if so):

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 this_ where this_.name='new_group'' at line 1 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) java.lang.reflect.Constructor.newInstance(Constructor.java:513) com.mysql.jdbc.Util.handleNewInstance(Util.java:409) com.mysql.jdbc.Util.getInstance(Util.java:384) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114) com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696) com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105) com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264) org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187) org.hibernate.loader.Loader.getResultSet(Loader.java:1791) org.hibernate.loader.Loader.doQuery(Loader.java:674) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) org.hibernate.loader.Loader.doList(Loader.java:2217) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2108) org.hibernate.loader.Loader.list(Loader.java:2103) org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94) org.hibernate.impl.SessionImpl.list(SessionImpl.java:1570) org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283) org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:984) org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372) org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:974) org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:967) pl.edu.agh.adam.core.projects.dao.GroupDAO.getGroup(GroupDAO.java:65) pl.edu.agh.adam.core.projects.GroupService.createGroup(GroupService.java:76) pl.edu.agh.adam.core.projects.web.CreateGroupBean.create(CreateGroupBean.java:80) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) java.lang.reflect.Method.invoke(Method.java:597) com.sun.el.parser.AstValue.invoke(AstValue.java:234) com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297) org.apache.myfaces.view.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:83) javax.faces.component._MethodExpressionToMethodBinding.invoke(_MethodExpressionToMethodBinding.java:88) org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:100) javax.faces.component.UICommand.broadcast(UICommand.java:120) javax.faces.component.UIViewRoot._broadcastAll(UIViewRoot.java:890) javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:234) javax.faces.component.UIViewRoot._process(UIViewRoot.java:1202) javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:623) org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:35) org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:143) org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:93) javax.faces.webapp.FacesServlet.service(FacesServlet.java:189) org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79) org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

Here are the entities (getters and setters omitted):

@Entity
@Table(name = "group")
public class Group implements Serializable {
    private static final long serialVersionUID = 1L;

    @Transient
    public static final String REF = "Group";

    @ManyToMany(targetEntity = pl.edu.agh.adam.core.account.hibernate.User.class)
    @JoinTable(name = "group_user", joinColumns = @JoinColumn(name = "group_id"), inverseJoinColumns = @JoinColumn(name = "user_id"))
    Set<User> users;

    public Set<User> getUsers() {
        return users;
    }

    public void setUsers(Set<User> users) {
        this.users = users;
    }

    @ManyToMany(targetEntity = pl.edu.agh.adam.core.projects.hibernate.Role.class)
    @JoinTable(name = "project_group_role", joinColumns = @JoinColumn(name = "group_id"))
    @MapKeyJoinColumn(name = "project_id")
    Map<Project, Role> projectRoles;

    @Transient
    public static final String PROP_ID = "id";

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "group_id")
    private Long id;

    @Transient
    public static final String PROP_NAME = "name";
    @Column(name = "name", length = 25, unique = true, nullable = false)
    private String name;

    @Transient
    public static final String PROP_PASSWORD = "password";

    @Column(name = "password", length = 40)
    private String password;

    @Transient
    public static final String PROP_SUBSCRIBABLE = "subscribable";

    @Column(name = "subscribable")
    private Boolean subscribable;
}

The other one (working one):

@Entity
@Table(name = "project")
public class Project implements Serializable {

private static final long serialVersionUID = 1L;

@Transient
public static final String REF = "Project";

@ManyToMany(targetEntity = pl.edu.agh.adam.core.projects.hibernate.Tag.class)
@JoinTable(name = "project_tag", joinColumns = @JoinColumn(name = "project_id"), inverseJoinColumns = @JoinColumn(name = "tag_id"))
private List<Tag> tags;

@ManyToMany(targetEntity = pl.edu.agh.adam.core.projects.hibernate.Group.class)
@JoinTable(name = "project_group_role",
        joinColumns = @JoinColumn(name = "project_id"),
        inverseJoinColumns = @JoinColumn(name = "group_id"))
private Set<Group> groups;

@ManyToMany(targetEntity = pl.edu.agh.adam.core.projects.hibernate.Role.class)
@JoinTable(name = "project_group_role",
        joinColumns = @JoinColumn(name = "project_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id"))
private Set<Role> roles;


@Transient
public static final String PROP_ID = "id";

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "project_id")
private Long id;

@Transient
public static final String PROP_NAME = "name";
@Column(name = "name", length = 60, unique = true, nullable = false)
private String name;

@Transient
public static final String PROP_SHORTNAME = "shortname";

@Column(name = "shortname", length = 25, unique = true)
private String shortname;

@Transient
public static final String PROP_HOMEPAGE = "homepage";
@Column(name = "homepage", length = 60)
private String homepage;

@Transient
public static final String PROP_DESCRIPTION = "description";
@Column(name = "description", columnDefinition = "LONGTEXT")
private String description;

  }

Also the table structures: group: -group_id int(11) auto increment, not null -name varchar(25) unique, not null -password varchar(40) -subscribable tinyint(1)

project

-project_id int(11) auto increment, not null -name varchar(60) unique, not null -shortname varchar(25) -homepage varchar(25) -description text

I've turned on hibernate query logging to System.out; it the following are produced:

select
    this_.project_id as project1_3_0_,
    this_.description as descript2_3_0_,
    this_.homepage as homepage3_0_,
    this_.name as name3_0_,
    this_.shortname as shortname3_0_ 
from
    project this_ 
where
    this_.name=?

And the not working one:

select
    this_.group_id as group1_4_0_,
    this_.name as name4_0_,
    this_.password as password4_0_,
    this_.subscribable as subscrib4_4_0_ 
from

group this_ where
    this_.name=?

What is going on?? :(

Answer 1

I didn't spot that immediately but group is a reserved keyword so the generated query is indeed incorrect. Use another table name for the Group entity. For example:

@Entity
@Table(name = "groups")
public class Group implements Serializable {

    //...
}