如何将查询获取的邮箱列表转为单个逗号分隔字符串？

我有如下Java代码用于查询邮箱并发送邮件，但sendNotification方法仅接收字符串类型的邮箱参数，需要将查询得到的List<UserDto>合并为单个逗号分隔的字符串。

查询邮箱方法 #

public List<UserDto> getEmail() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet searchResultSet = null;
    try {
        connection = getConnection();
        preparedStatement = connection.prepareStatement(
                    "SELECT LISTAGG(USER.U_EMAIL, ', ') WITHIN GROUP (ORDER BY USER.U_EMAIL) AS Emails FROM USER USER WHERE USER.U_SEQ IN ('1','560') GROUP BY USER.U_EMAIL");
        searchResultSet = preparedStatement.executeQuery();
        return getEmail(searchResultSet);
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        try {
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

private List<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;
}

邮件发送代码 #

Delegate delegate = new Delegate();
List<UserDto> users = iimDelegate.getEmail();
delegate.sendNotification("****", "****", users, "", "", "", body);

方案1：优化SQL直接获取合并后的邮箱字符串 #

当前SQL中使用LISTAGG却又按U_EMAIL分组，会导致每个邮箱单独返回一行，完全浪费了LISTAGG的合并作用。直接移除GROUP BY即可让SQL返回一个包含所有目标邮箱的逗号分隔字符串：

修改后的查询方法：

public String getEmailString() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet searchResultSet = null;
    try {
        connection = getConnection();
        // 移除GROUP BY，让LISTAGG合并所有符合条件的邮箱
        preparedStatement = connection.prepareStatement(
                    "SELECT LISTAGG(USER.U_EMAIL, ', ') WITHIN GROUP (ORDER BY USER.U_EMAIL) AS Emails FROM USER USER WHERE USER.U_SEQ IN ('1','560')");
        searchResultSet = preparedStatement.executeQuery();
        if (searchResultSet.next()) {
            return searchResultSet.getString("Emails");
        }
        return ""; // 无数据时返回空字符串
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        // 统一关闭所有数据库资源
        try {
            if (searchResultSet != null) searchResultSet.close();
            if (preparedStatement != null) preparedStatement.close();
            if (connection != null) connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

对应的邮件发送代码：

Delegate delegate = new Delegate();
String emails = iimDelegate.getEmailString();
delegate.sendNotification("****", "****", emails, "", "", "", body);

方案2：在Java代码中合并现有List中的邮箱 #

如果不想修改查询方法，可通过Java代码直接处理List<UserDto>：

Java 8+ 版本（用Stream API） #

Delegate delegate = new Delegate();
List<UserDto> users = iimDelegate.getEmail();
// 提取邮箱、过滤空值后合并为逗号分隔字符串
String emails = users.stream()
                    .map(UserDto::getEmailAddress)
                    .filter(Objects::nonNull)
                    .collect(Collectors.joining(", "));
delegate.sendNotification("****", "****", emails, "", "", "", body);

Java 8 之前版本（用循环拼接） #

Delegate delegate = new Delegate();
List<UserDto> users = iimDelegate.getEmail();
StringBuilder sb = new StringBuilder();
for (UserDto user : users) {
    String email = user.getEmailAddress();
    if (email != null && !email.isEmpty()) {
        if (sb.length() > 0) {
            sb.append(", ");
        }
        sb.append(email);
    }
}
String emails = sb.toString();
delegate.sendNotification("****", "****", emails, "", "", "", body);

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