How to loop sql statement in java

Question

I'm trying to create a for cycle with a dynamic sql statement:

for(int i = 0; i < size; i++) {         
            ps2 = db.prepareStatement("select * from student where id=?");
            ps2.setInt(1,studList[i]);
            rs2=ps2.executeQuery();
            while(rs2.next()){
                list1.add(new Student(rs2.getInt("id"), rs2.getString("name"), rs2.getString("city"));
            }
            rs2.close();
            ps2.close();
        }

studList[i] is an array with all the student ID and the variable "size" is the length of this array. The problem with this code is that only one element is added to the list(corresponding to the first student ID). I noticed that the code goes inside 'while(rs2.next())' just if the value of studList[i] doesn't change. Why?

put `rs2.close(); ps2.close();` outside of for-loop and check. — dkb, Feb 07 '19 at 11:11
change `while(rs2.next()){` with `while(rs2.hasNext()){`, and at the end of while loop `rs2 = rs2.next();`, this is what I Mean: https://stackoverflow.com/a/867206/2987755 — dkb, Feb 07 '19 at 11:24

score 3 · Accepted Answer · answered Feb 07 '19 at 11:40

3

Seemingly the logic is fine.

First the code would be better with

local declarations at the first usage, otherwise ps2, rs2, ps3, rs3 and so one risk undetected typos;
try-with-resources that close even on exception/return.

So:

String sql = "select name, city from student where id=?";
try (PreparedStatement ps2 = db.prepareStatement(sql)) {
    for (int i = 0; i < size; i++) {  
        int id = studList[i];
        ps2.setInt(1, id);
        try (ResultSet rs2 = ps2.executeQuery()) {
            System.out.printf("[%d] %d%n", i, id);
            while (rs2.next()) {
                System.out.printf("- %s%n", rs2.getString("name"));
                list1.add(new Student(id, rs2.getString("name"), rs2.getString("city"));
            }
        }
    }
}

So the error might happen before: the array filled false, the database student table corrupted.

One beginner error is to reuse an object (Student), with new field values. Adding the same object to a list then would have the last student's data replicated several times.

Here it could be that in the original code code was called reusing ps2 or such.

answered Feb 07 '19 at 11:40

Joop Eggen

107,315
7
83
138

same error, just one time inside 'while (rs2.next())' – Rudy Feb 07 '19 at 11:47
One time in the `while` is okay (could have been `if`), as `id` is the primary key. – Joop Eggen Feb 07 '19 at 11:50
but is not the result that i'm expecting, the list should have 2 entries but it has just 1 – Rudy Feb 07 '19 at 11:54
And the second id is in the database? – Joop Eggen Feb 07 '19 at 11:55
(And `new Student` does not do beginner's error like `static` fields.) The printfs indeed show [0]... / - ... / [1] ... / nothing. Then it _must_ be the database or the code shown is not the code used. You principly had correct code. – Joop Eggen Feb 07 '19 at 12:00
**The second id should not be present in the database table.** – Joop Eggen Feb 07 '19 at 12:01
there was an error before in the code, now it works. Thank you!! – Rudy Feb 07 '19 at 12:12

How to loop sql statement in java

1 Answers1