java - 如何一次更新所有电子邮件？

Question

我创建了 Java 应用程序来从表中发送电子邮件，然后更新表中的“已发送电子邮件”列。我现在想要的是一次更新所有电子邮件，而不是一次更新一封。这是我的代码发送电子邮件:

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);
        }
    }
}

这是我用来更新表中字段的代码行:

pstmt.executeUpdate();

我不确定是否可以将其移至其他位置，这样就可以更新包含所有电子邮件的归档文件，而不是逐一更新它们。如果有人可以帮助我，请告诉我。

Answer 1

应该足够简单，可以批量运行。在您的 sendEmail 方法中，您只需将 pstmt.executeUpdate(); 更改为 pstmt.addBatch();，然后在您的for 循环，只需调用 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("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.addBatch(); // Add to batch
    }

    pstmt.executeBatch(); // Execute batch
}