通过java存储过程实现oracle邮发，无限扩展

junsansi · 发表于 2007-7-31 18:11

　　这阵子做项目，感觉可能会有这方面需求，在网上搜索了下找到牛人通过oracle proc实现的邮发，但感觉不够灵活性，不足以满足需求，于是想到通过java来实现

　　我感觉通过java存储过程实现的邮发可扩展功能将非常强大，而且扩展性很强，比如你可以在这段脚本的基础上，轻松将附件地址改造为支持网络地址，发送过程将更加便利。

此邮发的核心是通过javamail实现，所以需要先载入两个类包即mail.jar和activation.jar，用过javamail的朋友应该非常熟悉

1.进入dos命令窗口，载入类包(表忘了将用户改成你的oracle用户及sid)
C:\>loadjava -user jss/jss@jssweb_jss -r -v -f c:\sendmail\lib\mail.jar
C:\>loadjava -user jss/jss@jssweb_jss -r -v -f c:\sendmail\lib\activation.jar
C:\>loadjava -user jss/jss@jssweb_jss -r -v -f c:\sendmail\lib\ByteArrayDataSource.class

2.赋权
declare
begin
dbms_java.grant_permission('JSS','SYS:java.util.PropertyPermission','*','read,write');
dbms_java.grant_permission('JSS','SYS:java.net.SocketPermission','*','connect,resolve'); --需要连网的嘛，当然为了安全，你可以不用给*这么大范围，直接将smtp地址写上也可以
dbms_java.grant_permission('JSS','SYS:java.io.FilePermission','*','read'); --如果要发送附件，此权限将赋于该oracle用户拥有读取该文件的权限
end;

junsansi · 发表于 2007-7-31 18:12

3.建立java procedures
先执行MailService.sql，再执行MailSender.sql

代码如下：

MailService.sql
[PHP]
create or replace and compile java source named MailService as
import java.util.*;
import java.io.*;

import javax.activation.*;
import javax.mail.*;
import javax.mail.internet.*;

/**
* @author junsansi
* @qq 5454589
* @website www.5ienet.com
*/
public class MailService {
String from;
String host;
String subject;
String to;
Vector cc;
Vector bcc;
String priority;
String replyTo;
String userName;
String password;
Properties props;
boolean smtpAuth;
String mailBodyBegin, mailBodyEnd;
String title = "";
String filename;
public MailService(String host) {
      this.host = host;
      this.init();
}

public MailService() {
      this.host = "localhost";
      this.init();
}

public void setTitle(String title) {
      this.title = title;
}

void init() {
      props = System.getProperties();
      props.put("mail.smtp.host", host);
      this.mailBodyBegin = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\r\n" +
                           "<html xmlns=\"http://www.w3.org/1999/xhtml\">\r\n" +
                           "<head>\r\n" +
                           "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\" />\r\n" +
                           "<title>junsansi.cn</title>\r\n" +
                           "</head>\r\n" +
                           "<body>\r\n";
      this.mailBodyEnd = "\r\n</body>\r\n" +
                        "</html>";

}

public void setTo(String to) {
      this.to = to;
}

public void setUserName(String userName) {
      this.userName = userName;
}

public void setPassword(String password) {
      this.password = password;
}

public void setSmtpAuth(boolean smtpAuth) {
      if (smtpAuth) {
         props.put("mail.smtp.auth", "true"

;
} else {
props.put("mail.smtp.auth", "false"

;
      }
}

public void setReplyTo(String replyTo) {
      this.replyTo = replyTo;
}

public void addCC(String ccStr) {
      if (cc == null) {
         cc = new Vector(3);
      }
      cc.addElement(ccStr);
}

public void addBCC(String bccStr) {
      if (bcc == null) {
         bcc = new Vector(3);
      }
      bcc.addElement(bccStr);
}

public void setSubject(String subject) {
      this.subject = subject.replace('\n', ' ').replace('\r', ' ');
}

public void setFrom(String from) {
      this.from = from;
}

public void setPriority(String priority) {
      this.priority = priority;
}

public void setCc(Vector cc) {
      this.cc = cc;
}

public void setBcc(Vector bcc) {
      this.bcc = bcc;
}

public void setMailBodyEnd(String mailBodyEnd) {
      this.mailBodyEnd = mailBodyEnd;
}

public void setMailBodyBegin(String mailBodyBegin) {
      this.mailBodyBegin = mailBodyBegin;
}

public void setFilename(String filename) {
      this.filename = filename;
}

// bypass invalid email addresses
public boolean send(String content, int ishtml) {
      String mailer = "jss.mailRobot";
      // Get a Session object
      Session session = Session.getDefaultInstance(props, null);

      // construct the message
      Message msg = new MimeMessage(session);
      boolean ok = false;
      try {
         msg.setFrom(new InternetAddress(from));
         Address[] mailto = null;
         try {
            mailto = InternetAddress.parse(to, false);
         } catch (Exception e) {
            System.out.println(to + "地址错误，无法发送"

;
            return false;
         }
         if (mailto == null) {
            return false;
         }
         msg.setRecipients(Message.RecipientType.TO, mailto);

         if (cc != null) {
            InternetAddress[] ia = new InternetAddress[cc.size()];
            for (int i = 0; i < cc.size(); i++) {
                  String s = (String) cc.elementAt(i);
                  ia = new InternetAddress(s);
            }
            try {
                  msg.setRecipients(Message.RecipientType.CC, ia);
            } catch (Exception me) {
                  //me.printStackTrace();
            }
         }

         if (bcc != null) {
            InternetAddress[] ia = new InternetAddress[bcc.size()];
            for (int i = 0; i < bcc.size(); i++) {
                  String s = (String) bcc.get(i);
                  ia = new InternetAddress(s);
            }

            try {
                  msg.setRecipients(Message.RecipientType.BCC, ia);
            } catch (Exception me) {
                  //me.printStackTrace();
            }
         }

         if (replyTo != null) {
            InternetAddress[] addrs = new InternetAddress[1];
            try {
                  addrs[0] = new InternetAddress(replyTo, false);
                  msg.setReplyTo(addrs);
            } catch (Exception me) {
                  //me.printStackTrace();
            }
         }

         msg.setSubject(subject);
         if (ishtml == 1) {
            msg.setDataHandler(new DataHandler(new ByteArrayDataSource(
                     new String((this.getMailBodyBegin() + content +
                                 this.getMailBodyEnd()).getBytes("GBK",
                                 "ISO8859_1",
                     "text/html"));
         } else {
            msg.setText(content);
         }
         if (filename != null) {
            MimeBodyPart mbpBody = new MimeBodyPart();
            if (ishtml == 1) {
                  mbpBody.setDataHandler(new DataHandler(new
                        ByteArrayDataSource(
                                 new String((this.getMailBodyBegin() +
                                             content + this.getMailBodyEnd()).
                                             getBytes("GBK", "ISO8859_1",
                                 "text/html"));
            } else {
                  mbpBody.setText(content);
            }

            MimeBodyPart mbpFile = new MimeBodyPart();

            // attach the file to the message
            FileDataSource fds = new FileDataSource(filename);
            mbpFile.setDataHandler(new DataHandler(fds));
            mbpFile.setFileName(fds.getName());

            // create the Multipart and its parts to it
            Multipart mp = new MimeMultipart();
            mp.addBodyPart(mbpBody);
            mp.addBodyPart(mbpFile);

            // add the Multipart to the message
            msg.setContent(mp);
         }

         if (priority != null) {
            msg.setHeader("X-Priority", priority);
         } else {
            msg.setHeader("X-Mailer", mailer);
         }
         msg.setSentDate(new Date());

         Session mailSession = Session.getInstance(props, null);
         Transport transport = mailSession.getTransport("smtp";
         try {
            transport.connect(host, 25, userName,
                              password);
            transport.sendMessage(msg,
                                    msg.getRecipients(Message.RecipientType.TO));
            if (cc != null) {
                  transport.sendMessage(msg,
                                       msg.getRecipients(Message.
                        RecipientType.CC));
            }
            if (bcc != null) {
                  transport.sendMessage(msg,
                                       msg.getRecipients(Message.
                        RecipientType.BCC));
            }
            ok = true;
         } catch (Exception ee) {
            ok = false;
            ee.printStackTrace();
         } finally {
            transport.close();
         }
      } catch (Exception e) {
         e.printStackTrace();
         ok = false;
      }
      /*
               if (ok) {
         System.out.println(title + " mail to:" + to);
               } else {
         System.out.println(title + " error to:" + to);
               }
      */
      return ok;
}

public String getFrom() {
      return from;
}

public Vector getCc() {
      return cc;
}

public Vector getBcc() {
      return bcc;
}

public String getPassword() {
      return password;
}

public String getPriority() {
      return priority;
}

public String getReplyTo() {
      return replyTo;
}

public boolean isSmtpAuth() {
      return smtpAuth;
}

public String getSubject() {
      return subject;
}

public String getTitle() {
      return title;
}

public String getTo() {
      return to;
}

public String getUserName() {
      return userName;
}

public String getMailBodyEnd() {
      return mailBodyEnd;
}

public String getMailBodyBegin() {
      return mailBodyBegin;
}

public String getFilename() {
      return filename;
}
}
//

[/PHP]

mailsender.java
[PHP]
create or replace and compile java source named MailSender as
import java.io.File;
import java.io.PrintWriter;
import java.sql.*;
import java.util.*;

/**
* @author junsansi
* @qq 5454589
* @website www.5ienet.com
*/

public class MailSender {
//ishtml  1是 0否
public static MailService ms = null;

/**
   * initialize mailService
   * @throws Exception
   */
void init() throws Exception {
      ms = new MailService("smtp.sina.com.cn");  //smtp地址

      try {
         ms.setFrom("junsansi@sina.com");  //发送邮箱(不重要，只是显示)
         ms.setUserName("junsansi@sina.com");  //邮箱帐号
         ms.setPassword("123456");  //输入你的邮箱密码，呵呵，此处是我瞎写，您就表尝试用这个登陆了
         ms.setSmtpAuth(true);
      } catch (Exception e) {
         throw e;
      }
}

/**
   * sendmail no attachment
   * @param to String
   * @param sendTopic String
   * @param sendContent String
   * @param ishtml int
   * @return String
   */
public static String send(String to, String sendTopic,
                           String sendContent, int ishtml) {
      return send(to, null, null, sendTopic, sendContent, null, ishtml);
}

/**
   * sendmail by attachment
   * @param to String
   * @param sendTopic String
   * @param sendContent String
   * @param filename String
   * @param ishtml int
   * @return String
   */
public static String send(String to, String sendTopic,
                           String sendContent, String filename, int ishtml) {
      return send(to, null, null, sendTopic, sendContent, filename, ishtml);
}

/**
   * sendmail by cc,bcc but no attachment
   * @param to String
   * @param cc String
   * @param bcc String
   * @param sendTopic String
   * @param sendContent String
   * @param ishtml int
   * @return String
   */
public static String send(String to, String cc, String bcc,
                           String sendTopic,
                           String sendContent, int ishtml) {
      return send(to, cc, bcc, sendTopic, sendContent, null, ishtml);
}

/**
   * sendmail by cc,bcc and attachment
   * @param to String
   * @param cc String
   * @param bcc String
   * @param sendTopic String
   * @param sendContent String
   * @param filename String
   * @param ishtml int
   * @return String
   */
public static String send(String to, String cc, String bcc,
                           String sendTopic,
                           String sendContent, String filename, int ishtml) {
      MailSender makeSta = new MailSender();
      String result = null;
      try {
         if (ms == null) {
            makeSta.init();
         }
         ms.setTo(to);
         if (cc != null ) {
            cc = cc.trim();
            if (cc.length() > 0){
                  String[] cclist = split(cc, ",");

                  Vector list = new Vector(cclist.length);
                  int i;
                  for (i = 0; i < cclist.length; i++) {
                     list.add(cclist);
                  }
                  ms.setCc(list);
            }
         }
         if (bcc != null) {
            bcc = bcc.trim();
            if (bcc.length() > 0){
                  String[] bcclist = split(bcc, ",");
                  Vector list = new Vector(bcclist.length);
                  int i;
                  for (i = 0; i < bcclist.length; i++) {
                     list.add(bcclist);
                  }
                  ms.setBcc(list);
            }
         }
         if (filename != null) {
            filename = filename.trim();
            if (filename.length() > 0){
                  ms.setFilename(filename);
            }
         }

         ms.setSubject(sendTopic);
         if (ms.send(sendContent, ishtml)) {
            result = "successed to sent the mail to " + to;
         } else {
            result = "Failed to sent the mail.";
         }

      } catch (Exception e) {
      e.printStackTrace();
         result = "Failed to sent the mail, err:" + e.getMessage();
      } finally {
         return result;
      }

}

/**
   * no split function in java.lang.String in jdk1.3
   * so,i have write on by my self.
   * @param strtxt String
   * @param key String
   * @return String[]
   */
public static String[] split(String strtxt, String key) {
      StringTokenizer st = new StringTokenizer(strtxt, key);
      ArrayList al = new ArrayList();

      while (st.hasMoreTokens()) {
         al.add(st.nextToken());
      }
      String[] result = new String[al.size()];
      al.toArray(result);

      return result;
}

}
//

[/PHP]

junsansi · 发表于 2007-7-31 18:17

4.建立packages，此处为演示方便，我将java proc成了function，从实用角度考虑，应该是映射成普通的procedure更常用些，大家根据自己情况建吧

[PHP]
create or replace package Java_Sendmail as

/**
* 向指定地址发邮件
* @param to String
* @param sendTopic String
* @param sendContent String
* @param ishtml int
* @return String
*/
  function send(strTo    In Varchar2,
            strTopic In Varchar2,
            strContent In Varchar2,
            isHtml    in number) return varchar2;

/**
* 向指定地址发邮件，有附件
* @param to String
* @param sendTopic String
* @param sendContent String
* @param strFilename String
* @param ishtml int
* @return String
*/
  function send(strTo    In Varchar2,
            strTopic In Varchar2,
            strContent  In Varchar2,
            strFilename In varchar2,
            isHtml    in number) return varchar2;

/**
* 向指定地址发邮件，有抄送，有密送
* @param to String
* @param strcc String
* @param strbcc String
* @param sendTopic String
* @param sendContent String
* @param ishtml int
* @return String
*/
  function send(strTo    In Varchar2,
            strcc    in varchar2,
            strbcc    in varchar2,
            strTopic In Varchar2,
            strContent In Varchar2,
            isHtml    in number) return varchar2;

/**
* 向指定地址发邮件，有抄送，有密送，有附件
* @param to String
* @param strcc String
* @param strbcc String
* @param sendTopic String
* @param sendContent String
* @param strFilename String
* @param ishtml int
* @return String
*/
  function send(strTo    In Varchar2,
            strcc    in varchar2,
            strbcc    in varchar2,
            strTopic In Varchar2,
            strContent  In Varchar2,
            strFilename In varchar2,
            isHtml    in number) return varchar2;

end Java_Sendmail;

create or replace package body Java_Sendmail as

  function send(strTo    In Varchar2,
            strTopic In Varchar2,
            strContent In Varchar2,
            isHtml    in number) return varchar2 as
LANGUAGE JAVA NAME 'MailSender.send(java.lang.String,
java.lang.String,
java.lang.String,
int) return java.lang.String';

  function send(strTo    In Varchar2,
            strTopic In Varchar2,
            strContent  In Varchar2,
            strfilename In varchar2,
            isHtml    in number) return varchar2 as
LANGUAGE JAVA NAME 'MailSender.send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
int) return java.lang.String';

  function send(strTo    In Varchar2,
            strcc    in varchar2,
            strbcc    in varchar2,
            strTopic In Varchar2,
            strContent In Varchar2,
            isHtml    in number) return varchar2 as
LANGUAGE JAVA NAME 'MailSender.send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
int) return java.lang.String';

  function send(strTo    In Varchar2,
            strcc    in varchar2,
            strbcc    in varchar2,
            strTopic In Varchar2,
            strContent  In Varchar2,
            strfilename In varchar2,
            isHtml    in number) return varchar2 as
LANGUAGE JAVA NAME 'MailSender.send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
int) return java.lang.String';

end Java_Sendmail;
--
[/PHP]

junsansi · 发表于 2007-7-31 18:18

示例：
/**向指定地址发送邮件*/
select Java_SendMail.send('junsansi@sina.com','发送不带附件的html主题','随便发发看看呗hehe',1) from dual;

/**向指定地址发送邮件，有附件*/
select Java_SendMail.send('junsansi@sina.com','发送带附件的html主题','随便发发附件看看呗hehe','e:\sqlnet.log',1) from dual;

/**向指定地址发送邮件，有抄送*/
select Java_SendMail.send('5454589@qq.com','junsansi@sina.com,junsansi@hotmail.com','','再次
发送带附件的html主题,cc','随便发发附件看看呗hehe',1) from dual;

/**向指定地址发送邮件，有抄送，有密送，有附件*/
select Java_SendMail.send('5454589@qq.com','junsansi@sina.com,junsansi@hotmail.com','junsansi@sina.com,junsansi@hotmail.com','再次发送带附件的html主题,cc,bcc','随便发发附件看看呗hehe','e:\sqlnet.log',1) from dual;