1

I'm working on my application where I want to update my table after email has been sent. I created function that connect sql database and java, also in other class I created function that updates table but what I need is these two classes together. I want to use my array-list after execution for updating of my table. Here is my code for connection and sending 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.List;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
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 emailTo;
    private String emailSubject;
    private String emailBody;
    private String emailAttachments;
    private Integer RecordId;

    public TestSendEmails(){

    }

    public TestSendEmails(String emailTo, String emailSubject, String emailBody, String emailAttachments, Integer 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 Integer getRecordId(){
        return RecordId;
    }

    public void setRecordId(Integer RecordId){
        this.RecordId = RecordId;
    }
}

class TestSendEmailD{
    private Connection con;

    private static final String GET_EMAILS = "Select* From Emails";

    private void connect() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        con = DriverManager.getConnection("jdbc:sqlserver://100.000.000.00\\:3333;databaseName=Test;user=mmmm;password=1234");
    }

    public List<TestSendEmails> getTestSendEmails() throws Exception{
        connect();
        PreparedStatement ps = con.prepareStatement(GET_EMAILS);
        ResultSet rs = ps.executeQuery();
        List<TestSendEmails> result = new ArrayList<TestSendEmails>();
        while(rs.next()){
            result.add(new TestSendEmails(rs.getString("emailTo"), rs.getString("emailSubject"),rs.getString("emailBody"),rs.getString("emailAttachments",rs.getInt("RecordId"))));
        }
        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", "100.000.000.00");
        props.put("mail.smtp.port", "678");

        session = Session.getInstance(props,
                  new javax.mail.Authenticator() {
                    protected PasswordAuthentication getPasswordAuthentication() {
                        return new PasswordAuthentication("[email protected]", "123");
                    }
                  });
    }

    public void sendEmail(TestSendEmails s) throws MessagingException{
        init();
        Message message = new MimeMessage(session);
        message.setFrom(new InternetAddress("[email protected]"));
        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("Done");
    }

    public void sendEmail(List<TestSendEmails> emails) throws MessagingException{
        for(TestSendEmails TestSendEmails:emails ){
            sendEmail(TestSendEmails);
        }
    }
}

Here is my Update code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;

public class UpdateEmail {
  public static Connection getConnection() throws Exception {
    String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String url = "jdbc:sqlserver://100.000.000.00\\:3333;databaseName=Test";
    String username = "mmmm";
    String password = "1234";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }

  public static void main(String[] args) throws Exception {
    java.util.Date date = new Date();
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "update Emails set SentOn = ? where Id = ? ";
      pstmt = conn.prepareStatement(query); // create a statement
      pstmt.setTimestamp(1, new java.sql.Timestamp(date.getTime()));
      pstmt.setInt(2, 200); // In this line I want to use my array-list to update my table.
      pstmt.executeUpdate(); // execute update statement
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}

I'm not sure if I have to create new connection for my update in my second program and where I should implement my update code. If you know what I should change please let me know. Thanks in advance.

Main.java code:

import java.util.List;

public class Main {
    public static void main(String[] args) throws Exception {
        TestSendEmailD dao=new TestSendEmailD();
        List<TestSendEmails> list=dao.getTestSendEmails();
        EmailSender sender=new EmailSender();
        sender.sendEmail(list);
    }
}
16
  • First result.add(new TestSendEmails(rs.getString("emailTo"), rs.getString("emailSubject"),rs.getString("emailBody"),rs.getString("emailAttachments"))); where is the id here Commented Jul 20, 2015 at 13:00
  • rs.getInt("RecordId") Commented Jul 20, 2015 at 13:03
  • where you are stored all these ids. Commented Jul 20, 2015 at 13:06
  • Only one ID, rest of them are Strings. I stored them in ArrayList result. Commented Jul 20, 2015 at 13:07
  • s.getRecordId() while printing this statement whats the output. is it like id1;id;id3;.... @user3023588 Commented Jul 20, 2015 at 13:34

2 Answers 2

3

I guess you have an ArrayList() called yourList. The following code goes before

String query ...

StringBuilder ids = "";
String prefix ="";
for (Integer id: yourList) {
     append(prefix);
     prefix = ",";
     ids.append(String.valueOf(id));
}

change your query to:

String query = "update Emails set SentOn =? where Id in (" + ids.toString() + ")";

and send only the SentOn as parameter:

pstmt.setTimestamp(1, new java.sql.Timestamp(date.getTime()));
Sign up to request clarification or add additional context in comments.

Comments

0
        Connection conn = null;
        PreparedStatement pstmt = null;
        conn = getConnection();
        java.util.Date date = new Date();

        message.setFrom(new InternetAddress("[email protected]"));
        String query = "update Emails set SentOn = ? where Id = ? ";
        pstmt = conn.prepareStatement(query); // create a statement

        String str[]=String.valueOf(s.getRecordId()).split(";");//id1;id;id3;.... 
        for(int i=0;i<str.length();i++)
        {
        message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(s.getEmailTo().replace(";", ",")[i]));
        message.setSubject(s.getEmailSubject());
        message.setText(s.getEmailBody());
        message.setContent(s.getEmailBody(),"text/html");
        Transport.send(message);
        System.out.println("Done");

        pstmt.setTimestamp(1, new java.sql.Timestamp(date.getTime()));
        pstmt.setInt(2, str[i]); // In this line I want to use my array-list to update my table.
        pstmt.executeUpdate(); // execute update statement
        System.out.println(str[i]+" "+s.getEmailTo().replace(";", ",")[i]+" "+new java.sql.Timestamp(date.getTime()));//to check whether its working or not.
      }

Its exactly not an exact answer. check whether its working or not. Here you must take care of exceptions also. I didn't written that code here.

5 Comments

Do I need code for connection above this code or not?
we need get conn object. Since its not available.
I'm getting error on that line, it say conn cannot be resolved.
now see my code. The above error occurs since you are not declaring conn object Connection conn = null;
k. welcome. but you must care of exceptions while sending a mail or updating a record. I didn't try here for anything here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.