Beginner needs help with EJB-QL and EJB relations.

[balteo]

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.

[sesques]

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

[balteo]

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.

[sesques]

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 ;-)