I am basically displaying MySQL database records on JSP/JSTL page using Servlet & JDBC. I have two tables one is "Employee Table" & other "Location Table"(both tables are related via PK & FK). Now I am able to display Employee Table on JSP which looks like:
Now the hyperlink is appearing on the column "Department #" that is supposed to display the "Location Table" for each employee, not all rows of different employees. My coding for this project includes 2 POJO classes for Employee & Location table, an EmployeeDbUtil class for JDBC connection, Employee Servlet and two JSP files for Employee & Location table display.
Location Table for single Employee when clicked on hyperlink
Now what my problem is that I have hard coded the SQL query part on DbUtil class for getting the single row for Location Table by saying "where location.dept_no = 10" that's why it gives same row when clicked on any of the hyperlinks, this is not good. So please show me code segment that I can use such that I can get different results when clicked on different hyperlinks for different employees. I really don't know how to do that so please help me here. Thank you!
Here's my complete code (DbUtil, Servlet & JSP):
EMPLOYEE DbUtil(For JDBC Connection)
private DataSource dataSource;
public EmployeeDbUtil(DataSource theDataSource) {
dataSource = theDataSource;
}
public List<Employee> getEmployee() throws Exception {
List<Employee> employees = new ArrayList<>();
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
// Get connection
myConn = dataSource.getConnection();
// SQL Query
String sql = "SELECT * FROM order_customer.employee; ";
myStmt = myConn.createStatement();
myRs = myStmt.executeQuery(sql);
while (myRs.next()) {
// retrieving data
String employeeID = myRs.getString("employee_id");
String name = myRs.getString("name");
int salary = myRs.getInt("salary");
String hireDate = myRs.getString("hire_date");
int deptNum = myRs.getInt("dept_no");
// create new customer object
Employee tempEmployee = new Employee(employeeID, name, salary, hireDate, deptNum);
// Now add tempCustomer to the ArrayList
employees.add(tempEmployee);
}
return employees;
}
finally {
close(myConn, myStmt, myRs);
}
}
private void close(Connection myConn, Statement myStmt, ResultSet myRs) {
}
////////////////////////////////////////////////////////////////////////
public List<Location> getLocation() throws Exception {
List<Location> location = new ArrayList<>();
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
// Get connection
myConn = dataSource.getConnection();
// SQL Query
String sql = "SELECT location.dept_no,location.state,location.city \r\n" +
"from order_customer.employee JOIN order_customer.location\r\n" +
"on employee.dept_no=location.dept_no\r\n" +
"WHERE location.dept_no = '10' ";
myStmt = myConn.createStatement();
myRs = myStmt.executeQuery(sql);
while (myRs.next()) {
// retrieving data
int deptNum = myRs.getInt("dept_no");
String state = myRs.getString("state");
String city = myRs.getString("city");
// create new customer object
Location tempLocation = new Location(deptNum, state, city);
location.add(tempLocation);
}
return location;
}
finally {
close(myConn, myStmt, myRs);
}
}
}
Servlet Code:
private EmployeeDbUtil employeeDbUtil;
@Resource(name = "jdbc/order_customer")
private DataSource dataSource;
@Override
public void init() throws ServletException {
super.init();
try {
employeeDbUtil = new EmployeeDbUtil(dataSource);
} catch (Exception exc) {
throw new ServletException(exc);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
// read the "command" parameter
String theCommand = request.getParameter("command");
if (theCommand == null) {
theCommand = "LIST";
}
// route to the appropriate method
switch (theCommand) {
case "LIST":
listEmployee(request, response);
break;
case "LOAD":
loadLocation(request, response);
break;
default:
listEmployee(request, response);
}
} catch (Exception exc) {
throw new ServletException(exc);
}
}
private void loadLocation(HttpServletRequest request, HttpServletResponse response) throws Exception {
List<Location> location = employeeDbUtil.getLocation();
request.setAttribute("THE_LOCATION", location);
RequestDispatcher dispatcher = request.getRequestDispatcher("/location-info.jsp");
dispatcher.forward(request, response);
}
private void listEmployee(HttpServletRequest request, HttpServletResponse response) throws Exception {
List<Employee> employees = employeeDbUtil.getEmployee();
request.setAttribute("EMPLOYEE_LIST", employees);
RequestDispatcher dispatcher = request.getRequestDispatcher("/list-employee.jsp");
dispatcher.forward(request, response);
}
}
JSP PAGE CODE:
<
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<title>Employee Details</title>
<link type="text/css" rel="stylesheet" href="css/style.css">
</head>
<body>
<div id="wrapper">
<div id="header">
</div>
</div>
<div id="container">
<div id="content">
<table>
<tr>
<th>Employee ID</th>
<th>Name</th>
<th>Salary</th>
<th>Hire Date</th>
<th>Department #</th>
</tr>
<c:forEach var="tempEmployee" items="${EMPLOYEE_LIST}">
<c:url var="employeeLink" value="EmployeeServlet">
<c:param name="command" value="LOAD" />
<c:param name="deptNum" value="${tempEmployee.deptNum}" />
</c:url>
<tr>
<td>${tempEmployee.employeeID}</td>
<td>${tempEmployee.name}</td>
<td>${tempEmployee.salary}</td>
<td>${tempEmployee.hireDate}</td>
<td> <a href= "${employeeLink}"> ${tempEmployee.deptNum} </a></td>
</c:forEach>
</table>
</div>
</div>
</body>
JSP for Location table:
<<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<title>Employee Department</title>
<link type="text/css" rel="stylesheet" href="css/style.css">
</head>
<body>
<div id="wrapper">
<div id="header">
<h2>Department Details</h2>
</div>
</div>
<div id="container">
<div id="content">
<table>
<tr>
<th>Department #</th>
<th>State</th>
<th>City</th>
</tr>
<c:forEach var="tempLocation" items="${THE_LOCATION}">
<tr>
<td>${tempLocation.deptNum}</td>
<td>${tempLocation.state}</td>
<td>${tempLocation.city}</td>
<%-- <td> ${tempLocation.name} </td> --%>
</tr>
</c:forEach>
</table>
</div>
</div>
</body>
</html>
