JDBC PreparedStatement Query

Question

I'm working on JDBC with Oracle database. I have the below methods:

// 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 a prepared statement. Now, my question is while a prepared statement be created everytime I change parameters?

I'm closing the statement only after the end of all the processing in method1. I'm worried if a new statement is created everytime I change the parameters (since I'm not closing all of them), it may end up in un-closed statement. Should I be worried?

Thank you,
Sash

does this code even compile `fetchRecords(userStatement,); startRow=startRow+50; endRow=endRow+50;` ? — Scary Wombat, Mar 28 '17 at 07:57
@ScaryWombat Also using `userStatement` inside `fetchRecords` when the parameter name is just `statement` — BackSlash, Mar 28 '17 at 07:59
You have one `PreparedStatement` that you are using multiple times with different parameters. There is no problem with that, and it won't generate additional statements. Also look at this topic that you may find useful : http://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times — Arnaud, Mar 28 '17 at 07:59

score 3 · Accepted Answer · answered Mar 28 '17 at 08:02

3

java.sql.PreparedStatement is designed to be reusable.

When you set new parameters, you will overwrite the previous ones but you will not create a new Statement.

You can also decide to clear all the parameters yourself using clearParameters()

answered Mar 28 '17 at 08:02

Edd

1,350
11
14

JDBC PreparedStatement Query

1 Answers1