wood burning stoves*
The moose likes EJB and other Java EE Technologies and the fly likes how to write this query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "how to write this query" Watch "how to write this query" New topic
Author

how to write this query

Raj Bhandari
Ranch Hand

Joined: Oct 19, 2005
Posts: 97
Hi Everyone,

Being new to EJB QL, I'm unable to figure this out. Please help. I'll provide a simplified scenario.
You have a customer table with columns: name, state. state is a lookup/fk value like 11,12 where the full description comes from another table STATE having colms state_id, state_desc.
How do I write this query in EJB3 QL so that I get back the customer name and state description?

I don't prefer doing a join like:
Select c.name, s.desc from customer c, state s where c.state=s.state_id. I think thats easy to figure out in EJB QL.

The reason I don't want this is that I could have 10 other lookup columns in my customer table and I don't want to go on doing joins. (Actually in my case I have one lookup table to store all lookup values so it makes it complicated!!).

I'd prefer an equivalent of something like this:
Select c.name, (Select s.desc from state s where s.state_id=c.state) from customer c.

I'm not sure how to do this.
Please suggest and guide.. Tks!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30356
    
150

Originally posted by Raj Bhandari:
Select c.name, (Select s.desc from state s where s.state_id=c.state) from customer c.

This is still a join, just written differently.

Why don't you want to a join? Do you have a known performance problem? Joins on small tables rarely take a long time. And for small tables, you would add an index to speed things up.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Raj Bhandari
Ranch Hand

Joined: Oct 19, 2005
Posts: 97
Hi Jeanne,

Tks for the reply.
If thats the case, I have the following doubts:

1. Is it possible to run this query anyhow using EJB QL:

Select c.name, (Select s.desc from state s where s.state_id=c.state) from customer c ?

2. If you are suggesting this sql instead: Select c.name, s.desc from customer c, state s where c.state=s.state_id; what would you suggest in the case when there's an additional column say country_id in customer table. How would the query be so that I get customer name, state description, country description? Here are my tables:



Tks
Raj
[ June 24, 2008: Message edited by: Raj Bhandari ]
Raj Bhandari
Ranch Hand

Joined: Oct 19, 2005
Posts: 97
Ok, so I tried this:

Created an Entity like this:


I also wrote a native sql query and am able to get the values.
But now my problem is that when I try to insert Customer using
manager.persist(customer) it tries to insert stateDesc also but there's no column as such in customer table but its just for search resultset.
Here's my table again:



Now if I make stateDesc @Transient so that the insert and update ignores then my select query doesn't put value in there. How do I get around this?

Tks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to write this query