• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

EJBQL Exercise

 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


/bow
 
Valentin Crettaz
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic