Hi,
I'm using Jboss server version 3.0.1RC1. It seems to me that this version does not generate correct SQL query.
I have the following ejb-query in my ejb-jar.xml
<ejb-ql><![CDATA[select object(P) from Permission p, IN (p.role.userRole) AS r where r.user.userName=?1]]></ejb-ql>
the relationships between permission, role, userRole and users are:
- one user has many userRole, and one userRole belongs to one user
- one role has many userRole and one userRole belongs to one role
- one role has many permissions, and one permission belongs to one role
The generated SQL is:
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.biz/bsite/direct/entity/PermissionEJB.findAllUserPermission] SQL: SELECT t0_p.PermissionId FROM permission t0_p, userrole t2_r, users t1_r_user WHERE (t1_r_user.UserName = ?) AND (t2_r.userId=t1_r_user.UserId AND t4_p_role.RoleId=t2_r.roleId AND t0_p.roleId=t4_p_role.RoleId)
As can be seen in the query above, the relation t4_p_role is not specified in the FROM clause causing SQL exception when the method is executed.
Can some one please help me with this problem?
Regards,
Quang
