How to loop SQL statements?

Question

I am attempting to run a new SQL command for each value in my List. I have the below, whic just repeats the same value again and again for the amount in my list.

Please note that I have to loop the SQL through the amount of values in my array list, and insert each value as the name in the SQL as shown below.

What is the best way to do this as this definately isn't.

    int listSize = al.size();

    for(int i = 0; i < listSize; i++) {         
        ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+al.listIterator().next()+"'");
        al1.add(rs1.getString(1));
        rs1.close();

    }
    System.out.println(al1);

Result:

[70, 70, 70, 70, 70, 70, 70, 70, 70, 70]

Expected Result:

[70, 80, 110, 60, 35, 10, 15, 10, 0, 25]

Answer 1

Problem is in

al.listIterator().next()

which returns the same value all the time. Hence the query returns the same result set string, that is why your result is 70 all the time.

try this code instead:

int listSize = al.size();

for(int i = 0; i < listSize; i++) {         
    ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+al.get(i)+"'");
    while(rs1.next()){
        al1.add(rs1.getString(1));
    }
    rs1.close();

}
System.out.println(al1);