Hi, really frustating... been trying to get my CMR setup for last three days but
without much success (shame).
My setup is simple. I have three beans:
1. UserBean
2. GroupBean
3. GroupMembershipBean
Each User (UserBean) can belong to multiple Group(s) (GroupBean).
1. UserBean maps to table USERS, table schema:
Primary key: UIN
Other fields: login, email, ... etc
2. GroupBean maps to table GROUPS, table schema:
Primary key: UIN
Other fields: name, decription, ... etc
3. GroupMembershipBean maps to table GROUPMEMBERSHIP, table schema:
Composite Primary Key: {GROUPUIN, USERUIN}
Foreign key on USERUIN and GROUPUIN that points to table USERS and table GROUPS
There's a One-To-Many relationship between GroupBean (One-side) and GroupMembership
(Many-side).
1. GroupBean:
| /**
| * @ejb.bean
| * name="GroupEJB"
| * local-jndi-name="ejb/UserGroup"
| * display-name="User Group EJB"
| * description="User Group EJB"
| * cmp-version="2.x"
| * type="CMP"
| * view-type="local"
| * schema="Group"
| * reentrant="false"
| * primkey-field="UIN"
| *
| * @ejb.persistence
| * table-name="GROUPS"
| *
| * @ejb.finder
| * query="SELECT OBJECT(g) FROM Group AS g"
| * signature="java.util.Collection findAllGroups()"
| *
| * @ejb.interface
| * local-extends="javax.ejb.EJBLocalObject,
java.lang.Comparable, java.io.Serializable"
| * local-class="com.aa.samples.interfaces.GroupLocal"
| *
| * @ejb.home
| * local-class="com.aa.samples.interfaces.GroupLocalHome"
| *
| * @jboss.persistence
| * datasource="java:/jdbc/dev01"
| * datasource-mapping="mySQL"
| * create-table="false"
| * remove-table="false"
| */
| public abstract class GroupBean implements EntityBean, Serializable, Comparable {
| ... other stuff ...
| /**
| * @ejb.relation
| * name="GroupEJB-GroupMembershipEJB"
| * role-name="GroupEJB-has-GroupMembershipEJB"
| *
| * @ejb.interface-method
| */
| public abstract Collection getGroupMembershipBean();
| public abstract void setGroupMembershipBean(Collection memberships);
| ... other stuff ...
| }
|
2. GroupMembershipBean:
| /**
| *
| * @ejb.bean
| * name="GroupMembershipEJB"
| * local-jndi-name="ejb/GroupMembership"
| * display-name="Group Membership EJB"
| * description="Group Membership Group EJB"
| * cmp-version="2.x"
| * type="CMP"
| * view-type="local"
| * schema="GroupMembership"
| * reentrant="false"
| *
| * @ejb.persistence
| * table-name="GROUPMEMBERSHIP"
| *
| * @ejb.pk
| * class="com.aa.samples.primarykeys.GroupMembershipKey"
| *
| * @ejb.interface
| * local-extends="javax.ejb.EJBLocalObject,
java.lang.Comparable, java.io.Serializable"
| * local-class="com.aa.samples.interfaces.GroupMembershipLocal"
| *
| * @ejb.home
| *
local-class="com.aa.samples.interfaces.GroupMembershipLocalHome"
| *
| * @jboss.persistence
| * datasource="java:/jdbc/dev01"
| * datasource-mapping="mySQL"
| * create-table="false"
| * remove-table="false"
| */
| public abstract class GroupMembershipBean implements EntityBean, Serializable,
Comparable {
| ... other stuff ...
| /**
| * @ejb.persistent-field
| * @ejb.persistence
| * column-name="GROUPUIN"
| * sql-type="INTEGER"
| * @ejb.pk-field
| * @ejb.interface-method
| *
| */
| public abstract Integer getGroupUIN();
|
| /**
| * @ejb.interface-method
| */
| public abstract void setGroupUIN(Integer groupUIN);
|
| /**
| * @ejb.persistent-field
| * @ejb.persistence
| * column-name="USERUIN"
| * sql-type="INTEGER"
| * @ejb.pk-field
| * @ejb.interface-method
| */
| public abstract Integer getUserUIN();
|
| /**
| * @ejb.interface-method
| */
| public abstract void setUserUIN(Integer userUIN);
|
| /**
| * @ejb.relation
| * name="GroupEJB-GroupMembershipEJB"
| * role-name="GroupMembershipEJB-has-GroupEJB"
| * target-multiple="yes"
| * cascade-delete="yes"
| *
| * @jboss.relation
| * related-pk-field="UIN"
| * fk-column="GROUPUIN"
| *
| * @ejb.interface-method
| */
| public abstract GroupLocal getGroup();
|
| public abstract void setGroup(GroupLocal group);
| ... other stuff ...
| }
|
I ran XDoclet, BOTH "ejb-jar.xml" and "jbosscmp-jdbc.xml" looks right:
1. ""ejb-jar.xml"
... other stuff ...
<ejb-relation >
<ejb-relation-name>GroupEJB-GroupMembershipEJB</ejb-relation-name>
<ejb-relationship-role >
<ejb-relationship-role-name>GroupMembershipEJB-has-GroupEJB</ejb-relationship-role-name>
Many
<cascade-delete/>
<relationship-role-source >
<ejb-name>GroupMembershipEJB</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>group</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role >
<ejb-relationship-role-name>GroupEJB-has-GroupMembershipEJB</ejb-relationship-role-name>
One
<relationship-role-source >
<ejb-name>GroupEJB</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>groupMembershipBean</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
... other stuff ...
2. "jbosscmp-jdbc.xml"
... other stuff ...
<ejb-relation>
<ejb-relation-name>GroupEJB-GroupMembershipEJB</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>GroupMembershipEJB-has-GroupEJB</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>GroupEJB-has-GroupMembershipEJB</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>UIN</field-name>
<column-name>GROUPUIN</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>
... other stuff ...
The problem is,
| ...
| GroupLocal group=groupHome.findByPrimaryKey(groupUIN);
| Collection membershiplist=group.getGroupMembershipBean(); //EXCEPTION THROWN
| ...
|
And the exception msg is:
| javax.ejb.TransactionRolledbackLocalException: Load relation failed;
CausedByException is:
| Syntax error or access violation message from server: "You have an error in
your SQL syntax. Check the manual that corresponds to your MySQL server version for
the right syntax to use near 'FROM GROUPMEMBERSHIP WHERE (GROUPUIN=1)' at line 1";
CausedByException is:
| Load relation failed; CausedByException is:
| Syntax error or access violation message from server: "You have an error in
your SQL syntax. Check the manual that corresponds to your MySQL server version for
the right syntax to use near 'FROM GROUPMEMBERSHIP WHERE (GROUPUIN=1)' at line 1"
|
I used p6spy (http://www.p6spy.com/) to intercept any SQL to database, and I found:
| 1099567741656|-1||resultset|SELECT name, description, isSuspended FROM GROUPS
WHERE (UIN='1')|description = Administrators Group, name = Administrators
|
| 1099567755138|161|0|statement|SELECT FROM GROUPMEMBERSHIP WHERE
(GROUPUIN=?)|SELECT FROM GROUPMEMBERSHIP WHERE (GROUPUIN='1')
|
Notice on second line:
SELECT FROM (GROUPUIN='1')
instead of:
SELECT * FROM (GROUPUIN=1)
There're two things weird about this:
a. SELECT * --- The "*" is missing - this is not present in error message when I
stepped thru the code.
b. GROUPUIN='1' --- why's there's single quote around '1'?
Help! I've been staring at this for sometime now and it's getting desparate. HELP!
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3853850#3853850
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3853850
-------------------------------------------------------
This SF.Net email is sponsored by:
Sybase ASE Linux Express Edition - download now for FREE
LinuxWorld Reader's Choice Award Winner for best database on Linux.
http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
