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>

Array List returning 0 means there is nothing. I think there is an error in your query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.