[大数据]java实战:邮件群发推广微信公众号（二），内含java操作excel及java操作mysql

? ? ? 上一篇写好了用于发送邮件的类，本篇把余下的设定定时任务、从excel读取发件人账号、从mysql读取收件人邮箱以及随机标题和内容来实现。先看整个应用的文件框架结构，如下图：

? ? ? 上图中log4j.properties文件没有用到，这是专用于日志的jar包log4j的配置文件，开始用它总是在第二天会出现?rename报错，后来改用了jar包log4j2，配置文件改成了xml类型。

? ? ? 三、主程序初始化任务

? ? ? 主程序App中启动一个无限循环，从SendTimer1类中读取生成的每天随机发送邮件的小时和分钟数，然后和当前系统时间的小时数分钟数比较，如果一致则启动发送任务。

/*
 * Class Name: App
 * Description: 定时邮件群发软件，内含mysql数据库操作、excel表操作、ini文件操作、定时器管理
 * Date: 2021-09-03 20:58
 * Copyright: 乱世刀疤
 */

package QinMing.Mail;

//log4j2导入的包
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.text.SimpleDateFormat;
import java.util.Date;

public class App {

    //log4j2使用方法，参数使用log4j2.xml文件内容
    private static Logger logger= LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);

    public static void main( String[] args ) {

        //System.out.println( "Start Mail Send App!" );
        logger.debug("Start Mail Send App!");
        logger.debug("waiting for send mail...");

        //SendTask st = new SendTask();
        //st.DailyTask();

        //启动第一个定时器，用于每天00:00:30生成随机的开始群发邮件的小时数和分钟数
        SendTimer1 sendTimer1 = new SendTimer1();
        sendTimer1.setStrSendHour("07");    //首次默认指定群发时间为07:15,
        sendTimer1.setStrSendMinute("15");
        sendTimer1.timerRun();

        //每60秒循环执行，判断当前小时数和分钟数是否与随机生成的指定数相同，相同则执行群发任务
        while(true){

            String strCurrentHour;
            String strCurrentMinute;
            Date today = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("HH");
            strCurrentHour = sdf.format(today);
            sdf = new SimpleDateFormat("mm");
            strCurrentMinute = sdf.format(today);
            System.out.println("define time:" + sendTimer1.getStrSendHour() + ":" + sendTimer1.getStrSendMinute());
            System.out.println("current time:" + strCurrentHour + ":" + strCurrentMinute);
            if((strCurrentHour.equals(sendTimer1.getStrSendHour())) && (strCurrentMinute.equals(sendTimer1.getStrSendMinute()))){
                SendTask st = new SendTask();
                st.DailyTask();
            }

            try{
                Thread.sleep(60000);
            }catch (InterruptedException e){
                logger.debug(e.toString());
            }

        }

    }

}

? ? ? 四、设定每天定时任务启动的小时数和分钟数，类SendTimer1的内容如下：

/*
 * Class Name: SendTimer1
 * Description:用于生成每天随机执行邮件发送任务的小时数和分钟数
 * Date: 2021-09-06 20:58
 * Copyright: 乱世刀疤
 */

package QinMing.Mail;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
import java.util.Timer;
import java.util.TimerTask;

public class SendTimer1 {

    private String strSendHour;
    private String strSendMinute;

    public String getStrSendHour() {
        return strSendHour;
    }

    public void setStrSendHour(String strSendHour) {
        this.strSendHour = strSendHour;
    }

    public String getStrSendMinute() {
        return strSendMinute;
    }

    public void setStrSendMinute(String strSendMinute) {
        this.strSendMinute = strSendMinute;
    }

    public void timerRun() {
        // 一天的毫秒数
        long OneDayMs = 24 * 60 * 60 * 1000;
        //执行间隔毫秒数
        long periodMs = 2000;
        // 规定的每天时间00:00:30运行
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 07:00:00");
        // 首次运行时间
        try {
            Date startTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(sdf.format(new Date()));
            // 如果今天的已经过了，首次运行时间就改为明天
            if (System.currentTimeMillis() > startTime.getTime()) {
                startTime = new Date(startTime.getTime() + OneDayMs);
            }
            Timer t = new Timer();
            TimerTask task = new TimerTask() {
                @Override
                public void run() {
                    Random rd = new Random(); //.nextInt(max-min+1)  + min; 取介于min和max之间的随机数
                    strSendHour = String.format("%02d", rd.nextInt(10 - 7 + 1) + 7);  //转成自动在前面补零的字符串
                    strSendMinute = String.format("%02d", rd.nextInt(59 - 1 + 1) + 1);
                }
            };
            // OneDayMs以每24小时执行一次task任务，在startTime指定时间点执行
            t.schedule(task, startTime, OneDayMs);   //periodMs
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

? ? ? 五、邮件群发任务类SendTask，内含读取excel操作和读取、更新mysql操作，内容如下：

/*
 * Class Name: SendTask
 * Description: 邮件群发任务，读取配置文件中指定的excel文件的发件人信息，向数据库中的目标收件人逐个发送。
 * Date: 2021-09-07 17:34
 * Copyright: 乱世刀疤
 */

package QinMing.Mail;

//import org.apache.log4j.Logger;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Random;

public class SendTask {

    private XSSFWorkbook xssfWb;
    private XSSFSheet xssfSheet;
    private FileInputStream fileInputStream;

    //群发邮件任务，逻辑：从excel中读出可以用于发送邮件的账号清单，根据设置的发送数量范围随机生成当天群发数量；
    //从数据库中读取收件人目标邮箱逐个发送，标题和内容从数据表中随机选取。
    public void DailyTask() {

        String excelFile;
        String excelSheet;
        String mysqlDirver;
        String mysqlUrl;
        String mysqluser;
        String mysqlpass;

        log4j使用方法
        //Logger logger = Logger.getLogger(App.class);
        //logger.debug("start send mail ...");

        log4j2使用方法
        Logger logger= LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);
        logger.debug("start send mail ...");

        //读取app.properties文件中的相关参数
        try {
            Properties properties = new Properties();
            //使用InPutStream流读取properties文件
            //BufferedReader bufferedReader = new BufferedReader(new FileReader(System.getProperty("user.dir") + "/app.properties"));
            BufferedReader bufferedReader = new BufferedReader(new FileReader("app.properties"));
            properties.load(bufferedReader);
            // 获取key对应的value值
            excelFile = properties.getProperty("filename");
            excelSheet = properties.getProperty("sheetname");
            mysqlDirver = properties.getProperty("driver");
            mysqlUrl = properties.getProperty("url");
            mysqluser = properties.getProperty("username");
            mysqlpass = properties.getProperty("password");
            bufferedReader.close();
        }catch (IOException e){
            logger.debug(e.toString());
            return;
        }

        //读取可用于发送邮件的邮箱账号excel表，并进行群发操作
        try {
            fileInputStream = new FileInputStream(excelFile);
            //打开excel文件
            xssfWb = new XSSFWorkbook(fileInputStream);
            //取sheet表
            xssfSheet = xssfWb.getSheet(excelSheet);
        } catch (IOException e) {
            logger.debug(e.toString());
        }

        //读取每一行并根据收件箱进行处理，行数从0开始，1表示去掉标题行
        for(int i=1; i<xssfSheet.getPhysicalNumberOfRows(); i++){
            //读取一行中的每一列数据，列数从0开始
            XSSFRow xssfRow = xssfSheet.getRow(i);
            Cell cell;
            String sender = xssfRow.getCell(0).toString();
            String smtp_server = xssfRow.getCell(1).toString();
            //强制类型转换为string，防止整形数从excel读出后自动被加上小数点
            cell = xssfRow.getCell(2);
            cell.setCellType(CellType.STRING);
            String user_name = cell.toString();
            cell = xssfRow.getCell(3);
            cell.setCellType(CellType.STRING);
            String password = cell.toString();
            cell = xssfRow.getCell(4);
            cell.setCellType(CellType.STRING);
            int min_num = Integer.valueOf(cell.toString()).intValue();
            cell = xssfRow.getCell(5);
            cell.setCellType(CellType.STRING);
            int max_num = Integer.valueOf(cell.toString()).intValue();
            //根据每个邮箱设置的当天发送最大数和最小数范围，发送随机数量邮件
            Random rd = new Random();
            int j = rd.nextInt((max_num - min_num + 1) + min_num);

            //调用邮件发送接口
            SendMailSmtp sms = new SendMailSmtp();
            sms.setMailSender(sender);
            sms.setMailSmtpServer(smtp_server);
            sms.setMailUser(user_name);
            sms.setMailPass(password);

            String gt = sms.GetTransport();
            if(gt == "success") {
                logger.debug(sender + " connect smtp transport!");
                //调用Class.forName()方法加载mysql驱动程序
                try {
                    Class.forName(mysqlDirver);
                }catch (ClassNotFoundException e){
                    logger.debug("mysql driver error!" + e.toString());
                }

                try {
                    Connection conn = DriverManager.getConnection(mysqlUrl, mysqluser, mysqlpass);
                    Statement stmt = conn.createStatement(); //创建Statement对象用于select

                    for (int c = 0; c < j; c++) {
                        //随机取邮件标题和内容防封号
                        String mail_title = "，您的好友送您一份超值优惠券！";
                        String mail_content = "</br>您好！</br>您的好友委托我们为您送来一份超值优惠券，吃喝玩乐全都有，最低只要一折起，微信扫码即可享受。"
                                + "</br>下面列举几个优惠券供参考：</br></br>"
                                + "<img src='https://images0.cnblogs.com/i/256716/201407/301432229935710.jpg'/>";
                        ResultSet rs = stmt.executeQuery("SELECT * FROM jjlm_title_content ORDER BY RAND() LIMIT 1");
                        if (rs.next()) {
                            mail_title = rs.getString("mail_title");
                            mail_content = rs.getString("mail_content");
                            //增加标题使用次数，可以用来观察随机量是否均衡
                            String sql = "update jjlm_title_content set used_num=used_num+1 where mail_title=?";
                            PreparedStatement stmt1 = conn.prepareStatement(sql);
                            stmt1.setString(1, mail_title);
                            stmt1.executeUpdate();
                        }
                        rs.close();

                        //从收件人邮箱清单中取一个未使用过的进行发送
                        rs = stmt.executeQuery("select * from jjlm_dest_mail_list where send_time is null");
                        if (rs.next()) {
                            sms.SendText(rs.getString("recv_mail"), rs.getString("recv_sn") + mail_title, mail_content);
                            //更新目标邮箱相关发送信息
                            String sql = "update jjlm_dest_mail_list set send_time=sysdate(),send_title=?,send_result='success',send_mail=? where recv_mail=?";
                            PreparedStatement stmt1 = conn.prepareStatement(sql);
                            stmt1.setString(1, mail_title);
                            stmt1.setString(2, sender);
                            stmt1.setString(3, rs.getString("recv_mail"));
                            stmt1.executeUpdate();
                            logger.debug(rs.getString("recv_mail") + " send complete!");
                        }
                        rs.close();

                        //发送完一个邮件，随机延时一段时间再发下一个，随机值定在15至20秒范围内
                        try {
                            Random rd1 = new Random();
                            int delay_ms = rd1.nextInt((10000 - 6000 + 1) + 6000);
                            Thread.sleep(delay_ms);
                        } catch (InterruptedException e) {
                            logger.debug(e.toString());
                        }
                    }
                    stmt.close();
                    conn.close();
                }catch (SQLException e){
                    logger.debug("mysql operation error!" + e.toString());
                }
            }else{
                logger.debug(sender + " connect smtp transport failure:" + gt);
            }

            String ct = sms.CloseTransport();
            if(ct == "success"){
                logger.debug(sender + " disconnect smtp transport!");
            }else{
                logger.debug(sender + " disconnect smtp transport failure:" + ct);
            }

        }

    }

}

? ? ? 六、相关配置文件名称和内容，以及excel表格式和mysql数据表

? ? ? 1、maven项目配置文件pom.xml内容如下：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>QinMing.Mail</groupId>
  <artifactId>SendMail</artifactId>
  <version>1.0</version>

  <name>SendMail</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
    <javax.mail>1.4.7</javax.mail>
  </properties>

  <dependencies>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>javax.mail</groupId>
      <artifactId>mail</artifactId>
      <version>1.4.7</version>
    </dependency>

    <dependency>
      <groupId>javax.activation</groupId>
      <artifactId>activation</artifactId>
      <version>1.1.1</version>
    </dependency>

    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-api</artifactId>
      <version>2.14.1</version>
    </dependency>

    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-core</artifactId>
      <version>2.14.1</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.25</version>
    </dependency>

    <!-- office -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
    </dependency>

  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

? ? ? 2、应用所需的配置文件app.properties，放在可运行程序同目录，内容如下：

### excel文件名及存放邮件账号的sheet表名 ###
filename = mail.xlsx
sheetname = maillist

### mysql数据库连接串 ###
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/数据库名称?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
username = root
password = root用户的密码

? ? ? 3、日志log4j2配置文件log4j2.xml文件内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">   <!--设置log4j2自身运行的日志显示级别-->
    <Properties>
        <Property name="PR">logs2</Property>
    </Properties>
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">  			 <!--输出到控制台-->
            <PatternLayout pattern="%-d{yyyy-MM-dd HH:mm:ss} [%c]-[%p] %m%n"/>
        </Console>
        <RollingFile name="RollingFile" fileName="${PR}/applog.log"
                     filePattern="${PR}/文件名-%i.all.log.gz">   <!--输出到日志文件-->
            <PatternLayout pattern="%-d{yyyy-MM-dd HH:mm:ss} [%c]-[%p] - %m%n"/>
            <!-- 日志文件大小 -->
            <SizeBasedTriggeringPolicy size="100MB"/>
            <!-- 最多保留文件数 -->
            <DefaultRolloverStrategy max="200"/>
        </RollingFile>
    </Appenders>
    <Loggers>     								<!--指定所使用的日志记录器以及显示级别-->
        <Root level="debug">						<!--显示级别-->
            <AppenderRef ref="RollingFile"/>
            <AppenderRef ref="Console"/>
        </Root>
    </Loggers>
</Configuration>

? ? ? 4、mysql数据表创建语句

#创建数据库
create database if not exists send_mail;

#切换到新建数据库
use send_mail;

#创建待发送目标邮箱表
create table jjlm_dest_mail_list
(
    recv_mail             varchar(50),               #收件人邮箱
    recv_sn               varchar(11),               #收件人手机号
    send_time             DATETIME,                  #发送时间
    send_title            varchar(200),              #邮件标题
    send_result           varchar(10),               #发送成功标志，失败情况下到日志内查询详细原因
    send_mail             varchar(50)                #发件人邮箱
);

#创建随机标题和邮件内容表
create table jjlm_title_content
(
    mail_title            varchar(200),              #邮件标题
    mail_content          text,                      #邮件内容
    used_num              int                        #被使用次数
);

? ? ? 5、excel表格式及内容解释

? ? ??

?? ? ?

? ? ? 至此，一个邮件群发应用开发完毕，自己评价还是很实用的，希望对看到此篇文章的猿友们有所帮助。如果您觉得有用，请扫描关注下面二维码，算是对鄙人的打赏。朋友的微信公众号，吃喝玩乐优惠券什么都有，最低只要一折，真的是超级优惠。

?

?