1 Reply Latest reply on Apr 26, 2005 9:56 PM by Amit Basu

    Problems with <left-join> in many-to-many relations

    ara Newbie


      Here's a problem that I have not been able to find a workaround to.
      I'd be thankful for any help/insights.

      I have two cmp-ejbs A and B, that have a many-to-many CMR-relation.
      Starting from an instance of A i want to pre-load the related B-objects.

      I try to optimize this by adding <left-join> tags to jbosscmp-jdbc.xml.
      This works fine if there ARE some B-objects related to A , but if not
      i get an exception "Internal error setting instance field IdB".
      The reason seems to be that JBoss tries to initialize a B-object from a NULL-valued key-field.

      Here is the sql that JBoss produces (table-names edited by me for clarity):

      SELECT A.IdA, A.Description, B.IdB, B.Attribute FROM
      LEFT OUTER JOIN Attribute B ON B.IdB=AtoB.IdB WHERE A.IdA=?

      Here, the fields AtoB.IdB and B.IdB and B.Attribute will be null when
      there is no match.
      So, JBoss tries to construct a B-object from the read-ahead resultset
      even if there is no such object!

      I have not tested if this happens also for empty one-to-many relations
      of if this is just a problem with many-to-many relations.

      I've tried to change the type of IdB to be Integer instead of int and hence
      allow null's. This works in a way, but requires that i also make all other fields in B
      (e.g the field B.Attribute) into something that can be null.
      I'e a B-object with all fields set to null will be created. I'm not sure i want that..

      *** So, my questions are:
      *** Is there a workaround?
      *** Am I doing something wrong?
      *** Is this fixed in later versions. (I'm using JBoss 3.2.5)


        • 1. Re: Problems with <left-join> in many-to-many relations
          Amit Basu Newbie

          your options are:

          1. use jboss-3.2.7 where it is fixed.

          2. switch to org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler using ql-compiler in jbossjdbc-cmp.xml. (of course you will lose the benefit of LEFT OUTER JOIN, as the finder wont go nested anymore, and also get all fields in the find)

          3. Update jboss.jar's JDBCAbstractCMPFieldBridge.class with the one from jboss-3.2.7 or recompiling in 3.2.6 source.

          I dont really recommend 2 as it is self-defeating.