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
select value from from Xtable where type='MEDIUM' and id1=:id1 and id2=:id2
select value from from Xtable where type='HIGH' and id1=:id1 and id2=:id2
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
select value from Xtable where type in ('LOW', 'MEDIUM', 'HIGH', 'NA') and id1=:id1 and id2=:id2
That should be supported by Hibernate.
Joined: Jan 16, 2007
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 Today 16:14:28
Ulf Dittmer Today 16:14:51
Great minds think alike ... at the same time
Joined: Mar 22, 2005
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?
Joined: Jan 16, 2007
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.