cmr + self referencing table
mattia123 Nov 26, 2002 3:27 AMHi 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