Java – database design, without passing JDBC

I have a database design problem and I am facing one of my projects I try to implement a service, and part of this service is a DB layer Its setup is like this. I have a help class that executes get / update methods to the database and the layer above them as a gatekeeper For example:

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{}...

Updating the DB class will form several other updating programs in case they need to update the values in other tables (that is, saving the student means that I must touch the class table to which the student belongs to update its last updated time)

The problem I encounter is to update the DB class. If I am touching multiple tables to have transaction and rollback functions, I must pass a connection from my janitor class See what I mean above Is there a better way to do this? This type of try and catch, passed into conn to DB helpers, must be completed for any multi transaction operation in my manager

In short, you can see that the code usually repeats multiple methods like this:

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();
       }

Solution

Whenever you have a code sequence that is repeated, but it is only different in some parts, you can use a template method

In your case, I will introduce a transactiontemplate class and use callback interfaces for different parts For example

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 looks like this

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

As you can see, transactiontemplate manages transactions, and transactioncallback implements the logic that must be completed in a transaction

Then your client code will be 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);
    }

}

Put logic in 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,conn);
               updateClassroomDBHelper.markUpdated(classRoomid,conn);
               conn.commit();
            }
            catch
            {
               throw new MyCustomException(ErrorCode.Student);
            }
            return null;
        }

}