PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to perform a select by sections since it contains many records using Java?

I have a table which contains approximately 100,000 records, I did a SELECT to obtain the total number of records because it can vary, I need to consult the table and obtain all the records of 4 specific fields(nombre, apellido, puesto) but since it contains a lot of information it takes too long,

I am thinking of doing it in parts, that is making a SELECT that brings me only 10 000 records and with a FOR iterates until reaching the maximum number of records that the table has and at the same time these records are stored in a list since after obtaining these records I need to insert them into another table.

this is my code but it doesn’t do anything.

I’m not sure if the for is done correctly, what I want to do is for the select to be performed until the maximum number of records is reached, and for the data to be saved in the Employees type list

Could someone correct me or tell me how the FOR should look like to obtain the 100,000 records and have them stored in the list?

public class TestConnect {

    List<Empleados> empleadoList;

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


    }

    //metodo para traer los datos de la tabla empleados
    public void read() {
        String user = "postgres";
        String pss = "admin123";

        empleadoList = new ArrayList<>();
        Empleados empleado = null;
        ResultSet result = null;
        PreparedStatement stm = null;
        Connection connect = null;
        try {
            connect = DriverManager.getConnection("jdbc:postgresql://localhost:5432/bd_test", user, pss);
            PreparedStatement stmCount = connect.prepareStatement("SELECT COUNT(*) from empleados");
            ResultSet resultCount = stmCount.executeQuery();

            int records = 0;
            while (resultCount.next()) {
                records = resultCount.getInt(1);
            }
            System.out.println(" RECORDS    " + records);

            for (int i = 0; i <= records; i += 10000) {
                stm = connect.prepareStatement("SELECT * FROM empleados limit 10000");
                result = stm.executeQuery();
                while (result.next()) {

                    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);
                }
            }

        } catch (SQLException ex) {
        } finally {
            try {
                result.close();
                stm.close();
                connect.close();

            } catch (SQLException ex) {
            }
        }
    }


}
Tags:

2

Answers


  1. 0

    Create a Postgres View

    `CREATE MATERIALIZED VIEW test_view as SELECT a, b, c, d from test1`

    it will return only those 4 columns.
    you don’t need for statement in that case.
    call the view using

    `connect.prepareStatement("SELECT * from test_view")`
    Login or Signup to reply.
  2. 0
    1. select only what you need, so specify the columns you want.
    2. Set a fetch-size to retrieve bigger blocks of rows at once (reduces the round trips to the database)
    3. Use a proper connection pool as creating DB connections is a heavy operation.
    public void read() {
    String user = "postgres";
    String pss = "admin123";

    empleadoList = new ArrayList<>();        
    try (   var connect = DriverManager.getConnection("jdbc:postgresql://localhost:5432/bd_test", user, pss);
            var stmt = connect.prepareStatement("SELECT id, nombre, apellido, puesto FROM empleados");) {
            stmt.setFetchSize(1000)
            var result = stmt.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);
            }
        }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

    Something like this. To use a proper connection pool you would need to create a DataSource with something like Hikari Connectionpool and reuse that in places you need DB access.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search