MySQLSyntaxErrorException though everything seems fine (Hibernate)
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.Dele开发者_如何转开发gatingConstructorAccessorImpl.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?? :(
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 {
//...
}
精彩评论