aspose file tools*
The moose likes Object Relational Mapping and the fly likes converting sql union query into java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "converting sql union query into java" Watch "converting sql union query into java" New topic
Author

converting sql union query into java

Udayan Kumar
Ranch Hand

Joined: Jan 16, 2007
Posts: 66
Hi there,

We have a scenario where we used to fire a SQL query via JDBC . This query uses the sql union clause something like this
---------
select value from from Xtable where type='LOW' and id1=:id1 and id2=:id2
union
select value from from Xtable where type='MEDIUM' and id1=:id1 and id2=:id2
union
select value from from Xtable where type='HIGH' and id1=:id1 and id2=:id2
union
select value from from Xtable where type='NA' and id1=:id1 and id2=:id2
---------
I have to discard this jdbc query approach. Instead I will be getting an entire collection object (Hibernate pojo object collection based on id1 and id2). Now I have to simulate the same behavior (union query) on this collection. Can you post your ideas on how to go about the same


-Uday
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

You can use the IN operator to return records where a field has to match an entry in a list of values


OCUP UML fundamental and ITIL foundation
youtube channel
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39543
    
  27
Isn't that the same as

select value from Xtable where type in ('LOW', 'MEDIUM', 'HIGH', 'NA') and id1=:id1 and id2=:id2

That should be supported by Hibernate.


Ping & DNS - updated with new look and Ping home screen widget
Udayan Kumar
Ranch Hand

Joined: Jan 16, 2007
Posts: 66
Oops. I think I got my post wrong. What I meant to ask is I want to discard the sql query approach as this gets frequently called during the application flow. So I will fetch the whole collection in an upfront manner and plum the collection as required during the application flow.
I want to know how can i create a java equivalent solution of the queries mentioned in the above replies.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

Jan Cumps Today 16:14:28
Ulf Dittmer Today 16:14:51
Great minds think alike ... at the same time

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39543
    
  27
Can you elaborate what you mean by "java equivalent solution"? Relational databases speak only SQL, so that's what -whatever frontend technology you use- will end up being issued. Which performance problem in particular do you see with JDBC queries?
Udayan Kumar
Ranch Hand

Joined: Jan 16, 2007
Posts: 66
The reason we are asked to replace the "union query" with a java equivalent is the number of database roundtrips we have per request.
Currently as part of per request we have to execute this query 20-25 times. The business condition warrants this.

This numerous database hits per request was observed and hence the replacement.

As part of the application flow the plan is to grab the data in a collection (where id1=:id1 and id2=:id2).
Then work with the collection rather than going back to the database.
In my earlier post I kept the query brief. the actual where clause is something like this where (id1=:id1 and id2=:id2 and id3=:id3) and this id3 can be any values (1..25) which gets spun out per request.


Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

At this point this thread becomes irellevant for the Object Relational Mapping forum, and becomes a general Java topic: How to search/filter Java Collections.
Udayan Kumar
Ranch Hand

Joined: Jan 16, 2007
Posts: 66
Any ideas on how to go about the same?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: converting sql union query into java
 
Similar Threads
composite-id Query
how to get column information for a given sql string
Hibernate query help
struts2 drop down - unique values
Selecting data from multiple tables