Resultsets nested within resultsets?

[shaf maff]

Hi Guys,

Due to the nature of how my system works, one way to overcome many of the problems was to use a resultset within a resultset. At the time it seemed fine but now I have realised that the main resultset will only go through one record even if there are several records in the database. When I comment out the two nested resultsets the main rs goes through all the records fine. So I am in need of some help, do I need to fix afew things here and there or do I need to restructure the whole code (gulp) ?! Comments most welcome:

rs = DB.exQuery("SELECT * FROM mail WHERE user_id="+user.getId());
int count1 = 0;
try {
while (rs.next()) {
//Insert DB values into variables
letterId = rs.getString("letter_id");
title = rs.getString("title");
content = rs.getString("content");
isDraft = rs.getString("is_Draft");
recipientPriceId = rs.getString("recipient_price_id");
stampId = rs.getString("stamp_id");
letterPriceId = rs.getString("letter_price_id");

//Format Date Modified
java.util.Date d = rs.getDate("last_Modified");
SimpleDateFormat sdf = new SimpleDateFormat("dd/MMM/yyyy");

lastModified = sdf.format(d);
//Create and insert into LetterSession Object
LetterSession mail = new LetterSession();//Create Class object
mail.setId(letterId);
mail.setTitle(title);
mail.setContent(content);
mail.setDateModified(lastModified);
mail.setRecipientPriceId(recipientPriceId);
mail.setStampId(stampId);
mail.setLetterPriceId(letterPriceId);
mail.setIsDraft(isDraft);

//Get all contacts from the DB associated with this mail object
ResultSet rs2 = DB.exQuery("SELECT letter_recipients.contact_id,user_contacts.* FROM letter_recipients"
+ " INNER JOIN user_contacts ON letter_recipients.contact_id=user_contacts.contact_id"
+ " WHERE letter_recipients.letter_id="+letterId);
try {
while(rs2.next()) {
//Add contact details to variables
contactId = rs2.getString("contact_id");
contactFirstName = rs2.getString("first_name");
contactLastName = rs2.getString("last_name");
add1 = rs2.getString("address1");
add2 = rs2.getString("address2");
townCity = rs2.getString("town_or_city");
postcode = rs2.getString("postcode");

//Create Contacts class object
Contacts contact = new Contacts();

//Insert data into contact object
contact.setId(contactId);
contact.setFirstName(contactFirstName);
contact.setLastName(contactLastName);
contact.setAddress1(add1);
contact.setAddress2(add2);
contact.setTownCity(townCity);
contact.setPostCode(postcode);

//Add contact object to mail object
mail.setContact(contact);
counter++;
} rs2.close();
} catch(SQLException e) { System.out.println("Exception : "+ e.getMessage()); }

//Get all prices for this mail object
ResultSet rs3 = DB.exQuery("SELECT letter_stamp.stamp_name, letter_stamp.stamp_price,letter_type_price.letter_type_name,"
+ "letter_type_price.letter_price,recipient_price.r_price"
+ " FROM letter_stamp"
+ " INNER JOIN letter_type_price ON letter_type_price.letter_price_id="+letterPriceId
+ " INNER JOIN recipient_price ON recipient_price.recipient_price_id="+recipientPriceId
+ " WHERE letter_stamp.stamp_id="+stampId);
try {
while(rs3.next()) {
//Set DB values into variables
recipientPrice = new BigDecimal(rs3.getString("r_price"));
stampNm = rs3.getString("stamp_name");
stampPrice = new BigDecimal(rs3.getString("stamp_price"));
letterPrice = new BigDecimal(rs3.getString("letter_price"));
letterTypeNm = rs3.getString("letter_type_name");

//Insert variables into mail object
mail.setRecipientPrice(recipientPrice.toString());
mail.setStampName(stampNm);
mail.setStampPrice(stampPrice.toString());
mail.setLetterPriceName(letterTypeNm);
mail.setLetterPrice(letterPrice.toString());

//Workout total
BigDecimal count = new BigDecimal(counter);//Get Contact Counter
//Add prices together and mulitply by contact counter
total = recipientPrice.add(stampPrice);
total = total.add(letterPrice);
total = total.multiply(count);
//Add total to mail object
mail.setTotal(total.toString());
//Number of contacts that the letters need to be sent to
mail.setNoOfContacts(counter);
count1++;
} rs3.close();
} catch(SQLException e) { System.out.println("*****Exception*****"+ e.getMessage()); }

//Place mail object into letters list
letters.add(mail);

} rs.close();
} catch(SQLException e) {
System.err.println("**** sql exception begin ****");
e.printStackTrace(System.err);
}

[Shailesh Chandra]

Sounds like you have problem with your DB class or your method DB.exQuery() .

Since you have not posted the code of DB class only thing I can guess is you are sharing the statement/Resultset across multiple calls and you loosing reference of previous resultset.

Could you post the code of DB class.

Thanks,
Shailesh