Achieving transaction by calling multiple procedures using Spring jdbc template

Question

I have a scanario where i have to make multiple stored procedure calls. If any one of the stored procedures fail, i have to roll back all the procedures.

May i please know how to achieve this using spring jdbc template. What i know is that i can call only one stored procedure using the spring jdbc template.

Is there any way to invoke a group of procedures in sequence using spring jdbc template?

One way of solving this problem is to create another new stored procedure and call all the procedures in this.

Is there any other efficient way to achieve this?

R.G R.G · Accepted Answer · 2020-03-20T01:56:49

Following code will call multiple stored procedures within the same transaction.

@Transactional(rollbackFor=Exception.class)
public void callStoredProcedures(){
   // Stored procedure 1
   //....
   // Stored procedure n
}

A transaction would be initialized at the method start. All the subsequent database calls within that method will take part in this transaction and any exception within the method context would rollback the transaction.

Note that the transaction rollback for this method is configured for any Exception. By default a transaction is marked for rollback on exceptions of type RuntimeException and JdbcTemplate methods throw DataAccessException which is its subclass. If no rollback is required for checked exceptions the (rollbackFor=Exception.class) can be removed.

Also , for @Transactional annotation to work , enable transaction management. Please go through @EnableTransactionManagement

Achieving transaction by calling multiple procedures using Spring jdbc template

1 Answers