Win a copy of AWS Security this week in the Cloud/Virtualization forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

EJBQL Exercise

 
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.
 
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.
 
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • 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
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • 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
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • 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
 
    Bookmark Topic Watch Topic
  • New Topic