java - 使用语句和结果集管理多个查询可选查询

Question

目前我们有一个 Java 应用程序，它有许多不同的查询，并且并非所有查询都在一个特定时间运行。因此，对于每个查询，我们计划有一个新的语句和结果集并立即关闭它们？下面给出了我们现在如何运行查询的代码片段。我们尝试使用 try 和 catch 覆盖每个查询，但问题是，如果查询失败，则回滚将无法在全局级别上运行。如何最好地将它们放置到位以确保不存在内存泄漏？

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

要使回滚生​​效，您必须首先检查 autoCommit 最初是否设置为 false。仅当所有操作均已成功执行时，您才需要提交。

实现此目的的一种方法可能是使用如下结构:

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
}