XDoclet, JBossQL and EJB QL: Joining multiple tables (2+) possible?

[Karl Beowulph]

Howdy.

I'm trying to figure out how I can join multiple tables together in EJB QL using XDoclet.

Suppose for the moment I have three tables. School, Car and Person.

In my PersonEntityBean I've got the following method:

  
   /**
     * Returns a related collection of Schools
     *
     * @return A Collection of related Schools
     * @ejb.interface-method
     *    view-type="local"
     * @ejb.relation
     *    name="School-Person"
     *    role-name="Person-School"
     *    target-ejb="SchoolBean"
     *    target-multiple="no"
     *    target-role-name="School-Person"
     *    cascade-delete="false"
     * @jboss.target-relation
     *    fk-constraint="true"
     *    related-pk-field="SCHOOL_NUM"
     *    fk-column="SCHOOL_NUM"
     */
 
    public abstract java.util.Collection getSchools();

But that simply gets me all the schools that that any particular person belongs to.

What I want is a way to retrieve all the people who are in a certain school and drive a particular car.

The SQL would be (approximately)

SELECT personID 
FROM Person-School, Person-Car
WHERE Person-School.personID = Person-Car.personID
AND Person-School.schoolID = X
AND Person-Car.schoolID = Y

Note that School and Car have no relation to each other at all, asides from simply sharing the non-key personID field.

Is what I'm trying to do possible with CMP EJB QL or JBossQL?

[Karthik Guru]

If you have the following relationships defined

schools.persons //school 1 -> (many) person
person.cars // person 1 -> (many) car

then you should be able to define this method in the SchoolBean

Collection findPersonsThatGotoThisSchoolAndDriveThisCar(String schoolName,String model);
 

select object(p) from School as s, in (s.persons) as person , in (p.cars) as car where car.model = ? and s.schoolName = ?
 

Please check
@ejb-finder tag to embed this query.

[Arlet Contreras]

Hi, The answer is very good from my point of view, it helped me a lot, thank you.
And what do you put inside the tag * @jboss:table-name table-name="table" ?
And which are the methods that the CMP includes (get and set)? which table they belong?

cheers.