Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Reusing PreparedStatement

843854Apr 29 2004 — edited Apr 29 2004
Could someone comment on my code please. I'm reusing the PreparedStatement (delete) below for different queries before closing the PreparedStatement (delete is closed in this.close()). The API for Statement.close() states:

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.

...but what resources would be wasted if the PreparedStatement is reused AND is this opening the app up to innefficency/overhead?

Your reply is appreciated. Code below....

public boolean cascadeDeleteRfxForm() throws SQLException, Exception{
boolean deleteFlag=true;
try{
//create connection
this.createConnection();
//set autocommit to fals - allows batch commit/rollback
con.setAutoCommit(false);

//create prepare statement from string
delete=con.prepareStatement(deleteRfxItemAttr);
delete.setString(1, orgId);
delete.setString(2, rfxName);
delete.setString(3, rfxUniqueId);
delete.setString(4, revNbr);
//execute prepared statement
delete.execute();

//create prepared statment from string
delete=con.prepareStatement(deleteRfxAttr);
delete.setString(1, orgId);
delete.setString(2, rfxName);
delete.setString(3, rfxUniqueId);
delete.setString(4, revNbr);
//execute prepared statement
delete.execute();

//create prepared statment from string
delete=con.prepareStatement(deleteRfxItem);
delete.setString(1, orgId);
delete.setString(2, rfxName);
delete.setString(3, rfxUniqueId);
delete.setString(4, revNbr);
//execute prepared statement
delete.execute();

//delete parent - create prepared statment from string
delete=con.prepareStatement(deleteUniqueRfxHeader);
delete.setString(1, orgId);
delete.setString(2, rfxName);
delete.setString(3, rfxUniqueId);
delete.setString(4, revNbr);
delete.execute();
}
catch(Exception e){
deleteFlag=false;
con.rollback();
throw e;
}
finally {
try {
if(deleteFlag==true){
con.commit();
System.out.println("Comited");
}
con.setAutoCommit(true);
this.close();
}
catch (Exception ex) {}
}
return deleteFlag;
}
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 27 2004
Added on Apr 29 2004
2 comments
719 views