9 Replies Latest reply on Apr 11, 2004 12:31 PM by sesques

    Relationships and Foreign Keys

    Michael Forster Newbie

      Sorry to be keep asking similar questions all the time but I am trying to get JBoss working after moving from another less useful bean server (openejb) and am trying to get the Container to manage the relationships and CMP using Foreign Keys, I have started with the simplest beans to allow me to understand and get the technique for how it works and have scoured the internet for help and advice and it all seems to come up wanting, I have had several errors from "You can have only 1 PK" to Cannot find cmpfield id in bean transtype.
      I was under the impression that if I use a Foreign key I can pass the object in and it will save the reference automatically in the database (and vice versa when requesting the Bean - it will return the sub Bean as well.

      Here are the DB tables I am trying to get JBoss to create.

      transtype
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      tt_desc VARCHAR(30),
      PRIMARY KEY ( `id` )

      transaction
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
      tr_date DATETIME,
      tr_state INTEGER,
      tr_summary TINYINT,
      tr_non_refundable TINYINT,
      tr_type BIGINT UNSIGNED NOT NULL <--- This is the Foreign Key
      PRIMARY KEY ( `id` )


      In the Beans i am using different names in the bean (but this should also work if configured correctly) - DATETIME maps to a Date() Object, and other fields map to objects (we are not using int long etc....)

      the TransTypeBean works fine for Create using the Autonumber key
      and the TransTypeBean findByPrimaryKey works as well.

      Now we are having problems getting the TransactionBean to create a new entry in the DB,

      The Create is declared as follows
      public Object ejbCreate( Date tr_date, Integer tr_state, Boolean tr_summary, Boolean tr_non_refundable, TransType tr_type)

      how do we get the tr_type to save just the id field in the db - is it as simple as setTr_type(tr_type.getId());

      also when doing a findByPrimaryKey() on the Transaction entity will it return automatically the TransType Object or will I have to do some other stuff to get it out?

      my ejb-jar has the following entries

      <entity>
       <ejb-name>TransTypeBean</ejb-name>
       <home>com.ingotz.entity.TransTypeHome</home>
       <remote>com.ingotz.entity.TransType</remote>
       <local-home>com.ingotz.entity.TransTypeLocalHome</local-home>
       <local>com.ingotz.entity.TransTypeLocal</local>
       <ejb-class>com.ingotz.entity.TransTypeBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <!--<prim-key-class>java.lang.Object</prim-key-class>-->
       <prim-key-class>java.lang.Long</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-field>
       <field-name>Id</field-name>
       </cmp-field>
       <cmp-field>
       <field-name>Description</field-name>
       </cmp-field>
       <primkey-field>Id</primkey-field>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>TransTypeBeanAPS</abstract-schema-name>
      
       <resource-ref>
       <res-ref-name>jdbc/mysql</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
       </resource-ref>
       </entity>
      
       <entity>
       <ejb-name>TransactionBean</ejb-name>
       <home>com.ingotz.entity.TransactionHome</home>
       <remote>com.ingotz.entity.Transaction</remote>
       <local-home>com.ingotz.entity.TransactionLocalHome</local-home>
       <local>com.ingotz.entity.TransactionLocal</local>
       <ejb-class>com.ingotz.entity.TransactionBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <!--<prim-key-class>java.lang.Object</prim-key-class>-->
       <prim-key-class>java.lang.Long</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-field><field-name>id</field-name></cmp-field>
       <cmp-field><field-name>tr_date</field-name></cmp-field>
       <cmp-field><field-name>tr_state</field-name></cmp-field>
       <cmp-field><field-name>tr_summary</field-name></cmp-field>
       <cmp-field><field-name>tr_non_refundable</field-name></cmp-field>
       <cmp-field><field-name>tr_type</field-name></cmp-field>
       <primkey-field>id</primkey-field>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>TransactionBeanAPS</abstract-schema-name>
      
       <resource-ref>
       <res-ref-name>jdbc/mysql</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
       </resource-ref>
       </entity>
      
      
       </enterprise-beans>
      
       <relationships>
       <ejb-relation>
       <ejb-relation-name>Type-Transaction</ejb-relation-name>
       <ejb-relationship-role>
       <ejb-relationship-role-name>TransTypeBeanEnd</ejb-relationship-role-name>
       <multiplicity>One</multiplicity>
       <relationship-role-source>
       <ejb-name>TransTypeBean</ejb-name>
       </relationship-role-source>
       </ejb-relationship-role>
       <ejb-relationship-role>
       <ejb-relationship-role-name>TransactionBeanEnd</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source>
       <ejb-name>TransactionBean</ejb-name>
       </relationship-role-source>
       <cmr-field>
       <cmr-field-name>Tr_type</cmr-field-name>
       </cmr-field>
       </ejb-relationship-role>
       </ejb-relation>
      </relationships>
      


      My jbosscmp-jdbc.xml file has the following relationship entries
      <relationships>
      <ejb-relation>
      <ejb-relation-name>Type-Transaction</ejb-relation-name>
      <foreign-key-mapping />
      <ejb-relationship-role>
      <ejb-relationship-role-name>TransTypeBeanEnd</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>Id</field-name> <!-- Property this end -->
      <column-name>tr_type</column-name> <!-- Foreign key at other end -->
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>TransactionBeanEnd</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>
      </relationships>
      


      I do hope someone can help me understand what I am missing.


      Mike.

        • 1. Re: Relationships and Foreign Keys
          sesques Novice

          Hi,

          Can you post the exact error log you have because I don't really understand your problem:

          1 : If your tables must have the primary key auto incremented by the database, you must declared an <unknown-pk> tag for each table
          2 : The CMR fields (the foreign key) must be set in ejbPostCreate, not in ejbCreate, and you set the CMR field not the foreign CMP field
          3 : JBoss is buggeg with database auto-incremented key. So you must set the CMP field tr_type in ejbCreate with setTr_type(tr_type.getId()), the sets the CMR field tr_type in ejbPostCreate.

          In your description (ejb-jar and jbosscmp-jdbc), you are not in database auto-incremented configuration. If this is right, you can delete the CMP field tr_type, you don't need it. Just work with the CMR field.
          Also, in your case, you should experiment problems naming the CMP and CMR field tr_type and Tr_Type because the getters will have the same name getTr_type();



          • 2. Re: Relationships and Foreign Keys
            Michael Forster Newbie

            The Database is being created with AutoIncrement, but The issue we have is not exactly that, the fields id are auto_increment but we are not going to be creating records using the relationship (in this case) with the related field having to create an autoincrement, We have several Predefined TransTypes, that each transaction can refer to one of them. We would do a find to get the transtype and pass it into the transaction create function - So you are saying that we pass the Class in anyway but the setTr_type function that we use is in the postcreate not the ejbcreate function for it to process the class correctly. and please enlighten me - the cmr field is the field in the table that is the FK, and that field doesn't have to be declared as a CMP field either as it is processed in the postcreate.... The errors we have been having are varied from Cant find tr_type to cant have 2 primary keys - We tried the using the unknown pk for the autoincrement at first but somehow we have it working correctly here anyway....
            but we need to enforce the field tr_type as a bigint not a blob as we need to just store the reference -
            If we are doing that will it work automatically or not - are there any easy to understand tutorials that cover the system we are using?

            Mike.

            • 3. Re: Relationships and Foreign Keys
              Michael Forster Newbie

              I changed the code to have a setTransType() which takes the TransType class as a parameter, and I changed the CMR field to be TransType,

              I now get this error

              javax.ejb.CreateException: Could not create entity:java.sql.SQLException: Column not found message from server: "Unknown column 'TransType' in 'field list'"
              Could not create entity:java.sql.SQLException: Column not found message from server: "Unknown column 'TransType' in 'field list'"
              Could not create entity:java.sql.SQLException: Column not found message from server: "Unknown column 'TransType' in 'field list'"

              How do you get the fields linking properly?

              • 4. Re: Relationships and Foreign Keys
                sesques Novice

                1) Database auto-increment : You don't experiment this yet because as you says, the TransTypes are predefined (so you do not create records in this table) but the transaction table have the same problem. As I understand, you don't suuceed to create a new transaction record. But if you says that it works anyway for you, it's OK for me.

                2) CMR field not CMP : This is correct. You don't have to create the CMP field tr_type. The CMR field is sufficient. Then you can set it in ejbPostCreate passing the TransType class. In addition, in your case, naming both the CMP and the CMR can occurs in a conflict for the getters and the setters.

                3) If you experiment a "cannot insert null value" or "explict value" for the primary key creating your Transation record, remember that database auto-incrementted keys are handling through unknown-pk, without declaring teh CMP field for the primary key id.

                Pascal

                • 5. Re: Relationships and Foreign Keys
                  sesques Novice

                  Do you change your jbosscmp-jdbc.xml ?
                  The mapping is done in this file.
                  It should be set to


                  <ejb-relation>
                  <ejb-relation-name>Type-Transaction</ejb-relation-name>
                  <foreign-key-mapping />
                  <ejb-relationship-role>
                  <ejb-relationship-role-name>TransTypeBeanEnd</ejb-relationship-role-name>
                  <key-fields>
                  <key-field>
                  <field-name>TransType</field-name>
                  <column-name>tr_type</column-name>
                  </key-field>
                  </key-fields>
                  </ejb-relationship-role>
                  <ejb-relationship-role>
                  <ejb-relationship-role-name>TransactionBeanEnd</ejb-relationship-role-name>
                  <key-fields/>
                  </ejb-relationship-role>
                  </ejb-relation>

                  • 6. Re: Relationships and Foreign Keys
                    sesques Novice

                    Sorry, I made a mistake in the previous jbosscmp-jdbc.xml:
                    It shoul be:

                    <relationships>
                    <ejb-relation>
                    <ejb-relation-name>Type-Transaction</ejb-relation-name>
                    <foreign-key-mapping />
                    <ejb-relationship-role>
                    <ejb-relationship-role-name>TransTypeBeanEnd</ejb-relationship-role-name>
                    <key-fields>
                    <key-field>
                    <field-name>id</field-name>
                    <column-name>tr_type</column-name>
                    </key-field>
                    </key-fields>
                    </ejb-relationship-role>
                    <ejb-relationship-role>
                    <ejb-relationship-role-name>TransactionBeanEnd</ejb-relationship-role-name>
                    <key-fields/>
                    </ejb-relationship-role>
                    </ejb-relation>
                    </relationships>
                    



                    and ejb-jar.xml :
                     <entity>
                     <ejb-name>TransTypeBean</ejb-name>
                     <home>com.ingotz.entity.TransTypeHome</home>
                     <remote>com.ingotz.entity.TransType</remote>
                     <local-home>com.ingotz.entity.TransTypeLocalHome</local-home>
                     <local>com.ingotz.entity.TransTypeLocal</local>
                     <ejb-class>com.ingotz.entity.TransTypeBean</ejb-class>
                     <persistence-type>Container</persistence-type>
                     <prim-key-class>java.lang.Long</prim-key-class>
                     <reentrant>False</reentrant>
                     <cmp-field>
                     <field-name>Id</field-name>
                     </cmp-field>
                     <cmp-field>
                     <field-name>Description</field-name>
                     </cmp-field>
                     <primkey-field>Id</primkey-field>
                     <cmp-version>2.x</cmp-version>
                     <abstract-schema-name>TransTypeBeanAPS</abstract-schema-name>
                    
                     <resource-ref>
                     <res-ref-name>jdbc/mysql</res-ref-name>
                     <res-type>javax.sql.DataSource</res-type>
                     <res-auth>Container</res-auth>
                     </resource-ref>
                     </entity>
                    
                     <entity>
                     <ejb-name>TransactionBean</ejb-name>
                     <home>com.ingotz.entity.TransactionHome</home>
                     <remote>com.ingotz.entity.Transaction</remote>
                     <local-home>com.ingotz.entity.TransactionLocalHome</local-home>
                     <local>com.ingotz.entity.TransactionLocal</local>
                     <ejb-class>com.ingotz.entity.TransactionBean</ejb-class>
                     <persistence-type>Container</persistence-type>
                     <prim-key-class>java.lang.Long</prim-key-class>
                     <reentrant>False</reentrant>
                     <cmp-field><field-name>id</field-name></cmp-field>
                     <cmp-field><field-name>tr_date</field-name></cmp-field>
                     <cmp-field><field-name>tr_state</field-name></cmp-field>
                     <cmp-field><field-name>tr_summary</field-name></cmp-field>
                     <cmp-field><field-name>tr_non_refundable</field-name></cmp-field>
                     <primkey-field>id</primkey-field>
                     <cmp-version>2.x</cmp-version>
                     <abstract-schema-name>TransactionBeanAPS</abstract-schema-name>
                    
                     <resource-ref>
                     <res-ref-name>jdbc/mysql</res-ref-name>
                     <res-type>javax.sql.DataSource</res-type>
                     <res-auth>Container</res-auth>
                     </resource-ref>
                     </entity>
                    
                    
                     </enterprise-beans>
                    
                     <relationships>
                     <ejb-relation>
                     <ejb-relation-name>Type-Transaction</ejb-relation-name>
                     <ejb-relationship-role>
                     <ejb-relationship-role-name>TransTypeBeanEnd</ejb-relationship-role-name>
                     <multiplicity>One</multiplicity>
                     <relationship-role-source>
                     <ejb-name>TransTypeBean</ejb-name>
                     </relationship-role-source>
                     </ejb-relationship-role>
                     <ejb-relationship-role>
                     <ejb-relationship-role-name>TransactionBeanEnd</ejb-relationship-role-name>
                     <multiplicity>Many</multiplicity>
                     <relationship-role-source>
                     <ejb-name>TransactionBean</ejb-name>
                     </relationship-role-source>
                     <cmr-field>
                     <cmr-field-name>tr_type</cmr-field-name>
                     </cmr-field>
                     </ejb-relationship-role>
                     </ejb-relation>
                    </relationships>
                    


                    Then it should work. If not, post your code.
                    Sorry for the mistake.


                    • 7. Re: Relationships and Foreign Keys
                      Michael Forster Newbie

                      Now I am getting this errror when trying to call the create function,

                      13:44:41,220 ERROR [LogInterceptor] EJBException, causedBy:
                      java.lang.ClassCastException
                      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.setInstanceV
                      alue(JDBCCMRFieldBridge.java:773)
                      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.setValue(JDB
                      CCMRFieldBridge.java:624)
                      at org.jboss.ejb.plugins.cmp.bridge.EntityBridgeInvocationHandler.invoke
                      (EntityBridgeInvocationHandler.java:121)
                      at org.jboss.proxy.compiler.Runtime.invoke(Runtime.java:59)
                      at com.ingotz.points.core.entity.TransactionBean$Proxy.setTr_type(<gener
                      ated>)
                      at com.ingotz.points.core.entity.TransactionBean.ejbPostCreate(Unknown S
                      ource)
                      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
                      java:39)
                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
                      sorImpl.java:25)
                      at java.lang.reflect.Method.invoke(Method.java:324)
                      at org.jboss.ejb.plugins.CMPPersistenceManager.postCreateEntity(CMPPersi
                      stenceManager.java:240)
                      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.post
                      CreateEntity(CachedConnectionInterceptor.java:277)
                      at org.jboss.ejb.EntityContainer.postCreateHome(EntityContainer.java:725
                      )
                      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
                      java:39)
                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
                      sorImpl.java:25)
                      at java.lang.reflect.Method.invoke(Method.java:324)
                      at org.jboss.ejb.EntityContainer$ContainerInterceptor.invoke(EntityConta

                      The line that causes the problem is as follows (in a JSP file)

                      Transaction tranMan = tran.create(new Date(),new Integer(1),new Boolean(false), new Boolean(false),transtype);


                      The TransactionHome Create function is as follows
                      public Transaction create( Date tr_date, Integer tr_state, Boolean tr_summary, Boolean tr_non_refundable, TransType tr_type ) throws RemoteException, CreateException;

                      The ejbcreate code is as follows

                      public Long ejbCreate( Date tr_date, Integer tr_state, Boolean tr_summary, Boolean tr_non_refundable, TransType tr_type)
                      throws CreateException
                      {
                      setTr_date(tr_date);
                      setTr_state(tr_state);
                      setTr_summary(tr_summary);
                      setTr_non_refundable(tr_non_refundable);
                      return null;
                      }

                      public void ejbPostCreate(Date tr_date, Integer tr_state, Boolean tr_summary, Boolean tr_non_refundable, TransType tr_type )
                      throws CreateException
                      {
                      setTr_type(tr_type);
                      }

                      • 8. Re: Relationships and Foreign Keys
                        Matthew Greet Newbie

                        I'm working with mpforste on this problem. Only having tr_type (the foreign key) as a cmr-field means when JBoss creates the table in mySQL, the foreign key field is a BLOB. The foreign key is supposed to be a BIGINT and creating transactions fails when it is a BIGINT.

                        When I leave it as a BLOB, code works as I expected, including creating a transaction with a transtype object, retrieving an associated transtype from a transaction and setting its description, which appears in the transtype table. In other words, we have CMR but using an object reference, not a foreign key field.

                        Surely this can't be JBoss's method of persisting relationships? It can't be made to use a relational model or have I misconfigured something? I would prefer to use a relational persistence scheme so other reporting tools can read the database.

                        • 9. Re: Relationships and Foreign Keys
                          sesques Novice

                          I agree with you warmachine, I just don't use myself database created by JBoss, but with a standard modeling tool from Sybase, so I don't experiment this problem. As you says, it is not a good way to lets Jboss create foreign keys itself with a standard relationship persistance method. So there is no restriction to declare the foreign key as an additional CMP field. Just do not use the same field name.

                          Concerning the error, TransType is the remote class. For CMR fields, you must use the local interface, not the remote.