java 生成数据库表中每条记录的PDF并作为附件发送到电子邮件

nr7wwzry  于 2023-06-04  发布在  Java
关注(0)|答案(1)|浏览(200)

我在数据库表中有4条记录。该表具有id、name、score1、score2、score3、total、email列。每个记录具有不同的电子邮件。代码的目的是循环通过每个记录,通过记录“ID”为每个记录生成PDF,并将PDF作为电子邮件发送到记录的电子邮件值。我使用了while循环,但我很努力地尝试,我的代码生成了所有记录的PDF,并发送给每个收件人的电子邮件。'我只希望它只生成每个记录的PDF,并一次发送给收件人电子邮件'。我真的不知道我错在哪里。

  1. try{
  2. String sql="select * from score_table where id=score_table.id";
  3. pst=con.prepareStatement(sql);
  4. rs=pst.executeQuery();
  5. // Loop through each record
  6. while (rs.next()) {
  7. // Retrieve data for current record
  8. String name = rs.getString("name");
  9. String score1 = rs.getString("score1");
  10. String score2 = rs.getString("score2");
  11. String score3 = rs.getString("score3");
  12. String score4 = rs.getString("score4");
  13. int total = rs.getInt("total");
  14. String recipientEmail = rs.getString("email");
  15. // Create parameters for the report
  16. Map<String, Object> parameters = new HashMap<>();
  17. parameters.put("name", name);
  18. parameters.put("score1", score1);
  19. parameters.put("score2", score2);
  20. parameters.put("score3", score3);
  21. parameters.put("score4", score4);
  22. parameters.put("total", total);
  23. // Load report template
  24. JasperReport jasperReport = JasperCompileManager.compileReport("studentSco.jrxml");
  25. // Populate report with data
  26. JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, con);
  27. // Export report to PDF file
  28. byte[] pdfBytes = JasperExportManager.exportReportToPdf(jasperPrint);
  29. // Create new email message
  30. Properties props = new Properties();
  31. props.put("mail.smtp.host", "smtp.gmail.com");
  32. props.put("mail.smtp.port", "587");
  33. props.put("mail.smtp.auth", "true");
  34. props.put("mail.smtp.starttls.enable", "true");
  35. props.setProperty("mail.smtp.ssl.protocols", "TLSv1.2");
  36. Session session = Session.getInstance(props, new javax.mail.Authenticator() {
  37. @Override
  38. protected PasswordAuthentication getPasswordAuthentication() {
  39. return new PasswordAuthentication("rodysoftonline@gmail.com", "wdmssbarmwfgxxxw");
  40. }
  41. });
  42. Message message = new MimeMessage(session);
  43. message.setFrom(new InternetAddress("rodysoftonline@gmail.com"));
  44. message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(recipientEmail));
  45. message.setSubject("Report for " + name);
  46. MimeBodyPart attachment = new MimeBodyPart();
  47. ByteArrayDataSource source = new ByteArrayDataSource(pdfBytes, "application/pdf");
  48. attachment.setDataHandler(new DataHandler(source));
  49. attachment.setFileName("report_" + name + ".pdf");
  50. Multipart multipart = new MimeMultipart();
  51. multipart.addBodyPart(attachment);
  52. message.setContent(multipart);
  53. // Send email
  54. Transport.send(message);
  55. System.out.println("email sent successfully");
  56. }
  57. }catch(SQLException | MessagingException | JRException e){
  58. JOptionPane.showMessageDialog(null,e);
  59. }
  60. // Close database connection and resources
  61. finally{
  62. try{
  63. rs.close();
  64. pst.close();
  65. }
  66. catch(SQLException e){
  67. }
  68. }

这就是我目前所拥有的。虽然它在while循环中,但它只为所有记录生成一次pdf,并发送到记录中的每个电子邮件。我希望它只为每个记录生成PDF并发送到记录的电子邮件,生成下一个记录的PDF,发送到电子邮件等…直到完成表中的所有记录

jogvjijk

jogvjijk1#

下面是我如何重构你的代码。我最终得到了四个接口和四个类。您需要为其中两个接口添加实现。
首先对数据进行封装-User

  1. package report;
  2. import java.util.ArrayList;
  3. import java.util.Collections;
  4. import java.util.List;
  5. public class User {
  6. private final String name;
  7. private final String email;
  8. private final List<Double> scores;
  9. public User(String name, String email, List<Double> scores) {
  10. if ((name == null) || (name.trim().length() == 0)) throw new IllegalArgumentException("Name cannot be blank or null");
  11. if ((email == null) || (email.trim().length() == 0)) throw new IllegalArgumentException("Email cannot be blank or null");
  12. this.name = name.trim();
  13. this.email = email.trim();
  14. this.scores = (scores != null) ? new ArrayList<>(scores) : new ArrayList<>();
  15. }
  16. public String getName() {
  17. return name;
  18. }
  19. public String getEmail() {
  20. return email;
  21. }
  22. public List<Double> getScores() {
  23. return Collections.unmodifiableList(scores);
  24. }
  25. @Override
  26. public boolean equals(Object o) {
  27. if (this == o) return true;
  28. if (o == null || getClass() != o.getClass()) return false;
  29. User user = (User) o;
  30. if (!getName().equals(user.getName())) return false;
  31. if (!getEmail().equals(user.getEmail())) return false;
  32. return getScores().equals(user.getScores());
  33. }
  34. @Override
  35. public int hashCode() {
  36. int result = getName().hashCode();
  37. result = 31 * result + getEmail().hashCode();
  38. result = 31 * result + getScores().hashCode();
  39. return result;
  40. }
  41. @Override
  42. public String toString() {
  43. return "User{" +
  44. "name='" + name + '\'' +
  45. ", email='" + email + '\'' +
  46. ", scores=" + scores +
  47. '}';
  48. }
  49. }

我使用运行在JDK 17下的IntelliJ 2023.1创建了User类。这可能是使用记录类的一个很好的例子。
创建一个接口UserRepository和一个实现,它只与关系数据库交互:

  1. package report;
  2. import java.sql.Connection;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.sql.Statement;
  6. public interface UserRepository {
  7. User findByName(String name);
  8. default void close(ResultSet rs) {
  9. try {
  10. if (rs != null) {
  11. rs.close();
  12. }
  13. } catch (SQLException e) {
  14. // log at best
  15. }
  16. }
  17. default void close(Statement st) {
  18. try {
  19. if (st != null) {
  20. st.close();
  21. }
  22. } catch (SQLException e) {
  23. // log at best.
  24. }
  25. }
  26. default void close(Connection connection) {
  27. try {
  28. if (connection != null) {
  29. connection.close();
  30. }
  31. } catch (SQLException e) {
  32. // log at best.
  33. }
  34. }
  35. }

下面是一个实现:

  1. package report;
  2. import java.sql.Connection;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. public class UserRepositoryImpl implements UserRepository {
  7. public static final String FIND_BY_NAME = "SELECT NAME, EMAIL, SCORE1, SCORE2, SCORE3, SCORE4 FROM SCORE_TABLE WHERE NAME = ?";
  8. private final Connection connection;
  9. public UserRepositoryImpl(Connection connection) {
  10. this.connection = connection;
  11. }
  12. @Override
  13. public User findByName(String name) {
  14. User user;
  15. ResultSet rs = null;
  16. PreparedStatement ps = null;
  17. try {
  18. ps = this.connection.prepareStatement(FIND_BY_NAME);
  19. rs = ps.executeQuery();
  20. user = new UserMapper().mapRow(rs);
  21. } catch (SQLException e) {
  22. throw new RuntimeException(e);
  23. } finally {
  24. close(rs);
  25. close(ps);
  26. }
  27. return user;
  28. }
  29. }

存储库实现使用RowMapper接口和UserMapper实现:

  1. package report;
  2. import java.sql.ResultSet;
  3. public interface RowMapper<T> {
  4. T mapRow(ResultSet rs);
  5. }

下面是UserMapper的实现:

  1. package report;
  2. import java.sql.ResultSet;
  3. import java.sql.SQLException;
  4. import java.util.ArrayList;
  5. import java.util.List;
  6. public class UserMapper implements RowMapper<User> {
  7. @Override
  8. public User mapRow(ResultSet rs) {
  9. User user = null;
  10. try {
  11. while (rs.next()) {
  12. String name = rs.getString("NAME");
  13. String email = rs.getString("EMAIL");
  14. List<Double> scores = new ArrayList<>();
  15. scores.add(rs.getDouble("SCORE1"));
  16. scores.add(rs.getDouble("SCORE2"));
  17. scores.add(rs.getDouble("SCORE3"));
  18. scores.add(rs.getDouble("SCORE4"));
  19. user = new User(name, email, scores);
  20. }
  21. } catch (SQLException e) {
  22. user = null;
  23. }
  24. return user;
  25. }
  26. }

碧玉报告生成器和电子邮件客户端有以下接口:

  1. package report;
  2. public interface JasperReportGenerator<T> {
  3. byte [] generate(String jrxml, T data);
  4. }
  5. package report;
  6. public interface EmailClient {
  7. void send(String from, String to, String subject, String body, byte [] attachment);
  8. }

这是比你的单一方法更多的类。这种方法有几个优点:

  • 每个班做好一件事(单一责任原则)
  • 使用Junit单独测试每个类很容易。

看看这个方法是否对你有帮助。

展开查看全部

相关问题