javaoraclejdbcsavepoints

Savepoint on JDBC

I have a JDBC code where there are multiple Savepoints present; something like this:

1st insert statement
2nd insert statement
savepoint = conn.setSavepoint("S1");
1st insert statement
2nd update statement
savepoint = conn.setSavepoint("S2");
1st delete statement
2nd delete statement
savepoint = conn.setSavepoint("S3");
1st insert statement
2nd delete statement
savepoint = conn.setSavepoint("S4");

Now in the catch block, I am catching the exception and checking whether the Savepoint is null or not; if yes then rollback the entire connection else rollback till a Savepoint. But I am not able to understand till which Savepoint shall I roll back.

Will it be fine if I change all the savepoint names to "S1" ? In that case how will I understand how many till Savepoint did work correctly?

Please advise how to understand until what Savepoint the work was performed correctly?

Solution

  • Would view this as multiple transactions. Hence you could handle this with multiple try/ catch blocks. You also seem to be overwriting the savepoint objects hence it would be not feasible to rollback.

    More info. JDBC also supports to set save points and then rollback to the specified save point. The following method could be used to define save points.

    SavePoint savePoint1 = connection.setSavePoint();

    Rollback a transaction to an already defined save point using rollback call with an argument.

    connection.rollback(savePoint1);

    Reference. https://www.stackstalk.com/2014/08/jdbc-handling-transactions.html