I am testing spring based Annotation transaction mechanism for. Annontation based transaction is not getting rollback when i use my db connection file. And it works fine when I extend my DB connection class from org.springframework.jdbc.core.support.JdbcDaoSupport. I am sure I am missing something in my db connection class. Can any one point me in right direction.
I am using spring-framework-3.1.1.RELEASE jars, sqlitejdbc-v056.jar, jdk 1.6.0_31
The list of files i am using are mentioned below.
Ill be honest I did not look that closely at all the code you had posted. But I did notice that you are throwing SqlException. If you look at the API for @Trasactional the default behavior is to only roll back on runtime exception which SqlException is not. Typically when using spring you wrap your sql exceptions with the spring DataAccessException( this is a Runtime Exception) this is done automatically for you when you annotate your repositories with @Repository. I would consider using JdbcTemplate you can get rid of a lot of boiler plate code for managing the connection and closing statements, connectons etc. Otherwise if you want to continue on with what you have you will need to configure the rollback strategy on the @Transactional annotation to rollback on SqlException.
Thanks Bill for reply.
I am throwing SQLExceptions only just to avoid using try catch for now. I do acknowledge your point that using jdbctemplate would give a cleaner representation. Infact when I used the jdbcdaosupport class (which is internally using jdbctemplate) , same code works fine i.e. transactions are getting rolled back in case of runtime exceptions.
I have gone through couple of books on spring, they mentioned I can use a simple java class for my db operation and transaction management will be done by spring by defining the transaction manager bean.
With the kind of error that I am getting, it seems spring transaction management works only if database classes are extended from spring APIs. This is hard to believe fact that spring txn works with spring database APIs only.
Also the reason I put it lot of code here so that if some one wants to test it locally, he can use all this code.
Still looking for answer i.e. why my code is not doing the rollback when not extended from jdbcdaosupport classs.
You would not need the try catch if you used JDBC template and springs DataAccessException since they are Runtime exceptions they do not need to be caught and your @transactional would roll back correctly.
But back to your last statement:
Still looking for answer i.e. why my code is not doing the rollback when not extended from jdbcdaosupport classs
I had said in my previous post :
Otherwise if you want to continue on with what you have you will need to configure the rollback strategy on the @Transactional annotation to rollback on SqlException.
In other words make your @Transactional look like this
Joined: Jul 19, 2005
I am throwing the Runtime Exception randomly from Grinch.Ruin method and not the SQLEXception. I tried changes you have mentioned i.e but again rollback is still not working. I have infact debugged spring classes like TransactionInterceptor , TransactionAspectSupport, DefaultTransactionStatus. it seems to me rollback behaviour is exactly same in both the cases i.e. with and without JdbcDaoSupport class.
I am enabling the logging for Spring Classes to compare both the scenarios's log. I'll let you know if something more come out of it.
Joined: Jul 19, 2005
Finally found the answer why rollback is not working. I missed setting autocommit to false when creating the connection. Rollback is working fine all along. its just that there was nothing to rollback since autocommit is set to true. I know right from the start there is something wrong in DBConnection.java but didn't know what it is. Now, I knew it.
To set the record straight, I agree we should use preexisting Spring APIs for making DB queries to avoid this kind of scenarios.
Method getConnection in DBConnection.java should now look like this :