Before going into details let us observe the below code

try {
conn = getDBConnection();
conn.setAutoCommit(false);
PreparedStatement ps =
conn.prepareStatement(Some Update Sql 1);
ps.executeUpdate(Some Update Sql 1);
conn.commit();
ps.executeUpdate(Some Update Sql 2);
} finally {
try {
if (conn != null) conn.close();          
} catch (SQLException ex) {
Debug.logError(ex, "Some Error Message");       
}
}

What will happen to the second sql updation …? Will it get committed or rolled back? There are two flavours of answer to this…

1. When connections are not pooled
   In this case, Connection.close() method commits all the uncommitted statements (As per 9i Oracle JDBC Driver).
2. When connections are pooled
   In this case, Connection.close() really does not close the connection but releases it to the connection pool for reuse by somebody else. In this case, situation is bit undeterministic. The outcome purely depends on how the Pool manager manages. We found in Weblogic pool management, Connection.close() results in all open cursor closure and resetting of AutoCommit flag. But the uncommitted transactions are neither committed nor rolledback. All the exlusive locks obtained for the previous updation will remain alive. This behavior continues until the same connection from the pool is assigned for somebody else and he either commits or rolled back.

Now, Consider the below piece of code,

 PreparedSQLRunner ps = null;
try {
conn = getDBConnection();
conn.setAutoCommit(false);
PreparedSQLRunner.executeUpdate(Some Update Sql 1);
conn.setAutoCommit(true);
   ps = new PreparedSQLRunner();
ResultSet rs = ps.executeQuery(Some Select Sql);
   ....
} finally {
try {
if (ps != null) ps.close();            
} catch (SQLException ex) {
Debug.logError(ex, "  ");         
}
try {
if (conn != null) conn.close();            
} catch (SQLException ex) {
Debug.logError(ex, "  ");         
}
}

In the above context, what would happen to the uncommitted update sql ?

As per JDBC API specification, when we change the Auto Commit flag of a Connection object from false to true, the next immediate statement on that connection, will commits all its previous uncommitted operations (that were happened with Auto Commit Flag set false). So, the call to executeQuery method, results in the committing of the previous uncommitted updation in addition to its expected service.