JDBC PreparedStatement queries… here is a solution to the problem.
JDBC PreparedStatement queries
I am developing JDBC using Oracle Database.
I have the following method:
// Method1
public void method1(){
Connection connection=ConnectionFactory.getRemoteConnection();
selectSQL = "select * tablename where num>=? and num<=?";
PreparedStatement userStatement=connection.prepareStatement(selectSQL);
userStatement.setFetchSize(500);
int i=0;
int startRow=0;
int endRow=50;
do{
Reusing the statement
fetchRecords(userStatement,startRow,endRow);
startRow=startRow+50;
endRow=endRow+50;
i++;
if(i==50) endOfRows=true;
}while(!endOfRows);
userStatement.close();
}
Method2
public List<String> fetchRecords(PreparedStatement userStatement,int startRow,int endRow){
userStatement.setInt(1, startRow);
userStatement.setInt(2, endRow);
resultSet = userStatement.executeQuery();
/*Some logic*/
...
}
As you can see, I’m trying to reuse prepared statements.
Now, my question is is to create a prepared statement every time the parameter is changed?
I only close the statement after all processing in method 1 is complete.
I’m concerned that if a new statement is created every time the parameters are changed (since I didn’t close all the arguments), it might end up with an unclosed statement.
Should I be worried?
Thank you,
Belt
Solution
java.sql.PreparedStatement is designed to be reusable.
When you set a new parameter, you
overwrite the previous parameter, but you do not create a new statement.
You can also use clearParameters() Clear all parameters at your discretion