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
– dkb, Commented Feb 7, 2019 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: stackoverflow.com/a/867206/2987755 — dkb
– dkb, Commented Feb 7, 2019 at 11:24

Joop Eggen · Accepted Answer · 2019-02-07 11:40:06Z

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 7, 2019 at 11:40

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rudy Over a year ago

same error, just one time inside 'while (rs2.next())'

Joop Eggen Over a year ago

One time in the while is okay (could have been if), as id is the primary key.

Rudy Over a year ago

but is not the result that i'm expecting, the list should have 2 entries but it has just 1

Joop Eggen Over a year ago

And the second id is in the database?

Joop Eggen Over a year ago

(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.

|

Collectives™ on Stack Overflow

How to loop sql statement in java

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related