• 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
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

EJB QL & heterogeneous relationships

 
Ranch Hand
Posts: 457
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the following entity relationship:
<blockquote>code:
<pre name="code" class="core">
A ----> B
rel / \
C D
</pre>
</blockquote>

basically:
B is an abstract class,
an instanceA can have a rel that is an instanceC or and instanceD

Is there a way to use EJB QL to search for A's who have a (B that is a) C with name='foo'? (where name is a property on the C entity, not on the B or D)

I'm guessing the answer is no, but i really wish it was yes!

Otherwise I need to refactor this to:
<blockquote>code:
<pre name="code" class="core">
A ----> C
\ relC
----> D
relD
</pre>
</blockquote>
where only one of the C or D will ever be set,
and to make it worse, there's not only C & D,
there are E and F as well, and maybe more in
the future.

ugliness.

Anyway, if you have any insight, I'd like to hear it.



Things I wished worked:
1. understandably doesn't work...
<blockquote>code:
<pre name="code" class="core">SELECT a
FROM com.baz.A a
WHERE a.rel.name = 'foo'

(a.rel "is a" B, with no name, ergo no go)
</pre>
</blockquote>

2. possible new syntax, perhaps too Java-y
<blockquote>code:
<pre name="code" class="core">SELECT a FROM com.baz.A a,
IN ((com.baz.C)a.name) c
WHERE c = 'foo'

(adds casting syntax, basically casting to the needed subclass, returning null otherwise)
</pre>
</blockquote>

3. a more SQL syntax, adding a function
<blockquote>code:
<pre name="code" class="core">SELECT a
FROM com.baz.A a
WHERE CLASS(a.baz) LIKE com.baz.C
AND c = 'foo'

(adds a CLASS keyword)
</pre>
</blockquote>

Edit: OUCH. Trying to edit my post, it's spitting me HTML instead of UBB
[ July 17, 2008: Message edited by: Bill Shirley ]
 
Bill Shirley
Ranch Hand
Posts: 457
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found a solution.
The Sun full syntax and examples was useful (includes BNF).

It's similar as to objects that have no navigable relationships.

<blockquote>code:
<pre name="code" class="core">
SELECT a
FROM A a,
IN (a.rel) b
WHERE EXISTS
(SELECT c
FROM C c
WHERE c = b
AND c.name = 'foo')
</pre>
</blockquote>
 
A wop bop a lu bop a womp bam boom! Tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic