Can I rollback after calling stored procedure in java test?

Question

I have method in DAO layer which call stored procedure in Oracle database:

@Override
public void deleteNode(Integer nodeId) {
    SqlParameterSource in = new MapSqlParameterSource()
            .addValue("nodeId",nodeId)
            .addValue("user", "DUMMY");

    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(ebDataSource)
            .withSchemaName("SCHEMA")
            .withCatalogName("PK_GRAPH_DML")
            .withProcedureName("DeleteNode");

    simpleJdbcCall.execute(in);
}

And I have to test this method. But I want to rollback all changes after every execution of this test.

@Test
@Transactional
@Rollback
public void deleteEBNode_ok() {
    Integer nodeId = 5714;

    ebFlowService.deleteEBNode(nodeId.toString());

    EBFlowToolSelect nodeModelFromDatabase = ebFlowService.getNodeById(nodeId);
    Assert.assertNull(nodeModelFromDatabase.getNodeId());
}

I tried to annotate my test by @Transactional and @Rollback, but it didn't help.
Is it possible to make my test rollback all changes that were made by stored procedure?

That depends on the stored procedure. If that manages its own transaction there isn't much you can do, else it should participate in a transaction and `@Transactional` on the test method should be all you need. — M. Deinum, Sep 05 '17 at 09:39

score 2 · Accepted Answer · answered Sep 05 '17 at 10:18

That depends on the stored procedure.

If the stored procedure contains a COMMIT statement then you will only be able to rollback any actions taken after this statement.
If the stored procedure is an autonomous transaction then it will be independent of your transaction (and contain its own COMMIT statement) and you cannot roll it back.

If the stored procedure contains no COMMIT statement and is not autonomous then you should be able to roll it back.

Can I rollback after calling stored procedure in java test?

1 Answers1