Problem with many-to-many relationships under MySQL
zinumolbap Feb 25, 2004 1:35 PMHi all,
I'm having some troubles with many-to-many bidirectional relationships in MySQL.
There're two entity beans (OrderBean and ShopBean) wich are related this way.
An order can be placed in many shops and a shop can have many orders.
Everything deploys correctly, but when I try to create an Order I get this exception from JBoss:
2004-02-25 19:51:47,649 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Order] Error checking if entity exists
java.sql.SQLException: 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 'order WHERE idOrder=7' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:304)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertPKCreateCommand.beforeInsert(JDBCInsertPKCreateCommand.java:83)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:137)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:269)
at org.jboss.ejb.EntityContainer.createLocalHome(EntityContainer.java:581)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
....
Order table has idOrder as primary key and Shop table has idShop. There is also a mapping table called order_shop
with two fields : idOrder and idShop.
Here's the most important code in both beans :
public abstract class OrderBean extends EntityAdapter { public java.lang.Integer ejbCreate(Float total,CustomerLocal customer, java.util.Collection shops) throws javax.ejb.CreateException { } public void ejbPostCreate(Float total,CustomerLocal customer, java.util.Collection shops) throws javax.ejb.CreateException { setCustomer (customer); java.util.Collection myShops = this.getShops(); myShops.addAll(shops); } .... public abstract java.util.Collection getShops (); public abstract void setShops (java.util.Collection shops); .... } public abstract class ShopBean extends EntityAdapter { public java.lang.Integer ejbCreate(ShopTO data, java.util.Collection orders) throws javax.ejb.CreateException {...} public void ejbPostCreate(ShopTO data, java.util.Collection orders) throws javax.ejb.CreateException { java.util.Collection myOrders = getOrders(); myOrders.addAll(orders); } ... public abstract java.util.Set getOrders (); public abstract void setOrders (java.util.Set orders); ... }
and the relation in ejb-jar.xml is as follows :
<ejb-relation > <ejb-relation-name>order-shops</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>orders-placed-in-shop</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>Shop</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>orders</cmr-field-name> <cmr-field-type>java.util.Set</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>order-is-placed-in-shops</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>Order</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>shops</cmr-field-name> <cmr-field-type>java.util.Collection</cmr-field-type> </cmr-field> </ejb-relationship-role> </ejb-relation>
What am I doing wrong? Any help? Any idea to solve this?
Thanks in advance.
Pablo.