aspose file tools*
The moose likes JDBC and the fly likes some advice please Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "some advice please" Watch "some advice please" New topic
Author

some advice please

joe weakers
Ranch Hand

Joined: May 31, 2004
Posts: 38
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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.


[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
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

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

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
joe weakers
Ranch Hand

Joined: May 31, 2004
Posts: 38
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

Joined: Aug 13, 2004
Posts: 1081

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

Joined: Sep 23, 2004
Posts: 58
Hi
Thanks I had the same problem

With Regards
Vivek
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: some advice please