I created my Java App to Send emails from the table and after that to update Sent emails column in the table. What I want now is instead of updating one at the time I would like to update all emails at once. Here is my code Send Emails:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import javax.mail.Message;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
public class TestSendEmails {
private String RecordId;
private String emailTo;
private String emailSubject;
private String emailBody;
private String emailAttachments;
public TestSendEmails(){
}
public TestSendEmails(String emailTo, String emailSubject, String emailBody, String emailAttachments, String RecordId){
super();
this.emailTo = emailTo;
this.emailSubject = emailSubject;
this.emailBody = emailBody;
this.emailAttachments = emailAttachments;
this.RecordId = RecordId;
}
public String getEmailTo(){
return emailTo;
}
public void setEmailTo(String emailTo){
this.emailTo = emailTo;
}
public String getEmailSubject(){
return emailSubject;
}
public void setEmailSubject(String emailSubject){
this.emailSubject = emailSubject;
}
public String getEmailBody(){
return emailBody;
}
public void setEmailBody(String emailBody){
this.emailBody = emailBody;
}
public String getEmailAttachments(){
return emailAttachments;
}
public void setEmailAttachments(String emailAttachments){
this.emailAttachments = emailAttachments;
}
public String getRecordId(){
return RecordId;
}
public void setRecordId(String RecordId){
this.RecordId = RecordId;
}
}
class TestSendEmailD{
private Connection con;
private static final String GET_EMAILS = "Select Top 45 * From Email Where SentOn is null Order By RecordId";
private void connect() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
con = DriverManager.getConnection("jdbc:sqlserver://xxxxxxxxx\\SQLEXPRESS:1000;databaseName=Test;user=xxxx;password=xxxx");
}
public List<TestSendEmails> getTestSendEmails() throws Exception{
connect();
PreparedStatement ps = con.prepareStatement(GET_EMAILS);
ResultSet rs = ps.executeQuery();
List<TestSendEmails> result = new ArrayList<TestSendEmails>();
int count = 0;
while(rs.next()){
result.add(new TestSendEmails(rs.getString("emailTo"), rs.getString("emailSubject"),rs.getString("emailBody"),rs.getString("emailAttachments"),rs.getString("RecordId")));
count++;
}
System.out.println(count);
disconnect();
return result;
}
private void disconnect() throws SQLException{
if(con != null){
con.close();
}
}
}
class EmailSender{
private Session session;
private void init(){
Properties props = new Properties();
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.starttls.enable", "true");
props.put("mail.smtp.host", "xxxxxxxxx");
props.put("mail.smtp.port", "xxxx");
session = Session.getInstance(props,
new javax.mail.Authenticator() {
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication("[email protected]", "xxxxx");
}
});
}
//Creating new connection to Update stpEmailLog table
public static Connection getConnection() throws Exception {
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://xxxxxxxxx\\:1000;databaseName=Email";
String username = "xxxx";
String password = "xxxxx";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public void sendEmail(TestSendEmails s) throws Exception{
init();
Message message = new MimeMessage(session);
Connection conn = null;
PreparedStatement pstmt = null;
conn = getConnection();
java.util.Date date = new Date();
message.setFrom(new InternetAddress("[email protected]"));
String query = "update Email set SentOn = ? where RecordId = ? ";
pstmt = conn.prepareStatement(query); // create a statement
String str[]=String.valueOf(s.getRecordId()).split(";");//RecordId1;RecordId;RecordId3;....
for(int i=0;i<str.length;i++){
message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(s.getEmailTo().replace(";", ",")));
message.setSubject(s.getEmailSubject());
message.setText(s.getEmailBody());
message.setContent(s.getEmailBody(),"text/html");
Transport.send(message);
System.out.println("Email sent.");
pstmt.setTimestamp(1, new java.sql.Timestamp(date.getTime()));
pstmt.setString(2, str[i]);
pstmt.executeUpdate(); // Execute Update statement
}
}
public void sendEmail(List<TestSendEmails> emails) throws Exception{
for(TestSendEmails TestSendEmails:emails ){
sendEmail(TestSendEmails);
System.out.println(TestSendEmails);
}
}
}
Here is the line of code that I use to update my field in the table:
pstmt.executeUpdate();
I'm not sure if I can just move this to a different place and that way update that filed with all emails instead of updating them one by one. If anyone can help me with this please let me know.
Should be simple enough to make it run in a batch. In your sendEmail method you should just need to change the pstmt.executeUpdate(); to pstmt.addBatch();, then outside of your for loop, just call pstmt.executeBatch();.
public void sendEmail(TestSendEmails s) throws Exception{
init();
Message message = new MimeMessage(session);
Connection conn = null;
PreparedStatement pstmt = null;
conn = getConnection();
java.util.Date date = new Date();
message.setFrom(new InternetAddress("[email protected]"));
String query = "update Email set SentOn = ? where RecordId = ? ";
pstmt = conn.prepareStatement(query); // create a statement
String str[]=String.valueOf(s.getRecordId()).split(";");//RecordId1;RecordId;RecordId3;....
for(int i=0;i<str.length;i++){
message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(s.getEmailTo().replace(";", ",")));
message.setSubject(s.getEmailSubject());
message.setText(s.getEmailBody());
message.setContent(s.getEmailBody(),"text/html");
Transport.send(message);
System.out.println("Email sent.");
pstmt.setTimestamp(1, new java.sql.Timestamp(date.getTime()));
pstmt.setString(2, str[i]);
pstmt.addBatch(); // Add to batch
}
pstmt.executeBatch(); // Execute batch
}