JDBC

[Jimmy Lopez]

Good day everyone, I am an entry level Java developer and I am trying to figure out this code;

public LinkedList selectEmployeeList(String department)
throws ServletException, WalgreensDataException, PMTException {

DataAccess da = null;
LinkedList employeeList = new LinkedList();
StringBuffer sqlQuery = new StringBuffer();
sqlQuery.append("select dept.auth_id, ");
sqlQuery.append("emp.first_name, ");
sqlQuery.append("emp.last_name, ");
sqlQuery.append("emp.active_ind, ");
sqlQuery.append("emp.task_ind, ");
sqlQuery.append("emp.project_ind ");
sqlQuery.append("from rts_employee emp, ");
sqlQuery.append("rts_emp_dept dept ");
sqlQuery.append("where emp.auth_id = dept.auth_id ");
sqlQuery.append("and dept.department = ? ");
sqlQuery.append("and dept.default_dept_ind = ? ");
sqlQuery.append("order by active_ind desc, emp.last_name");

da = new DataAccess(conn, sqlQuery.toString());
da.setSQL(sqlQuery.toString());
da.setString(1, department);
da.setString(2, PMTConstants.ACTIVE);

da.executeQuery();


while (da.next()) {
EmployeeBean emp = new EmployeeBean();
emp.setAuthId(da.getString("AUTH_ID"));
emp.setFirstName(da.getString("FIRST_NAME"));
emp.setLastName(da.getString("LAST_NAME"));
emp.setActiveInd(da.getString("ACTIVE_IND"));
emp.setTaskInd(da.getString("TASK_IND"));
emp.setProjectInd(da.getString("PROJECT_IND"));

employeeList.add(emp);
}

if (da != null) {
da.close();
}


return employeeList;
} // end of selectEmployeeList


Any suggestions ?

[stu derby]

I am trying to figure out this code;

Any suggestions ?

Ask a more specific question. Are you saying that this is your code and doesn't work? How not? Or is the code you found some where and you want to know what it does and how it works?

Here's a commented version (with a minor change or two); not the effect of using the code tags to make it more readable.

public LinkedList selectEmployeeList(String department)
  throws ServletException, WalgreensDataException, PMTException {
 
  // Initialize a data access object (DAO), so that Java will let us 
  //  put a try/catch/finally block around its use
  DataAccess da = null;
 
  // create a new empty list to fill and return
  LinkedList employeeList = new LinkedList();
 
  // build a SQL query; the query joins an employee and a department table
  // and returns department auth_id and employee information for one 
  // department name, sort by some sort of status and then by name,
  // but excludes department records that are not in a particular state
  // (apparently, there can be more than one record per name) 
  StringBuffer sqlQuery = new StringBuffer();
  sqlQuery.append("select dept.auth_id, ");
  sqlQuery.append("emp.first_name, ");
  sqlQuery.append("emp.last_name, ");
  sqlQuery.append("emp.active_ind, ");
  sqlQuery.append("emp.task_ind, ");
  sqlQuery.append("emp.project_ind ");
  sqlQuery.append("from rts_employee emp, ");
  sqlQuery.append("rts_emp_dept dept ");
  sqlQuery.append("where emp.auth_id = dept.auth_id ");
  sqlQuery.append("and dept.department = ? ");
  sqlQuery.append("and dept.default_dept_ind = ? ");
  sqlQuery.append("order by active_ind desc, emp.last_name");
 
  // there needs to be a try here
  // DAO resources MUST MUST MUST be closed in a finally block
  //   to prevent Exceptions causing leak of resources, which in turn
  //   can stop a database or kills its performance
  try
  {
    // create some sort of data access object
    // this object abstracts away and hides the details of the data access
    // it could be using JDBC, or a dozen other different technologies
    DataAccess da = new DataAccess(conn, sqlQuery.toString());
 
    // tell the DAO what SQL it is to execute
    da.setSQL(sqlQuery.toString());
 
    // tell the DAO what data values the SQL is to use, 
    //   in place of the "?" placeholders 
    da.setString(1, department);
    da.setString(2, PMTConstants.ACTIVE);
 
    // tell the DAO to execute the SQL
    da.executeQuery();
 
    // iterate through each row returned by the SQL query,
    //   convert the row to a bean,
    //   and append the bean to the linked list
    while (da.next()) {
      EmployeeBean emp = new EmployeeBean();
      emp.setAuthId(da.getString("AUTH_ID"));
      emp.setFirstName(da.getString("FIRST_NAME"));
      emp.setLastName(da.getString("LAST_NAME"));
      emp.setActiveInd(da.getString("ACTIVE_IND"));
      emp.setTaskInd(da.getString("TASK_IND"));
      emp.setProjectInd(da.getString("PROJECT_IND"));
 
      employeeList.add(emp);
    }
 
    // might be a good idea to do something about some exceptions here,
    //   with a catch block
    //   or perhaps not, can't tell; maybe just logging, but that
    //   could also be handled at the higher level
    // Without knowing more, I'd guess that setting the return list
    //   to null might be a good idea
 
  }
 
  // tell the DAO to close any resources it may have open
  // do this in a finally, to prevent resource leaks
  finally
  {    
    if (da != null) {
      da.close();
    }
  }
 
  return employeeList;
} // end of selectEmployeeList
 

Hope that helps.