I am trying to get a List<Role>
from the following Entity class using HQL-
@Entity
@Table(name="APPL_APPROVAL_ROLES")
public class ApplApprovalRoles implements Serializable, Comparable<ApplApprovalRoles> {
private static final long serialVersionUID = 1L;
@Id
@Column(name="id", updatable=false)
@SequenceGenerator(name="appl_approval_roles_id_seq",sequenceName="appl_approval_roles_id_seq")
@GeneratedValue(generator="appl_approval_roles_id_seq")
private int id;
@Column(name="appl_name")
private String applName;
@OneToOne
@JoinColumn(name="client_id")
private Client client;
@OneToOne
@JoinColumn(name="role_id")
private Role role;
@Column(name="stage_num")
private int stageNum;
@Column(name="active",length=1)
@Type(type="yes_no")
private boolean active = false;
public ApplApprovalRoles(){}
public ApplApprovalRoles(int stageNum, String applName,
Client client, Role role, boolean active){
this.stageNum = stageNum;
this.applName = applName;
this.client = client;
this.role = role;
this.active = active;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getApplName() {
return applName;
}
public void setApplName(String applName) {
this.applName = applName;
}
public Client getClient() {
return client;
}
public void setClient(Client client) {
this.client = client;
}
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public int getStageNum() {
return stageNum;
}
public void setStageNum(int stageNum) {
this.stageNum = stageNum;
}
public boolean isActive() {
return active;
}
public void setActive(boolean active) {
this.active = active;
}
}
The Role Class is -
@Entity
@Table(name="role")
public class Role implements Serializable, Comparable<Role> {
@Id
@Column(name="id", updatable=false)
@SequenceGenerator(name="role_id_seq", sequenceName="role_id_seq")
@GeneratedValue(generator="role_id_seq")
private int id;
@OneToOne
@JoinColumn(name="client_id", nullable=false)
private Client client;
@Column(name="code", nullable=true)
private String code;
@Column(name="name", nullable=false)
private String name;
@Column(name="binary_code")
private String binaryCode;
public Role(){}
public Role(int id, String code, String name) {
this.id = id;
this.code = code;
this.name = name;
}
public Role(String code, String name, Client client) {
this.code = code;
this.name = name;
this.client = client;
}
public Role(Client client) {
this.client = client;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id=id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public Client getClient() {
return client;
}
public void setClient(Client client) {
this.client = client;
}
}
The HQL I am trying for -
@SuppressWarnings("unchecked")
public List<Role> getRoleByClientAndAppName(Client client, String applName){
String hql = "select role from ApplApprovalRoles where client_id = :clientId and appl_name = :applName";
Session session = this.getSessionFactory().getCurrentSession();
Query query = session.createQuery(hql);
query.setParameter("clientId", client.getId());
query.setParameter("applName", applName);
List<Role> roles = (List<Role>)query.list();
return roles;
}
Which gives me the following error -
javax.el.ELException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:332)
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:273)
at org.jboss.el.parser.AstMethodSuffix.getValue(AstMethodSuffix.java:59)
at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
at org.springframework.binding.expression.el.BindingValueExpression.getValue(BindingValueExpression.java:54)
at org.springframework.binding.expression.el.ELExpression.getValue(ELExpression.java:54)
... 58 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:692)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:625)
at com.infoimage.infotrac.proofing.service.ProofingLogService$$EnhancerByCGLIB$$d2fe9334.getRoleBasedProofingList(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:328)
... 64 more
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1051)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3460)
at com.caucho.sql.UserPreparedStatement.executeQuery(UserPreparedStatement.java:108)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 86 more
The equivalent oracle SQL query for selecting all role is -
select ROLE_ID from APPL_APPROVAL_ROLES where CLIENT_ID='clnt110' and APPL_NAME='Checking_statements';
The SQL works fine. But problem occurred with HQL. Can anyone help me? Thanks a lot.
Chaitanya thanks for your answer.When I tried with your answer it gives- org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'client.id'
error. Then I use the following hql (just a bit modification of your hql) which gives me the correct result -
String hql = "select role from ApplApprovalRoles a where a.client.id = :clientId and a.applName = :applName";