EJBQL Exercise

[Alec Lee]

An interesting question created by me (i am not sure about my answer so help me to verify):

Given 2 entity beans, and their relationships:

person (1) <---------> phoneNo (0..*)

(ie. a person entity has many phone numbers while each phone number belong to exactly 1 person)

Write a finder or select method and the ejbql such that from a given phoneNo entity it returns all phoneNo entities that also belongs to the same person as the given phoneNo belong to.

[Peter Wiederkehr]

If it would be allowed (but it isnt't):

SELECT p.phoneNrs FROM Person p, IN(p.phoneNrs) nr WHERE nr = ?1

You have to select the Person to which the number belongs and the call getPhoneNrs on that person.

[Alec Lee]

SELECT p.phoneNrs FROM Person p, IN(p.phoneNrs) nr WHERE nr = ?1

As you said, p.phoneNrs is a collection type CMR field so it is not correct.

Anybody want to try? I will post what I think should be answer later.

[Valentin Crettaz]

Person (1) <---------> PhoneNumber (0..*)

Since the relationship is bidirectional, we don't even need to use collection paths, instead we can use two identification variables as follows:

SELECT OBJECT(pn2)
FROM PhoneNumber pn1, pn2
WHERE pn1 = ?1 AND pn1.person = pn2.person

Briefly, we return all numbers that are related to the same person as the number given in argument.

[Peter Wiederkehr]

/bow

[Valentin Crettaz]

Actually, according to your question statement, we shouldn't return the phone number that has been given in argument.

The updated EJB-QL query would be:

SELECT OBJECT(pn2)
FROM PhoneNumber pn1, pn2
WHERE pn1 = ?1 AND pn1 <> pn2 AND pn1.person = pn2.person

[Alec Lee]

The version I created is:

SELECT Object(q) FROM phoneNo n, IN(n.person.phoneNos) q WHERE n=?1

(which also include the given phoneNo)

[Alec Lee]

SELECT OBJECT(pn2)
FROM PhoneNumber pn1, pn2...

Is this syntax ok? I guess FROM must be of them form
FROM identification_variable_declaration, identification_variable_declaration, ...

where identification_variable_declarationmust be either IN(...) syntax or abstract_schema_name [AS] identifier.

So I guess this syntax (if allowed) is required:

FROM PhoneNumber pn1, PhoneNumber pn2

But I am not sure if one schema can be assigned to 2 identifers

[Valentin Crettaz]

You are absolutely right

The correct query should be:

SELECT OBJECT(pn2)
FROM PhoneNumber pn1, PhoneNumber pn2
WHERE pn1 = ?1 AND pn1 <> pn2 AND pn1.person = pn2.person

Thanks