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();
}
}
Aucun commentaire:
Enregistrer un commentaire