3 Replies Latest reply on Jun 11, 2004 7:44 AM by aloubyansky

Bug in CMP Select

grodrig Jun 9, 2004 3:45 AM

I’ve been working with this select for a time in Jboss 3.2.1:

<query-method>
<method-name>ejbSelectPermisosXAplicacion</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) as pfs,
IN(pfs.perfil.permisos) as ps
where u.login=?1
and ps.permiso.autorizado=?2
and ps.permiso.aplicacion.nombre=?3
and ps.permiso.servicio is null
and ps.permiso.metodo is null</ejb-ql>

With Jboss 3.2.1 returns one record, the correct one, but with Jboss 3.2.3 and Jboss 3.2.4 it can’t find any record.

The generated select with Jboss 3.2.1 is (from MySql log):

SELECT DISTINCT t0_ps_permiso.id FROM securityusuarios t1_u, securityperfilesusuarios t3_pfs, securitypermisosperfiles t4_ps, securitypermisos t0_ps_permiso, securityaplicaciones t2_ps_permiso_aplicacion, securityperfiles t5_pfs_perfil WHERE (t1_u.login = 'grodrig' AND t0_ps_permiso.autorizado = 1 AND t2_ps_permiso_aplicacion.nombre = 'GTP' AND t0_ps_permiso.servicio IS NULL AND t0_ps_permiso.metodo IS NULL) AND (t4_ps.permiso=t0_ps_permiso.id AND t0_ps_permiso.aplicacion=t2_ps_permiso_aplicacion.id AND t5_pfs_perfil.id=t4_ps.PerfilesEJB_permisos AND t3_pfs.perfil=t5_pfs_perfil.id AND t1_u.id=t3_pfs.UsuariosEJB_perfiles)

But with jboss 3.2.4 it is:

SELECT DISTINCT t0_ps_permiso.id FROM securityusuarios t1_u, securityperfilesusuarios t9_pfs, securitypermisosperfiles t3_ps, securitypermisos t0_ps_permiso, securityservicios t4_ps_permiso_servicio, securitymetodos t6_ps_permiso_metodo, securityaplicaciones t2_ps_permiso_aplicacion, securityperfiles t10_pfs_perfil WHERE ((t1_u.login = 'grodrig' AND t0_ps_permiso.autorizado = 1 AND t2_ps_permiso_aplicacion.nombre = 'GTP' AND t0_ps_permiso.servicio IS NULL AND t0_ps_permiso.metodo IS NULL AND t3_ps.permiso=t0_ps_permiso.id AND t0_ps_permiso.servicio=t4_ps_permiso_servicio.id AND t0_ps_permiso.metodo=t6_ps_permiso_metodo.id AND t0_ps_permiso.aplicacion=t2_ps_permiso_aplicacion.id)) AND t3_ps.permiso=t0_ps_permiso.id AND t10_pfs_perfil.id=t3_ps.PerfilesEJB_permisos AND t9_pfs.perfil=t10_pfs_perfil.id AND t1_u.id=t9_pfs.UsuariosEJB_perfiles

And this sentence is wrong!!!!. It first check:
t0_ps_permiso.metodo IS NULL
and later:
t0_ps_permiso.metodo=t6_ps_permiso_metodo.id
and this two conditions can’t never be true at the same time.

There’s the same problem with:
t0_ps_permiso.servicio IS NULL
and:
t0_ps_permiso.servicio=t4_ps_permiso_servicio.id

Like you can see the select with Jboss 3.2.1 it’s ok (it only check for Null).

I’m using MySql (mysql-connector-java-2.0.14-bin.jar).

How can I solve this problem????????
Thanks in advance.
German R.

PD: excuse my poor English.

1. Re: Bug in CMP Select

aloubyansky Jun 9, 2004 4:28 AM (in response to grodrig)
Can you post EJB schema details?

You could also try new QL compiler
<jbosscmp-jdbc> <defaults> <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler> </defaults>
Actions
2. Re: Bug in CMP Select

grodrig Jun 9, 2004 6:20 AM (in response to grodrig)

Schema is a bit complex. CMP beans:

- UsuariosEJB (id, login, password, nombre, createTimestamp, …) and 1 relation one to many unidirectional with PerfilesUsuariosEJB (perfiles).
- PerfilesUsuariosEJB (id, createTimestamp, createUser, …) and 1 relation many to one unidirectional with PerfilesEJB (perfil).
- PerfilesEJB (id, nombre, descripcion, createTimestamp, createUser, …) and 1 relation one to many unidirectional with PermisosPerfilesEJB (permisos)
- PermisosPerfilesEJB (id, createTimestamp, createUser, …) and 1 relation many to one unidirectional with PermisosEJB (permiso).
- PermisosEJB (id, descripcion, autorizado, createTimestamp, createUser, …) and 3 relations:
Many to one unidirectional relation with MetodosEJB (field name: metodo).
Many to one unidirectional relation with ServiciosEJB (field name: servicio).
Many to one unidirectional relation with AplicacionesEJB (field name: aplicacion).
- MetodosEJB (id, nombre, descripcion, …).
- ServiciosEJB (id, nombre, descripcion, …).
- AplicacionesEJB (id, nombre, descripcion, …).

With the select I’m looking for all the PermisosEJB related to the user “grodrig” (UsuariosEJB.login=’grodrig’), with application name “GTP” (PermisosEJB.aplicacion.nombre=’GTP’) with allowed permission TRUE (PermisosEJB.autorizado = 1) and without method and service (PermisosEJB.metodo is null and PermisosEJB.servicio is null).

Using the new QL compiler is even worst, because it breaks with an incorrect SQL sentence (it is trying to use an inexistent field in a table). If you want later we can try to check this new problem.

The ejb-jar file is:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">
<ejb-jar>
<enterprise-beans>

<display-name>UsuariosSRV</display-name>
<ejb-name>UsuariosSRV</ejb-name>
<local-home>es.ameba.security.servicios.UsuariosSRVLocalHome</local-home>
es.ameba.security.servicios.UsuariosSRVLocal
<ejb-class>es.ameba.security.servicios.UsuariosSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>PerfilesUsuariosSRV</display-name>
<ejb-name>PerfilesUsuariosSRV</ejb-name>
<local-home>es.ameba.security.servicios.PerfilesUsuariosSRVLocalHome</local-home>
es.ameba.security.servicios.PerfilesUsuariosSRVLocal
<ejb-class>es.ameba.security.servicios.PerfilesUsuariosSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>PerfilesSRV</display-name>
<ejb-name>PerfilesSRV</ejb-name>
<local-home>es.ameba.security.servicios.PerfilesSRVLocalHome</local-home>
es.ameba.security.servicios.PerfilesSRVLocal
<ejb-class>es.ameba.security.servicios.PerfilesSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>PermisosPerfilesSRV</display-name>
<ejb-name>PermisosPerfilesSRV</ejb-name>
<local-home>es.ameba.security.servicios.PermisosPerfilesSRVLocalHome</local-home>
es.ameba.security.servicios.PermisosPerfilesSRVLocal
<ejb-class>es.ameba.security.servicios.PermisosPerfilesSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>PermisosSRV</display-name>
<ejb-name>PermisosSRV</ejb-name>
<local-home>es.ameba.security.servicios.PermisosSRVLocalHome</local-home>
es.ameba.security.servicios.PermisosSRVLocal
<ejb-class>es.ameba.security.servicios.PermisosSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>MetodosSRV</display-name>
<ejb-name>MetodosSRV</ejb-name>
<local-home>es.ameba.security.servicios.MetodosSRVLocalHome</local-home>
es.ameba.security.servicios.MetodosSRVLocal
<ejb-class>es.ameba.security.servicios.MetodosSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>ServiciosSRV</display-name>
<ejb-name>ServiciosSRV</ejb-name>
<local-home>es.ameba.security.servicios.ServiciosSRVLocalHome</local-home>
es.ameba.security.servicios.ServiciosSRVLocal
<ejb-class>es.ameba.security.servicios.ServiciosSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>AplicacionesSRV</display-name>
<ejb-name>AplicacionesSRV</ejb-name>
<local-home>es.ameba.security.servicios.AplicacionesSRVLocalHome</local-home>
es.ameba.security.servicios.AplicacionesSRVLocal
<ejb-class>es.ameba.security.servicios.AplicacionesSRVBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>

<display-name>CredencialSRV</display-name>
<ejb-name>CredencialSRV</ejb-name>
<local-home>es.ameba.security.servicios.CredencialSRVLocalHome</local-home>
es.ameba.security.servicios.CredencialSRVLocal
<ejb-class>es.ameba.security.servicios.CredencialSRVBean</ejb-class>
<session-type>Stateful</session-type>
<transaction-type>Container</transaction-type>

<display-name>UsuariosEJB</display-name>
<ejb-name>UsuariosEJB</ejb-name>
<local-home>es.ameba.security.datos.UsuariosEJBHome</local-home>
es.ameba.security.datos.UsuariosEJB
<ejb-class>es.ameba.security.datos.UsuariosEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>UsuariosEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>login</field-name>
</cmp-field>
<cmp-field>
<field-name>password_</field-name>
</cmp-field>
<cmp-field>
<field-name>nombre</field-name>
</cmp-field>
<cmp-field>
<field-name>apellido1</field-name>
</cmp-field>
<cmp-field>
<field-name>apellido2</field-name>
</cmp-field>
<cmp-field>
<field-name>alta</field-name>
</cmp-field>
<cmp-field>
<field-name>caducidad</field-name>
</cmp-field>
<cmp-field>
<field-name>cambioPassword</field-name>
</cmp-field>
<cmp-field>
<field-name>descripcion</field-name>
</cmp-field>
<cmp-field>
<field-name>nif</field-name>
</cmp-field>
<cmp-field>
<field-name>telefono</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findByLogin</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select Object(u) from UsuariosEJB as u where u.login=?1</ejb-ql>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(u) from UsuariosEJB as u</ejb-ql>

<query-method>
<method-name>findPermisos</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select ps.permiso from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps where u.login=?1</ejb-ql>

<display-name>AplicacionesEJB</display-name>
<ejb-name>AplicacionesEJB</ejb-name>
<local-home>es.ameba.security.datos.AplicacionesEJBHome</local-home>
es.ameba.security.datos.AplicacionesEJB
<ejb-class>es.ameba.security.datos.AplicacionesEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>AplicacionesEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>nombre</field-name>
</cmp-field>
<cmp-field>
<field-name>descripcion</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(a) from AplicacionesEJB as a</ejb-ql>

<query-method>
<method-name>findByNombre</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select Object(a) from AplicacionesEJB as a where a.nombre=?1</ejb-ql>

<display-name>ServiciosEJB</display-name>
<ejb-name>ServiciosEJB</ejb-name>
<local-home>es.ameba.security.datos.ServiciosEJBHome</local-home>
es.ameba.security.datos.ServiciosEJB
<ejb-class>es.ameba.security.datos.ServiciosEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>ServiciosEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>nombre</field-name>
</cmp-field>
<cmp-field>
<field-name>descripcion</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(s) from ServiciosEJB as s</ejb-ql>

<query-method>
<method-name>findByNombre</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select Object(s) from ServiciosEJB as s where s.nombre=?1</ejb-ql>

<query-method>
<method-name>findByAplicacion</method-name>
<method-params>
<method-param>es.ameba.security.datos.AplicacionesEJB</method-param>
</method-params>
</query-method>
<ejb-ql>select Object(s) from ServiciosEJB as s where s.aplicacion=?1</ejb-ql>

<display-name>MetodosEJB</display-name>
<ejb-name>MetodosEJB</ejb-name>
<local-home>es.ameba.security.datos.MetodosEJBHome</local-home>
es.ameba.security.datos.MetodosEJB
<ejb-class>es.ameba.security.datos.MetodosEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>MetodosEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>nombre</field-name>
</cmp-field>
<cmp-field>
<field-name>descripcion</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(m) from MetodosEJB as m</ejb-ql>

<query-method>
<method-name>findByNombre</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select Object(m) FROM MetodosEJB as m WHERE m.nombre=?1</ejb-ql>

<display-name>PermisosEJB</display-name>
<ejb-name>PermisosEJB</ejb-name>
<local-home>es.ameba.security.datos.PermisosEJBHome</local-home>
es.ameba.security.datos.PermisosEJB
<ejb-class>es.ameba.security.datos.PermisosEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>PermisosEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>descripcion</field-name>
</cmp-field>
<cmp-field>
<field-name>autorizado</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(p) from PermisosEJB as p</ejb-ql>

<query-method>
<method-name>findPermisosUsuario</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps
where u.login=?1</ejb-ql>

<query-method>
<method-name>findPermiso</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps
where u.login=?1
and ps.permiso.aplicacion.nombre=?2
and (ps.permiso.servicio.nombre=?3
or ps.permiso.servicio is null)
and (ps.permiso.metodo.nombre=?4
or ps.permiso.metodo is null)</ejb-ql>

<query-method>
<method-name>findPermisosValidos</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps
where u.login=?1
and ((ps.permiso.aplicacion.nombre=?2
and ps.permiso.servicio is null
and ps.permiso.metodo is null)
or (ps.permiso.aplicacion.nombre=?2
and ps.permiso.servicio.nombre=?3
and ps.permiso.metodo is null)
or (ps.permiso.aplicacion.nombre=?2
and ps.permiso.servicio.nombre=?3
and ps.permiso.metodo.nombre=?4))</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXAplicacion</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) as pfs,
IN(pfs.perfil.permisos) as ps
where u.login=?1
and ps.permiso.autorizado=?2
and ps.permiso.aplicacion.nombre=?3
and ps.permiso.servicio is null
and ps.permiso.metodo is null</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXAplicacionServicio</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps where u.login=?1
and ps.permiso.autorizado=?2
and ((ps.permiso.aplicacion is null )
or (ps.permiso.aplicacion.nombre=?3))
and ps.permiso.servicio.nombre=?4
and ps.permiso.metodo is null</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXAplicacionServicioMetodo</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps where u.login=?1
and ps.permiso.autorizado=?2
and ((ps.permiso.aplicacion is null )
or (ps.permiso.aplicacion.nombre=?3))
and ((ps.permiso.servicio is null)
or (ps.permiso.servicio.nombre=?4))
and ps.permiso.metodo.nombre=?5</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXMetodo</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps where u.login=?1
and ps.permiso.autorizado=?2
and ps.permiso.aplicacion is null
and ps.permiso.servicio is null
and ps.permiso.metodo.nombre=?3</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXAplicacionMetodo</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps where u.login=?1
and ps.permiso.autorizado=?2
and ps.permiso.aplicacion.nombre=?3
and ps.permiso.servicio is null
and ps.permiso.metodo.nombre=?4</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXServicioMetodo</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps
where u.login=?1
and ps.permiso.autorizado=?2
and ps.permiso.aplicacion is null
and ps.permiso.servicio.nombre=?3
and ps.permiso.metodo.nombre=?4</ejb-ql>

<query-method>
<method-name>ejbSelectPermisosXServicio</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Boolean</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<result-type-mapping>Local</result-type-mapping>
<ejb-ql>select ps.permiso
from UsuariosEJB as u,
IN(u.perfiles) pfs,
IN(pfs.perfil.permisos) ps where u.login=?1
and ps.permiso.autorizado=?2
and ps.permiso.aplicacion is null
and ps.permiso.servicio.nombre=?3
and ps.permiso.metodo is null</ejb-ql>

<display-name>PerfilesEJB</display-name>
<ejb-name>PerfilesEJB</ejb-name>
<local-home>es.ameba.security.datos.PerfilesEJBHome</local-home>
es.ameba.security.datos.PerfilesEJB
<ejb-class>es.ameba.security.datos.PerfilesEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>PerfilesEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>nombre</field-name>
</cmp-field>
<cmp-field>
<field-name>descripcion</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(p) from PerfilesEJB as p</ejb-ql>

<query-method>
<method-name>findByNombre</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select Object(p) from PerfilesEJB as p where p.nombre=?1</ejb-ql>

<display-name>PerfilesUsuariosEJB</display-name>
<ejb-name>PerfilesUsuariosEJB</ejb-name>
<local-home>es.ameba.security.datos.PerfilesUsuariosEJBHome</local-home>
es.ameba.security.datos.PerfilesUsuariosEJB
<ejb-class>es.ameba.security.datos.PerfilesUsuariosEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>PerfilesUsuariosEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(p) from PerfilesUsuariosEJB as p</ejb-ql>

<display-name>PermisosPerfilesEJB</display-name>
<ejb-name>PermisosPerfilesEJB</ejb-name>
<local-home>es.ameba.security.datos.PermisosPerfilesEJBHome</local-home>
es.ameba.security.datos.PermisosPerfilesEJB
<ejb-class>es.ameba.security.datos.PermisosPerfilesEJBBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>PermisosPerfilesEJB</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>createTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>createUser</field-name>
</cmp-field>
<cmp-field>
<field-name>modTimestamp</field-name>
</cmp-field>
<cmp-field>
<field-name>modUser</field-name>
</cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<ejb-ql>select Object(p) from PermisosPerfilesEJB as p</ejb-ql>

</enterprise-beans>

<ejb-relation>
<ejb-relation-name>servicio-metodo</ejb-relation-name>
<ejb-relationship-role>
serviciosEJB
<ejb-relationship-role-name>ServicioRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
serviciosEJB
<ejb-name>ServiciosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
metodosEJB
<cmr-field-name>metodos</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
metodosEJB
<ejb-relationship-role-name>MetodoRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
metodosEJB
<ejb-name>MetodosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
serviciosEJB
<cmr-field-name>servicio</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>permiso-metodo</ejb-relation-name>
<ejb-relationship-role>
permisosEJB
<ejb-relationship-role-name>PermisoRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
permisosEJB
<ejb-name>PermisosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
metodosEJB
<cmr-field-name>metodo</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
metodosEJB
<ejb-relationship-role-name>MetodoRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
metodosEJB
<ejb-name>MetodosEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>usuarios-perfilesUsuario</ejb-relation-name>
<ejb-relationship-role>
usuariosEJB
<ejb-relationship-role-name>UsuariosRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
usuariosEJB
<ejb-name>UsuariosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
perfilesUsuariosEJB
<cmr-field-name>perfiles</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
perfilesUsuariosEJB
<ejb-relationship-role-name>PerfilesUsuarioRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
perfilesUsuariosEJB
<ejb-name>PerfilesUsuariosEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>permisos-servicios</ejb-relation-name>
<ejb-relationship-role>
permisosEJB
<ejb-relationship-role-name>PermisosRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
permisosEJB
<ejb-name>PermisosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
serviciosEJB
<cmr-field-name>servicio</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
serviciosEJB
<ejb-relationship-role-name>ServiciosRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
serviciosEJB
<ejb-name>ServiciosEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>permisos-aplicaciones</ejb-relation-name>
<ejb-relationship-role>
permisosEJB
<ejb-relationship-role-name>PermisosRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
permisosEJB
<ejb-name>PermisosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
aplicacionesEJB
<cmr-field-name>aplicacion</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
aplicacionesEJB
<ejb-relationship-role-name>AplicacionesRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
aplicacionesEJB
<ejb-name>AplicacionesEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>servicios-aplicaciones</ejb-relation-name>
<ejb-relationship-role>
serviciosEJB
<ejb-relationship-role-name>ServiciosRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
serviciosEJB
<ejb-name>ServiciosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
aplicacionesEJB
<cmr-field-name>aplicacion</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
aplicacionesEJB
<ejb-relationship-role-name>AplicacionesRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
aplicacionesEJB
<ejb-name>AplicacionesEJB</ejb-name>
</relationship-role-source>
<cmr-field>
serviciosEJB
<cmr-field-name>serviciosEJB</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>perfilesUsuarios-perfiles</ejb-relation-name>
<ejb-relationship-role>
perfilesUsuariosEJB
<ejb-relationship-role-name>PerfilesUsuariosRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
perfilesUsuariosEJB
<ejb-name>PerfilesUsuariosEJB</ejb-name>
</relationship-role-source>
<cmr-field>
perfilesEJB
<cmr-field-name>perfil</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
perfilesEJB
<ejb-relationship-role-name>PerfilesRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
perfilesEJB
<ejb-name>PerfilesEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>perfiles-permisosPerfiles</ejb-relation-name>
<ejb-relationship-role>
perfilesEJB
<ejb-relationship-role-name>PerfilesRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
perfilesEJB
<ejb-name>PerfilesEJB</ejb-name>
</relationship-role-source>
<cmr-field>
permisosPerfilesEJB
<cmr-field-name>permisos</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
permisosPerfilesEJB
<ejb-relationship-role-name>PermisosPerfilesRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
permisosPerfilesEJB
<ejb-name>PermisosPerfilesEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>permisosPerfiles-permisos</ejb-relation-name>
<ejb-relationship-role>
permisosPerfilesEJB
<ejb-relationship-role-name>PermisosPerfilesRelationshipRole</ejb-relationship-role-name>
Many
<relationship-role-source>
permisosPerfilesEJB
<ejb-name>PermisosPerfilesEJB</ejb-name>
</relationship-role-source>
<cmr-field>
permisosEJB
<cmr-field-name>permiso</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
permisosEJB
<ejb-relationship-role-name>PermisosRelationshipRole</ejb-relationship-role-name>
One
<relationship-role-source>
permisosEJB
<ejb-name>PermisosEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

<assembly-descriptor>
<container-transaction>

<ejb-name>UsuariosEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PerfilesUsuariosEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PerfilesEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PermisosPerfilesEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>MetodosEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>ServiciosEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PermisosEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>AplicacionesEJB</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>UsuariosSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PerfilesUsuariosSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PerfilesSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PermisosPerfilesSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>PermisosSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>MetodosSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>ServiciosSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>AplicacionesSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>CredencialSRV</ejb-name>
<method-name>Autorizar</method-name>

<trans-attribute>RequiresNew</trans-attribute>
</container-transaction>
<container-transaction>

<ejb-name>CredencialSRV</ejb-name>
<method-name>*</method-name>

<trans-attribute>RequiresNew</trans-attribute>
</container-transaction>
</assembly-descriptor>
</ejb-jar>
Actions

3. Re: Bug in CMP Select

aloubyansky Jun 11, 2004 7:44 AM (in response to grodrig)

I fixed EJBQLToSQL92Compiler. The generated SQL is

SELECT t0_ps_permiso.permisosId, t0_ps_permiso.autorizado, t0_ps_permiso.SERVICIOS_FK, t0_ps_permiso.AP_FK, t0_ps_permiso.METODOS_FK
 FROM SECURITYUSUARIOS t2_u INNER JOIN SECURITYPERFILESUSUARIOS t4_pfs ON t2_u.UId=t4_pfs.U_FK
 INNER JOIN SECURITYPERFILES t5_pfs_perfil ON t4_pfs.PERFILES_FK=t5_pfs_perfil.perfilesId
 INNER JOIN SECURITYPERMISOSPERFILES t1_ps ON t5_pfs_perfil.perfilesId=t1_ps.PERFILES_FK
 LEFT OUTER JOIN SECURITYPERMISOS t0_ps_permiso ON t1_ps.PERMISOS_FK=t0_ps_permiso.permisosId
 LEFT OUTER JOIN SECURITYSERVICIOS t6_ps_permiso_servicio ON t0_ps_permiso.SERVICIOS_FK=t6_ps_permiso_servicio.serviciosId
 LEFT OUTER JOIN SECURITYMETODOS t7_ps_permiso_metodo ON t0_ps_permiso.METODOS_FK=t7_ps_permiso_metodo.metodosId
 LEFT OUTER JOIN SECURITYAPLICACIONES t3_ps_permiso_aplicacion ON t0_ps_permiso.AP_FK=t3_ps_permiso_aplicacion.apId
 WHERE t2_u.login = ? AND t0_ps_permiso.autorizado = ? AND t3_ps_permiso_aplicacion.nombre = ? AND t0_ps_permiso.SERVICIOS_FK IS NULL AND t0_ps_permiso.METODOS_FK IS NULL

This is what I would expect for your query.

Go to original post