it's been a while. I'm currently struggling on optimizing the performance of my application. Here's a snippet similar to what I currently have.
A Stored procedure with this query:
SELECT field_a, field_b, field_c
FROM table_a
WHERE field_a = param_a
AND field_b = param_b;
In Java, I have this ArrayList of SampleParameterClass with attributes of paramA and paramB declared as String.
ArrayList<SampleParameterClass> sampleParams = new ArrayList<SampleParameterClass>();
sampleParams.add(new SampleParameterClass(stringValueForParamA, stringValueForParamB);
Let's assume that sampleParams is populated by hundreds of elements.
try
{
conn = ConnectionPool.getConnection();
con.setAutoCommit(false);
CallableStatement myProcedure = connection.prepareCall("{ ? = call my_proc_name(?, ?) }");
for(int paramCounter = 0; paramCounter < sampleParams.size(); paramCounter ++){
myProcedure.registerOutParameter(1, Types.OTHER);
myProcedure.setObject(2, sampleParams.get(paramCounter).getParamA);
myProcedure.setObject(3, sampleParams.get(paramCounter).getParamB);
myProcedure.execute();
ResultSet rs = (ResultSet) myProcedure.getObject(1);
while (rs.next()) {
String resultA = rs.getString("param_a");
String resultB = rs.getString("param_b");
String resultC = rs.getString("param_c");
myArrayListOfResult.add(new Result(resultA, resultB, resultC);
}
}
}
catch (SQLException e) {
myProcedure.close();
conn.close();
}
}
Basically, what the code above does is to loop throughout all the parameters in the ArrayList and call the stored procedure for every parameter. If I have a thousands of data to be processed, it would be slow as a snail isn't it?
My question would be:
1) Is it possible to call the procedure just once even though I have thousands of parameters in queue? Something similar to passing the whole ArrayList of parameters.
2) If above question cannot be answered, are there any method to minimize the process?
Note:
-Same question for a procedure with an INSERT statement.
-I haven't found a similar question to this, if I missed something. Please do post a URL to that question and I'll mark it as an answer to this question.
-I haven't found a reading material something like passing a list in a procedure.
Thanks, any answer is much appreciated.
PreparedStatement, and probably make your life a bit easier.PreparedStatementdoesn't fix that problem. But user2310289's answer gives a solution which works for bothCallableStatementandPreparedStatement.