MOOC华东师范大学 Java核心技术（进阶）第七章作业 从数据库读取表格并发送邮件

假设数据库有一张表t_mail (id, from, to, subject, content), 里面存储着具体的邮件发件人、收件人、标题和内容。采用Druid连接池，读取id为1的记录，并基于Java Mail将该邮件发送出来。

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * 假设数据库有一张表t_mail (id, from, to, subject, content), 
 * 里面存储着具体的邮件发件人、收件人、标题和内容。
 * 采用Druid连接池，读取id为1的记录，并基于Java Mail将该邮件发送出来。
 */
public class DBMailSend {

	public static void main(String[] args) {
		Connection conn=null;
		String from;
		String to;
		String subject;
		String content;
		String password;
		String smtpServer;
		try {
			//从Druid获取
			conn=DruidFactory.getConnection();
			System.out.println("连接池构建成功");
			
			//构造数据库执行者
			Statement stmt=conn.createStatement();
			System.out.println("获取连接成功");
			
			//执行SQL语句并返回结果到ResultSet
			ResultSet rs=stmt.executeQuery("select id,mfrom,mto,msubject,content,password,smtpServer from t_mail order by id");
		    System.out.println("获取数据成功");
		    
		    //开始遍历ResultSet数据
		    while(rs.next()) {
		    	System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6)+","+rs.getString(7));
		    	
		    	if(rs.getInt(1)==1) {
		    		from=rs.getString(2);
		    		to=rs.getString(3);
		    		subject=rs.getString(4);
		    		content=rs.getString(5);
		    		password=rs.getString(6);
		    		smtpServer=rs.getString(7);
		    		MailClientSend client=new MailClientSend(from,password,smtpServer);
		    		client.init();
		    		client.sendMessage(from, to, subject, content);
		    		System.out.println("发送邮件成功");
		    	}
		    	
		    }
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(null!=null) {
					conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}

	}

}

import java.sql.Connection;

import com.alibaba.druid.pool.DruidDataSource;

public class DruidFactory {
   private static DruidDataSource dataSource=null;
   
   public static void init()throws Exception{
	   dataSource=new DruidDataSource();
	   dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
	   dataSource.setUsername("root");
	   dataSource.setPassword("123456");
	   dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC");
	   dataSource.setMinIdle(1);
	   dataSource.setMaxActive(10);
	   
	   //启动监控统计功能 dataSource.setFilters("stat");
   }
   public static Connection getConnection()throws Exception{
	   if(null==dataSource) {
		   init();
	   }
	   return dataSource.getConnection();
   }
}

import java.util.Properties;

import javax.mail.Authenticator;
import javax.mail.Message;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;

public class MailClientSend {
    private Session session;
    private Transport transport;
    private String username;
    private String password;
    //private String smtpServer="smtp.qq.com";
    private String smtpServer;
	
    public MailClientSend(String username, String password, String smtpServer) {
		super();
		this.username = username;
		this.password = password;
		this.smtpServer = smtpServer;
	}
    
    public void init()throws Exception{
    	//设置属性
    	Properties props=new Properties();
    	props.put("mail.transport.protocol", "smtp");
    	props.put("mail.smtp.class", "com.sun.mail.smtp.SMTPTransport");
    	props.put("mail.smtp.host", smtpServer);//设置发送邮件服务器
    	props.put("mail.smtp,port","25");
    	props.put("mail.smtp.auth", "true");//SMTP服务器需要身份验证
    	
    	//创建Session对象
    	session=Session.getInstance(props,new Authenticator() {//验证账户
    		public PasswordAuthentication getPasswordAuthentication() {
    			return new PasswordAuthentication(username,password);
    		}
    	});
    	//session.setDebug(true);//输出跟踪日志
    	
    	//创建Transport对象
    	transport=session.getTransport();
    	
    }
    public void sendMessage(String from, String to, String subject, String body)throws Exception{
    	//创建一个邮件
    	TextMessage tmsg=new TextMessage(from,to,subject,body);
    	
    	Message msg=tmsg.generate();
    	
    	transport.connect();
    	transport.sendMessage(msg, msg.getAllRecipients());
    	//打印结果
    	System.out.println("邮件已经发送成功");
    }
    public void close()throws Exception{
    	transport.close();
    }
    
}

import java.util.Date;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.Session;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

public class TextMessage {
    private String from;//发件人地址
    private String to;//收件人地址
    private String subject;//标题
    private String body;//正文
	
    public TextMessage(String from, String to, String subject, String body) {
		super();
		this.from = from;
		this.to = to;
		this.subject = subject;
		this.body = body;
	}
    
    public MimeMessage generate()throws Exception{
        //创建Session实例对象
        Session session=Session.getDefaultInstance(new Properties());
        //创建MimeMessage实例对象
        MimeMessage message=new MimeMessage(session);
        //设置发件人
        message.setFrom(new InternetAddress(from));
        //设置收件人
        message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(to));
        //设置发送日期
        message.setSentDate(new Date());
        //设置邮件主题
        message.setSubject(subject);
        //设置纯文本文件内容的邮件正文
        message.setText(body);
        //保存并生成最终的邮件内容
        message.saveChanges();
        
        //把MimeMessage对象中的内容写入到文件中
        //msg.writeTo(new FileOutputStream("e:/test.eml));
        return message;
    }
    
    
}

数据库表格如下：

注意：from 为SQL关键字，故不能作为表格字段，因此用mfrom替换