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?

      Thanks.
      -AP_

        • 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.

          -Raj

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

            Raj,

            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.

            -AP_

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

              Try having this in ur jbosscmp-jdbc.xml

              ................
              <read-ahead>
              on-find
              <eager-load-group>abc</eager-load-group>
              </read-ahead>

              <load-group>
              <load-group-name>abc</load-group-name>
              <field-name>ur cmr field</field-name>
              </load-group>


              This should preload ur CMR fields along with ur find