java - 如何从数据库访问代码中抽象出业务逻辑和对象定义？

Question

所以我有一个包含 2 个表的数据库 - Workflows 和 WorkflowSteps 我想使用存储在其中的行在 java 中创建对象，但问题是我希望将数据库代码与应用程序代码分开。从某一点开始 - 当创建 Workflow/WorkflowSteps 对象时，应用程序的其余部分将不必担心数据库访问。这就是我所拥有的:

public Workflow getPendingWorkflowId() {
    int workflowid = -1;
    Statement statement = null;
    ResultSet rs = null;
    try {
        statement = con.createStatement();

        rs = statement.executeQuery("SELECT id FROM xxx.workflows WHERE status = 'NOT-YET-STARTED' LIMIT 1");

        while (rs.next()) {
            workflowid = rs.getInt("id");
        }

        statement.close();
        rs.close();

    } catch (SQLException ex) {
        Logger.getLogger(DBAccessor.class.getName()).log(Level.SEVERE, null, ex);
        System.out.println("Error fetching workflows id");
    }

    return new Workflow(workflowid);
}

每个工作流对象都有一个列表来存储属于特定工作流的步骤，然后每个 WorkflowStep 有一个映射用于存储从第三个表中获取的数据:

public List<WorkflowStep> getUnworkedStepsByWFId(int id) {

    //can be changed
    ArrayList<WorkflowStep> steps = new ArrayList<WorkflowStep>();
    Statement statement = null;
    ResultSet rs = null;
    try {
        statement = con.createStatement();

        rs = statement.executeQuery("SELECT * FROM `workflow_steps` WHERE `workflow_id` =" + id + " AND status =  'NOT-YET-STARTED'");

        while (rs.next()) {

            steps.add(new WorkflowStep(rs.getInt(1), rs.getInt(3), rs.getInt(4)));

        }

        statement.close();
        rs.close();

    } catch (SQLException ex) {
        Logger.getLogger(DBAccessor.class.getName()).log(Level.SEVERE, null, ex);
        System.out.println("Error fetching workflows id");
    }

    return steps;
} 

这是对第三个表的查询: 公共(public) map getParametersForStep(intworkflowId, intworkstepPos) {

    Statement statement = null;
    ResultSet rs = null;
    Map<String, String> hMap = new HashMap<String, String>();

    try {
        statement = con.createStatement();

        //MIGHT BE WRONG
        rs = statement.executeQuery("SELECT wf.id AS workflowID, ws_steps.id AS workflowStepsID, name, param_value, pathname FROM workflows AS wf INNER JOIN workflow_steps AS ws_steps ON wf.id = ws_steps.workflow_id INNER JOIN ws_parameters ON ws_parameters.ws_id = ws_steps.id INNER JOIN submodule_params ON submodule_params.id = ws_parameters.sp_id AND wf.id =" + workflowId + " AND ws_steps.workflow_position =" + workstepPos);
        String paramName = null;
        String paramValue = null;

        while (rs.next()) {

            paramName = rs.getString("name");

            if (rs.getString("param_value") == null) {
                paramValue = rs.getString("pathname");
            } else {
                paramValue = rs.getString("param_value");
            }

            hMap.put(paramName, paramValue);
        }

        statement.close();
        rs.close();
        return hMap;

    } catch (SQLException ex) {
        Logger.getLogger(DBAccessor.class.getName()).log(Level.SEVERE, null, ex);
        System.out.println("Error fetching workflow step parameters names");
    }

    return Collections.emptyMap();
}

考虑到这段代码，我最终得到以下“过程”来初始化工作流及其所有 WorkflowSteps 及其参数:

Workflow wf = db.getPendingWorkflowId();
wf.initSteps(db.getUnworkedStepsByWFId(wf.getId()));
Iterator<WorkflowStep> it = wf.getSteps();

     while(it.hasNext()) {
         WorkflowStep step = it.next();             
         step.setParameters(db.getParametersForStep(wf.getId(), step.getPosInWorkflow()));
     }

我认为我有很好的解耦水平，但我想知道是否可以以某种方式重构 - 例如可能将 step.setParameters 移动到 WorkflowStep 类的方法，但随后我必须传递对数据库连接的引用(db) 到 WorkflowStep 对象，但在我看来这会破坏解耦？那么你们会如何重构这段代码呢？

Answer 1

看来你正在滚动自己的ORM 。我的建议是使用现有的之一，例如 Hibernate .