Hi,
i need an query with some subselects, which work correctly in my db-Terminal.
Thats the SQL-Query:
Code:
select distinct *
from FUNCTION F, FUNCTIONGROUPS FG
where F.FUNCTIONGROUP_ID = FG.FUNCTIONGROUP_ID
and F.FUNCTION_ID
in (select FUNCTION_ID from ROLES_FUNCTIONS where ROLE_ID
in (select ROLE_ID from USER_ROLES where USER_ID = 1));
In short:
There are Users, Roles, Functions and Functiongroups as tables.
Users <-> Roles is many2many (in table user_roles),
Roles <-> Functions is many2many (in table role_functions)
and each function is in exactly one Functiongroup.
The DB is Firebird, Hibernate is latest V.2
(I cant use V3, since i use Spring).
What i need to do with this query is to get all functions of an user. Since every user can have many roles and functions are appended to roles and not directly to users, i have to do this with this subselects.
Hibernate give an Exception:
Code:
org.springframework.orm.hibernate.HibernateQueryException: in expected: F [from PSS_FUNCTION F, PSS_FUNCTIONGROUPS FG where F.PSS_PSS_FUNCTIONGROUP_ID = FG.PSS_PSS_FUNCTIONGROUP_ID and F.PSS_PSS_FUNCTION_ID in (select PSS_PSS_FUNCTION_ID from ROLES_PSS_FUNCTIONS where ROLE_ID in (select ROLE_ID from PSS_USER_ROLES where PSS_USER_ID = ?]; nested exception is net.sf.hibernate.QueryException: in expected: F [from PSS_FUNCTION F, PSS_FUNCTIONGROUPS FG where F.PSS_PSS_FUNCTIONGROUP_ID = FG.PSS_PSS_FUNCTIONGROUP_ID and F.PSS_PSS_FUNCTION_ID in (select PSS_PSS_FUNCTION_ID from ROLES_PSS_FUNCTIONS where ROLE_ID in (select ROLE_ID from PSS_USER_ROLES where PSS_USER_ID = ?]
net.sf.hibernate.QueryException: in expected: F [from PSS_FUNCTION F, PSS_FUNCTIONGROUPS FG where F.PSS_PSS_FUNCTIONGROUP_ID = FG.PSS_PSS_FUNCTIONGROUP_ID and F.PSS_PSS_FUNCTION_ID in (select PSS_PSS_FUNCTION_ID from ROLES_PSS_FUNCTIONS where ROLE_ID in (select ROLE_ID from PSS_USER_ROLES where PSS_USER_ID = ?]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
... ...
Whats the problem?
P.S. the Javapart:
Code:
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
StringBuffer sb = new StringBuffer(100);
sb.append("select distinct ");
sb.append("F.FUNC_NAME, F.PSS_COMMAND, FG.PSS_PSS_FUNCTIONGROUP_NAME ");
sb.append("from PSS_FUNCTION F, PSS_FUNCTIONGROUPS FG ");
sb.append("where F.PSS_PSS_FUNCTIONGROUP_ID = FG.PSS_PSS_FUNCTIONGROUP_ID ");
sb.append("and F.PSS_PSS_FUNCTION_ID in ");
sb.append("(select PSS_PSS_FUNCTION_ID from ROLES_PSS_FUNCTIONS where ROLE_ID in ");
sb.append("(select ROLE_ID from PSS_USER_ROLES where PSS_USER_ID = "+userId+"))");
Query query = session.createQuery(sb.toString());
List list = query.list();
return list;
}
});
Who can help me?
Login
FAQ
Search