如何在Java中批量执行PostgreSQL查询并生成映射结果？

核心思路 #

遍历你的HashMap键值对，为每个键值对绑定SQL参数执行查询，最后将结果按"类型-值"的格式存入目标映射结构。

步骤1：预编译SQL语句 #

先将SQL预编译，避免重复解析提升执行效率，同时替换命名参数为占位符（或保留命名参数适配不同框架）：

SELECT d.nazw FROM gru u JOIN swd d USING(nswd)
WHERE u.ngru = ?::bigint AND ?::text = 'group'
UNION ALL 
SELECT nazw as PORTAL FROM wsu
WHERE prtl = ?::bigint AND ?::text = 'portal'
UNION ALL 
SELECT nazw FROM swd
WHERE nswd = ?::bigint AND ?::text = 'nswd'

步骤2：原生JDBC实现批量查询 #

如果使用原生JDBC，代码示例如下：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class BatchQueryHandler {

    public Map<String, List<String>> executeBatch(Map<String, Integer> inputMap, Connection conn) throws SQLException {
        Map<String, List<String>> resultMap = new HashMap<>();
        String sql = """
                SELECT d.nazw FROM gru u JOIN swd d USING(nswd)
                WHERE u.ngru = ?::bigint AND ?::text = 'group'
                UNION ALL 
                SELECT nazw as PORTAL FROM wsu
                WHERE prtl = ?::bigint AND ?::text = 'portal'
                UNION ALL 
                SELECT nazw FROM swd
                WHERE nswd = ?::bigint AND ?::text = 'nswd'
                """;

        // 复用PreparedStatement减少SQL解析开销
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (Map.Entry<String, Integer> entry : inputMap.entrySet()) {
                String type = entry.getKey();
                Integer data = entry.getValue();
                
                // 为三个UNION分支分别绑定参数
                pstmt.setInt(1, data);
                pstmt.setString(2, type);
                pstmt.setInt(3, data);
                pstmt.setString(4, type);
                pstmt.setInt(5, data);
                pstmt.setString(6, type);

                // 收集查询结果
                List<String> results = new LinkedList<>();
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        results.add(rs.getString("nazw"));
                    }
                }
                // 按指定格式存入结果映射
                resultMap.put(type + "-" + data, results);
            }
        }
        return resultMap;
    }
}

步骤3：Spring框架简化实现 #

如果项目使用Spring，用NamedParameterJdbcTemplate可以省去手动处理结果集的代码，更简洁：

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SpringBatchQueryHandler {

    private final NamedParameterJdbcTemplate namedJdbcTemplate;

    public SpringBatchQueryHandler(NamedParameterJdbcTemplate namedJdbcTemplate) {
        this.namedJdbcTemplate = namedJdbcTemplate;
    }

    public Map<String, List<String>> executeBatch(Map<String, Integer> inputMap) {
        Map<String, List<String>> resultMap = new HashMap<>();
        String sql = """
                SELECT d.nazw FROM gru u JOIN swd d USING(nswd)
                WHERE u.ngru = :data::bigint AND :type::text = 'group'
                UNION ALL 
                SELECT nazw as PORTAL FROM wsu
                WHERE prtl = :data::bigint AND :type::text = 'portal'
                UNION ALL 
                SELECT nazw FROM swd
                WHERE nswd = :data::bigint AND :type::text = 'nswd'
                """;

        for (Map.Entry<String, Integer> entry : inputMap.entrySet()) {
            String type = entry.getKey();
            Integer data = entry.getValue();
            
            // 构造命名参数映射
            Map<String, Object> params = new HashMap<>();
            params.put("data", data);
            params.put("type", type);
            
            // 直接获取结果列表
            List<String> results = namedJdbcTemplate.queryForList(sql, params, String.class);
            resultMap.put(type + "-" + data, results);
        }
        return resultMap;
    }
}

关键注意事项 #

• 防SQL注入：必须使用参数绑定方式，禁止直接拼接SQL字符串。
• 资源管理：用try-with-resources自动关闭JDBC资源，避免内存泄漏。
• 性能优化：复用PreparedStatement或依赖Spring框架的连接池机制，减少重复解析SQL的开销。

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