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