城市 - 部门 - 员工 三级联动
一. 创建web工程: threelevellink_ajax
Struts1 + Ajax + Jquery
工程结构:
二. 创建数据库
mysql.sql:
CREATE DATABASE threelevellink;
USE threelevellink;
# 创建城市表 locations
CREATE TABLE `locations` (
`LOCATION_ID` int(11) NOT NULL AUTO_INCREMENT,
`STREET_ADDRESS` varchar(40) DEFAULT NULL,
`POSTAL_CODE` varchar(12) DEFAULT NULL,
`CITY` varchar(30) DEFAULT NULL,
`STATE_PROVINCE` varchar(25) DEFAULT NULL,
`COUNTRY_ID` char(2) DEFAULT NULL,
PRIMARY KEY (`LOCATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 创建部门表 departments
CREATE TABLE `departments` (
`DEPARTMENT_ID` int(11) NOT NULL AUTO_INCREMENT,
`DEPARTMENT_NAME` varchar(20) DEFAULT NULL,
`MANAGER_ID` int(11) DEFAULT NULL,
`LOCATION_ID` int(11) DEFAULT NULL,
PRIMARY KEY (`DEPARTMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 创建雇员表 employees
CREATE TABLE `employees` (
`EMPLOYEE_ID` int(11) NOT NULL AUTO_INCREMENT,
`FIRST_NAME` varchar(20) DEFAULT NULL,
`LAST_NAME` varchar(25) DEFAULT NULL,
`EMAIL` varchar(25) DEFAULT NULL,
`PHONE_NUMBER` varchar(20) DEFAULT NULL,
`HIRE_DATE` date DEFAULT NULL,
`JOB_ID` varchar(20) DEFAULT NULL,
`SALARY` decimal(10,2) DEFAULT NULL,
`COMMISSION_PCT` decimal(4,2) DEFAULT NULL,
`MANAGER_ID` decimal(6,0) DEFAULT NULL,
`DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
设置外键关系:
插入测试数据:
二. 导入jar包
三. 正式开发
index.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>Page Index</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
</head>
<body>
<%
// 重定向到action
response.sendRedirect(basePath + "locations.do?method=listLocations");
%>
</body>
</html>
web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5">
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
<init-param>
<param-name>config</param-name>
<param-value>/WEB-INF/struts-config.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
</web-app>
struts-config.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"
"http://struts.apache.org/dtds/struts-config_1_3.dtd">
<struts-config>
<!-- Locations表单 -->
<form-beans>
<!-- 城市的ActionForm -->
<form-bean name="locationsForm" type="com.joseph.ajax.forms.LocationsForm"></form-bean>
<!-- 部门的ActionForm -->
<form-bean name="departmentsForm" type="com.joseph.ajax.forms.DepartmentsForm"></form-bean>
<!-- 员工的ActionForm -->
<form-bean name="employeesForm" type="com.joseph.ajax.forms.EmployeesForm"></form-bean>
</form-beans>
<!-- Locations表单对应的action -->
<action-mappings>
<!-- 城市的action -->
<action path="/locations" type="com.joseph.ajax.action.ThreeLevelLinkAction"
name="locationsForm" parameter="method">
<forward name="mainFrame" path="/WEB-INF/mainFrame.jsp"/>
</action>
<!-- 部门的action -->
<action path="/departments" type="com.joseph.ajax.action.ThreeLevelLinkAction"
name="departmentsForm" parameter="method">
</action>
<!-- 员工列表的action -->
<action path="/employee" type="com.joseph.ajax.action.ThreeLevelLinkAction"
name="employeesForm" parameter="method">
</action>
<!-- 员工的action -->
<action path="/employeeMsg" type="com.joseph.ajax.action.ThreeLevelLinkAction"
name="employeesForm" parameter="method">
</action>
</action-mappings>
</struts-config>
逆向工程生成bean和actionForm
Locations.java:
private Integer locationId;
private String streetAddress;
private String postalCode;
private String city;
private String stateProvince;
private String countryId;
+ getter/setter
Departments.java:
private Integer departmentId;
private String departmentName;
private Integer managerId;
private Integer locationId;
+ getter/setter
Employees.java:
private Integer employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private Date hireDate;
private String jobId;
private BigDecimal salary;
private BigDecimal commissionPct;
private Integer managerId;
private Integer departmentId;
+ getter/setter
LocationsForm.java:
public class LocationsForm extends ActionForm {
private Integer locationId;
private String streetAddress;
private String postalCode;
private String city;
private String stateProvince;
private String countryId;
+getter/setter
DepartmentsForm.java:
@SuppressWarnings("serial")
public class DepartmentsForm extends ActionForm {
private Integer departmentId;
private String departmentName;
private Integer managerId;
private Integer locationId;
+getter/setter
EmployeesForm.java:
@SuppressWarnings("serial")
public class EmployeesForm extends ActionForm {
private Integer employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private Date hireDate;
private String jobId;
private BigDecimal salary;
private BigDecimal commissionPct;
private Integer managerId;
private Integer departmentId;
DBHelper.java:
package com.joseph.ajax.action.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBHelper {
// 数据库连接参数
public static final String DRIVERCLASS = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/threelevellink?useUnicode=true&characterEncoding=UTF-8";
public static final String USRENAME = "root";
public static final String PASSWORD = "123456";
// Connection
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(DRIVERCLASS);
conn = DriverManager.getConnection(URL, USRENAME, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
// PrepareStatement
public static PreparedStatement prepare(Connection conn, String sql) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
// 关闭数据库, 释放资源
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement ps) {
if (ps != null) {
try {
ps.close();
ps = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
ThreeLevelLinkService.java:
package com.joseph.ajax.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.joseph.ajax.action.util.DBHelper;
import com.joseph.ajax.bean.Departments;
import com.joseph.ajax.bean.Employees;
import com.joseph.ajax.bean.Locations;
public class ThreeLevelLinkService {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
// 获取城市列表
public List<Locations> getLocations() {
String sql = "SELECT * FROM locations";
conn = DBHelper.getConn();
ps = DBHelper.prepare(conn, sql);
List<Locations> locationsList = new ArrayList<>();
try {
rs = ps.executeQuery();
while (rs.next()) {
Locations locations = new Locations();
locations.setLocationId(rs.getInt("LOCATION_ID"));
locations.setStreetAddress(rs.getString("STREET_ADDRESS"));
locations.setPostalCode(rs.getString("POSTAL_CODE"));
locations.setCity(rs.getString("CITY"));
locations.setStateProvince(rs.getString("STATE_PROVINCE"));
locations.setCountryId(rs.getString("COUNTRY_ID"));
locationsList.add(locations);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.close(rs);
DBHelper.close(ps);
DBHelper.close(conn);
}
return locationsList;
}
// 获取部门列表
public List<Departments> getDepartments(Integer locationId) {
String sql = "SELECT * FROM departments WHERE LOCATION_ID = ?";
conn = DBHelper.getConn();
ps = DBHelper.prepare(conn, sql);
List<Departments> departmentsList = new ArrayList<>();
try {
ps.setInt(1, locationId);
rs = ps.executeQuery();
while (rs.next()) {
Departments department = new Departments();
department.setDepartmentId(rs.getInt("DEPARTMENT_ID"));
department.setDepartmentName(rs.getString("DEPARTMENT_NAME"));
department.setManagerId(rs.getInt("MANAGER_ID"));
department.setLocationId(rs.getInt("LOCATION_ID"));
departmentsList.add(department);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.close(rs);
DBHelper.close(ps);
DBHelper.close(conn);
}
return departmentsList;
}
// 获取员工列表
public List<Employees> getEmployees(Integer departmentId) {
String sql = "SELECT * FROM employees WHERE DEPARTMENT_ID = ?";
conn = DBHelper.getConn();
ps = DBHelper.prepare(conn, sql);
List<Employees> employeesList = new ArrayList<>();
try {
ps.setInt(1, departmentId);
rs = ps.executeQuery();
while (rs.next()) {
Employees employee = new Employees();
employee.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
employee.setFirstName(rs.getString("FIRST_NAME"));
employee.setLastName(rs.getString("LAST_NAME"));
employee.setEmail(rs.getString("EMAIL"));
employee.setPhoneNumber(rs.getString("PHONE_NUMBER"));
employee.setHireDate(rs.getDate("HIRE_DATE"));
employee.setJobId(rs.getString("JOB_ID"));
employee.setSalary(rs.getBigDecimal("SALARY"));
employee.setCommissionPct(rs.getBigDecimal("COMMISSION_PCT"));
employee.setManagerId(rs.getInt("MANAGER_ID"));
employee.setDepartmentId(rs.getInt("DEPARTMENT_ID"));
employeesList.add(employee);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.close(rs);
DBHelper.close(ps);
DBHelper.close(conn);
}
return employeesList;
}
// 获取员工的具体信息
public Employees getEmployeeMsg(Integer employeeId) {
String sql = "SELECT * FROM employees WHERE EMPLOYEE_ID = ?";
conn = DBHelper.getConn();
ps = DBHelper.prepare(conn, sql);
Employees employee = new Employees();
try {
ps.setInt(1, employeeId);
rs = ps.executeQuery();
if (rs.next()) { // 返回单个员工对象
employee.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
employee.setFirstName(rs.getString("FIRST_NAME"));
employee.setLastName(rs.getString("LAST_NAME"));
employee.setEmail(rs.getString("EMAIL"));
employee.setPhoneNumber(rs.getString("PHONE_NUMBER"));
employee.setHireDate(rs.getDate("HIRE_DATE"));
employee.setJobId(rs.getString("JOB_ID"));
employee.setSalary(rs.getBigDecimal("SALARY"));
employee.setCommissionPct(rs.getBigDecimal("COMMISSION_PCT"));
employee.setManagerId(rs.getInt("MANAGER_ID"));
employee.setDepartmentId(rs.getInt("DEPARTMENT_ID"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelper.close(rs);
DBHelper.close(ps);
DBHelper.close(conn);
}
return employee;
}
}
ThreeLevelLinkAction.java:
package com.joseph.ajax.action;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.joseph.ajax.bean.Departments;
import com.joseph.ajax.bean.Employees;
import com.joseph.ajax.bean.Locations;
import com.joseph.ajax.service.ThreeLevelLinkService;
public class ThreeLevelLinkAction extends DispatchAction {
// 获取城市列表
public ActionForward listLocations(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
ThreeLevelLinkService locationsService = new ThreeLevelLinkService();
List<Locations> locationsList = locationsService.getLocations();
request.setAttribute("locationsList", locationsList);
return mapping.findForward("mainFrame");
}
// 获取城市对应的部门列表
public void listDepartments(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 获取JSP页面传递过来的locationId
Integer locationId = Integer.parseInt(request.getParameter("locationId"));
ThreeLevelLinkService locationsService = new ThreeLevelLinkService();
List<Departments> departmentsList = locationsService.getDepartments(locationId);
// 使用JackSON解析工具将Java对象转化成JSON字符串
ObjectMapper mapper = new ObjectMapper();
String jsonStr = mapper.writeValueAsString(departmentsList);
// 返回jsonStr
response.setContentType("text/javascript;charset=UTF-8"); // JSON格式数据
response.getWriter().print(jsonStr);
}
// 获取部门对应的员工列表
public void listEmployee(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 获取departmentId
Integer departmentId = Integer.parseInt(request.getParameter("departmentId"));
ThreeLevelLinkService threeLevelLinkService = new ThreeLevelLinkService();
List<Employees> employeesList = threeLevelLinkService.getEmployees(departmentId);
ObjectMapper mapper = new ObjectMapper();
String jsonStr = mapper.writeValueAsString(employeesList);
// 返回JsonStr
response.setContentType("text/javascript;charset=UTF-8");
response.getWriter().print(jsonStr);
}
// 获取单个员工信息
public void getEmployeeMsg(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 获取employeeId
Integer employeeId = Integer.parseInt(request.getParameter("employeeId"));
ThreeLevelLinkService threeLevelLinkService = new ThreeLevelLinkService();
Employees employee = threeLevelLinkService.getEmployeeMsg(employeeId);
ObjectMapper mapper = new ObjectMapper();
String jsonStr = mapper.writeValueAsString(employee);
// 返回JsonStr
response.setContentType("text/javascript;charset=UTF-8");
response.getWriter().print(jsonStr);
}
}
timg.gif: 缓冲图片
mainFrame.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>Page Index</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<script type="text/javascript" src="<%=basePath%>scripts/jquery-1.3.1.js"></script>
<script type="text/javascript" src="<%=basePath%>scripts/jquery.blockUI.js"></script>
<script type="text/javascript">
$(function() {
// blockUI插件
$(document).ajaxStart(function() {
$.blockUI({
message: $("#loading"),
css: {
top: ($(window).height() - 400) /2 + "px",
left: ($(window).width() - 200) /2 + "px",
width: "50px",
border: "none"
},
overlayCSS: { backgroundColor: "#fff" }
})
}).ajaxStop($.unblockUI);
// 由城市获取部门
$("#city").change(function() { // city下拉框值改变时触发
// 不是第一个节点, 即"请选择..."的都移除, 不然部门会叠加
$("#department option:not(:first)").remove();
// 当选择"请选择.."时(city的值为""), 不发送ajax请求
var locationId = $(this).val();
if (locationId != "") {
var url = "<%=basePath%>departments.do?method=listDepartments";
var args = {"locationId": locationId, "time": new Date()};
$.getJSON(url, args, function(data) {
if (data.length == 0) {
alert("当前城市没有部门");
} else {
for (var i=0; i<data.length; i++) {
// 这里的写法要和Department.java属性名一致
var deptId = data[i].departmentId;
var deptName = data[i].departmentName;
// 在创建<select id="department">节点下创建子节点<option>
// 即: <option value="departmentId">departmentName</option>
$("#department").append("<option value='" + deptId
+ "'>" + deptName + "</option>");
}
}
});
}
});
// 由部门获取员工
$("#department").change(function() {
$("#employee option:not(:first)").remove();
var departmentId = $(this).val();
if (departmentId != null) {
var url = "<%=basePath%>employee.do?method=listEmployee";
var args = {"departmentId": departmentId, "time": new Date()};
$.getJSON(url, args, function(data) {
if (data.length == 0) {
alert("该部门没有员工");
} else {
for (var i=0; i<data.length; i++) {
var employeeId = data[i].employeeId;
var firstName = data[i].firstName;
var lastName = data[i].lastName;
$("#employee").append("<option value='" + employeeId + "'>"
+ firstName + " | " + lastName + "</option>");
}
}
});
}
});
// 获取具体员工的信息
$("#employee").change(function() {
var employeeId = $(this).val();
if (employeeId != null) {
var url = "<%=basePath%>employeeMsg.do?method=getEmployeeMsg";
var args = {"employeeId": employeeId, "time": new Date()};
$.getJSON(url, args, function(data) {
if (data.length != 0) {
$("#employeeMsg").show();
$("#employeeId").text(data.employeeId);
$("#firstName").text(data.firstName);
$("#lastName").text(data.lastName);
$("#email").text(data.email);
$("#phoneNumber").text(data.phoneNumber);
$("#hireDate").text(data.hireDate);
$("#jobId").text(data.jobId);
$("#salary").text(data.salary);
$("#commissionPct").text(data.commissionPct);
$("#managerId").text(data.managerId);
$("#departmentId").text(data.departmentId);
}
});
}
});
});
</script>
</head>
<body>
<img id="loading" alt="loading" src="images/timg.gif" style="display: none;">
<br/>
<h3 align="center">City -- Department -- Employee 三级联动案例</h3>
<br/>
<div align="center">
City:
<select id="city">
<option value="">请选择...</option>
<c:forEach items="${locationsList }" var="locations">
<option value="${locations.locationId }">${locations.city }</option>
</c:forEach>
</select>
Department:
<select id="department">
<option value="">请选择...</option>
</select>
Employee:
<select id="employee">
<option value="">请选择...</option>
</select>
<br/><br/><br/>
<table id="employeeMsg" border="1" cellspacing="0"
cellpadding="7" style="display: none; width: 80%;">
<tr>
<th>employeeId</th>
<th>firstName</th>
<th>lastName</th>
<th>email</th>
<th>phoneNumber</th>
<th>hireDate</th>
<th>jobId</th>
<th>salary</th>
<th>commissionPct</th>
<th>managerId</th>
<th>departmentId</th>
</tr>
<tr style="color: red;" align="center">
<td id="employeeId"></td>
<td id="firstName"></td>
<td id="lastName"></td>
<td id="email"></td>
<td id="phoneNumber"></td>
<td id="hireDate"></td>
<td id="jobId"></td>
<td id="salary"></td>
<td id="commissionPct"></td>
<td id="managerId"></td>
<td id="departmentId"></td>
</tr>
</table>
</div>
</body>
</html>
-------------------------------------------------------------------
测试结果:
----------------------------------------- ( 完 ) ----------------------------------------
浙公网安备 33010602011771号