3 Replies Latest reply on Apr 30, 2004 11:49 AM by sesques

    Beginner needs help with EJB-QL and EJB relations.

    Julien Martin Expert

      Hello,

      Further to an earlier post, I have more questions relating to ejbs and ejb-ql.

      I have three tables in a relational database:

      *First table, called "establishments", contains two fiels "db_establishment_id" and "db_establishment_name".
      Here is a sample:

      db_establishment_id(pk) db_establishment_name
      ---------------------------------------------
      1 The black frog
      2 The king's head
      3 McDonalds
      


      *Second table, called "categories", contains two fields "db_category_id" and "db_category_name"
      Here is a sample:
      db_category_id(pk) db_category_name
      -----------------------------------
      1 restaurant
      2 pub
      



      *Third table, called "establishments_qualifiers", contains two fields "db_establishment_id" and "db_id_category"
      Here is a sample:
      db_establishment_id(pk,fk) db_category_id(pk,fk)
      --------------------------------------
      1 2
      2 2
      2 1
      3 1
      


      These THREE tables map to TWO ejbs in a many-to-many bi-di relation (Is this the way it should be?) as follows:

       <!--===========Establishment================ -->
       <entity>
       <ejb-name>EstablishmentEJB</ejb-name>
       <local-home>com.softwareag.test_guide.establishment.EstablishmentLocalHome</local-home>
       <local>com.softwareag.test_guide.establishment.EstablishmentLocal</local>
       <ejb-class>com.softwareag.test_guide.establishment.EstablishmentBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Integer</prim-key-class>
       <reentrant>false</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>Establishment</abstract-schema-name>
       <cmp-field>
       <field-name>id</field-name>
       </cmp-field>
       <cmp-field>
       <field-name>name</field-name>
       </cmp-field>
       <primkey-field>id</primkey-field>
       <query>
       <query-method>
       <method-name>findEstablishmentsByCategory</method-name>
       <method-params>
       <!-- category -->
       <method-param>java.lang.String</method-param>
       <!-- name -->
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <ejb-ql>
       SELECT OBJECT(es) FROM Establishment AS es, Category AS ca
       WHERE
       es.id=ca.establishment_id
       --I am stuck here
       </ejb-ql>
       </query>
       </entity>
      
       <!--===========Category================ -->
       <entity>
       <ejb-name>CategoryEJB</ejb-name>
       <home>com.softwareag.test_guide.category.CategoryHome</home>
       <remote>com.softwareag.test_guide.category.Category</remote>
       <local-home>com.softwareag.test_guide.category.CategoryLocalHome</local-home>
       <local>com.softwareag.test_guide.category.CategoryLocal</local>
       <ejb-class>com.softwareag.test_guide.category.CategoryBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Integer</prim-key-class>
       <reentrant>false</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>Category</abstract-schema-name>
       <cmp-field>
       <field-name>id</field-name>
       </cmp-field>
       <cmp-field>
       <field-name>category</field-name>
       </cmp-field>
       <primkey-field>id</primkey-field>
       </entity>
      
       <!--===========Establishments-Categories ================-->
       <ejb-relation>
       <ejb-relation-name>Establishment-Category</ejb-relation-name>
       <ejb-relationship-role>
       <ejb-relationship-role-name>Establishment-has-many-categories</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source>
       <ejb-name>EstablishmentEJB</ejb-name>
       </relationship-role-source>
       <cmr-field>
       <cmr-field-name>categories</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
       <ejb-relationship-role>
       <ejb-relationship-role-name>Category-has-many-establishments</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source>
       <ejb-name>CategoryEJB</ejb-name>
       </relationship-role-source>
       <cmr-field>
       <cmr-field-name>establishments</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
       </ejb-relation>
      


      Ideally I would like an EstablishmentEJB instance that contains a list of categories. How do I do that? Do I need to modify the design of my application? What is the ejb-ql I need?

      Thanks in advance,

      Julien Martin.

        • 1. Re: Beginner needs help with EJB-QL and EJB relations.
          sesques Novice

          Hi,


          Ideally I would like an EstablishmentEJB instance that contains a list of categories. How do I do that? Do I need to modify the design of my application? What is the ejb-ql I need?


          I do not understand your needs:

          Any EstablishmentEJB instance contains a list of categories. Just call getCategories() to get the collection.

          In your finder, you want to filter your EstablishmentEJB by categories.
          It can be done like that:
          <query>
           <query-method>
           <method-name>findEstablishmentsByCategory</method-name>
           <method-params>
           <!-- category -->
           <method-param>com.softwareag.test_guide.category.CategoryLocal</method-param>
           </method-params>
           </query-method>
           <ejb-ql>SELECT OBJECT(es) FROM Establishment AS es WHERE es.categories = ?1 </ejb-ql>
          </query>
          


          or
          <query>
           <query-method>
           <method-name>findEstablishmentsByCategory</method-name>
           <method-params>
           <!-- category name-->
           <method-param>java.lang.String</method-param>
           </method-params>
           </query-method>
           <ejb-ql>SELECT OBJECT(es) FROM Establishment AS es, IN(es.categories) ca WHERE ca.category = ?1 </ejb-ql>
          </query>
          


          Pascal


          • 2. Beginner needs help with EJB-QL and EJB relations.
            Julien Martin Expert

            Hello Pascal,
            Thanks for your reply. Judging by your reply, it seems the problem is simpler than I thought. I am going to give it a try with your code and I'll keep you posted if I have further questions.
            Thanks again for your quick reply!
            Julien.

            • 3. Re: Beginner needs help with EJB-QL and EJB relations.
              sesques Novice

              Sorry, in my last response, only the second method works because the multiplicity is many. So forget the first one which applies only to a relation one-many, on the one side.

              On Friday, I'm a little bit tired ;-)