HELP! CMR -- generated SQL doesn't look right...
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!