DB design without passing around jdbc

Question

I have a db design issue that I am facing with one of my projects. I am trying to implement a service and part of that service is a db layer. It is setup such that I have helper classes that perform get/update methods to the database and a layer on top of them that is a janitor. For ex:

public class GetStudentDBHelper {
   public List<Student> get(List<Integer> ids) {
      Conn getConnection...
      // run sql query and construct returning Student objects
   }
   public List<Student> get(List<Classroom> byClassroom) {
      // get all students in passed in classrooms
      // run sql query and construct returning Student objects
   }
}

public class StudentJanitor {
   public GetStudentDBHelper getStudentDBHelper;
   public UpdateStudentDBHelper updateStudentDBHelper;
   public UpdateClassroomDBHelper updateClassroomDBHelper;

   public List<Student> getStudents(List<Integer> ids) {
       return getStudentDBHelper.get(ids);
   }

   public void saveStudents(List<Students> students, int classRoomid) {
       Connection conn = Pool.getConnection(); // assume this gives a jdbc
       conn.autocommit(false);

       try {
           try 
           {
              updateStudentDBHelper.saveForClassroom(students, classRoomid, conn);
              updateClassroomDBHelper.markUpdated(classRoomid, conn);
              conn.commit();
           }
           catch
           {
              throw new MyCustomException(ErrorCode.Student);
           }
       }
       catch (SQLException c)
       {
           conn.rollback();
       }
       finally {
           conn.close();
       }
}

public class ClassroomJanitor{
   public void saveClassRoon(List<Classrooms> classrooms) {
       Connection conn = Pool.getConnection()// assume this gives a jdbc
       conn.autocommit(false);

       try {

           try {
              updateClassroomDBHelper.save(classrooms, conn);
              updateStudentDBHelper.save(classrooms.stream().map(Classroom::getStudents).collect(Collections.toList()), conn);
              conn.commit();
           }
           catch {
              throw new MyCustomException(ErrorCode.ClassRoom);
           }
       }
       catch (SQLException c)
       {
           conn.rollback();
       }
       finally {
           conn.close();
       }
}...

public class GetClassroomDBHelper{}...
public class UpdateClassroomDBHelper{}...

The update db classes all compose multiple other updators in case they need to update values in other tables (ie. saving a student means I have to touch a classroom table in which a student belongs to update its last updated time for instance).

The issue I am having is for the update db classes, I have to pass in a connection from my Janitor class if i am touching multiple tables in order to have transactions and their rollback capabilities. See above for what I mean. Is there a better way to do this? This type of try, catch, pass in conn to db helpers, will have to be done for any multi transaction operation in my janitors.

In short, you can see that the code is generally like this duplicated across multiple methods:

       Connection conn = Pool.getConnection()// assume this gives a jdbc
       conn.autocommit(false);

       try {

           try {
              //do some business logic requiring Connection conn
           }
           catch {
              throw new MyCustomException(ErrorCode);
           }
       }
       catch (SQLException c)
       {
           conn.rollback();
       }
       finally {
           conn.close();
       }

Answer 1

Whenever you have a code sequence that is duplicated but it only differs in some parts you can use a template method.

In your case I would introduce a TransactionTemplate class and use a callback interface for the parts that are different. E.g.

public class TransactionTemplate {

    private DataSource dataSource;

    public TransactionTemplate(DataSource dataSource) {
        this.dataSource = Objects.requireNonNull(dataSource);

    }

    public <T> T execute(TransactionCallback<T> transactionCallback) throws Exception {
        Connection conn = dataSource.getConnection();// assume this gives a jdbc
        try {
            conn.setAutoCommit(false);
            T result = transactionCallback.doInTransaction(conn);
            conn.commit();
            return result;
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.close();
        }
    }
}

The callback interface would look like this

public interface TransactionCallback<T> {
    public T doInTransaction(Connection conn) throws Exception;
}

As you can see the TransactionTemplate manages the transaction while the TransactionCallback implements the logic that must be done in one transaction.

Your client code will then look like this

public class StudentJanitor {

    private TransactionTemplate transactionTemplate;

    StudentJanitor(DataSource dataSource) {
        transactionTemplate = new TransactionTemplate(dataSource);
    }

    public void saveStudents(List<Students> students, int classRoomid) {
        SaveStudentsTransaction saveStudentsTransaction = new SaveStudentsTransaction(students, classRoomid);
        transactionTemplate.execute(saveStudentsTransaction);
    }

}

and the logic is placed in the TransactionCallback

public class SaveStudentsTransaction implements TransactionCallback<Void> {

    public GetStudentDBHelper getStudentDBHelper;
    public UpdateStudentDBHelper updateStudentDBHelper;
    public UpdateClassroomDBHelper updateClassroomDBHelper;

    private List<Students> students;
    private int classRoomid;

    public SaveStudentsTransaction(List<Students> students, int classRoomid) {
        this.students = students;
        this.classRoomid = classRoomid;
    }

    @Override
        public Void doInTransaction(Connection conn) throws Exception {
            try 
            {
               updateStudentDBHelper.saveForClassroom(students, classRoomid, conn);
               updateClassroomDBHelper.markUpdated(classRoomid, conn);
               conn.commit();
            }
            catch
            {
               throw new MyCustomException(ErrorCode.Student);
            }
            return null;
        }

}

Answer 2

Two main concerns you are currently facing are the boiler plate code for repetitive tasks related to connection (get/execute/close etc) and infrastructure for getting the same connection across method boundaries. The first is typically solved using Template pattern and the latter using Threadlocal variables to pass around appropriate connection across methods. These type of concerns have been solved in Java world long ago but will require you to rely on framework like Spring (JDBC template) etc which have this feature from last decade or so or you would need to roll out stripped down version of this infrastructure. If you are interested in latter then you can take hint from similar attmepts shared on Github like this.