数据库单条记录PDF生成及邮件发送异常排查

数据库表有4条记录，字段包含id、name、score1、score2、score3、total、email，每条记录对应不同邮箱。需求是遍历每条记录，根据id生成仅包含该条记录的PDF并发送到对应邮箱。但当前Java代码使用while循环时，生成的PDF包含所有记录，每个收件人都收到全量PDF，无法定位问题。

以下是当前代码：

try{
    String sql="select * from score_table where id=score_table.id";
    pst=con.prepareStatement(sql);
    rs=pst.executeQuery();
    // Loop through each record
    while (rs.next()) {
        // Retrieve data for current record
        String name = rs.getString("name");
        String score1 = rs.getString("score1");
        String score2 = rs.getString("score2");
        String score3 = rs.getString("score3");
        String score4 = rs.getString("score4"); 
        int total = rs.getInt("total");
        String recipientEmail = rs.getString("email");

        // Create parameters for the report
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("name", name);
        parameters.put("score1", score1);
        parameters.put("score2", score2);
        parameters.put("score3", score3);
        parameters.put("score4", score4);
        parameters.put("total", total);

        // Load report template
        JasperReport jasperReport = JasperCompileManager.compileReport("studentSco.jrxml");

        // Populate report with data
        JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, con);

        // Export report to PDF file
        byte[] pdfBytes = JasperExportManager.exportReportToPdf(jasperPrint);

        // Create new email message
        Properties props = new Properties();
        props.put("mail.smtp.host", "smtp.gmail.com");
        props.put("mail.smtp.port", "587");
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.starttls.enable", "true");
        props.setProperty("mail.smtp.ssl.protocols", "TLSv1.2");

        Session session = Session.getInstance(props, new javax.mail.Authenticator() {
            @Override
            protected PasswordAuthentication getPasswordAuthentication() {
                return new PasswordAuthentication("rodysoftonline@gmail.com", "wdmssbarmwfgxxxw");
            }
        });

        Message message = new MimeMessage(session);
        message.setFrom(new InternetAddress("rodysoftonline@gmail.com"));
        message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(recipientEmail));
        message.setSubject("Report for " + name);

        MimeBodyPart attachment = new MimeBodyPart();
        ByteArrayDataSource source = new ByteArrayDataSource(pdfBytes, "application/pdf");
        attachment.setDataHandler(new DataHandler(source));
        attachment.setFileName("report_" + name + ".pdf");

        Multipart multipart = new MimeMultipart();
        multipart.addBodyPart(attachment);

        message.setContent(multipart);

        // Send email
        Transport.send(message);
        System.out.println("email sent successfully");
    }
}catch(SQLException | MessagingException | JRException e){
    JOptionPane.showMessageDialog(null,e);
}
// Close database connection and resources
finally{
    try{
        rs.close();
        pst.close();
    }
    catch(SQLException e){
    }
}

核心问题 #

你调用JasperFillManager.fillReport(jasperReport, parameters, con)时直接传入了数据库连接con，如果你的JRXML模板中包含查询语句，JasperReports会执行该查询拉取所有记录，完全忽略你传入的参数，导致每个PDF都包含全量数据。

修复步骤 #

1. 选择单条记录的数据源方式

有两种可靠方式让Jasper生成单条记录的PDF：

方式一：通过参数过滤JRXML模板的查询

• 在studentSco.jrxml中添加名为id的参数，修改模板内的查询语句为：

  select * from score_table where id = $P{id}
  

• 在Java循环中，将当前记录的id加入参数：

  int currentId = rs.getInt("id");
  parameters.put("id", currentId);
  

• 填充报表时仍使用数据库连接，但Jasper会根据参数查询单条记录。

方式二：用Bean数据源直接传入单条数据

不需要修改JRXML模板的查询，直接将当前记录封装为JavaBean，用Bean集合作为数据源：

// 先定义实体类
class StudentScore {
    private String name;
    private String score1;
    private String score2;
    private String score3;
    private String score4;
    private int total;

    public StudentScore(String name, String score1, String score2, String score3, String score4, int total) {
        this.name = name;
        this.score1 = score1;
        this.score2 = score2;
        this.score3 = score3;
        this.score4 = score4;
        this.total = total;
    }

    // 为所有字段添加getter方法
    public String getName() { return name; }
    public String getScore1() { return score1; }
    // ... 其他getter省略
}

// 在循环内创建数据源
StudentScore student = new StudentScore(name, score1, score2, score3, score4, total);
JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(Collections.singletonList(student));
// 替换原fillReport的第三个参数为dataSource
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, dataSource);

2. 性能优化：提前编译JRXML模板

当前代码在循环内重复编译模板，严重浪费性能，将编译步骤移到循环外：

// 移到while循环之前
JasperReport jasperReport = JasperCompileManager.compileReport("studentSco.jrxml");

while (rs.next()) {
    // ... 其他代码
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, dataSource);
    // ... 其他代码
}

内容的提问来源于stack exchange，提问作者mpaypal100