relationships in jbosscmp-jdbc.xml

[paoletto]

so, i have this doubt about sematics of fields in jbosscmp-jdbc.

example taken from a working project:

 <ejb-relation>
 <ejb-relation-name>Students-Groups</ejb-relation-name>

 <foreign-key-mapping/>

 <ejb-relationship-role>
 <ejb-relationship-role-name>Student-belongs-to-group</ejb-relationship-role-name>
 <key-fields/>

 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>Group-has-students</ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>id</field-name>
 <column-name>refGroup</column-name>
 </key-field>
 </key-fields>

 </ejb-relationship-role>
 </ejb-relation>

ok. i had a look in the code and id is a CMP field of entity "group" and "refGroup" is a field of entity and table students.

now what i was wondering:
ok, i desume that this key-field block is used to express a foreign key, where field name is the local cmp field mapped above in the file, and column name is the foreign column in the other table, stated this time in ejb-jar.xml relations block.

is this correct? i ask because in the .dtd file it is not explained well, and it seems that usually <column-name> is the actual column name in the db of <field-name>, so it's not so obvious that things are how im figuring out

thanks for any explaination

[paoletto]

ok, i tried but clearly i'm making some mistake.

i tried to define a CMR field for entity Document that references "many" accounts, and i added in both bean and localhome interface the getters and setters for this CMR field named "account" in the <ejb-relation>.

I also added the block into jbosscmp-jdbc.xml and a join query.

Here's the code:

ejb-jar.xml

[...]
 <entity >
 <description><![CDATA[Description for Dipartimento]]></description>
 <display-name>Name for Dipartimento</display-name>

 <ejb-name>Dipartimento</ejb-name>

 <local-home>servizioDipartimentale.interfaces.DipartimentoLocalHome</local-home>
 <local>servizioDipartimentale.interfaces.DipartimentoLocal</local>

 <ejb-class>servizioDipartimentale.ejb.DipartimentoBean</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>Dipartimento</abstract-schema-name>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field idDipartimento]]></description>
 <field-name>IdDipartimento</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field Descrizione]]></description>
 <field-name>Descrizione</field-name>
 </cmp-field>
 <primkey-field>IdDipartimento</primkey-field>

 <query>
 <query-method>
 <method-name>findAll</method-name>
 <method-params>
 </method-params>
 </query-method>
 <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Dipartimento AS a]]></ejb-ql>
 </query>

 <query>
 <query-method>
 <method-name>findRelatedAccounts</method-name>
 <method-params></method-params>
 </query-method>
 <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Dipartimento AS d, IN (d.Accounts) as a WHERE d.descrizione = 'DIPARTIMENTO DI FISICA']]></ejb-ql>
 <!-- ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a WHERE a.Dipartimento.Descrizione = 'DIPARTIMENTO DI FISICA']]></ejb-ql-->
 </query>
 </entity>
[...]
 <relationships>

 <ejb-relation>
 <ejb-relation-name>Dipartimento-Account</ejb-relation-name>
 <ejb-relationship-role>
 <ejb-relationship-role-name>Department-has-accounts</ejb-relationship-role-name>
 <multiplicity>One</multiplicity>
 <relationship-role-source>
 <ejb-name>Dipartimento</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <cmr-field-name>Accounts</cmr-field-name>
 <!-- cmr-field-type>java.util.Collection</cmr-field-type-->
 </cmr-field>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>Account-belongs-to-department</ejb-relationship-role-name>
 <multiplicity>Many</multiplicity>
 <cascade-delete/>
 <relationship-role-source>
 <ejb-name>Account</ejb-name>
 </relationship-role-source>
 </ejb-relationship-role>
 </ejb-relation>

jbosscmp-jdbc.xml

[...]
 <entity>
 <ejb-name>Dipartimento</ejb-name>
 <create-table>true</create-table>
 <remove-table>true</remove-table>
 <table-name>DIPARTIMENTO</table-name>

 <cmp-field><field-name>idDipartimento</field-name></cmp-field>
 <cmp-field><field-name>descrizione</field-name></cmp-field>

 </entity>
[...]
 <relationships>
 <ejb-relation>
 <ejb-relation-name>Dipartimento-Account</ejb-relation-name>
 <foreign-key-mapping/>

 <ejb-relationship-role>
 <ejb-relationship-role-name>Department-has-accounts</ejb-relationship-role-name>
 <key-fields/>
 </ejb-relationship-role>

 <ejb-relationship-role>
 <ejb-relationship-role-name>Account-belongs-to-department</ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>dipartimento</field-name>
 <column-name>idDocumento</column-name>
 </key-field>
 </key-fields>
 </ejb-relationship-role>
 </ejb-relation>

and the error trying to deploy the application:
Finder findRelatedAccounts defined on Dipartimento should return only instances of Dipartimento but the query results in instances of Account


so should i put it in Account bean? but then the relationship? how to change it in bidirectional way?

[paoletto]

ok. i tried but i get a weird error. I followed jboss 3.2.7 developer guide.

the error is this:

12:35:25,352 ERROR [Account#findRelatedAccounts] Find failed
java.sql.SQLException: Colonna non trovata: T0_A.DEPARTMENT in statement [SELECT t0_a.utente FROM DIPARTIMENTO t1_d, ACCOUNT t0_a WHERE ((t1_d.descrizione = ?)) AND t1_d.idDipartimento=t0_a.department]

it's weird because i mapped idDipartimento to dipartimento, not department. department should only be the cmr field :/

now my entities are:

 <entity >
 <description><![CDATA[Description for Account]]></description>
 <display-name>Entity Bean relativa agli Account di accesso</display-name>

 <ejb-name>Account</ejb-name>

 <local-home>servizioDipartimentale.interfaces.AccountLocalHome</local-home>
 <local>servizioDipartimentale.interfaces.AccountLocal</local>

 <ejb-class>servizioDipartimentale.ejb.AccountBean</ejb-class>
 <persistence-type>Container</persistence-type>
 <prim-key-class>java.lang.String</prim-key-class>
 <reentrant>False</reentrant>
 <cmp-version>2.x</cmp-version>
 <abstract-schema-name>Account</abstract-schema-name>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field utente]]></description>
 <field-name>utente</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field pwd]]></description>
 <field-name>pwd</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field cognome]]></description>
 <field-name>cognome</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field nome]]></description>
 <field-name>nome</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field ruolo]]></description>
 <field-name>ruolo</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field tipo]]></description>
 <field-name>tipo</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field dipartimento]]></description>
 <field-name>dipartimento</field-name>
 </cmp-field>
 <primkey-field>utente</primkey-field>

 <query>
 <query-method>
 <method-name>findAll</method-name>
 <method-params>
 </method-params>
 </query-method>
 <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a]]></ejb-ql>
 </query>

 <query>
 <query-method>
 <method-name>findRelatedAccounts</method-name>
 <method-params>
 <method-param>java.lang.String</method-param>
 </method-params>
 </query-method>
 <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Dipartimento AS d, IN (d.accounts) as a WHERE d.descrizione = ?1]]></ejb-ql>
 <!--ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a WHERE a.Dipartimento.Descrizione = 'DIPARTIMENTO DI FISICA']]></ejb-ql-->
 </query>

 </entity>

 <entity >
 <description><![CDATA[Description for Dipartimento]]></description>
 <display-name>Name for Dipartimento</display-name>

 <ejb-name>Dipartimento</ejb-name>

 <local-home>servizioDipartimentale.interfaces.DipartimentoLocalHome</local-home>
 <local>servizioDipartimentale.interfaces.DipartimentoLocal</local>

 <ejb-class>servizioDipartimentale.ejb.DipartimentoBean</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>Dipartimento</abstract-schema-name>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field idDipartimento]]></description>
 <field-name>idDipartimento</field-name>
 </cmp-field>
 <cmp-field >
 <description><![CDATA[Getter for CMP Field Descrizione]]></description>
 <field-name>descrizione</field-name>
 </cmp-field>
 <primkey-field>idDipartimento</primkey-field>

 <query>
 <query-method>
 <method-name>findAll</method-name>
 <method-params>
 </method-params>
 </query-method>
 <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Dipartimento AS a]]></ejb-ql>
 </query>

 <!-- Write a file named ejb-finders-DipartimentoBean.xml if you want to define extra finders. -->

 </entity>

relationship

 <ejb-relation>
 <ejb-relation-name>Dipartimento-Account</ejb-relation-name>
 <ejb-relationship-role>
 <ejb-relationship-role-name>Department-has-accounts</ejb-relationship-role-name>
 <multiplicity>One</multiplicity>
 <relationship-role-source>
 <ejb-name>Dipartimento</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <cmr-field-name>accounts</cmr-field-name>
 <cmr-field-type>java.util.Collection</cmr-field-type>
 </cmr-field>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>Account-belongs-to-department</ejb-relationship-role-name>
 <multiplicity>Many</multiplicity>
 <cascade-delete/>
 <relationship-role-source>
 <ejb-name>Account</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <cmr-field-name>department</cmr-field-name>
 </cmr-field>
 </ejb-relationship-role>
 </ejb-relation>

and in jbosscmp-jdbc

 <ejb-relation>
 <ejb-relation-name>Dipartimento-Account</ejb-relation-name>
 <foreign-key-mapping/>

 <ejb-relationship-role>
 <ejb-relationship-role-name>Department-has-accounts</ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>idDocumento</field-name>
 <column-name>documento</column-name>
 </key-field>
 </key-fields>
 </ejb-relationship-role>

 <ejb-relationship-role>
 <ejb-relationship-role-name>Account-belongs-to-department</ejb-relationship-role-name>
 <key-fields/>
 </ejb-relationship-role>
 </ejb-relation>

[paoletto]

ok, there is an error in the last block: this is correct:
<field-name>idDipartimento</field-name>
<column-name>dipartimento</column-name>

but the error keeps to be the same