Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to rollback an insert/update/delete in a transaction fails while executing multiple queries

 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all, my name is Chaitanya, I am having a code which actually inserts records in to two tables in a single transaction.

Say for example, new employee details will be added to employee table and his work location will be added to work_location table. work_location table refers employee table.

Suppose my java code, after inserting employee details has failed due to some exception or hardware or software failure. Here the second insert statement is not executed. So his work location details are not inserted. So now I want to rollback the first insert.

I heard about the service layer design pattern. It is used for this purpose only. I tried it with simple jdbc and I could not rollback the first insert after generating an exception myself after first insert.

Can anyone tell me how to do this?

Thank you all in advance, good day.
 
Srinivasan Duraisamy
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ,

Please do the following steps in the plain JDBC program to achieve transaction management .





This is just an basic example , if you need more details , please check the savepoints in Connection.

Thanks
Srinivasan. D
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you srinivasan. I have one doubt, why should I set autocommit to true at the end of the program?
 
Rob Spoor
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's often more polite to leave variables in the state you've found them. By default connections have auto-commit set to true. It's not necessary though. And if you would choose to restore the auto-commit value, it's better to first query it - call getAutoCommit(), store its return value in a variable, and call setAutoCommit with that variable as its argument. That way you won't turn on auto-commit when it was turned off first.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic