Error in named query (QuerySyntaxException: unexpected token)
I am currently working on a utility project which will help me to manage the users of an application (a user has one or more role, and a role has one or more rights). I am new to Hibernate, and I don't know why I have encountered the following issue :
ERROR [hibernate.internal.SessionFactoryImpl] HHH000177: Error in named query: right.findAllByRoleId
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: right near line 1, column 8 [select right from com.google.code.jee.utils.user.management.model.Role as r left join r.rights as right where r.id = :roleId]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:276)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:106)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:81)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:1130)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:523)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1740)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1778)
at org.springframework.orm.hibernate4.LocalSessionFactoryBuilder.buildSessionFactory(LocalSessionFactoryBuilder.java:189)
at org.springframework.orm.hibernate4.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:350)
at org.springframework.orm.hibernate4.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:335)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1514)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1452)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:848)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:790)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:707)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:478)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:87)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:284)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1106)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:585)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:913)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:464)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
at Demo.main(Demo.java:9)
Right class :
@Entity
@Table(name = "RIG_RIGHT")
@NamedQueries({
@NamedQuery(name = RightDao.COUNT_BY_NAME, query = "select count(*) from Right as rig where rig.name = :name"),
@NamedQuery(name = RightDao.FIND_BY_NAME, query = "from Right as rig where rig.name = :name"),
@NamedQuery(name = RightDao.COUNT_FOR_ROLE_ID, query = "select count(*) from Role as r where r.id = :id"),
@NamedQuery(name = RightDao.FIND_ALL_BY_ROLE_ID, query = "select right from Role as r left join r.rights as right where r.id = :roleId"),
@NamedQuery(name = RightDao.COUNT_FOR_ROLE_ID_AND_NAME, query = "select count(*) from Role as r left join r.rights as right where r.id = :roleId and right.name = :rightName"),
@NamedQuery(name = RightDao.FIND_BY_ROLE_ID_AND_NAME, query = "select right from Role as r left join r.rights as right where r.id = :roleId and right.name = :rightName") })
@SuppressWarnings("serial")
public class Right extends AbstractHibernateDto<Integer> {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "RIG_ID", nullable = false)
private Integer id;
@Column(name = "RIG_NAME", nullable = false, unique = true, length = 50)
private String name;
// Getters & Setters
Role class :
@Entity
@Table(name = "ROL_ROLE")
@NamedQueries({
@NamedQuery(name = RoleDao.COUNT_BY_NAME, query = "select count(*) from Role as r where r.name = :name"),
@NamedQuery(name = RoleDao.FIND_BY_NAME, query = "from Role as r where r.name = :name"),
@NamedQuery(name = RoleDao.COUNT_FOR_USER_ID, query = "select count(*) from User as u where u.id = :id"),
@NamedQuery(name = RoleDao.FIND_ALL_BY_USER_ID, query = "select role from User as u left join u.roles as role where u.id = :userId"),
@NamedQuery(name = RoleDao.COUNT_FOR_USER_ID_AND_NAME, query = "select count(*) from User as u left join u.roles as role where u.id = :userId and role.name = :roleName"),
@NamedQuery(name = RoleDao.FIND_BY_USER_ID_AND_NAME, query = "select role from User as u left join u.roles as role where u.id = :userId and role.name = :roleName") })
@SuppressWarnings("serial")
public class Role extends AbstractHibernateDto<Integer> {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ROL_ID", nullable = false)
private Integer id;
@Column(name = "ROL_NAME", nullable = false, unique = true, length = 50)
private String name;
@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
@JoinColumn(name = "RIG_ROLE_ID", nullable = false)
private List<Right> rights;
// Getters & Setters
Right DAO :
public interface RightDao extends GenericDao<Integer, Right> {
public final String COUNT_BY_NAME = "right.countByName";
public final String FIND_BY_NAME = "right.findByName";
public final String COUNT_FOR_ROLE_ID = "right.countForRoleId";
public final String FIND_ALL_BY_ROLE_ID = "right.findAllByRoleId";
public final String COUNT_FOR_ROLE_ID_AND_NAME = "right.countForRoleIdAndName";
public final String FIND_BY_ROLE_ID_AND_NAME = "right.findByRoleIdAndName";
/**
* Search the number of elements with the 'name' parameter.
*
* @param name the name
* @return the number of element found
*/
public Integer countByName(String name);
/**
* Search an element by its name.
*
* @param name the name
* @return the right
*/
public Right findByName(String name);
/**
* Count the number of rights of a specific role
*
* @param roleId the role id
* @return the number of rights
*/
public Integer countForRoleId(Integer roleId);
/**
* Finds all rights by roleId.
*
* @param roleId the role primary key
* @return the list
*/
public List<Right> findAllByRoleId(Integer roleId);
/**
* Count the number of rights with a specific name and corresponding to
* a specific role
*
* @param roleId the roleId
* @param rightName the right name
* @return the number of rights
*/
public Integer countForRoleIdAndName(Integer roleId, String rightName);
/**
* Finds the right.
*
* @param roleId the role primary key
* @param rightName the right name
* @return the right
*/
public Right findByRoleIdAndName(Integer roleId, String rightName);
}
Role DAO :
public interface RoleDao extends GenericDao<Integer, Role> {
public final String COUNT_BY_NAME = "role.countByName";
public final String FIND_BY_NAME = "role.findByName";
public final String COUNT_FOR_USER_ID = "role.countForUserId";
public final String FIND_ALL_BY_USER_ID = "role.findAllByUserId";
public final String COUNT_FOR_USER_ID_AND_NAME = "role.countForUserIdAndName";
public final String FIND_BY_USER_ID_AND_NAME = "role.findByUserIdAndName";
/**
* Search the number of elements with the 'name' parameter.
*
* @param name the name
* @return the number of element found
*/
public Integer countByName(String name);
/**
* Search an element by its name.
*
* @param name the name
* @return the user
*/
public Role findByName(String name);
/**
* Count the number of roles of a specific user
*
* @param userId the user id
* @return the number of roles
*/
public Integer countForUserId(Integer userId);
/**
* Finds all roles by user id.
*
* @param userId the user primary key
* @return the list
*/
public List<Role> findAllByUserId(Integer userId);
/**
* Count the number of roles with a specific name and corresponding to
* a specific user
*
* @param userId the userId
* @param roleName the role name
* @return the number of roles
*/
public Integer countForUserIdAndName(Integer userId, String roleName);
/**
* Finds the role.
*
* @param userId the user primary key
* @param roleName the role name
* @return the role
*/
public Role findByUserIdAndName(Integer userId, String roleName);
}
Any idea ? Thank you in advance !
Julien Neuhart
Answer
It seems like the source of the error is using right as a variable name in one of your named queries (RightDao.FIND_ALL_BY_ROLE_ID, RightDao.FIND_BY_ROLE_ID_AND_NAME). right is a reserved word in HQL (right outer join)
Either:
Use different alias for
right:@NamedQuery(name = RightDao.FIND_ALL_BY_ROLE_ID, query = "select r from Role as role left join role.rights as r where role.id = :roleId"), @NamedQuery(name = RightDao.FIND_BY_ROLE_ID_AND_NAME, query = "select r from Role as role left join role.rights as r where role.id = :roleId and r.name = :rightName") })Escape the reserved word
rightin the query by surrounding it with square brackets[right], or backticks. Also, you might fins the following related question useful: How to escape reserved words in Hibernate's HQL.