Transaction in java

Get Started
1

Dear All,
I have an application where I got few different sql queries that I would like to have an ACID properties. I have now setAutoCommit false. Where best to put commit and rollback statement?

try
{
dbconn = DriverManager.getConnection(“jdbc:mysql://192.168.1.45:3306/?"+"user=*&password=*****”);
dbconn.setAutoCommit(false);
stmt = dbconn.createStatement();

    String selectQuery2 = "Select * from tripData Where deviceID="+ intDeviceID +" and dateTimer>'"+dateTimer+"' Order By dateTimer Desc Limit 1"; 
    ResultSet rs2 = stmt.executeQuery(selectQuery2);
 
   //update query
            count = stmt.executeUpdate(updateQuery);
  
   //insert query  	                   
    count = stmt.executeUpdate(insertQuery);

}
catch (SQLException ex)
{
System.out.println("MyError:Error SQL Exception : "+ex.toString());
ex.printStackTrace(System.out);
}
finally
{

[INDENT]try
{
if ( stmt != null )
{
stmt.close();
}
else
{
System.out.println(“MyError:stmt is null in finally close”);
}
}
catch(SQLException ex){
System.out.println(“MyError:SQLException has been caught for stmt close”);
ex.printStackTrace(System.out);
}
try
{
if ( dbconn != null )
{
dbconn.close();
}
else
{
System.out.println(“MyError:dbconn is null in finally close”);
}
}
catch(SQLException ex){
System.out.println(“MyError:SQLException has been caught for dbconn close”);
ex.printStackTrace(System.out);
}[/INDENT]
}

2

it depends on your application. what makes sense from a business perspective?
Could your update and insert happen in different transactions or different times, and your data still be considered correct?

3

Dear Jurn,
I would like to keep them all together if one fail all fail. So in that case where to put the commit and rollback? I feel I need a global value to keep track if one fail then rollback ? How to achieve that via java?

4

In that case you can put your ‘commit’ after the count statement. Everything in the try block would be your success path.
The ‘rollback’ can go into the catch block.

I don’t see any no need for a variable, let alone a global var…

5

Dear Jurn,
Thank you and will follow that accordingly.