3 Replies Latest reply on Oct 2, 2003 1:28 PM by Raja

    Combine two selects into one...

    Alex Paransky Newbie

      I have two entity beans Component and ComponentType with a Many Components have One ComponentType relation. When ever I load a Component, I ussualy retrieve ComponentType.

      In the above scenario I ussualy get 2 SQL statements executed. One to find Component, and the other to find the ComponentType, as in:

      select ... from component where componentId = ?
      select ... from componentType where componentTypeId = ?

      Is there a way to change jbosscmp descriptor so that when component is loaded, so does component type in a SINGLE SQL statement such as:

      select ... from component c, componentType ct where c.componentTypeId = ct.componentTypeId?


        • 1. Re: Combine two selects into one...
          Raja Master

          Do you need a single EJB-QL that would do it. If so, you can try this

          SELECT OBJECT(d) From component c, in (c.componenttypes) d where d.typeid = ?

          Remember that if u are doing this from Component Bean, you can only do a EJB Select because the return value can only be of the bean in which it is defined. So you cant have a ejb finder doing this.


          • 2. Re: Combine two selects into one...
            Alex Paransky Newbie


            The usage pattern is that, I ussualy first find the Component(s) via some finder, and then execute component.getType(), which results in a single finder for Component and then a number of select ... from componentType where componentTypeId=? calls (1 for each component).

            This is quite expensive. Since I know my usage pattern and each component has only 1 type component (n) -> (1) type, I would like to somehow execute the complex join sql, and set the CMR relation at the time the find is done.


            • 3. Re: Combine two selects into one...
              Raja Master

              Try having this in ur jbosscmp-jdbc.xml


              <field-name>ur cmr field</field-name>

              This should preload ur CMR fields along with ur find