I have a form with dropdowns.
Based on that form input you should be able to view the details of multiple employee's in another JSP. I cannot get this to work and when testing, the arraylist employeesList size returns zero.
My code is below - can anyone see where I am going wrong? Or even help me with how to troubleshoot to find out myself where it's going wrong? note - my database connection is working as I have tested that
index.jsp
<%@page import="com.sea.stst.contact.*" %> <!DOCTYPE> <html> <head> <title>Contact List</title> </head> <form action="searchEmployees" method="POST"> <div id="JobRole1"> <label class="formSmlFieldLbl" for="tab">Job Role:</label> <select id="tab" name="tab" class="formSmlOptn"> <option value="">Please Select</option> <option value="PRODUCTION">Production</option> <option value="ENGINEERING">Engineering</option> <option value="SUPPORT">Support</option> </select> </div> <div id="Shift1"> <label class="formSmlFieldLbl" for="Shift">Shift:</label> <select id="shift" name="shift" class="formSmlOptn"> <option value="default">Current</option> <option value="A">A</option> <option value="B">B</option> <option value="C">C</option> </select> </div><br /> <input type="submit" value="Submit" /> </form> </body> </html>DAO.java - basically the two values of the dropdown selections go into my SQL WHERE clause(note - DatabaseUtil is a separate file where the DB connection is made)
package com.sea.stst.contact;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import com.sea.stst.contact.DatabaseUtil;
public class Dao {
private static Logger logger = Logger.getLogger(Dao.class);
public Dao()
{
}
public static ArrayList SearchContactList(String role, String shift)
{
ArrayList result = new ArrayList();
Connection con = null;
String lsSQL = "";
try
{
con = DatabaseUtil.getDBConnection("ContactDS");
PreparedStatement stmt = null;
ResultSet res = null;
try
{
lsSQL = "SELECT EMP.EMPNAME, CFG.ROLE, CFG.TAB, PH.EMPID, PH.EXT EXTENSION, PH.SHIFT, PERM.PERM_GRANTED FROM TOOL_CONTACT_OWNER.USER_PERM PERM, TOOL_CONTACT_OWNER.USER_PHONE PH, TOOL_CONTACT_OWNER.ROLE_TAB_CFG CFG, WAFER.EMPLOYEE EMP WHERE PH.IS_FAB_NUM = 'N' AND PERM.EMPID=EMP.EMPID AND PERM.EMPID = PH.EMPID AND PH.ROLE_KEY = CFG.ROLE_KEY AND CFG.TAB=? AND PH.SHIFT=?";
stmt = con.prepareStatement(lsSQL);
stmt.setString(1, role);
stmt.setString(2, shift);
res = stmt.executeQuery();
while (res.next())
{
result.add(new Employee (res.getString("EMPID"),
res.getString("EMPNAME"),
res.getString("SHIFT"),
res.getString("EXT"),
res.getString("ROLE"),
res.getString("PERM_GRANTED"),
res.getString("TAB")));
}
}
catch (Exception e)
{
logger.error("Error getting data",e);
}
finally
{
try { res.close(); } catch (Exception e) {} finally { res = null; }
try { stmt.close(); } catch (Exception e) {} finally { stmt = null; }
}
}
catch (Exception e)
{
logger.error("Error getting connection",e);
}
finally
{
try { con.close(); } catch (Exception e) {} finally { con = null; }
}
return result;
}
}SearchEmployees.java (Servlet) - I am trying to then add the results of the SELECT query to a new Employee object
package com.sea.stst.contact.servlet;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.log4j.Logger;
import com.sea.stst.contact.Dao;
public class SearchEmployees extends HttpServlet {
private Logger logger = Logger.getLogger(SearchEmployees.class);
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException {
String tab = request.getParameter("tab");
String shift = request.getParameter("shift");
try {
ArrayList employees = Dao.SearchContactList(tab, shift);
request.setAttribute("emplist", employees);
RequestDispatcher rd = request.getRequestDispatcher("/Contacts.jsp");
rd.forward(request, response);
}
catch (IOException e) { logger.error("doPost: " + e);
}
}
}Contacts.jsp (where you should view the employee's and their details based on your search)
<%@page import="java.util.Enumeration,java.util.ArrayList,com.sea.stst.contact.*" %> <%
// Get Employee Data
ArrayList employeesList = (ArrayList)request.getAttribute("emplist");
%> <!DOCTYPE> <html> <head> <title>Employees</title> </head> <body> <table width="100%" align="center"> <tr> <th>Employee ID</th> <th>Name</th> <th>Shift</th> <th>Phone No.</th> </tr> <%
for (int i=0; i<employeesList.size(); i++)
{
Employee thisemp = (Employee)employeesList.get(i);
%> <tr> <td><%=thisemp.getEmpGid()%></td> <td><%=thisemp.getEmpName()%></td> <td><%=thisemp.getShift()%></td> <td><%=thisemp.getEmpPhoneNo()%></td> </tr> <%
}
%> </table> </body> </html>