7 Replies Latest reply on Jan 2, 2002 3:21 PM by Dain Sundstrom

    Wrong SQL for uni-directional one-2-many-relationship

    Jochen Cordes Novice

      Hi !

      Im running into problems when trying to invoke a finder-method on a unidirectional one-many-relationship (Customer-Address). I defined a table-mapping and addresses get inserted accordingly. The query looks like this

      SELECT OBJECT(a) FROM Customer c, IN (c.addresses) AS a WHERE c.id = ?1 AND a.type='shipping'

      where the generated SQL is

      SELECT t2_a.id FROM Customer t1_c, Address t2_a, Customer_Address t3_t1_c_to_addresses WHERE (t1_c.id = ? AND t2_a.type = 'shipping') AND t1_c.id=t4_t1_c_to_addresses.customer_id AND t2_a.id=t4_t
      1_c_to_addresses.address_id

      So I get SQL-errors because t4 should be t3. Is anybody experiencing the same problems or has a clue ?

      Bye,

      Jochen.

        • 1. Re: Wrong SQL for uni-directional one-2-many-relationship
          Marcus Brito Novice

          Make sure the section on your ejb-jar is correct.

          • 2. Re: Wrong SQL for uni-directional one-2-many-relationship
            Jochen Cordes Novice

            Hm ...

            I'm quite sure that they are correct. But nevertheless, here are my configurations (BTW I'm using the recent CVS-Version for Jboss-3.0.0alpha):

            ejb-jar:

            <ejb-relation>
            <ejb-relation-name>Customer-Address</ejb-relation-name>
            <ejb-relationship-role>
            <ejb-relationship-role-name>CustomerHasAddress</ejb-relationship-role-name>
            One
            <relationship-role-source>
            <ejb-name>CustomerEJB</ejb-name>
            </relationship-role-source>
            <cmr-field>
            <cmr-field-name>addresses</cmr-field-name>
            <cmr-field-type>java.util.Collection</cmr-field-type>
            </cmr-field>
            </ejb-relationship-role>
            <ejb-relationship-role>
            <ejb-relationship-role-name>AddressHasCustomer</ejb-relationship-role-name>
            Many
            <relationship-role-source>
            <ejb-name>AddressEJB</ejb-name>
            </relationship-role-source>
            </ejb-relationship-role>
            </ejb-relation>

            jbosscmp-jdbc:

            <ejb-relation>
            <ejb-relation-name>Customer-Address</ejb-relation-name>
            <table-mapping>
            <table-name>Customer_Address</table-name>
            <create-table>true</create-table>
            <remove-table>false</remove-table>
            <ejb-relationship-role>
            <ejb-relationship-role-name>CustomerHasAddress</ejb-relationship-role-name>
            <table-key-fields>
            <table-key-field>
            <field-name>id</field-name>
            <column-name>customer_id</column-name>
            </table-key-field>
            </table-key-fields>
            </ejb-relationship-role>
            <ejb-relationship-role>
            <ejb-relationship-role-name>AddressHasCustomer</ejb-relationship-role-name>
            <table-key-fields>
            <table-key-field>
            <field-name>id</field-name>
            <column-name>address_id</column-name>
            </table-key-field>
            </table-key-fields>
            </ejb-relationship-role>
            </table-mapping>

            I've got a lot more of these kind of relationships, so any help would be appreciated.

            Bye,

            Jochen.

            • 3. Re: Wrong SQL for uni-directional one-2-many-relationship
              Dominique Newbie

              I don't know about table-mapping,

              But if you'd use foreign-key-mapping, according to your ejb-jar,
              your should look like this... I think :

              <ejb-relation>
              <ejb-relation-name>Customer-Address</ejb-relation-name>
              <foreign-key-mapping>
              <ejb-relationship-role>
              <ejb-relationship-role-name>CustomerHasAddress</ejb-relationship-role-name>
              <foreign-key-fields/> <!- the one side does not hold the foreign key -->
              </ejb-relationship-role>
              <ejb-relationship-role>
              <ejb-relationship-role-name>AddressHasCustomer</ejb-relationship-role-name>
              <foreign-key-fields>
              <foreign-key-field>
              <field-name>id</field-name>
              <column-name>address_id</column-name>
              </foreign-key-field>
              </foreign-key-fields>
              </ejb-relationship-role>
              </foreign-key-mapping>
              </ejb-relation>

              Hope it helps

              dk.

              • 4. Re: Wrong SQL for uni-directional one-2-many-relationship
                Jochen Cordes Novice

                Hi !

                I need a table-mapping because its a uni-directional relationship (a customer knows about his addresses, not the other way round). Therefore I can't use a foreign-key mapping without a third table. But I think the problem is not related (or at least not directly) to the mapping, because normal operations on the beans work perfectly, but rather could be a problem with the ejb-ql parser and the IN clause. Is somebody out there who got a query similar to mine up and running ? Im desperate in need of these kind of queries, otherwise i would have to do some workarounds (not talking about changing relationships, additional entities etc.) !!!

                Jochen.

                • 5. Re: Wrong SQL for uni-directional one-2-many-relationship
                  Dain Sundstrom Master

                  This isn't true. The directionality of a relationship at the object level has no bearing on the database mapping. You can have a fk even if you have no accessor. Of course, you may want to restrict this in your database, and in that case you would have to use a relation table.

                  If your are using the 3.0 alpha, there was a bug in the parsing of IN clauses. You may want to build from source.

                  • 6. Re: Wrong SQL for uni-directional one-2-many-relationship
                    Jochen Cordes Novice

                    Hi !

                    Off course you're right, but maybe I forgot to mention that my intention was to reuse the address-entity in different contexts. I always build Jboss 3.0 alpha from source (latest was 18.12.2001). Could you give me a hint when this issue with the IN clause has been solved ?

                    Jochen.

                    • 7. Re: Wrong SQL for uni-directional one-2-many-relationship
                      Dain Sundstrom Master

                      Ok, I didn't closely read your message. This is a new bug. The bug I was referring to was fixed about a week after the alpha was released.

                      Unfortunately, I'm in the middle of rewriting the read ahead cache to support on-find and relationships, so it will be a week or more before I get to this.

                      Will you post a bug report at source forge?