Combine two selects into one...

[aparaapara]

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_

[raja05]

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

[aparaapara]

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_

[raja05]

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