Java Mysql Transaction

[newbie14]

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
{
[INDENT]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);[/INDENT]
}
catch (SQLException ex) 
{ 
[INDENT]System.out.println("MyError:Error SQL Exception : "+ex.toString());
ex.printStackTrace(System.out);[/INDENT]
} 
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]}

[Majestics]

After a preview section................ ;-)

[mKorbel]

@newbie14 close Statement too, same as you close Connection,

[newbie14]

Dear mKorbel,
Just put both commit and rollback in the final section is it? But how will it know either to commit or rollback?

[mKorbel]

hmmm code from tutorial http://www.java2s.com/Tutorial/Java/0340__Database/RollBackaTransaction.htm and http://www.java2s.com/Code/Java/Database-SQL-JDBC/CommitorrollbacktransactioninJDBC.htm, maybe have to try that with these code ...

[JamesCherrill]

If this is the app we have been discussing elsewhere then you have quite a high transaction volume - in which case maybe you should be using/reusing a PreparedStatement rather than creating and parsing a new Statement for each transaction.
http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

[newbie14]

Dear James,
Yes prepared statement is my next step after doing the transaction. The problem now I am stuck with transaction where to put the commit and rollback? Is it possible for me to to have try and catch for each statemment if any one fail then rollback is that ok?

[newbie14]

Dear mKorbel,
I have visited this site http://www.java2s.com/Code/Java/Database-SQL-JDBC/CommitorrollbacktransactioninJDBC.htm. My question will be it only rollback when the connection is null what if the insert statement got some problem how will it rollback?

hmmm code from tutorial http://www.java2s.com/Tutorial/Java/0340__Database/RollBackaTransaction.htm and http://www.java2s.com/Code/Java/Database-SQL-JDBC/CommitorrollbacktransactioninJDBC.htm, maybe have to try that with these code ...

[JamesCherrill]

Dear James,
Yes prepared statement is my next step after doing the transaction. The problem now I am stuck with transaction where to put the commit and rollback? Is it possible for me to to have try and catch for each statement if any one fail then rollback is that ok?

There's no one answer that's right for all programs, but why not start with the simplest version and see how that works for you? That would be a single long try statement with all the SQL statements executed in it. If it gets to the end then you have completed all the statements without an Exception, so the last thing in the try can the the commit. If any statement fails it will automatically skip all the rest (and the commit) and jump straight to the catch, where you can rollback.

try {
  do 1st statement
  do 2nd 
  do 3rd
  commit
} catch(...) {
  diagnose exception
  rollback
}

[mKorbel]

@JamesCherrill you forgot to start SqlTransaction for that ??? :-)

[JamesCherrill]

Hi mKorbel. Am I missing something here? I thought setAutoCommit(false) was enough?

[mKorbel]

:-) hmmm if I write SttoredProcedure or Trigger for multipleyed Sql Queries, then there are more than ... :-), but your post was correct for this case

[newbie14]

Dear James,
You idea looks good. So if in this case will I now via the catch which statement was prolematic in case there is any problem?

[JamesCherrill]

You can probably get the info you need from the Exception itself, but if not you could use a simple variable that you increment for each step of the process, eg

int step = 0; // which step of the process are we doing?
try {
  step = 1;
  do 1st statement
  step = 2;
  do 2nd 
  step = 3;
  do 3rd
  step = 4;
  commit
} catch(...) {
  System.out.println("Execption thrown in step " + step);
  diagnose exception
  rollback
}

[newbie14]

Dear James,
Thank you. I will implement accordingly.