Managing multiple queries optional queries with statement and resultsets

Question

Currently we have a java application which have many different queries and not all are ran at one particular time. Thus for each query we plan to have a new statement and resultset and close them immediately? Below is given the snippet of code for how we run a query now.We have tried to cover each query with try and catch but the problem if the query fails them the rollback is not working on the global level. How best top put them in place to ensure no memory leak too?

try{ //main try outside

//lots of inner queries run based on some logics of if else etc


//sample query of opening and closing both statement and resultsets.

Statement stmt1 = null;
stmt1 = dbconn.createStatement();
String selectQuery1  = "Select query";
ResultSet rs1 = stmt1 .executeQuery(selectQuery1);
while(rs1.next()) {
//process here

}
try{
  if (rs1 != null ){
     rs1.close();
  }
  if (stmt1!= null ){
    stmt1.close()
  }
}
catch(SQLException ex){
ex.printStackTrace(System.out);
}

dbconn.commit();
}
catch (SQLException ex) { 
 try {    
   dbconn.rollback();  
 } 
 catch (Exception rollback){    
  rollback.printStackTrace(System.out);
 }
}
catch (Exception e){
 try{    
    dbconn.rollback();  
 } 
 catch (Exception rollback) {    
   rollback.printStackTrace(System.out);
 }
}

Answer 1

For rollback to work, you have to first check whether autoCommit is set to false initially. You would want to commit only when all your operations have been executed successfully.

One way of doing this might to use a structure like this:

Connection connection = getDBConnection(); //Depends on how you get your connection
boolean autoCommit = connection.getAutoCommit();
try{
    //Set autoCommit to false. You will manage commiting your transaction
    connection.setAutoCommit(false); 
    //Perform your sql operation

    if(doCommit){ //all your ops have successfully executed, you can use a flag for this
        connection.commit();
    }
}catch(Exception exe){
    //Rollback
}finally{
    connection.setAutoCommit(autoCommit); //Set autoCommit to its initial value
}