9 Replies Latest reply on Oct 10, 2002 11:06 AM by James

    query join clause in cmr

    James Newbie

      Hi,

      I have been trying to setup a CMR with a customer table and a custmer transaction table. It is more than just a simple id FK join though. Can JBoss support the following using CMP and CMR? How is the join defined since type isn't in the custmer table?

      Customer
      id numeric,
      name varchar,
      addr1 varchar

      Customertrans
      id numeric,
      customerid numeric,
      amount numeric,
      type numeric

      The join query is
      select c.name,c.addr,t.amount from customer c,customertrans t where t.customerid=c.id and t.type=1

        • 1. Re: query join clause in cmr
          Alexey Loubyansky Master

          Not sure I understood you completely.
          Yes, you can implement relationship between the two.
          And then you can execute select statement with JDBC.
          But it won't work as EJB-QL. In EJB-QL results of select can be beans' local interface or cmp field.
          Or what did you mean?

          • 2. Re: query join clause in cmr
            James Newbie

            I wanted to setup a one to many between customer and customertrans. In SQL I would join the two with the above mentioned sql join. How do I define the join criteria in the CMR delcaration. I see where I can specify customerid=id for FK, but where do I tie in the extra type column?

            • 3. Re: query join clause in cmr
              Alexey Loubyansky Master

              You need to implement relationships between two entities?
              Declare CMR accessors in beans and configure relationships in ejb-jar.xml and jbosscmp-jdbc.xml.
              Be more specific in questions, please.

              • 4. Re: query join clause in cmr
                James Newbie

                Sorry, this kind of hard to describe. I understand how to setup relationships in ejb-jar.xml and jbosscmp-jdbc.xml. However not all underlying customertrans records in the database relate to the customer records, only those with a type of "1". In other words if I just setup a relationship between customer and customertrans jbosscmp will give me customertrans entries that have a type of "1","2","3", etc.. The only ones that "relate" to the customer are ones with type of "1". does this make sense? thanks for your help and patients.

                • 5. Re: query join clause in cmr
                  Alexey Loubyansky Master

                  I hope I understood you correctly. You don't want to have customertrans with type != 1 among customer CMR fields, right? Then just don't add them to customer's cmr.
                  Or use EJB-QL to fetch cutomertrans with type=1.
                  Did you mean that?

                  • 6. Re: query join clause in cmr
                    James Newbie

                    Yes, that is what I mean. I don't understand what you are saying though by don't add them to the customer's cmr? Won't the container automtically add them?

                    In EJB-QL, isn't that only for finders. If the container is managing the relationship, then how am I going to have control over that?

                    • 7. Re: query join clause in cmr
                      Alexey Loubyansky Master

                      CMR fields are configured with foreign keys. But if you want to fetch some entities that have a foreign key and some specific cmp field value, then you have to write a query for it.
                      Does it solve your problem?

                      • 8. Re: query join clause in cmr
                        Alexey Loubyansky Master

                        By wrtting a query, I meant implementing a finder or select method. It won't be a CMR accessor.

                        • 9. Re: query join clause in cmr
                          James Newbie

                          That makes sense. Thanks for all your help. I suppose the only other option would be to add a "hardcoded" type field to the customer EJB and have a compound foreign key, so that type would always = 1.