Java JDBC：高效批量处理ResultSet中的多条数据

// 原始查询语句
"SELECT EMAIL FROM USER WHERE USER.U_SEQ IN ('1','650')"

    private UserDto getEmail(ResultSet searchResultSet) throws SQLException {
        List<UserDto > result = new ArrayList<UserDto >();
        UserDto userDto = null;
        while (searchResultSet.next()) {
            userDto = new UserDto ();
            userDto .setEmailAddress(searchResultSet.getString(1));
            result.add(userDto );
        }
        // 问题所在：只返回了列表中的第一个对象
        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 和 getConnection() 方法已定义
// public class UserDto { private String emailAddress; public void setEmailAddress(String email) { this.emailAddress = email; } public String getEmailAddress() { return emailAddress; }}
// public Connection getConnection() throws SQLException { /* 实现数据库连接逻辑 */ }

public class Delegate { // 假设 Delegate 包含数据访问逻辑

    // 假设 getConnection() 方法已定义并返回一个数据库连接
    private Connection getConnection() throws SQLException {
        // 实际应用中应从连接池获取或通过其他方式创建连接
        throw new UnsupportedOperationException("getConnection() method not implemented.");
    }

    /**
     * 从数据库查询所有符合条件的邮件地址并封装为 UserDto 列表。
     * @return 包含所有用户邮件地址的 UserDto 列表。
     * @throws RuntimeException 如果发生数据库操作异常。
     */
    public List<UserDto> getEmails() { // 方法名改为复数，表示返回多个
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet searchResultSet = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(
                    "SELECT EMAIL FROM USER WHERE USER.U_SEQ IN ('1','650')");
            searchResultSet = preparedStatement.executeQuery();
            return extractEmailsFromResultSet(searchResultSet); // 调用辅助方法提取列表
        } catch (Exception e) {
            throw new RuntimeException("Error fetching emails from database.", e);
        } finally {
            // 确保 PreparedStatement 和 Connection 资源被关闭
            try {
                if (searchResultSet != null) searchResultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                System.err.println("Error closing database resources: " + e.getMessage());
            }
        }
    }

    /**
     * 辅助方法：从 ResultSet 中提取所有邮件地址并构建 UserDto 列表。
     * @param searchResultSet 数据库查询结果集。
     * @return 包含所有用户邮件地址的 UserDto 列表。
     * @throws SQLException 如果访问 ResultSet 发生错误。
     */
    private List<UserDto> extractEmailsFromResultSet(ResultSet searchResultSet) throws SQLException {
        List<UserDto> result = new ArrayList<>();
        while (searchResultSet.next()) { // 遍历 ResultSet 中的每一行
            UserDto userDto = new UserDto();
            userDto.setEmailAddress(searchResultSet.getString(1)); // 获取第一列（EMAIL）的值
            result.add(userDto);
        }
        return result; // 返回完整的 UserDto 列表
    }

    // 假设 sendNotification 方法已定义
    public void sendNotification(String subject, String from, String to, String cc, String bcc, String attachment, String body) {
        System.out.println("Sending email to: " + to + " with subject: " + subject);
        // 实际的邮件发送逻辑
    }
}

// 假设 Delegate 和 UserDto 类已在上面定义

public class EmailSenderService {

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

        try {
            List<UserDto> users = delegate.getEmails(); // 获取所有用户邮件地址列表

            if (users != null && !users.isEmpty()) {
                String subject = "重要通知";
                String body = "这是一封来自系统的通知邮件。";

                for (UserDto userDto : users) { // 遍历列表，逐一发送邮件
                    String toEmail = userDto.getEmailAddress();
                    if (toEmail != null && !toEmail.trim().isEmpty()) {
                        delegate.sendNotification(subject, "noreply@example.com", toEmail, "", "", "", body);
                        System.out.println("Email sent successfully to: " + toEmail);
                    } else {
                        System.out.println("Skipping email for user with empty address.");
                    }
                }
            } else {
                System.out.println("No email addresses found to send notifications.");
            }
        } catch (RuntimeException e) {
            System.err.println("An error occurred during email fetching or sending: " + e.getMessage());
            e.printStackTrace();
        }
    }
}