1 to Many relationship
Hi all,
My question may be stupid but I am unable to create a working 1 to Many relationship with JBoss 3.0.3. My DB is MySQL 3.23.51-Max using InnoDB tables.
Here are my 2 tables:
CREATE TABLE user (
userID int(11) NOT NULL default '0',
email char(64) NOT NULL default '',
password char(64) NOT NULL default '',
PRIMARY KEY (userID),
UNIQUE KEY email_idx (email)
) TYPE=InnoDB;
CREATE TABLE galleryimage (
imageID int(11) NOT NULL default '0',
userID int(11) NOT NULL default '0',
file char(64) NOT NULL default '',
PRIMARY KEY (imageID),
KEY userID_idx (userID)
) TYPE=InnoDB;
My 2 entity beans (without queries & resource-ref) and my relationship in ejb-jar.xml:
<ejb-name>User</ejb-name>
nix.prj.eovia.ejb.UserHome
nix.prj.eovia.ejb.User
<local-home>nix.prj.eovia.ejb.UserLocalHome</local-home>
nix.prj.eovia.ejb.UserLocal
<ejb-class>nix.prj.eovia.ejb.UserBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>nix.prj.eovia.ejb.UserPK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>user</abstract-schema-name>
<cmp-field>
<field-name>userID</field-name>
</cmp-field>
<cmp-field>
<field-name>email</field-name>
</cmp-field>
<cmp-field>
<field-name>password</field-name>
</cmp-field>
<ejb-name>GalleryImage</ejb-name>
nix.prj.eovia.ejb.GalleryImageHome
nix.prj.eovia.ejb.GalleryImage
<local-home>nix.prj.eovia.ejb.GalleryImageLocalHome</local-home>
nix.prj.eovia.ejb.GalleryImageLocal
<ejb-class>nix.prj.eovia.ejb.GalleryImageBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>nix.prj.eovia.ejb.GalleryImagePK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>galleryimage</abstract-schema-name>
<cmp-field>
<field-name>imageID</field-name>
</cmp-field>
<cmp-field>
<field-name>userID</field-name>
</cmp-field>
<cmp-field>
<field-name>file</field-name>
</cmp-field>
<ejb-relation>
<ejb-relation-name>User-GalleryImage</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>
User-has-many-GalleryImages
</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>User</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>
GalleryImage-has-a-User
</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>GalleryImage</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>user</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
The same items in jbosscmp-jdbc.xml:
<ejb-name>User</ejb-name>
<table-name>user</table-name>
<ejb-name>GalleryImage</ejb-name>
<table-name>galleryimage</table-name>
<ejb-relation>
<ejb-relation-name>User-GalleryImage</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>
User-has-many-GalleryImages
</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>userID</field-name>
<column-name>userID</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>
GalleryImage-has-a-User
</ejb-relationship-role-name>
</ejb-relationship-role>
</ejb-relation>
When I run query using the foreign key like:
SELECT OBJECT(gi) FROM galleryimage gi WHERE gi.user.file=?1
My relationship is working fine.
But when I try to insert a row into galleryimage via GalleryImage Bean, JBoss executes the following query to do the job:
10:46:03,595 DEBUG [GalleryImage] Executing SQL: INSERT INTO galleryimage (imageID, userID, file, userID) VALUES (?, ?, ?, ?)
And JDBC driver obviously returns the following error:
java.sql.SQLException: General error: Column 'userID' specified twice
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.PreparedStatement.executeUpdate(Unknown Source)
at org.gjt.mm.mysql.PreparedStatement.executeUpdate(Unknown Source)
at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeUpdate(LocalPreparedStatement.java:308)
…
If I remove userID field from key-fields into jbosscmp-jbd.xml relationship or from cmp-field into ejb-jar.xml entity bean, JBoss is unable to do the relationship.
So my question is very simple: how can I avoid the previous error and thus have a relationship working perfectly?
This issue makes me crazy and freezes my work since 3 days ago. I am sure that I do a very simple mistake. Probably in jbosscmp-jdbc.xml. But which one? I have read the DTD and made several tests but no success. Some help will be so cool.
Thanks,
Alexis
