How do I abstract my business logic and object defintions away from my database access code?

So I have a database with 2 tables - Workflows and WorkflowSteps I want to use the rows stored there to create objects in java BUT the catch is that I want to have my database code separated from my application code. From one point onwards - when Workflow/WorkflowSteps objects are create the rest of the application will not have to worry about DB access. So here is what I have:

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

Each workflow object has a List to store the steps that pertain to a particular Workflow and then each WorkflowStep has a Map which is used to store data taken from a 3rd table:

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

And here is the query for the 3rd table: public Map getParametersForStep(int workflowId, int workstepPos) {

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

Having this code in mind I end up with the following "procedure" to initialize a Workflow with all its WorkflowSteps and their Parameters:

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

开发者_StackOverflow中文版     while(it.hasNext()) {
         WorkflowStep step = it.next();             
         step.setParameters(db.getParametersForStep(wf.getId(), step.getPosInWorkflow()));
     }

I think I have a good level of decoupling but I wonder if this can be refactored somehow - for example probably move the step.setParameters to a method of the WorkflowStep class but then I would have to pass a reference to the database connection (db) to a WorkflowStep object but in my view this will break the decoupling? So how would you people refactor this code?

---

It seems that you are rolling your own ORM. My suggestion would be to use one of existing ones like Hibernate.

---

This is the function of an Object Relational Mapper. It serves to abstract your DB access away from your business model. In fact, used properly, an ORM library allows you to write no database code at all.