如何用Java分批查询含大量记录的数据库表并存储结果

原代码无法正确获取全部数据的核心问题：

• 分页查询缺少OFFSET参数，每次循环都重复查询前10000条数据，导致List中全是重复记录，且无法覆盖所有数据。
• 循环条件i <= records会导致最后一次循环查询超出总记录数的范围，产生无效查询。
• 使用SELECT *查询所有字段，浪费数据库资源和网络带宽，不符合只查指定字段的需求。
• 异常处理过于简略，无法排查执行过程中的错误。
• 资源关闭逻辑存在空指针风险（如result未初始化时调用close()）。

修正后的代码 #

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

public class TestConnect {

    List<Empleados> empleadoList;

    public static void main(String[] args) throws SQLException {
        TestConnect test = new TestConnect();
        test.read();
    }

    // 从empleados表分批读取数据
    public void read() {
        String user = "postgres";
        String pss = "admin123";
        final int BATCH_SIZE = 10000; // 每批次查询数量

        empleadoList = new ArrayList<>();
        ResultSet result = null;
        PreparedStatement stm = null;
        PreparedStatement stmCount = null;
        Connection connect = null;

        try {
            connect = DriverManager.getConnection("jdbc:postgresql://localhost:5432/bd_test", user, pss);
            
            // 查询总记录数
            stmCount = connect.prepareStatement("SELECT COUNT(*) FROM empleados");
            ResultSet resultCount = stmCount.executeQuery();
            int records = 0;
            if (resultCount.next()) {
                records = resultCount.getInt(1);
            }
            System.out.println("总记录数: " + records);
            resultCount.close();

            // 预编译分页查询语句，只查询需要的字段
            String sql = "SELECT id, nombre, apellido, puesto FROM empleados LIMIT ? OFFSET ?";
            stm = connect.prepareStatement(sql);
            stm.setInt(1, BATCH_SIZE); // 设置每批次数量

            // 循环分批查询数据
            for (int offset = 0; offset < records; offset += BATCH_SIZE) {
                stm.setInt(2, offset); // 设置偏移量
                result = stm.executeQuery();

                // 处理当前批次的记录
                while (result.next()) {
                    Empleados empleado = new Empleados();
                    empleado.setId(result.getInt("id"));
                    empleado.setNombre(result.getString("nombre"));
                    empleado.setApellido(result.getString("apellido"));
                    empleado.setPuesto(result.getString("puesto"));
                    empleadoList.add(empleado);
                }
                result.close(); // 及时关闭当前批次的ResultSet
            }

            System.out.println("成功读取记录数: " + empleadoList.size());

        } catch (SQLException ex) {
            // 打印异常信息，方便调试
            ex.printStackTrace();
        } finally {
            // 按顺序关闭资源，先ResultSet，再PreparedStatement，最后Connection，同时判断是否为null
            try {
                if (result != null) result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stmCount != null) stmCount.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connect != null) connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

// 假设Empleados实体类定义如下
class Empleados {
    private int id;
    private String nombre;
    private String apellido;
    private String puesto;

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getNombre() { return nombre; }
    public void setNombre(String nombre) { this.nombre = nombre; }
    public String getApellido() { return apellido; }
    public void setApellido(String apellido) { this.apellido = apellido; }
    public String getPuesto() { return puesto; }
    public void setPuesto(String puesto) { this.puesto = puesto; }
}

关键修改说明 #

1. 分页逻辑修复：使用LIMIT ? OFFSET ?实现真正的分页查询，每次循环更新偏移量offset，确保每批次查询不同的记录段。
2. 指定字段查询：将SELECT *替换为SELECT id, nombre, apellido, puesto，减少数据传输量，提升查询效率。
3. 循环条件优化：将i <= records改为offset < records，避免最后一次无效查询。
4. 资源管理优化：
   • 预编译SQL语句，重复利用PreparedStatement，提升性能。
   • 及时关闭每批次的ResultSet，释放资源。
   • 资源关闭时增加null判断，避免空指针异常。
5. 异常处理增强：打印异常堆栈信息，便于排查执行过程中的错误。

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