Java JDBC：处理多行ResultSet数据并实现批量邮件发送

public UserDto getEmail() {
    // ... JDBC 连接和语句准备 ...
    ResultSet searchResultSet = preparedStatement.executeQuery();
    return getEmail(searchResultSet); // 调用私有方法处理ResultSet
    // ... 资源关闭和异常处理 ...
}

private UserDto getEmail(ResultSet searchResultSet) throws SQLException {
    List<UserDto> result = new ArrayList<>();
    UserDto userDto = null;
    while (searchResultSet.next()) {
        userDto = new UserDto();
        userDto.setEmailAddress(searchResultSet.getString(1));
        result.add(userDto);
    }
    // 问题所在：即使result列表中有多条记录，这里也只返回了第一条
    return result == null ? null : result.size() == 0 ? null : result.get(0);
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

// 假设UserDto类包含一个String emailAddress字段及其getter/setter
class UserDto {
    private String emailAddress;

    public String getEmailAddress() {
        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }

    @Override
    public String toString() {
        return "UserDto{emailAddress='" + emailAddress + "'}";
    }
}

// 模拟数据库连接获取
class DatabaseConnectionManager {
    public static Connection getConnection() throws SQLException {
        // 实际应用中应配置数据库连接池或驱动
        // 这里仅为示例，使用一个简单的模拟连接
        // throw new UnsupportedOperationException("Not implemented for example");
        // 实际代码应替换为您的数据库连接逻辑
        System.out.println("Getting database connection...");
        return null; // 示例中不真正建立连接
    }
}

public class Delegate {

    // 模拟获取数据库连接的方法
    private Connection getConnection() throws SQLException {
        return DatabaseConnectionManager.getConnection();
    }

    /**
     * 从数据库中获取所有符合条件的邮箱地址列表。
     *
     * @return 包含UserDto对象的列表，每个UserDto包含一个邮箱地址。
     */
    public List<UserDto> getEmails() { // 方法名改为复数更符合语义
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet searchResultSet = null;
        try {
            connection = getConnection(); // 获取数据库连接
            // 注意：实际应用中，'1','650'这样的硬编码参数应通过PreparedStatement设置
            preparedStatement = connection.prepareStatement(
                    "SELECT EMAIL FROM USER WHERE USER.U_SEQ IN ('1','650')");

            searchResultSet = preparedStatement.executeQuery();

            return extractEmailsFromResultSet(searchResultSet); // 调用私有方法提取所有邮箱

        } catch (Exception e) {
            // 捕获所有异常并包装为RuntimeException，便于上层处理
            throw new RuntimeException("Error fetching emails from database: " + e.getMessage(), e);
        } finally {
            // 确保JDBC资源在任何情况下都被关闭
            try {
                if (searchResultSet != null) searchResultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close(); // 实际应用中，连接池的连接是归还而不是关闭
            } catch (SQLException e) {
                System.err.println("Error closing JDBC resources: " + e.getMessage());
                // 通常不重新抛出，但记录日志很重要
            }
        }
    }

    /**
     * 私有辅助方法，用于从ResultSet中提取所有邮箱地址。
     *
     * @param searchResultSet 数据库查询结果集。
     * @return 包含所有UserDto对象的列表。
     * @throws SQLException 如果访问ResultSet时发生SQL错误。
     */
    private List<UserDto> extractEmailsFromResultSet(ResultSet searchResultSet) throws SQLException {
        List<UserDto> result = new ArrayList<>();
        while (searchResultSet.next()) { // 循环遍历ResultSet的每一行
            UserDto userDto = new UserDto();
            // getString(1) 获取当前行的第一个列的值（即EMAIL列）
            userDto.setEmailAddress(searchResultSet.getString(1)); 
            result.add(userDto);
        }
        return result; // 返回包含所有邮箱地址的列表
    }

    /**
     * 模拟发送通知邮件的方法。
     *
     * @param subject 邮件主题
     * @param fromEmail 发件人邮箱
     * @param toEmail 收件人邮箱
     * @param ccEmail 抄送邮箱
     * @param bccEmail 密送邮箱
     * @param replyToEmail 回复邮箱
     * @param body 邮件正文
     */
    public void sendNotification(String subject, String fromEmail, String toEmail, 
                                 String ccEmail, String bccEmail, String replyToEmail, String body) {
        System.out.println("Sending email to: " + toEmail + " with subject: " + subject);
        // 实际邮件发送逻辑（例如使用JavaMail API）
        // ...
        System.out.println("Email sent successfully to " + toEmail);
    }
}

public class EmailService {

    public static void main(String[] args) {
        Delegate delegate = new Delegate();

        try {
            // 调用修改后的方法，获取所有用户的邮箱列表
            List<UserDto> users = delegate.getEmails(); 

            if (users != null && !users.isEmpty()) {
                System.out.println("Retrieved " + users.size() + " email addresses.");
                // 遍历用户列表，为每个用户发送邮件
                for (UserDto userDto : users) {
                    String toEmail = userDto.getEmailAddress();
                    if (toEmail != null && !toEmail.trim().isEmpty()) {
                        String subject = "Important Notification";
                        String fromEmail = "noreply@example.com";
                        String body = "Dear User,\n\nThis is a test notification email.";

                        delegate.sendNotification(subject, fromEmail, toEmail, 
                                                   "", "", "", body);
                    } else {
                        System.out.println("Skipping email for user with empty address: " + userDto);
                    }
                }
            } else {
                System.out.println("No email addresses found to send notifications.");
            }
        } catch (RuntimeException e) {
            System.err.println("An error occurred during email processing: " + e.getMessage());
            e.printStackTrace();
        }
    }
}