Using PreparedStatements - update using multiple parameters (unknown number)

Question

i understand that the best practice is to have a fixed number of parameters and then define them using preparedStatement.setParam(xxx).

What if i have a list of student id and i would like to update a column for each student data row:

update student set student_grade=FAIL where student_id in (?) where ? will be the list of student ids in my list object. what's the best way to go about doing this?

Loop over your students, inserting one `?` into your update statement for each student. — GriffeyDog, Jan 29 '15 at 22:04
Use Apache commons' `StringUtils` class - `StringUtils.repeat("?", ",", ids.size());` — Dawood ibn Kareem, Jan 29 '15 at 23:28
Have you looked at [stackoverflow in alternatives](http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives?rq=1)? — Jeff Miller, Jan 30 '15 at 16:41

score 0 · Answer 1 · answered Jan 29 '15 at 22:49

/* I'd rather use individual updates and loop over them instead of using the IN operator, similar to GriffeyDog's suggestion. It would be recommended to probably split the bulk updates to 1000 or less.*/

/* assume al is the arraylist containing the list of student_ids and assuming student_ids data type is int */

String updateTableSQL = "update student set student_grade='FAIL' where student_id = (?) ";              
PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL);
for (int i=0;i<al.size();i++){
preparedStatement.setInt(1,al.get(i) );
preparedStatement.addBatch();

}
preparedStatement.executeBatch();

dbConnection.commit();

Why? The IN operator will be miles faster. – Dawood ibn Kareem Jan 29 '15 at 23:26 — Dawood ibn Kareem, Jan 29 '15 at 23:26

Using PreparedStatements - update using multiple parameters (unknown number)

1 Answers1