cmr + self referencing table

[mattia123]

Hi all,
I have a simple tree structure stored in a DB table as follow:

id int(5)
parent int(5) references id
name varchar(255)
isLeaf int(1)

and this is the relationship as defined in jbosscmp-jdbc.xml

<ejb-relation>
<ejb-relation-name>Children</ejb-relation-name>
<foreign-key-mapping />
<ejb-relationship-role>
<ejb-relationship-role-name>TreeElementRelationshipRole</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>parent</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>TreeElementRelationshipRole1</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>Parent</ejb-relation-name>
<foreign-key-mapping />
<ejb-relationship-role>
<ejb-relationship-role-name>TreeElementRelationshipRole</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>id</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>TreeElementRelationshipRole1</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>parent</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>


So I have a CMP Entity Bean (TreeElement.java) with 3 cmp fields (id,name,isLeaf) and 2 cmr fields (parent,children). So the EJB has 1 parent (one-to-one) and many children (one-to-many).

And now comes the problem:
when I create a new TreeElement this is the query launched by JBoss:
exec sp_executesql N'INSERT INTO tree (id, type, name, id, parent, parent) VALUES (@P1, @P2, @P3, @P4, @P5, @P6)', N'@P1 int ,@P2 int ,@P3 nvarchar(4000) ,@P4 int ,@P5 int ,@P6 int ', 44, NULL, N'ElementTest', NULL, NULL, NULL"

and the obvious exception: "Column name 'id' appears more than once in the result column list".
How can I manage those kind of recursive relationship?
Finder querys suffer the same problem (duplicate 'parent' and 'id' selection) but at least it is not an error.

Is it my own error or a bug/missing feature in JBoss?
wouldn't be possible to check for duplicate column names when the query is created?

TIA,
mattia

[hvenstrom]

Hi there, I am not sure why you would like to do something like this. But in any case, your jbosscmp-jdbc
look a bit strange to me. Have you tried something like this?

<ejb-relation>
<ejb-relation-name>Parent</ejb-relation-name>
<foreign-key-mapping />
<ejb-relationship-role>
<ejb-relationship-role-name>TreeElementRelationshipRole</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>TreeElementRelationshipRole1</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>isLeaf</field-name>
<column-name>child</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>

The <column-name> should correspond to your CMR filed

[mattia123]

well I'm just trying to represent a simple tree structure (eg: a forum, a document category structure...).

> The <column-name> should correspond to your CMR filed

AFAIK <field-name> should correspond to a PK in my object and <column-name> to database column name.

I'm thinking of another solution:
leave only 1 relation (tha Children relationship) and create a custom finder findParentByChild(int child) with
<ejb-ql>SELECT OBJECT(t) FROM Tree (t) IN t.children (c) WHERE c.id = ?1</ejb-ql>

that means find the object of which ID is child.
hmmm... I'll test it and let you know of the results :)

thanks
--
mattia

[mattia123]

it works!

the correct EJBQL is
<ejb-ql>SELECT OBJECT(t) FROM Tree t IN (t.children) c WHERE c.id = ?1</ejb-ql>

and instead of a Elem.getParent() I have to invoke ElemHome.findParentByChild(Elem.getId())

It seems correct to me:
- avoid duplicate column
- think of this relation as a descend-only relation in the tree (an Element has many children and knowing a child I can find its parent)

would a bidirectional relationship be useful?

bye
--
mattia

[hvenstrom]

Well, it depend on how many iterations you need to do down the list and how often. In the bidirectional case you would not need the itiration.
As to the CMR and the colum you are right of course, I was thinking of the case of unknown publik keys.