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

Answer 2

if 1 and 2 succeeds and 3 fails, will the transaction on 1 and 2 rolls back or not?

If the third operation fails, the first and second operations will be rollback. But if they are part of the same transaction or transaction context.

For example, if you define your transaction manager as DataSourceTransactionManager it will rollback the JDBC operations of the same connection. In your case, you have all three operations inside the same method and are using the same jdbcTemplate, so it will rolback your other two transactions.

You can test this if make a sql instruction to fail, for example:

1. INSERT correct
2. INSERT correct
3. INSERT but you can try to insert a String in a Number column, or a null on a not null column.

You can see more here http://www.journaldev.com/2603/spring-transaction-management-jdbc-example

http://docs.spring.io/autorepo/docs/spring/4.2.x/spring-framework-reference/html/transaction.html

About returning boolean I think it's better to throw an exception, and it will rollback the transaction automatically.