How to handle manually transactions while using jdbcTemplate?

Question

I have web-app which have functionality 'interpreter SQL'. User can write some commands and execute it.

For example, user want to execute 3x operations:

1.  ALTER TABLE "aaab".WHATEVER RENAME TO "Something";
2.  ALTER TABLE "aaab".RESOURCES RENAME TO "SomethingElse";
3.  ALTER TABLE "aaab".EXAMCATEGORIES MODIFY NAME NUMBER;

---

What i want to achieve:

1. If operation 1, 2, 3 was successful then commit all 3x operations
2. If operation 1, 2 was successful but 3 was not, then rollback all 3x operations

Generally if any operation from list was not successful then i want to rollback all operations.

---

So here is my interpreter:

public ArrayList<String> executeSQL(String[] queryRows) {
    ArrayList<String> listException = new ArrayList<String>();
    for (int i = 0; i < queryRows.length; ++i) {
        String query = queryRows[i];
        if(query.trim().length() > 5 && query.trim().substring(0, 6).toUpperCase().equals("SELECT")){
            try{
                mapList = jdbcTemplate.queryForList(query);
                int rows = mapList.size();
                listException.add("Success! { affected rows --> [" + rows + "] }");
                updateFlag = true;
            }catch (DataAccessException exceptionSelect){
                listException.add(exceptionSelect.getCause().getMessage());
                updateFlag = false;
                break;
            }
        }
        else if(whatKindOfStatementIsThat(query,"DDL")){
            try{
                jdbcTemplate.execute(query);
                listException.add("Success!");
                updateFlag = true;
            }catch (DataAccessException exceptionDDL){
                listException.add(exceptionDDL.getCause().getMessage());
                updateFlag = false;
                break;
            }
        }
        else if (whatKindOfStatementIsThat(query,"DML")){
            try {
                int rows = jdbcTemplate.update(query);
                listException.add("Success! { zaafektowane wiersze --> [" + rows + "] }");
                updateFlag = true;
            }catch (DataAccessException exceptionDML){
                listException.add(exceptionDML.getCause().getMessage());
                updateFlag = false;
                break;
            }
        }
        else{
            try{
                jdbcTemplate.execute(query);
                listException.add("Success!");
                updateFlag = true;
            }catch (Exception exception){
                listException.add(exception.getCause().getMessage());
                updateFlag = false;
                break;
            }
        }
    }
    return listException;
}

It is really simple, firstly i check what kind of statement was inputted.
1. If statement is a select then i need list of result mapList = jdbcTemplate.queryForList(query);
2. If statement is a DDL then i don't need anything jdbcTemplate.execute(query);
3. If statement is a DML then i need number of rows affected int rows = jdbcTemplate.update(query);
4. Everything else, just execute native query jdbcTemplate.execute(query);

I keep my statements inside ArrayList and they are executed one after another in loop.
So for example if i have that loop:

public void executeSQL (String[] queryRows){ 
     for(int i = 0; i < queryRows.length; ++i){
          // statements are executed there one after another.
     }
}

---

How can i achieve something like that?

public void executeSQL(String[] queryRows){ 
     ...begin()
     for(int i = 0; i < queryRows.length; ++i){
          // statements are executed there one after another.
     }
     if(myCondition)
          ...commit()
     else
          ...rollback()
}

---

Answer 1

Annotate your method with @Transactional, and it will commit only if all operations succeeded if you call it from outside your class

Describes a transaction attribute on an individual method or on a class

Read more in Understanding the Spring Framework’s declarative transaction implementation

It is not sufficient to tell you simply to annotate your classes with the @Transactional annotation, add @EnableTransactionManagement to your configuration, and then expect you to understand how it all works

Answer 2

You could try something like that.

@Component
class X {
    @Transactional
    public void executeSQL(String[] queryRows){ 
         ...begin()
         for(int i = 0; i < queryRows.length; ++i){
              // statements are executed there one after another.
         }
         if(myCondition)
              ...commit()
         else
              throw new RuntimeException(); //or any other exception
              //or TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); (not recomended).

    }

    public void test(){
       executeSQ(...);// won't work
    }
}

@Component
class Y {
    @Autowired
    X x;

    public void test(){
       x.executeSQ(...);//should work
    }
}

It will work only if method is invoked from other bean.