【JavaWeb-jQuery】网站 --- 应用 Ajax，jQuery 实现级联查询
网站功能：
- 网页打开的时候，自动加载省份信息，用户通过选择不同的省份列表值，对应的城市信息也随之变化。
网站结构：
网站预览：
建表语句：
CREATE TABLE `pro` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`jiancheng` varchar(255) DEFAULT NULL,
`shenghui` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `pro` VALUES ('1','河北','冀','石家庄');
INSERT INTO `pro` VALUES ('2','山西','晋','太原市');
INSERT INTO `pro` VALUES ('3','内蒙古','蒙','呼和浩特市');
INSERT INTO `pro` VALUES ('4','辽宁','辽','沈阳');
INSERT INTO `pro` VALUES ('5','江苏','苏','南京');
INSERT INTO `pro` VALUES ('6','浙江','浙','杭州');
INSERT INTO `pro` VALUES ('7','安徽','皖','合肥');
INSERT INTO `pro` VALUES ('8','福建','闽','福州');
INSERT INTO `pro` VALUES ('9','江西','赣','南昌');
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`provinceid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `city` VALUES ('1', '石家庄市', '1');
INSERT INTO `city` VALUES ('2', '秦皇岛', '1');
INSERT INTO `city` VALUES ('3', '保定市', '1');
INSERT INTO `city` VALUES ('4', '张家口', '1');
INSERT INTO `city` VALUES ('5', '南昌市', '9');
INSERT INTO `city` VALUES ('6', '九江市', '9');
INSERT INTO `city` VALUES ('7', '宜春市', '9');
INSERT INTO `city` VALUES ('8', '福州市', '8');
INSERT INTO `city` VALUES ('9', '厦门市', '8');
INSERT INTO `city` VALUES ('10', '泉州市', '8');
INSERT INTO `city` VALUES ('11', '龙岩市', '8');
INSERT INTO `city` VALUES ('12', '太原', '2');
INSERT INTO `city` VALUES ('13', '大同', '2');
INSERT INTO `city` VALUES ('14', '呼和浩特', '3');
INSERT INTO `city` VALUES ('15', '包头', '3');
INSERT INTO `city` VALUES ('16', '呼伦贝尔', '3');
index.jsp（网站欢迎资源文件）：
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>省市级联查询</title>
<script type="text/javascript" src="js/jquery-3.6.0.js"></script>
<script type="text/javascript">
//自定义函数，用于加载省份数据
function getData() {
$.ajax({
url: "queryProvince",
dataType: "json",
success: function (resp) {
//删除旧的省份select子对象
$("#province").empty();
//保留“--请选择--”子对象
$("#province").append("<option value=''>" + "--请选择--" + "</option>");
//将省份名称打到省份select上
$.each(resp, function (i, n) {
$("#province").append("<option value='" + n.provinceId + "'>" + n.provinceName + "</option>");
});
}
});
}
//自定义函数，用于处理服务端返回的数据
function callBack(resp) {
//清空旧的省份列表子对象
$("#city").empty();
$.each(resp,function (i, n) {
$("#city").append("<option value '" + n.cityId + "'>" + n.cityName + "</option>");
});
}
$(function () {
//加载省份信息
getData();
//省份列表选中值改变时，调用的方法：
$("#province").change(function () {
//获取已被选中的列表值
var id = $("#province>option:selected").val();
//以 get 方式向服务端发送请求
$.get("queryCity", {provinceId: id}, callBack, "json");
});
})
</script>
</head>
<body>
<p>省市级联查询，使用 Ajax，jQuery</p>
<table border="1px">
<tr>
<td>省份：</td>
<td>
<select id="province">
<option value="">--请选择--</option>
</select>
</td>
</tr>
<tr>
<td>城市：</td>
<td>
<select id="city">
<option value="">--请选择--</option>
</select>
</td>
</tr>
</table>
</body>
</html>
com.burnyouth.util.JdbcUtil（JDBC工具类）：
package com.burnyouth.util;
import java.sql.*;
public class JdbcUtil {
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private JdbcUtil() {
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/burning_youth"
, "root", "888");
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
com.burnyouth.entity.Province（省份实体类）：
package com.burnyouth.entity;
public class Province {
private Integer provinceId;
private String provinceName;
public Province() {
}
public Integer getProvinceId() {
return provinceId;
}
public void setProvinceId(Integer provinceId) {
this.provinceId = provinceId;
}
public String getProvinceName() {
return provinceName;
}
public void setProvinceName(String provinceName) {
this.provinceName = provinceName;
}
}
com.burnyouth.entity.City（城市实体类）：
package com.burnyouth.entity;
public class Province {
private Integer provinceId;
private String provinceName;
private String jianCheng;
private String shengHui;
public Province() {
}
public Province(Integer provinceId, String provinceName, String jianCheng, String shengHui) {
this.provinceId = provinceId;
this.provinceName = provinceName;
this.jianCheng = jianCheng;
this.shengHui = shengHui;
}
public Integer getProvinceId() {
return provinceId;
}
public void setProvinceId(Integer provinceId) {
this.provinceId = provinceId;
}
public String getProvinceName() {
return provinceName;
}
public void setProvinceName(String provinceName) {
this.provinceName = provinceName;
}
public String getJianCheng() {
return jianCheng;
}
public void setJianCheng(String jianCheng) {
this.jianCheng = jianCheng;
}
public String getShengHui() {
return shengHui;
}
public void setShengHui(String shengHui) {
this.shengHui = shengHui;
}
}
com.burnyouth.dao.QueryProvinceDao（连接数据库，进行查询操作并返回数据）：
package com.burnyouth.dao;
import com.burnyouth.entity.City;
import com.burnyouth.entity.Province;
import com.burnyouth.util.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class QueryProvinceDao {
//查询数据中所有省份
public List<Province> queryProvince(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
List<Province> provinces = new ArrayList<>();
try {
connection = JdbcUtil.getConnection();
//按照id升序输出
String sql = "select * from pro order by id";
ps = connection.prepareStatement(sql);
resultSet = ps.executeQuery();
while (resultSet.next()) {
Province province = new Province();
province.setProvinceId(resultSet.getInt("id"));
province.setProvinceName(resultSet.getString("name"));
provinces.add(province);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtil.close(connection,ps,resultSet);
}
return provinces;
}
//查询省份对应的所有城市
public List<City> queryCity(Integer provinceId){
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
City city = null;
List<City> citys = new ArrayList<>();
try {
connection = JdbcUtil.getConnection();
String sql = "select * from city where provinceid=?";
ps = connection.prepareStatement(sql);
ps.setInt(1,provinceId);
resultSet = ps.executeQuery();
while (resultSet.next()) {
city = new City();
city.setCityId(resultSet.getInt("id"));
city.setCityName(resultSet.getString("name"));
citys.add(city);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtil.close(connection,ps,resultSet);
}
return citys;
}
}
xml：
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="https://jakarta.ee/xml/ns/jakartaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://jakarta.ee/xml/ns/jakartaee https://jakarta.ee/xml/ns/jakartaee/web-app_5_0.xsd"
version="5.0">
<servlet>
<servlet-name>QueryProvinceServlet</servlet-name>
<servlet-class>com.burnyouth.controller.QueryProvinceServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>QueryCityServlet</servlet-name>
<servlet-class>com.burnyouth.controller.QueryCityServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>QueryProvinceServlet</servlet-name>
<url-pattern>/queryProvince</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>QueryCityServlet</servlet-name>
<url-pattern>/queryCity</url-pattern>
</servlet-mapping>
<!--设置一下欢迎资源文件-->
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
com.burnyouth.controller.QueryProvinceServlet（返回 json 格式的省份信息）：
package com.burnyouth.controller;
import com.burnyouth.dao.QueryProvinceDao;
import com.burnyouth.entity.Province;
import com.fasterxml.jackson.databind.ObjectMapper;
import jakarta.servlet.*;
import jakarta.servlet.http.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
public class QueryProvinceServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String json = "";
QueryProvinceDao dao = new QueryProvinceDao();
List<Province> provinces = new ArrayList<>();
PrintWriter out = null;
provinces = dao.queryProvince();
//将list转为json格式的字符串
if (provinces != null) {
//调用Jackson工具库，实现List ---> json
ObjectMapper objectMapper = new ObjectMapper();
json = objectMapper.writeValueAsString(provinces);
}
//使客户端浏览器使用正确的编辑器解读数据
response.setContentType("application/json;charset=utf-8");
out = response.getWriter();
//输出json数据
out.write(json);
out.flush();
out.close();
}
}
com.burnyouth.controller.QueryCityServlet（根据客户端发送过来的省份 id ，返回对应的城市信息【json 格式】）：
package com.burnyouth.controller;
import com.burnyouth.dao.QueryProvinceDao;
import com.burnyouth.entity.City;
import com.burnyouth.entity.Province;
import com.fasterxml.jackson.databind.ObjectMapper;
import jakarta.servlet.*;
import jakarta.servlet.http.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
public class QueryCityServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String json = "";
//获取省份id
String provinceId = request.getParameter("provinceId");
QueryProvinceDao dao = new QueryProvinceDao();
List<City> cities = new ArrayList<>();
PrintWriter out = null;
cities = dao.queryCity(Integer.valueOf(provinceId));
//将list转为json格式的字符串
if (provinceId != null && !"".equals(provinceId.trim())) {
//调用Jackson工具库，实现List ---> json
ObjectMapper objectMapper = new ObjectMapper();
json = objectMapper.writeValueAsString(cities);
}
response.setContentType("application/json;charset=utf-8");
out = response.getWriter();
//输出json数据
out.write(json);
out.flush();
out.close();
}
}
