Send mail to email of users saved in database

Question

I am able to send email on a particular email id, but now I want to retrieve email ids of multiple users from a database and send them a message. I have tried several ways but I'm unable to get the desired results.

Here is my original code:

<%@page import="oracle.net.aso.i"%>
<%@ page import="java.io.*,java.util.*,javax.mail.*,java.sql.*"%>
<%@ page import="javax.mail.internet.*,javax.activation.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<%!
Connection con;
Statement st;
ResultSet rs;
%>
<%
Class.forName("oracle.jdbc.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","rahul","odeya");
st=con.createStatement();
rs=st.executeQuery("select email from register");
%>
<%
String host="", user="", pass="";
int i=0;
String id[] = new String[100];
while(rs.next())
{
    id[i] = rs.getString(1);
}
host ="smtp.gmail.com"; 
user ="ocrsindore@gmail.com"; 
pass ="odeya";

String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";

String from ="ocrsindore@gmail.com"; 

String subject =request.getParameter("subject");

String messageText =request.getParameter("message");

boolean sessionDebug = true;

Properties props = System.getProperties();
props.put("mail.host", host);
props.put("mail.transport.protocol.", "smtp");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.", "true");
props.put("mail.smtp.port", "465");
props.put("mail.smtp.socketFactory.fallback", "false");
props.put("mail.smtp.socketFactory.class", SSL_FACTORY);
Session mailSession = Session.getDefaultInstance(props, null);
mailSession.setDebug(sessionDebug);
Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(from));

InternetAddress[] address = {new InternetAddress(id[i])};

msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(subject);
msg.setContent(messageText, "text/html"); 

Transport transport = mailSession.getTransport("smtp");
transport.connect(host,from,pass);
try {
transport.sendMessage(msg, msg.getAllRecipients());

out.println("message successfully sended"); 
}
catch (Exception err) {

out.println("message not successfully sended"); 
}
transport.close();
%>
<jsp:include page="adminis.html"/>

What i understand from your question is CHECK if a new message in databse arrive or not? is that what you want? — Youcef LAIDANI, May 08 '16 at 14:19
if my answer helped you, can you please mark it as accepted ? — Vic Seedoubleyew, May 12 '16 at 19:03

score 0 · Answer 1 · edited May 23 '17 at 12:07

There are 3 problems :

First off, your i never gets incremented. You need to add i++; at the end of your loop. The way you are doing now, you are looping over all results of your database query, but always saving in id[0].
Also, replace rs.getString(1) by rs.getString(0). That's because email is column 0 of your database query results, not 1.
Finally, in your code, when you collect email addresses at the end, you capture only one, because you don't loop on i. You should loop on your id array, and create an InternetAddress object for each email.

Overall, what you need to do is to replace your while loop by :

while(rs.next())
{
   id[i] = rs.getString(0);
   i++;
}

and replace InternetAddress[] address = {new InternetAddress(id[i])}; by :

InternetAddress[] address = new InternetAddress[id.length];
for(int i=0; i<id.length; i++){
   address[i] = new InternetAddress[id[i]];
}

That said, it should work. Now, a few comments about your code :

why initialize id with length 100 ? If you do that, you won't be able to handle a case with more than 100 email addresses. You probably need to use an ArrayList instead of an array. See here
anyway, there is a simpler solution, to transfer a ResultSet to an array, simply use ResultSet's getArray method
you don't need to add any %><% like this :
```
%>
<%
```
This is useless
some indentation could make your code clearer and easier to understand, for you and your readers.
no need to declare host, user, and pass, as empty, only to fill them a few lines later

Overall, if you implement these comments and the important changes, your code would look like this :

<%@page import="oracle.net.aso.i"%>
<%@ page import="java.io.*,java.util.*,javax.mail.*,java.sql.*"%>
<%@ page import="javax.mail.internet.*,javax.activation.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<%!
Connection con;
Statement st;
ResultSet rs;

Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "rahul", "odeya");
st = con.createStatement();
rs = st.executeQuery("select email from register");

String[] id = rs.getArray("email");

String host ="smtp.gmail.com"; 
String user ="ocrsindore@gmail.com"; 
String pass ="odeya";

String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";

String from ="ocrsindore@gmail.com"; 

String subject = request.getParameter("subject");

String messageText = request.getParameter("message");

boolean sessionDebug = true;

Properties props = System.getProperties();
props.put("mail.host", host);
props.put("mail.transport.protocol.", "smtp");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.", "true");
props.put("mail.smtp.port", "465");
props.put("mail.smtp.socketFactory.fallback", "false");
props.put("mail.smtp.socketFactory.class", SSL_FACTORY);

Session mailSession = Session.getDefaultInstance(props, null);
mailSession.setDebug(sessionDebug);
Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(from));

InternetAddress[] address = new InternetAddress[id.length];
for(int i=0; i<id.length; i++){
   address[i] = new InternetAddress[id[i]];
}

msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(subject);
msg.setContent(messageText, "text/html"); 

Transport transport = mailSession.getTransport("smtp");
transport.connect(host, from, pass);
try {
   transport.sendMessage(msg, msg.getAllRecipients());
   out.println("message successfully sent"); 
}
catch (Exception err) {
   out.println("message send failed"); 
}
transport.close();
%>
<jsp:include page="adminis.html"/>

Send mail to email of users saved in database

1 Answers1