aspose file tools*
The moose likes JDBC and the fly likes Why do these queries break this loop????? Im lost! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Why do these queries break this loop????? Im lost!" Watch "Why do these queries break this loop????? Im lost!" New topic
Author

Why do these queries break this loop????? Im lost!

DC Dalton
Ranch Hand

Joined: May 28, 2001
Posts: 287
Ive got this loop in a servlet that needs to do multiple queries on seperate tables in a db.(MySql) I have to do it in seperate queries because a join is impossible with the data I am trying to return. Anyways if you look at the loop you will see the 2 inner queries & their corresponding if statements. If I pull those inner out this thing runs like a wolf & return over 50 friends to this list but when I insert these inner queries I only get 1 line....period! Ive tryed commenting out the inner if statements, naming the ResultSets different things nothing makes it work, except pulling the inner queries & their corresponding if statements completely out............My head is starting to bleed from banging it on the desk! Heres is the method with the offending loop:I had to pull out the HTML because it was freaking out this BB.
private void showFriends(PrintWriter out, String sesReturn, String userName, Statement stmt) throws SQLException {

////////// headers for columns
[html pulled out here]
String query = "Select friends_list.friend, mastMem.memStatus, mastMem.gender from friends_list, mastMem WHERE friends_list.friend=mastMem.userName AND friends_list.userName='"+userName+"'";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()){

String friendName = rs.getString(1);
int memStatusA = rs.getInt(2);
String memStatus = "";
String gender = "";
switch (memStatusA){
case 1 :
memStatus = "F";
break;
case 2 :
memStatus = "P";
break;
case 3 :
memStatus = "L";
break;
}
int genderA = rs.getInt(3);
switch (genderA){
case 1 :
gender = "M";
break;
case 2 :
gender = "F";
break;
}

String friendNameLong = "";
friendNameLong = friendName + gender + memStatus;

[more html pulled out here]
query = "SELECT MAX(intMail.dateSent) from intMail where fromUserName='"+friendNameLong+"' AND toUserName='"+sesReturn+"'";
String dateFrom = "";
ResultSet rs2 = stmt.executeQuery(query);
if (rs2.next()){
if (rs2.getString(1) == null){
dateFrom = "This member has never sent you mail";
}
else{
dateFrom = rs2.getString(1);
}
}
[one line html here]

query = "SELECT MAX(intMail.dateSent) from intMail where fromUserName='"+sesReturn+"' AND toUserName='"+friendNameLong+"'";
String dateTo = "";
ResultSet rs3 = stmt.executeQuery(query);
if (rs3.next()){
if (rs3.getString(1) == null){
dateTo = "You have never sent email to this member";
}
else{
dateTo = rs3.getString(1);
}
}
}
}
Anyone care a guess here? I dont know if its the MySql (im getting no errors) or the loop......are the rs's expiring somhow from the first query? Thanks for any help you can give here.
[This message has been edited by DC Dalton (edited August 06, 2001).]
Kripal Singh
Ranch Hand

Joined: Jul 26, 2001
Posts: 254
It's probably because you are using the same statement object for all queries in code above
create new statement object for every query and make sure to close all objects
when you are done
//your code
Statement stmt = myconnobj.createStatement();
ResultSet rs = stmt.executeQuery(query);
//blah
..
Statement stmt2 = myconnobj.createStatement();
ResultSet rs2 = stmt2.executeQuery(query);
//more blah
...
stmt2.close();
//more blah blah
...
Statement stmt3 = myconnobj.createStatement();
ResultSet rs3 = stmt.executeQuery(query);
....
stmt3.close();

//more code

stmt.close();


# Help an unprivileged kid.<br /> Whatever u do will make a difference...<br /> ...to a child's life & ur own #<br /><a href="http://www.cry.org/" target="_blank" rel="nofollow">www.cry.org/</a>
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

In the inner loops, it might be faster to use PreparedStatements instead of statements. Create it once, then reuse. you'd be surprised of the performance gained.
Jamie
john_guthrie
Greenhorn

Joined: Jun 14, 2001
Posts: 16
One warning about PreparedStatements - they *may* speed things up, but they may not. Two reasons I can think of:
1) your statement gets "prepared" over on the database, database is heavily used, your prepared statement gets flushed before you get a chance to use it (this would actually slow down preformance, I guess)
2) some drivers (e.g. Sybase jConnect) actually ignore your request to prepare the statement by default (I guess the assumption is that you aren't going to reuse it enough to make it worthwhile to pre-compile the statement). For the jConnect drivers you have to create the Connection with a property set that states you really want to pre-compile. So in this case, if you used the default PreparedStatement, you'd find no difference.
Having said that, I agree that you should use them. Just be aware.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Why do these queries break this loop????? Im lost!