This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

some advice please

 
joe weakers
Ranch Hand
Posts: 38
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there. Can anybody offer me some sound advice on the following. I have an array of numbers (size can range from 1....100 depending on certain criteria) and for each number in the array I need to execute three specific SQL queries (hence 300 possible queries). Each query returns quite different result sets and hence joining them is not really an option. Code looks roughly as follows:

.....
String query = null;
ServerConnection sc = new ServerConnection(); //connect to database once
Statement stmt = sc.conn.createStatement(); //create statement once
for(int i = 0; i < session_ids.length; i++) //for each array value
{
query = "select from table1 where session_id = session_ids[i] and ..."
ResultSet rs = stmt.execute(query);
while(rs.next){...}
query = "select from table2 where session_id = session_ids[i] and ..."
ResultSet rs = stmt.execute(query);
while(rs.next){...}
query = "select from table 3 where session_id = session_ids[i] and ..."
ResultSet rs = stmt.execute(query);
while(rs.next){...}
}
rs.close();
stmt.close();
sc.conn.close();
....

My question is as follows. Is this code ok or do I need to create three different Statement and ResultSet objects - one for each query that will be executed for each value in the array? Also is there a need to open and close the connection after each iteration of the for loop? Finally, is there anything seriously wrong with the code or is there a better way of executing up to 300 queries in one method. Thnaks a lot, Joe
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
These really should be prepared statements since they are getting executed multiple times.

Also, you need to close the result set after every query, not just once per loop. I would have three prepared statement objects, one per query. These could be created outside the loop. You don't have to close the prepared statements and connection on each iteration. Just make sure to do so at the end. And do it in a finally block.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
I think you should really be asking for solutions to the problem you are trying to solve, and not for ways of implementing your proposed solution.

I get the impression that there may be a simpler solution to your problem than executing 300 queries (in a loop).

But since you gave no details of your problem -- but only of your proposed solution -- there's not much I can offer in the way of a (better) alternative.

You didn't mention the database you are using. Most databases support stored procedures, and a stored procedure would be a much better way to execute 300 queries (if that is what you really have to do). And you can execute stored procedures from java. And stored procedures can return multiple result sets.

Better to know all your options before deciding on a solution (and how to implement it).

Good Luck,
Avi.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,

I am not aware of your requirement, but the three queries inside the loop the where
clause is on session_id.

Even if all three table are different, there must be some link among session_ids.

If I were at your place, I would have tried following approach please verify them
as I am not aware of your requirements.


1) Making a single query using inner or outer joins which will reduce my three query to one
means if loop run 100 times then 100 query will execute instead of 200.

2) By creating a view in database which will again lead to a single query.

3) If above two option fails then my first preference would be executing
queries on database server by creating a stored procedure and passing the session id to same
which would return a REF CURSOR (in oracle for other database I am not aware) back to my program.



hope this adds something to get a good solution

thanks
Shailesh
[ January 12, 2005: Message edited by: Shailesh Chandra ]
 
joe weakers
Ranch Hand
Posts: 38
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys. Thanks for your reply. Jeanne in response to your advice I updated my code incorporating your suggested changes and the code works fine. My code now looks as follows:

....
ServerConnection sc = new ServerConnection();
PreparedStatement ps1 = sc.conn.prepareStatement("Select feature_id, \n"+
"feature_score from joeweaker.session_interest_features \n"+"where user_id = ? and session_id = ? order by feature_score desc, feature_id");
PreparedStatement ps2 = sc.conn.prepareStatement("Select feature_id, area_id from \n"+"joeweaker.interest_area_features where user_id = ? and session_id = ? \n"+"order by area_id, feature_id");
PreparedStatement ps3 = sc.conn.prepareStatement("Select feature_id from \n"+"joeweaker.final_map_frames where user_id = ? and session_id = ? order by \n"+"feature_id");

for(int i = 0; i < session_ids.length; i++)
{
ps1.setInt(1,current_user_id);
ps1.setInt(2,session_ids[i]);
ResultSet rs = ps1.executeQuery();
while(rs.next()){
...}
ps2.setInt(1,current_user_id);
ps2.setInt(2,session_ids[i]);
rs = ps2.executeQuery();
while(rs.next()){
...}
ps3.setInt(1,current_user_id);
ps3.setInt(2,session_ids[i]);
rs = ps3.executeQuery();
while(rs.next()){
...}
rs.close();
}
ps1.close();
ps2.close();
ps3.close();
sc.conn.close();

Shailesh, Avi, as you can see there is no real point in attempting to merge the three queries into one for each iteration of the for loop as the result sets would be longwinded and incoherent. Secondly, I have used functions and procedures before in my code (I am using Oracle database) and question how much better they would be than using PreparedStatements in this particular scenario. The reason I am saying this is that I need to take the results of each sql query and process them in Java based on the value of the session_id as oppose to processing them in the database. Each result set will be processed individually and hence I think it might make more sense to process the result sets in Java. Have you guys any further suggestions. Thnaks a lot for your time, Joe
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe ,

Since Database operations are very expensive and in any case
I will not prefer current approach however I love using prepared statement.

I am suggesting one more solution, which I think is best at least for me,
every thing will be done in 3 query executions.

I am taking in account that length of session array (arr) is 100.

I will create a combined string of session passing same to another method and try to form
one query.

Please read my comments given with code






thanks
[ January 13, 2005: Message edited by: Shailesh Chandra ]
 
S.R.K.Vivek Raju
Ranch Hand
Posts: 58
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Thanks I had the same problem

With Regards
Vivek
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic