Ajax 案例之三级联动

2023-07-20 23:25:52

　　每次在博客园网站写博客，格式真的好难搞，还望好心人告知更好的编辑工具。接下来进入正题：三级联动（其效果演示可看我的博文Ajax 学习总结末尾）。

数据表设计（Oracle）
1. 新建数据表 Employees（员工信息）、Locations（城市信息）、Departments（部门信息），其中 Departments 表的外键为 locations 表的 location_id ，Employees 表的外键为 Departments 表Department_id
DAO 层设计（c3p0 数据库连接池、JDBCUtils）

a. 获取释放数据库连接的工具类：JDBCTools

package com.javaweb.userajax.serlet.list.show.tools;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Created by shkstart on 2017/12/05.
*/
public class JDBCTools {
private static DataSource dataSource;
static {
dataSource = new ComboPooledDataSource("listShow");
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public void releaseConnection(Connection connection) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

b. 获取某张数据表的所有数据以及根据限定的查询条件获取部分值的 DAO 类：DAO
package com.javaweb.userajax.serlet.list.show.dao;
import com.javaweb.userajax.serlet.list.show.tools.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* Created by shkstart on 2017/12/05.
*/
public class DAO<T> {
private QueryRunner queryRunner;
private Class<T> type;
public DAO() {
queryRunner = new QueryRunner();
Type superClass = getClass().getGenericSuperclass();
if (superClass instanceof ParameterizedType) {
ParameterizedType parameterizedType = (ParameterizedType) superClass;
Type[] args = parameterizedType.getActualTypeArguments();
if (args != null && args.length > 0) {
if (args[0] instanceof Class) {
type = (Class<T>) args[0];
}
}
}
}
public List<T> getAll(String sql) {
Connection connection = JDBCTools.getConnection();
List<T> list = null;
try {
list = queryRunner.query(connection, sql, new BeanListHandler<T>(type));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List<T> getList(String sql, Object ... args) {
Connection connection = JDBCTools.getConnection();
List<T> list = null;
try {
list = queryRunner.query(connection, sql, new BeanListHandler<T>(type), args);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public T getValue(String sql, Object ... args) {
Connection connection = JDBCTools.getConnection();
T entity = null;
try {
entity = queryRunner.query(connection, sql, new BeanHandler<T>(type), args);
} catch (SQLException e) {
e.printStackTrace();
}
return entity;
}
}

c. 根据案例需求定义数据表对应的 dao 接口

DepartmentsDao:
package com.javaweb.userajax.serlet.list.show.dao;
import com.javaweb.userajax.serlet.list.show.domain.Departments;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public interface DepartmentsDao {
List<Departments> getAll(Integer locationId);
}

EmployeesDao:
package com.javaweb.userajax.serlet.list.show.dao;
import com.javaweb.userajax.serlet.list.show.domain.Employees;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public interface EmployeesDao {
List<Employees> getAll(Integer departmentId);
Employees getEmp(Integer employeeId);
}

LocationsDao:
package com.javaweb.userajax.serlet.list.show.dao;
import com.javaweb.userajax.serlet.list.show.domain.Locations;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public interface LocationsDao {
List<Locations> getAll();
}

d. 根据数据表编写对应的 domain 类

Departments：
package com.javaweb.userajax.serlet.list.show.domain;
/**
* Created by shkstart on 2017/12/06.
*/
public class Departments {
private Integer departmentId;
private String departmentName;
private Integer locationId;
public Integer getDepartmentId() {
return departmentId;
}
public void setDepartmentId(Integer departmentId) {
this.departmentId = departmentId;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public Integer getLocationId() {
return locationId;
}
public void setLocationId(Integer locationId) {
this.locationId = locationId;
}
public Departments(){
}
@Override
public String toString() {
return "Departments{" +
"departmentId=" + departmentId +
", departmentName='" + departmentName + '\'' +
", locationId=" + locationId +
'}';
}
}

Employees：
package com.javaweb.userajax.serlet.list.show.domain;
/**
* Created by shkstart on 2017/12/06.
*/
public class Employees {
private Integer employeeId;
private Integer departmentId;
private String lastName;
private String email;
@Override
public String toString() {
return "Employees{" +
"employeeId=" + employeeId +
", departmentId=" + departmentId +
", lastName='" + lastName + '\'' +
", email='" + email + '\'' +
'}';
}
public Integer getDepartmentId() {
return departmentId;
}
public void setDepartmentId(Integer departmentId) {
this.departmentId = departmentId;
}
public Integer getEmployeeId() {
return employeeId;
}
public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Employees(){
}
}

Locations：
package com.javaweb.userajax.serlet.list.show.domain;
/**
* Created by shkstart on 2017/12/06.
*/
public class Locations {
private Integer locationId;
private String city;
public Integer getLocationId() {
return locationId;
}
public void setLocationId(Integer locationId) {
this.locationId = locationId;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public Locations() {
}
@Override
public String toString() {
return "Locations{" +
"locationId=" + locationId +
", city='" + city + '\'' +
'}';
}
}

e. 根据 DAO 类以及 domain类实现数据表对应的 dao 接口

DepartmentsImpl：
package com.javaweb.userajax.serlet.list.show.daoimpl;
import com.javaweb.userajax.serlet.list.show.dao.DAO;
import com.javaweb.userajax.serlet.list.show.dao.DepartmentsDao;
import com.javaweb.userajax.serlet.list.show.domain.Departments;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public class DepartmentsImpl extends DAO<Departments> implements DepartmentsDao {
@Override
public List<Departments> getAll(Integer locationId) {
String sql = "SELECT department_id departmentId, department_name departmentName, location_id locationId FROM departments WHERE location_id=?";
System.out.println(sql);
List<Departments> departmentsList = getList(sql, locationId);
return departmentsList;
}
}

EmployeesImpl:
package com.javaweb.userajax.serlet.list.show.daoimpl;
import com.javaweb.userajax.serlet.list.show.dao.DAO;
import com.javaweb.userajax.serlet.list.show.dao.EmployeesDao;
import com.javaweb.userajax.serlet.list.show.domain.Employees;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public class EmployessDaoImpl extends DAO<Employees> implements EmployeesDao {
@Override
public List<Employees> getAll(Integer departmentId) {
String sql = "SELECT employee_id employeeId, last_name lastName FROM employees WHERE department_id=?";
System.out.println(sql);
List<Employees> employeesList = getList(sql, departmentId);
return employeesList;
}
@Override
public Employees getEmp(Integer employeeId) {
String sql = "SELECT employee_id employeeId, department_id departmentId, last_name lastName, email FROM employees WHERE employee_id=?";
Employees employees = getValue(sql, employeeId);
return employees;
}
}

LocationsImpl:
package com.javaweb.userajax.serlet.list.show.daoimpl;
import com.javaweb.userajax.serlet.list.show.dao.DAO;
import com.javaweb.userajax.serlet.list.show.dao.LocationsDao;
import com.javaweb.userajax.serlet.list.show.domain.Locations;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public class LocationsDaoImpl extends DAO<Locations> implements LocationsDao {
@Override
public List<Locations> getAll() {
String sql = "SELECT location_id locationId, city FROM locations";
List<Locations> locationsList = getAll(sql);
return locationsList;
}
}
1. JSP 页面实现
  
  a. 我们先需要从 servlet 转发到 JSP 页面，将所有的 locations 信息封装在 request 中传回 jsp 页面在页面初始化的时候将所有 locations 信息显示
  
  index.jsp（案例所访问的页面，此页面直接跳转到 servlet，在 Servlet 中获取 location 信息后转发会显示页面）
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>ForwardPage</title>
<body>
<h3></h3>
<jsp:forward page="getCity.do"/>
</body>
</html>

getCity 方法（servlet 中利用反射处理多个请求，前面的博文已经讲述过，此方法将 location 信息封装到 request 中然后转发到 selectList.jsp 页面）
protected void getCity(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
LocationsDao locationsDao = new LocationsDaoImpl();
List<Locations> locationsList = locationsDao.getAll();
request.setAttribute("locations", locationsList);
request.getRequestDispatcher("/selectList/selectList.jsp").forward(request, response);
}

selectList.jsp （将转发回页面的 locations 信息显示）
<%--
Created by IntelliJ IDEA.
User: yin'zhao
Date: 2017/12/04
Time: 21:37
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>ShowInfoPage</title>
</head>
<body>
<spac>City: </spac>
<select name="citySelect" id="city">
<option value="choose">请选择</option>
<c:forEach items="${requestScope.locations}" var="location">
<option value="${location.locationId}">${location.city}</option>
</c:forEach>
</select>
<span> </span>
<span>Department: </span>
<select name="departmentSelect" id="department">
<option value="choose">请选择</option>
</select>
<span> </span>
<span>Employee: </span>
<select name="employeeSelect" id="employee">
<option value="choose">请选择</option>
</select>
<table cellspacing="0" border="1" cellpadding="10" id="table">
<tr>
<th>employeeId</th>
<th>lastName</th>
<th>email</th>
</tr>
<tr>
<td id="employeeId"></td>
<td id="lastName"></td>
<td id="email"></td>
</tr>
</table>
</body>
</html>

b. 为下拉框添加 change 事件，每当 locations 改变后根据 location_id 获取departments 信息，并将其显示在对应的下拉框中（信息的显示利用 Ajax，若所选择 location 没有departments 则提示对应的消息）

selectList.jsp （处理 Ajax 请求部分）
<script type="text/javascript" src="${pageContext.request.contextPath}/jquery-1.7.2.js"></script>
<script type="text/javascript">
$(function () {
$("#city").change(function () {
// 每次选择都将除了第一个的所有选项移除即更新操作
$("#department option:not(:first)").remove();
var url = "${pageContext.request.contextPath}/getDepartment.do";
// 获取选中的 value 值，locationId
var val = $(this).val();
// 如果所选值不是第一个那么就开始执行 Ajax 操作，并将所选择的location 的 id 传到 servlet 中
if (val != "choose") {
var args = {"time": new Date, "value": val};
$.getJSON(url, args, function (data) {
if (data == 0) {
// 若传回的数据为空则提示错误消息
alert("当前城市没有部门");
} else {
// 否则将其加入对应的下拉框中
for (var i = 0; i < data.length; i++) {
var departmentId = data[i].departmentId;
var departmentName = data[i].departmentName;
$("#department").append("<option value='" + departmentId + "'>" + departmentName + "</option>");
}
}
})
}
})
$("#department").change(function () {
$("#employee option:not(:first)").remove();
var url = "${pageContext.request.contextPath}/getEmployee.do";
// 获取选中的 value 值，departmentId
var val = $(this).val();
alert(val)
if (val != "choose") {
var args = {"time": new Date, "value": val};
$.getJSON(url, args, function (data) {
if (data == 0) {
alert("当前部门没有员工");
} else {
for (var i = 0; i < data.length; i++) {
var employeeId = data[i].employeeId;
var lastName = data[i].lastName;
$("#employee").append("<option value='" + employeeId + "'>" + lastName + "</option>");
}
}
})
}
})
</script>

getDepartments（Servlet 方法，根据所选择的 location 信息获取对应的 departments 信息）
protected void getDepartment(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer locationId = Integer.valueOf(request.getParameter("value"));
DepartmentsDao departmentsDao = new DepartmentsImpl();
List<Departments> departmentsList = departmentsDao.getAll(locationId);
ObjectMapper mapper = new ObjectMapper();
将返回值转换为 json 格式
String result = mapper.writeValueAsString(departmentsList);
response.setContentType("text/javascript");
response.getWriter().print(result);
}

getEmployees （Servlet 方法，根据所选择的 departments 信息获取对应的 employees 列表）
protected void getEmployee(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer departmentId = Integer.valueOf(request.getParameter("value"));
EmployeesDao departmentsDao = new EmployessDaoImpl();
List<Employees> employeesList = departmentsDao.getAll(departmentId);
ObjectMapper mapper = new ObjectMapper();
String result = mapper.writeValueAsString(employeesList);
response.setContentType("text/javascript");
response.getWriter().print(result);
}

c. 选择对应的 employee 将其信息打印在页面（在这里我将完成的 jsp 页面和 servlet 代码粘贴在这里）

selectList.jsp
<%--
Created by IntelliJ IDEA.
User: yin'zhao
Date: 2017/12/04
Time: 21:37
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>ShowInfoPage</title>
<script type="text/javascript" src="${pageContext.request.contextPath}/jquery-1.7.2.js"></script>
<script type="text/javascript">
$(function () {
/*
* 刚开始将用于 employees 显示的表格隐藏
* */
$("#table").hide();
$("#city").change(function () {
$("#department option:not(:first)").remove();
var url = "${pageContext.request.contextPath}/getDepartment.do";
// 获取选中的 value 值，locationId
var val = $(this).val();
if (val != "choose") {
var args = {"time": new Date, "value": val};
$.getJSON(url, args, function (data) {
if (data == 0) {
alert("当前城市没有部门");
} else {
for (var i = 0; i < data.length; i++) {
var departmentId = data[i].departmentId;
var departmentName = data[i].departmentName;
$("#department").append("<option value='" + departmentId + "'>" + departmentName + "</option>");
}
}
})
}
})
$("#department").change(function () {
$("#employee option:not(:first)").remove();
var url = "${pageContext.request.contextPath}/getEmployee.do";
// 获取选中的 value 值，departmentId
var val = $(this).val();
alert(val)
if (val != "choose") {
var args = {"time": new Date, "value": val};
$.getJSON(url, args, function (data) {
if (data == 0) {
alert("当前部门没有员工");
} else {
for (var i = 0; i < data.length; i++) {
var employeeId = data[i].employeeId;
var lastName = data[i].lastName;
$("#employee").append("<option value='" + employeeId + "'>" + lastName + "</option>");
}
}
})
}
})
$("#employee").change(function () {
var url = "${pageContext.request.contextPath}/showEmployee.do";
// 获取选中的 value 值，departmentId
var val = $(this).val();
if (val != "choose") {
var args = {"time": new Date, "value": val};
$.getJSON(url, args, function (data) {
var employeeId = data.employeeId;
var lastName = data.lastName;
var email = data.email;
// 如果所选择的为 employees 而不是第一行，那么就将表格显示，并加入数据
$("#table").show();
$("#employeeId").text(employeeId);
$("#lastName").text(lastName);
$("#email").text(email);
})
} else {
// 若选择的不是 employees 则将其隐藏
$("#table").hide();
}
})
})
</script>
</head>
<body>
<spac>City: </spac>
<select name="citySelect" id="city">
<option value="choose">请选择</option>
<c:forEach items="${requestScope.locations}" var="location">
<option value="${location.locationId}">${location.city}</option>
</c:forEach>
</select>
<span> </span>
<span>Department: </span>
<select name="departmentSelect" id="department">
<option value="choose">请选择</option>
</select>
<span> </span>
<span>Employee: </span>
<select name="employeeSelect" id="employee">
<option value="choose">请选择</option>
</select>
/*
将 employees 的详细信息打印在此表格中
*/
<table cellspacing="0" border="1" cellpadding="10" id="table">
<tr>
<th>employeeId</th>
<th>lastName</th>
<th>email</th>
</tr>
<tr>
<td id="employeeId"></td>
<td id="lastName"></td>
<td id="email"></td>
</tr>
</table>
</body>
</html>

ShowInfoServlet.java
package com.javaweb.userajax.serlet.list.show.servlet;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.javaweb.userajax.serlet.list.show.dao.DepartmentsDao;
import com.javaweb.userajax.serlet.list.show.dao.EmployeesDao;
import com.javaweb.userajax.serlet.list.show.dao.LocationsDao;
import com.javaweb.userajax.serlet.list.show.daoimpl.DepartmentsImpl;
import com.javaweb.userajax.serlet.list.show.daoimpl.EmployessDaoImpl;
import com.javaweb.userajax.serlet.list.show.daoimpl.LocationsDaoImpl;
import com.javaweb.userajax.serlet.list.show.domain.Departments;
import com.javaweb.userajax.serlet.list.show.domain.Employees;
import com.javaweb.userajax.serlet.list.show.domain.Locations;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
/**
* Created by shkstart on 2017/12/06.
*/
public class ShowInfoServlet extends HttpServlet {
// 获取请求的参数，利用反射执行当前类中对应的方法
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String servletPath = request.getServletPath();
String methodName = servletPath.substring(1, servletPath.length() - 3);
try {
Method method = getClass().getDeclaredMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
method.invoke(this, request, response);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void getCity(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
LocationsDao locationsDao = new LocationsDaoImpl();
List<Locations> locationsList = locationsDao.getAll();
request.setAttribute("locations", locationsList);
request.getRequestDispatcher("/selectList/selectList.jsp").forward(request, response);
}
protected void getDepartment(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer locationId = Integer.valueOf(request.getParameter("value"));
DepartmentsDao departmentsDao = new DepartmentsImpl();
List<Departments> departmentsList = departmentsDao.getAll(locationId);
ObjectMapper mapper = new ObjectMapper();
String result = mapper.writeValueAsString(departmentsList);
response.setContentType("text/javascript");
response.getWriter().print(result);
}
protected void getEmployee(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer departmentId = Integer.valueOf(request.getParameter("value"));
EmployeesDao departmentsDao = new EmployessDaoImpl();
List<Employees> employeesList = departmentsDao.getAll(departmentId);
ObjectMapper mapper = new ObjectMapper();
String result = mapper.writeValueAsString(employeesList);
response.setContentType("text/javascript");
response.getWriter().print(result);
}
protected void showEmployee(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer employeeId = Integer.valueOf(request.getParameter("value"));
EmployeesDao employeesDao = new EmployessDaoImpl();
Employees employees = employeesDao.getEmp(employeeId);
ObjectMapper mapper = new ObjectMapper();
String result = mapper.writeValueAsString(employees);
System.out.println(result);
response.setContentType("text/javascript");
response.getWriter().print(result);
}
}

以上就是我这次博文的内容，如果哪位读者发现错误以及表述不正确的地方还望指出，谢谢！

码农公寓

相关文章