Prepared setting values ​​in position IN

Question

The code must be database independent, so I use plain sql.

My query may change at runtime say table1, changed to table2. But let's say that for the query table1 I need to update 2000 records with an interval of 50, so as soon as 50 records are processed, I will commit.

I have two approaches for setting values ​​in the IN section of sql statement. I want to know which of the codes below is more efficient ( priority is optimization and maintainability is secondary)?

PreparedStatement preparedStatement = sqlObj.prepareStatement(
    "UPDATE table1 column1=? WHERE table_id IN (" + StringUtils.repeat("?,", paramArray.length-1)+ "?)");
preparedStatement.setInt(1, 0);
for(int idx = 0; idx < paramArray.length; idx++) {
    preparedStatement.setInt(idx+2, paramArray[idx]);
}
preparedStatement.executeQuery();

or

PreparedStatement preparedStatement = sqlObj.prepareStatement(
    "UPDATE table1 column1=? WHERE table_id IN (?)");
for(int idx = 0; idx < paramArray.length; idx++) {
    preparedStatement.setInt(1, 0);
    preparedStatement.setInt(2, paramArray[idx]);

    preparedStatement.addBatch();
}
preparedStatement.executeBatch();

Edit: Let's say param.length is 50all this code exectutes 40 timesi.e. We process 2000 records.

So, in the first case, he will add 50? and then set a variable for them by creating one update request, and in the second case, create a package of 50 update requests.

Answer 1

, IN, WHERE table_id =? 1 . , Batch.

, / ? ?

Oracle, -

create or replace TYPE "NUMBER_ARRAY" IS TABLE OF NUMBER(18,0);   

Java

PreparedStatement preparedStatement = sqlObj.prepareStatement(
"UPDATE table1 column1=? WHERE table_id IN (SELECT column_value FROM TABLE (CAST(? AS number_array)))");    
JdbcUtils.setArray(2, tableIds.toArray(new Long[tableIds.size()]), ps);    

Answer 2

, . . : . EDITED: , . , , , .

Answer 3

, , , . , .

, , , . ?

If this is not a performance bottleneck, and you want to optimize just in case: no. It is much more important to have supported code than to optimize non-critical code. Use the option that has the least difficulty and the easiest to understand: option 2.