• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

EJBQL Exercise

 
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


/bow
 
Valentin Crettaz
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
She's out of the country right now, toppling an unauthorized dictatorship. Please leave a message with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic