JBossCMP2 Bug - Generated insert stmt incorrect

[twhphan]

Having problem with JBoss 3 generated insert stmt, not sure why it generates "INSERT INTO ViewShortcut (username, line, resourcesProperties, url, username) VALUES (?, ?, ?, ?, ?)" when I execute:

Collection c = viewSettingLocal.getViewShortcuts();
c.add(getViewShortcutLocalHome().create(username, line, resourcesProperties, url));

Note: it thinks that the table has two username columns
---
Error:

[Default] 2446400 [RMI TCP Connection(22)-] DEBUG org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ViewShortcut ViewFacade ViewShortcut - Executing SQL: SELECT COUNT(*) FROM ViewShortcut WHERE username=? AND line=?
...
[Default] 2446412 [RMI TCP Connection(22)-] DEBUG org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ViewShortcut ViewFacade ViewShortcut - Executing SQL: INSERT INTO ViewShortcut (username, line, resourcesProperties, url, username) VALUES (?, ?, ?, ?, ?)
...
[ViewShortcut] Could not create entity
java.sql.SQLException: General error: Column 'username' specified twice

---
Background info:

ViewSetting is the parent, and ViewShortcut is the child
ViewSetting.username is the primary key for the parent table
ViewShortcut.username and ViewShortcut.line for the child table
ViewShortcut.username is the foreign key

---
ejb-jar.xml


<![CDATA[No Description.]]>
<ejb-name>ViewSetting</ejb-name>
<local-home>esb.interfaces.ViewSettingLocalHome</local-home>
esb.interfaces.ViewSettingLocal
<ejb-class>esb.ejb.ViewSettingCMP</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>ViewSetting</abstract-schema-name>
<cmp-field>
<![CDATA[]]>
<field-name>username</field-name>
</cmp-field>
...
<primkey-field>username</primkey-field>



<![CDATA[No Description.]]>
<ejb-name>ViewShortcut</ejb-name>
<local-home>esb.interfaces.ViewShortcutLocalHome</local-home>
esb.interfaces.ViewShortcutLocal
<ejb-class>esb.ejb.ViewShortcutCMP</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>esb.interfaces.ViewShortcutPK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>ViewShortcut</abstract-schema-name>
<cmp-field>
<![CDATA[]]>
<field-name>username</field-name>
</cmp-field>
<cmp-field>
<![CDATA[]]>
<field-name>line</field-name>
</cmp-field>
<cmp-field>
<![CDATA[]]>
<field-name>resourcesProperties</field-name>
</cmp-field>
<cmp-field>
<![CDATA[]]>
<field-name>url</field-name>
</cmp-field>


<ejb-relation>
<ejb-relation-name>setting-shortcut</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>one-setting-has-many-shortcuts</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>ViewSetting</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>viewShortcuts</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>one-shortcut-belongs-to-one-setting</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>ViewShortcut</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
---
jbosscmp-jdbc.xml:


<ejb-name>ViewShortcut</ejb-name>
<table-name>ViewShortcut</table-name>
<create-table>true</create-table>
<remove-table>false</remove-table>
<tuned-updates>true</tuned-updates>
<read-only>false</read-only>
<time-out>100</time-out>
<cmp-field>
<field-name>line</field-name>
<column-name>line</column-name>
</cmp-field>
<cmp-field>
<field-name>resourcesProperties</field-name>
<column-name>resourcesProperties</column-name>
</cmp-field>
<cmp-field>
<field-name>url</field-name>
<column-name>url</column-name>
</cmp-field>
<cmp-field>
<field-name>username</field-name>
<column-name>username</column-name>
</cmp-field>


<ejb-relation>
<ejb-relation-name>setting-shortcut</ejb-relation-name>
<foreign-key-mapping>
<ejb-relationship-role>
<ejb-relationship-role-name>one-setting-has-many-shortcuts</ejb-relationship-role-name>
<foreign-key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>one-shortcut-belongs-to-one-setting</ejb-relationship-role-name>
<fk-constraint>false</fk-constraint>
<foreign-key-fields>
<foreign-key-field>
<field-name>username</field-name>
<column-name>username</column-name>
</foreign-key-field>
</foreign-key-fields>
</ejb-relationship-role>
</foreign-key-mapping>
</ejb-relation>

---
The extra username field may be created after JBoss reads <foreign-key-field/> in the jbosscmp-jdbc.xml

Is this a JBoss bug or I misunderstand something?

Thanks

[dsundstrom]

I just fixed some bugs int the autogeneration code for relationship keys. I wouldn't recomend updating to cvs as it is still slightly broken. In the mean time just full specify the column mapping of your relation keys.

[twhphan]

Thx Dain

Where may I find the instructions to fully specify the column mapping of my relation keys?

And should I watch for changes to JDBCCreateEntityCommand.java and SQLUtil.java in the CVS. When will u plan to update it?

Thx very much :)

[twhphan]

Hi, I wonder if I can add a new CMR now, i.e. has the generated insert statement fixed yet?

In addition, I saw:

public void removeLineItem(long lineItemID) {
Collection col = getLineItems();
Iterator iter = col.iterator();
while (iter.hasNext()) {
LineItem li = (LineItem) iter.next();
if (li.getId() == lineItemID) {
iter.remove();
return;
}
}
}

on http://javaboutique.internet.com/resources/books/EJB/ejb2_3.html

and attempted to do the same thing in JBoss, i.e. deleting a child row using the CMR returned collection iterator's remove method. But JBoss gaves javax.ejb.RemoveException: java.lang.IllegalStateException

From your JBossCMP-Docs, you use a finder method to get the collection (children) to be deleted, and call their interfaces' remove method to remove them one by one. Is it possible to remove all children without using a finder method? (in most cases, the get method returns the same rows as the finder method. However, if I apply the remove/clear method to the collection returned by get, JBoss just change their keys to 0/null. If I apply remove to the returned collection's elements, i.e. the local interface or the same element as the finder returns, I got some transaction error)

Thanks

[giorgio42]

The IllegalStateException is required by the EJB2.0 spec.

Please read section 10.3.6.1. It states:

"If elements are added or removed from the underlying
container-managed collection used by an iterator other
than by the java.util.Iterator.remove() method, the
container should throw the java.lang.IllegalStateException
on the next operation on the iterator."

Do you call the iterator's remove() method (legal)
or that of LineItem (illegal) ?

Maybe you have to use the sequence (inside the loop)

iter.remove();
li.remove();

[twhphan]

Hi,

My code uses lterator.remove(), it works fine without looking at the data in the database. What I see is that JBoss sets my key (which relates to its parent table) to 0 or null; in this case, the record won't be queried out (since the parent-child doesn't have the relation), but it exists. What I want is to have the record to be deleted!

so I tried:

for (Iterator i = obj1Local.getObj2s().iterator(); i.hasNext();) {
obj2Local = (Obj2Local) i.next();
i.remove();
obj2Local.remove(); // this line causes javax.ejb.RemoveException: Could not remove entity
}

where obj1 is the parent, and obj2 is the child

without obj2Local.remove(), I cannot think of any code to delete the record; unless i.remove() does that (instead of setting the key)

Any suggestion? Thx