[EJBQL] Sélectionner plusieurs tables

Sélectionner plusieurs tables

Je souhaiterais faire une requête EJBQL sur plusieurs tables mais je ne sais pas comment faire ...

Voici mes EJB:

Citation:

USER :
-long code (key)
-String libelle

CATALOG :
-long code (key)
-String libelle

USERCATALOG : (catalogues autorisés)
-int odreAffichage
-USER (key)
-CATALOG (key)

j'ai créé 2 relations entre USER et USERCATALOG (1-1) ainsi qu'entre USERCATALOG et CATALOG (1-1). J'aurais souhaiter faire une seule relation entre USER ET CATALOG de type MANY-MANY mais avec Websphere, on ne peut pas rajouter d'attribut à une relation. C'est pourquoi j'ai créé une table supplémentaire ainsi que 2 relations pour joindre les 3 tables.

la requête que je souhaiterais faire:
R1: récupérer tous les catalogues autorisés pour un utilisateur donné en récupérant le libéllé du catalogue.

je fais cette requête là :

Code:

select object(c) from Catalogue c, IN (c.usercatalog) AS ca where ca.code = ?1

mais elle ne marche pas. Websphere me donne l'erreur suivante lorsque je génère le code de déploiement et le code RMIC:

Citation:

Erreur lors de la génération de la requête findAllByUtilisateur(long) pour le bean Catalogue(nom du schéma Abstract=Catalogue). Erreur=WQRY0017E:Cannot define correlation id ca over a single-valued path expression catalogueautorise
WQRY0110E: correlation id ca is not defined.

Requête='select object(c) from Catalogue c, IN (c.usercatalog) AS ca where ca.code = ?1'

Voici mon descripteur de déploiement EJB

Citation:

<?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 id="ejb-jar_ID">
<display-name>ejb</display-name>
<enterprise-beans>
<entity id="Catalog">
<ejb-name>Catalog</ejb-name>
<local-home>fr.seb.siteMarchand.ejb.catalog.CatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.catalog.CatalogLocal</local>
<ejb-class>fr.seb.siteMarchand.ejb.catalog.CatalogBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>fr.seb.siteMarchand.ejb.catalog.CatalogKey</prim-key-class>
<reentrant>False</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Catalog</abstract-schema-name>
<cmp-field id="CMPAttribute_1086599069203">
<field-name>code</field-name>
</cmp-field>
<cmp-field id="CMPAttribute_1086599069204">
<field-name>libelle</field-name>
</cmp-field>
<cmp-field id="CMPAttribute_1086599069205">
<field-name>libCourt</field-name>
</cmp-field>
<ejb-local-ref id="EJBLocalRef_1086594530703">
<ejb-ref-name>ejb/usercatalog</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.usercatalog.UserCatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.usercatalog.UserCatalogLocal</local>
<ejb-link>usercatalog</ejb-link>
</ejb-local-ref>
<query>
<description></description>
<query-method>
<method-name>findAll</method-name>
<method-params></method-params>
</query-method>
<ejb-ql>select c from Catalog c</ejb-ql>
</query>
<query>
<description></description>
<query-method>
<method-name>findByCode</method-name>
<method-params>
<method-param>long</method-param></method-params>
</query-method>
<ejb-ql>select c from Catalog c where c.code = ?1</ejb-ql>
</query>
<query>
<description></description>
<query-method>
<method-name>findAllByuser</method-name>
<method-params>
<method-param>long</method-param>
</method-params>
</query-method>
<ejb-ql>select object(c) from Catalog c, IN (c.usercatalog) AS ca where ca.code = ?1</ejb-ql>
</query>
</entity>
<entity id="user">
<ejb-name>user</ejb-name>
<local-home>fr.seb.siteMarchand.ejb.user.UserLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.user.UserLocal</local>
<ejb-class>fr.seb.siteMarchand.ejb.user.UserBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>fr.seb.siteMarchand.ejb.user.UserKey</prim-key-class>
<reentrant>False</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>user</abstract-schema-name>
<cmp-field id="CMPAttribute_1086599069219">
<field-name>code</field-name>
</cmp-field>
<ejb-local-ref id="EJBLocalRef_1087919936641">
<ejb-ref-name>ejb/usercatalog</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.usercatalog.UserCatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.usercatalog.UserCatalogLocal</local>
<ejb-link>usercatalog</ejb-link>
</ejb-local-ref>
<query>
<description></description>
<query-method>
<method-name>findAll</method-name>
<method-params></method-params>
</query-method>
<ejb-ql>select object(u) from user u</ejb-ql>
</query>
<query>
<description></description>
<query-method>
<method-name>findByCode</method-name>
<method-params>
<method-param>long</method-param>
</method-params>
</query-method>
<ejb-ql>select object(o) from user o where o.code = ?1</ejb-ql>
</query>
</entity>
<entity id="usercatalog">
<ejb-name>usercatalog</ejb-name>
<local-home>fr.seb.siteMarchand.ejb.usercatalog.usercatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.usercatalog.usercatalogLocal</local>
<ejb-class>fr.seb.siteMarchand.ejb.usercatalog.usercatalogBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>fr.seb.siteMarchand.ejb.usercatalog.usercatalogKey</prim-key-class>
<reentrant>False</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>usercatalog</abstract-schema-name>
<cmp-field id="CMPAttribute_1086599069251">
<field-name>ordreAffichage</field-name>
</cmp-field>
<cmp-field id="CMPAttribute_1086599069252">
<field-name>enSommeil</field-name>
</cmp-field>
<cmp-field>
<description>Generated to support relationships. Do NOT delete.</description>
<field-name>catalog_code</field-name>
</cmp-field>
<cmp-field>
<description>Generated to support relationships. Do NOT delete.</description>
<field-name>user_code</field-name>
</cmp-field>
<ejb-local-ref id="EJBLocalRef_1086594530719">
<ejb-ref-name>ejb/Catalog</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.catalog.CatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.catalog.CatalogLocal</local>
<ejb-link>Catalog</ejb-link>
</ejb-local-ref>
<ejb-local-ref id="EJBLocalRef_1087919936672">
<ejb-ref-name>ejb/user</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.user.UserLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.user.UserLocal</local>
<ejb-link>user</ejb-link>
</ejb-local-ref>
<query>
<description></description>
<query-method>
<method-name>findusercatalogByuser</method-name>
<method-params>
<method-param>long</method-param>
</method-params>
</query-method>
<ejb-ql>select object(o) from usercatalog o where o.user.code = ?1</ejb-ql>
</query>
</entity>
<session id="Session">
<ejb-name>Session</ejb-name>
<local-home>fr.seb.siteMarchand.ejb.session.SessionLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.session.SessionLocal</local>
<ejb-class>fr.seb.siteMarchand.ejb.session.SessionBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>
<ejb-local-ref id="EJBLocalRef_1086686586094">
<description></description>
<ejb-ref-name>ejb/Catalog</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.catalog.CatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.catalog.CatalogLocal</local>
<ejb-link>Catalog</ejb-link>
</ejb-local-ref>
<ejb-local-ref id="EJBLocalRef_1086686586141">
<description></description>
<ejb-ref-name>ejb/user</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.user.UserLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.user.UserLocal</local>
<ejb-link>user</ejb-link>
</ejb-local-ref>
<ejb-local-ref id="EJBLocalRef_1086686586156">
<description></description>
<ejb-ref-name>ejb/usercatalog</ejb-ref-name>
<ejb-ref-type>Entity</ejb-ref-type>
<local-home>fr.seb.siteMarchand.ejb.usercatalog.UsercatalogLocalHome</local-home>
<local>fr.seb.siteMarchand.ejb.usercatalog.UsercatalogLocal</local>
<ejb-link>usercatalog</ejb-link>
</ejb-local-ref>
</session>
</enterprise-beans>
<relationships>
<ejb-relation>
<description></description>
<ejb-relation-name>usercatalog-Catalog</ejb-relation-name>
<ejb-relationship-role id="EJBRelationshipRole_1086599069250">
<ejb-relationship-role-name>catalog</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>usercatalog</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>catalog</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role id="EJBRelationshipRole_1086599069251">
<ejb-relationship-role-name>usercatalog</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>Catalog</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>usercatalog</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<description></description>
<ejb-relation-name>user-usercatalog</ejb-relation-name>
<ejb-relationship-role id="EJBRelationshipRole_1087920550360">
<ejb-relationship-role-name>user</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>usercatalog</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>user</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role id="EJBRelationshipRole_1087920550375">
<ejb-relationship-role-name>usercatalog</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>user</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>usercatalog</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
</relationships>
</ejb-jar>

C bon, j'ai trouvé la solution comme un grand. C'est très simple en fait.

J'explique pour ceux que ça pourrait aider. Je fais une requête qui me récupère la liste des catalogues autorisés pour un utilisateur donné et c'est tout.

Code:

select object(o) from CatalogAutorise o where o.user.code = ?1

Ensuite dans ma page jsp, je fais ceci pour récupérer mon libelle:

Code:

catalogueAutorises[i].getCatalogue().getLibelle();