数据库设计
CREATE TABLE tab_province(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(11) UNIQUE
);
CREATE TABLE tab_city(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(11) UNIQUE,
pid INT,
CONSTRAINT fk_111 FOREIGN KEY(pid) REFERENCES tab_province(pid)
);
INSERT INTO tab_province VALUES(1,"河南");
INSERT INTO tab_province VALUES(2,"北京");
INSERT INTO tab_province VALUES(3,"天津");
INSERT INTO tab_province VALUES(4,"云南");
INSERT INTO tab_province VALUES(5,"河北");
INSERT INTO tab_city VALUES(1000,"郑州",1),(NULL,"汝州",1),(NULL,"濮阳",1),(NULL,"洛阳",1),(NULL,"信阳",1),(NULL,"南阳",1);
INSERT INTO tab_city VALUES(2000,"朝阳区",2),(NULL,"海淀区",2),(NULL,"昌平区",2),(NULL,"通州区",2),(NULL,"顺义区",2),(NULL,"丰台区",2);
INSERT INTO tab_city VALUES(3000,"塘沽区",3),(NULL,"东丽区",3),(NULL,"西青区",3),(NULL,"静海区",3),(NULL,"红桥区",3),(NULL,"武清区",3);
INSERT INTO tab_city VALUES(4000,"昆明",4),(NULL,"大理",4),(NULL,"丽江",4),(NULL,"西双版纳",4),(NULL,"香格里拉",4),(NULL,"玉溪",4);
INSERT INTO tab_city VALUES(5000,"石家庄",5),(NULL,"廊坊",5),(NULL,"白洋淀",5),(NULL,"衡水",5),(NULL,"保定",5),(NULL,"唐山",5);
实体类
public class City implements Serializable {
private Integer cid;
private String cname;
private Integer pid;
}
public class Province implements Serializable {
private Integer pid;
private String pname;
}
jar
dao
public class CityDao {
/**
* 根据省份 id获取该城市
*
* @param pid 省份id
* @return
*/
public List<City> getAllByProvinceId(int pid) {
try (Connection connection = JDBCUtils.getConnection()) {
QueryRunner runner = new QueryRunner();
BeanListHandler<City> beanListHandler = new BeanListHandler<>(City.class);
String sql = "select * from tab_city where pid=?";
return runner.query(connection, sql, beanListHandler, pid);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
public class ProvinceDao {
/**
* 获取所有城市
*
* @return
*/
public List<Province> getAll() {
try (Connection connection = JDBCUtils.getConnection()) {
QueryRunner runner = new QueryRunner();
String sql = "select * from tab_province";
BeanListHandler<Province> beanListHandler = new BeanListHandler<>(Province.class);
return runner.query(connection, sql, beanListHandler);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 根据 省名获取省
*
* @param pname
* @return
*/
public Province getOneByName(String pname) {
try (Connection connection = JDBCUtils.getConnection()) {
QueryRunner runner = new QueryRunner();
String sql = "select * from tab_province where pname=?";
BeanHandler<Province> beanHandler = new BeanHandler<>(Province.class);
return runner.query(connection, sql, beanHandler, pname);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
servlet
@WebServlet("/city/QueryProvinceName")
public class CityQueryOneServlet extends HttpServlet {
private CityDao dao = new CityDao();
private ProvinceDao pdao = new ProvinceDao();
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
String pname = request.getParameter("pname");
Province province = pdao.getOneByName(pname);
List<City> list = dao.getAllByProvinceId(province.getPid());
String json = JSON.toJSONString(list);
response.getWriter().print(json);
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
@WebServlet("/province/getAll")
public class ProvinceGetAllServlet extends HttpServlet {
private ProvinceDao dao = new ProvinceDao();
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//这里如果不设置响应头会出现乱码！！！
response.setContentType("text/html;charset=utf-8");
List<Province> list = dao.getAll();
String json = JSON.toJSONString(list);
System.out.println(json);
response.getWriter().print(json);
}
}
页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
<script src="${pageContext.request.contextPath}/js/jquery-2.1.0.min.js"></script>
</head>
<body>
请选择你的地址：<select name="province">
<option>请选择省份</option>
</select>
<select name="city">
<option>请选择城市</option>
</select><br/>
</body>
<script>
//页面打开 通过ajax获取所有的身份 并拼凑 option，添加第一个 select中
$(function () {
let province = $("select[name='province']"); //获取第一个下拉框
let city = $("select[name='city']"); //获取第二个下拉框
$.ajax({
url: "${pageContext.request.contextPath}/province/getAll",
dataType: "json",
type: "get",
success: function (data) {
data.forEach(e => {
province.append($("<option value=" + e.pname + ">" + e.pname + "</option>"));
})
},
error: function (data) {
console.log("error" + data)
}
});
//给 select name='province' 添加change事件
province.bind("change", function () {
if (province.val()) {
//获取省份的名字
var pname = province.val();
console.log("pname", pname)
//remove() 方法删除被选元素及其子元素。
//empty() 方法删除被选元素的子元素。
$("select[name='city'] option:gt(0)").remove();
//通过 Ajax获取当前省份对应的城市 并写入第二个select中
$.ajax({
url: "${pageContext.request.contextPath}/city/QueryProvinceName",
dataType: "json",
type: "post",
data: {"pname": pname},
success: function (data) {
console.log("succ", data)
data.forEach(e => {
city.append($("<option value=" + e.cname + ">" + e.cname + "</option>"))
});
}
})
}
})
})
</script>
</html>
