Should I create a Model & DAO for this in my Java web application?

Question

I have the following action servlet and was wondering if I should create a model called supervisor and a corresponing supervisorDAO as I did for program? The programDAO puts multiple program model beans into the returned arraylist. For supervisors, I am using a general input/output database utility to get an arraylist of hashmaps (retALM) for any passed in SQL string. The supervisor list is used to create a select pulldown on the html form.

The concern I have is storing the sql string in the action servlet. I'm not sure it warrants creating a supervisor model and DAO if I have a User model and UserDAO class. Actually after typing this post, I further beleive that is not the right approach. So it is down to either leaving it the way I have it below or adding the supervisor SQL call to get a list of supervisors in the UserDAO class since a user can be a supervisor. I welcome other critiques to my action servlet approach below as well.

public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, 
    IOException {
    ProgramDAO prgDAO = new ProgramDAO();
    STKUser authenticatedUser = (STKUser) request.getSession().getAttribute("STKUserSession");
    List programs = null;
    List supervisors = null;

    try {
        programs = prgDAO.getProgramList(authenticatedUser);
    } catch (DAOException e) {
        request.setAttribute("message", e);
    }

    String strSQL = "SELECT DISTINCT phonebook.badge, phonebook.lname, phonebook.fname FROM phonebook 
        WHERE phonebook.badge IN (SELECT DISTINCT phonebook.ata_badge FROM phonebook WHERE 
        phonebook.dept='" + authenticatedUser.getDepartment() + "') ORDER BY lname";
    supervisors = General_IO.retALM(strSQL);

    request.setAttribute("supervisors", supervisors);
    request.setAttribute("programs", programs);
    RequestDispatcher view = request.getRequestDispatcher("views/commitment_template.jsp");
    view.forward(request, response);
}   

Answer 1

I suggest reading the motivations for the DAO pattern. In short, it is used to abstract out the way your data is accessed. If used properly, your servlet shouldn't have to include SQL.

I also suggest using a persistance API like JPA or JDO to access your objects. Building your own SQL for every type of data access is tedious, error prone, and often inefficent.

Separately from how you retrieve your data is the question of how you model your data. Since supervisor is a user, presumably with all the attributes of a user (name, employee ID, a supervisor) it probably makes sense to store those attributes in the same table you store the user data.

Answer 2

If your supervisors are users and you have a UserDAO, then perhaps your UserDAO should have a getSupervisors() method, or maybe a getSupervisors(String dept) method.

Never, ever, ever construct SQL by string concatenation, its just asking for http://en.wikipedia.org/wiki/SQL_injection