wrapping multiple calls in a transaction in spring boot application?

Question

I am using jdbctemplate to execute queries against db in my spring application.

Here is the method that is annotated with @Transactional

@Transactional
public boolean doSomething(){    
    try {
        jdbcTemplate.update(sql1); //1
        jdbcTemplate.update(sql2); //2
        jdbcTemplate.update(sql3); //3
        return true; 
    } catch (Exception ex){
        return false;
    }
}

My question is if 1 and 2 succeeds and 3 fails, will the transaction on 1 and 2 rolls back or not? How can I test this?

Additionally is having a boolean as return value a good practice to indicate the state of transaction?

Answer 1

NO! If you catch the exception your sql will not rollback!!!!

This will not trigger a rollback. You need to remove the try-catch. But yes these will rollback as expected if a runtime exception is thrown. Please reference the documentation below.

@Transactional settings

http://docs.spring.io/autorepo/docs/spring/4.2.x/spring-framework-reference/html/transaction.html#transaction-declarative-attransactional-settings

You'll see a few lines down that rollbacks are triggered by any RuntimeException. So if you catch the exception it will not trigger a rollback it will simply end the transaction when you return false. Notice further that it will not rollback if you throw a checked exception so don't try to fix this by throwing an exception to do whatever you were going to do with that boolean return value.

I haven't tried this myself but it appears you can rollback for checked exceptions if you set the rollbackFor property on @Transactional or maybe just catch RuntimeException on the outside of this method? Or throw your own Runtime Exception? I leave that to you.

As asked above this also works for repositories you can see an example here

Spring Data(Repositories) transactions

http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#transactions