Where should logic go for deciding which SQL query to execute

I have a DAO with a method CommitmentListDAO.getListByOwnerBadge that returns an arraylist of commitment items against a supervisor badge (database field OWNED_BY)

    String SQL_VIEW_LIST_BY_SUPERVISOR = SELECT_QUERY + 
    " WHERE c.OWNED_BY = ? " +
    " ORDER BY p.PROGRAM_NAME";

Now, I want to add a pull down on my web form to allow the user to choose between Owned By or Tasked To I'll need to add a WHERE c.TASKED_TO = ? clause in the DAO.

Do I perform the logic for which field to search on within the DAO - say a passed in parameter of the pulldown (Never the request object) and rename the method to getListByBadge(String whichField, String badge) or shoul开发者_如何学编程d my CommitmentListForm class have this logic and then make the appropriate call to either getListByOwnerBadge or getListByTaskeToBadge

---

I would go with a DAO on it with two different methods to clearly differentiate what the call does.

The point of a DAO is to hide the SQL implementation details. You should always consider a question like this from the standpoint of, "What if I switched to a different persistence mechanism, like HBase?" The HBase implementation may not store this in a way that simply differentiates by a field name. The DAO should make it possible to hide that detail, thus the different methods.

Just my opinion, of course. :)

---

I would determine the logic in your controller and separate the two SQL queries into getListByOwnerBadge or getListByTaskeToBadge. That way you avoid having a method that "does it all" and could quickly get out of hand. Furthermore, other developers who choose to use the "does it all" method will have to inspect the method's internals to see what valid Strings can be passed in, whereas an explicit method call makes it obvious as to what the method is accomplishing.

---

I think that the second solution is better. Keep DAO as simple as it is possible. No logic, no flags. Create 2 simple methods and make decision in form which one to call. Or even create yet another layer between form and DAO that decides which DAO method to call.

---

To summarize:

From @McStretch - The logic for which to call goes in your controller.

From @rfreak - The query methods themselves go in the DAO

Here's an example:

//Controller
CommitmentListAction {

updateForm(...){ 
  List<CommitmentItem> commitmentItems;
  if (formUsesOwnedBy){
    commitmentItems = CommitmentItemDAO.getListByOwnerBadge(...);
  } else {
    commitmentItems = CommitmentItemDAO.getListByTaskeToBadge(...);
  }
  // Do stuff with commitmentItems.
}
// DAO

getListByOwnerBadge(...){
 String SQL_VIEW_LIST_BY_SUPERVISOR = SELECT_QUERY + 
    " WHERE c.OWNED_BY = ? " +
    " ORDER BY p.PROGRAM_NAME"
  return doQuery(SQL_VIEW_LIST_BY_SUPERVISOR); // Performs the actual query
}

getListByTaskeToBadge(...){
String SQL_VIEW_LIST_BY_TASKED_TO = SELECT_QUERY + 
    " WHERE c.TASKED_TO = ? " +
    " ORDER BY p.PROGRAM_NAME"
  return doQuery(SQL_VIEW_LIST_BY_TASKED_TO); // Performs the actual query
}

If you're going to have many different views of the CommitmetItems or many different criteria, consider passing in the criteria to the DAO. But only do this at the time when it appears there is an excessive number of getListBy[blah] methods polluting the DAO.