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("test@gmail.com", "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("test@gmail.com"));
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.
