• Post Reply Bookmark Topic Watch Topic
  • New Topic

best way to iterate a result set..  RSS feed

 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have a result set containing empId,MailId,and departmentNo. Total number of records in the resultset might be around 20,000. I need to send an email to all users depending upon their department. So if employee - A & employee - B are in same dept then they 'll receive the same mail. what will be the best approach to solve this ?
Approach 1 :
1. Keep all mailId's, departmentNo in a array list or any collection.
2. For each department
2.1 get all the mailid's that belongs to this department
2.2 send mail
3. Remove the processed mailId's from the collection so that, the number of iterations ( or records ) processed by next department gets reduced.
Approach2 :
1. Update the SQL query to get the results ordered by departmentno.
2. Iterate the result set and create a Arraylist having all mailid's
3. while iterating, if department number changes, then save the array list created in step2, to hashtable with departmentno as key.
4. After iterating, the last department will not be in the hashtable so add it.
5. Send mail to employees.
 
Nadeem Awad
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Srinivasa,
I would go for Approach 2. I'm not really sure about performance, but at least it's more organized. Having to iterate over the departments list more than once in Approach 1 sounds messy.

Best regards,
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Would there be any value in moving the mail step (5) up into (3)? Instead of building a hashmap and saving it until you're done reading the resultset, mail the prior department as soon as you hit the new department ... and again at end. You might even be able to run the mailing on its own thread.

That would get the first mailing out sooner and avoid storing all the mail ids in the hashmap even for a short while. Don't know if it would get the LAST mailing out any sooner, tho.
[ January 25, 2006: Message edited by: Stan James ]
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd check your mail server to see if it can interface to something like an LDAP server to get lists of users whose departments are treated as roles in the JAAS sense. Map LDAP to your table and you just send as many pieces of email as you have different departments.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!