1 Reply Latest reply on Feb 26, 2004 2:39 AM by zinumolbap

    Problem with many-to-many relationships under MySQL

    zinumolbap Newbie

      Hi 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.