Dynamic transaction isolation levels for @Transactional

Question

I have been working on to fix SQL injections in a very old Java + Spring MVC code base with few hundred classes at DAO layer which is currently using java.sql.PreparedStatement & java.sql.Connection .

DB Connection isolation level , DB Connection commit & Connection rollback is directly handled on Connection object using - Connection.setIsolationLevel(int isolationLevel), Connection.commit() & Connection.rollback() .

Lets say , I have a method like below ,

     public List<String> getReportName() {
            try {

Connection  connection = getConnection();
connection.setIsolationLevel(Isolation.READ_UNCOMMITTED); // Just an example
                String sql = //ActualSQLString;
                PreparedStatement  pstmt = connection.prepareStatement(sql);
                ResultSet  rs = pstmt.executeQuery();
                while (rs.next()) {
                 ......
                 ......
                }
            } catch (Exception e) {
                ...
            } finally {
                //Close connection, statement & result set
            }
            }

if I wish to introduce org.springframework.jdbc.core.JdbcTemplate in place of java.sql.PreparedStatement , there is an automatic requirement that I get rid of call connection.setTransactionIsolation(isolationLevel) , commit & rollback since JdbcTemplate works on DatSource instead of individual connection object. So I change above method as below. getJdbcTemplate() is for illustration purposes only & I can have that via @Autowired too. Also, this requirement has nothing to do with core requirement of fixing SQL injections.

@Transactional(isolation = Isolation.READ_UNCOMMITTED, readOnly = true)
public List<String> getReportName() {
            try {
                String sql = //ActualSQLString;
                return getJdbcTemplate().queryForList(sql);
            } catch (Exception e) {
                ...
            } 
            }

Scenario of commit & rollback is handled by rollbackFor attribute if that would have been happening in this above method.

Now , I am confused about when method signature looks like below i.e. Connection gets created in another DAO Class method, isolation level set there and passed on to this method ( which is in another DAO class ) ,

public List<String> getReportName(Connection  connection) {
            try {
                String sql = //ActualSQLString;
                PreparedStatement  pstmt = connection.prepareStatement(sql);
                ResultSet  rs = pstmt.executeQuery();
                while (rs.next()) {
                 ......
                 ......
                }
            } catch (Exception e) {
                ...
            } finally {
                //Close connection, statement & result set
            }
            }

There are various callers of this above method from different classes & call hierarchy is usually multiple levels i.e. Connection object was created two or three levels above & isolation set there.

e.g. Connection is created in DAOClass1.method() and passed on to above getReportName of DAOClass3 .

DAOClass1.method() -> DAOClass2.method(Conenction connection) -> DAOClass3.getReportName(Conenction connection)

Is this scenario re engineering possible by introducing @Transactional & JdbcTemplate combo ? Would I be applying @Transactional only at call initiator where Connection is created or at this method too?

I guess , this is more of a transaction propagation case but confused a bit.

My question is duplicate of below Question # 1 but need solution for my specific scenario.

Related Question 1 - Variable transaction isolation levels by request

Related Question 2 - How can I get a spring JdbcTemplate to read_uncommitted?

Answer 1

I'm doubtful about the premise that dynamic isolation levels are a requirement here. When isolation levels make a difference it's not because of something about the particular data, it's the way the statements themselves are combined that is the issue. It seems unlikely that you're going to have a case where a method called with one set of data should have one isolation level, while the same method called with another set of data should go through under another isolation level.

I'm guessing the core issue with the legacy code is that there is no concept of a transactional service layer. Instead you have a hodge-podge of data access objects that are called directly by controllers, and isolation levels seem to be specified haphazardly.

Specifying transactional details like isolation level needs to be done at the service level. I would go through the controllers, separate the web-layer stuff they do from business logic, and push the business logic down into service methods that you can annotate with things like isolation level.

Once you do that you will have DAOs that can be reused in different services, where the isolation level is given in the specific service, and all the code fetching connections, catching exceptions, and closing jdbc resources can be deleted.