操作数据库和前端页面:
1、在数据库中建表
(1)省province
(2)市city
(3)区area
2、创建DB_Helper连接数据库
package cn.su.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DB_Helper {
public static final String URL = "jdbc:mysql://localhost:3306/school?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
public static final String USERNAME = "root";
public static final String PASSWORD = "123456";
// 定义数据库的连接对象
public static Connection conn;
// 数据库连接对象
public static Connection getConn(){
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
try {
// 驱动管理得到数据库的连接并返回一个数据库对象
conn= DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
// 关闭数据库连接
public static void closeConn(Connection conn){
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
System.out.println(DB_Helper.getConn());
}
}
3、前端页面
<%--
Created by IntelliJ IDEA.
User: manager
Date: 2021/3/3
Time: 11:03
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>省市区三级联动</title>
<script src="jquery/jquery-3.4.1.js"></script>
<%--省--%>
<script>
$(function () {
$.ajax({
type: "get",
url: "findprovince",
dataType: "json",
success: function (data) {
var obj = $("#province");
for (var i = 0; i < data.length; i++) {
var ob = "<option value='" + data[i].pid + "'>" + data[i].pname + "</option>";
obj.append(ob);
}
}
})
})
</script>
</head>
<body>
<select name="province" id="province">
<option value="0">请选择</option>
</select>省
<select name="city" id="city">
<option value="0">请选择</option>
</select>市
<select name="area" id="area">
<option value="0">请选择</option>
</select>县
<%--市--%>
<script>
function city() {
$(function () {
$("#city option").remove();
$.ajax({
type: "get",
async: false,
url: "findcity?pid=" + $("#province").val(),
dataType: "json",
success: function (data) {
var obj = $("#city");
for (var i = 0; i < data.length; i++) {
var ob = "<option value='" + data[i].cid + "'>" + data[i].cname + "</option>";
obj.append(ob);
}
}
})
})
}
</script>
<%--区--%>
<script>
function area() {
$(function () {
$("#area option").remove();
$.ajax({
type: "get",
async: false,
url: "findarea?cid=" + $("#city").val(),
dataType: "json",
success: function (data) {
var obj = $("#area");
for (var i = 0; i < data.length; i++) {
var ob = "<option value='" + data[i].aid + "'>" + data[i].aname + "</option>";
obj.append(ob);
}
}
})
})
}
</script>
<script>
$("#province").change(function () {
city();
area();
});
$("#city").change(function () {
area();
});
</script>
</body>
</html>
一、省:
1、创建省的实体类
package cn.su.domain;
// 省的实体类
public class Province {
private int pid;
private String pname;
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public Province(int pid, String pname) {
this.pid = pid;
this.pname = pname;
}
public Province() {
}
}
2、创建搜索省的接口
package cn.su.dao;
import cn.su.domain.Province;
import java.util.List;
public interface ProvinceDao {
public List<Province> FindAll();
}
3、创建搜索省接口的实现类
package cn.su.dao.impl;
import cn.su.dao.ProvinceDao;
import cn.su.domain.Province;
import cn.su.util.DB_Helper;
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 ProvinceImpl implements ProvinceDao {
@Override
public List<Province> FindAll() {
List<Province> lists = new ArrayList<Province>();
// 1、获得数据库的连接对象
Connection conn = DB_Helper.getConn();
// 2、书写SQL语句
String sql = "select * from province";
try {
// 3、预编译SQL语句
PreparedStatement ps = conn.prepareStatement(sql);
// 4、执行查询命令
ResultSet rs = ps.executeQuery();
// 5、循环读取结果集中的数据
while (rs.next()){
Province province = new Province();
province.setPid(rs.getInt(1));
province.setPname(rs.getString(2));
lists.add(province);
}
} catch (SQLException e) {
e.printStackTrace();
}
return lists;
}
}
4、创建搜索省的servlet
package cn.su.servlet;
import cn.su.dao.ProvinceDao;
import cn.su.dao.impl.ProvinceImpl;
import cn.su.domain.Province;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/findprovince")
public class FindProvince extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 设置编码格式
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
ProvinceDao pi = new ProvinceImpl();
List<Province> lists = pi.FindAll();
response.getWriter().write(JSONObject.toJSONString(lists));
this.doPost(request, response);
}
}
二、市:
1、创建市的实体类
package cn.su.domain;
// 市的实体类
public class City {
private int cid;
private String cname;
private int pid;
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
// 定义有参的构造方法
public City(int cid, String cname, int pid) {
this.cid = cid;
this.cname = cname;
this.pid = pid;
}
public City() {
}
}
2、创建搜索市的接口
package cn.su.dao;
import cn.su.domain.City;
import java.util.List;
public interface CityDao {
public List<City> FindAllCity(int pid);
}
3、创建搜索市接口的实现类
package cn.su.dao.impl;
import cn.su.dao.CityDao;
import cn.su.domain.City;
import cn.su.util.DB_Helper;
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 CityImpl implements CityDao {
@Override
public List<City> FindAllCity(int pid) {
List<City> lists = new ArrayList<City>();
// 1、获得数据库的连接对象
Connection conn = DB_Helper.getConn();
// 2、书写SQL数据
String sql = "select * from city where pid=?";
try {
// 预编译sql语句
PreparedStatement ps = conn.prepareStatement(sql);
// 占位符赋值
ps.setInt(1,pid);
// 执行查询命令
ResultSet rs = ps.executeQuery();
// 循环读取结果集中的数据
while (rs.next()){
City city = new City();
city.setCid(rs.getInt(1));
city.setCname(rs.getString(2));
city.setPid(rs.getInt(3));
lists.add(city);
}
} catch (SQLException e) {
e.printStackTrace();
}
return lists;
}
}
4、创建搜索市的servlet
package cn.su.servlet;
import cn.su.dao.CityDao;
import cn.su.dao.impl.CityImpl;
import cn.su.domain.City;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/findcity")
public class FindCity extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String pid = request.getParameter("pid");
CityDao city = new CityImpl();
List<City> cities = city.FindAllCity(Integer.parseInt(pid));
response.getWriter().write(JSONObject.toJSONString(cities));
this.doPost(request, response);
}
}
三、区:
1、创建区的实体类
package cn.su.domain;
public class Area {
private int aid;
private String aname;
private int cid;
public int getAid() {
return aid;
}
public void setAid(int aid) {
this.aid = aid;
}
public String getAname() {
return aname;
}
public void setAname(String aname) {
this.aname = aname;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public Area(int aid, String aname, int cid) {
this.aid = aid;
this.aname = aname;
this.cid = cid;
}
public Area() {
}
}
2、创建搜索区的接口
package cn.su.dao;
import cn.su.domain.Area;
import java.util.List;
public interface AreaDao {
public List<Area> FindAllArea(int cid);
}
3、创建搜索区接口的实现类
package cn.su.dao.impl;
import cn.su.dao.AreaDao;
import cn.su.domain.Area;
import cn.su.util.DB_Helper;
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 AreaImpl implements AreaDao {
@Override
public List<Area> FindAllArea(int cid) {
List<Area> areas = new ArrayList<Area>();
// 1、获得数据库的连接对象
Connection conn = DB_Helper.getConn();
// 2、书写SQL语句
String sql = "select * from area where cid=?";
try {
// 3、预编译SQL语句
PreparedStatement ps = conn.prepareStatement(sql);
// 4、占位符赋值
ps.setInt(1,cid);
// 5、执行查询命令
ResultSet rs = ps.executeQuery();
// 循环读取结果集中的数据
while (rs.next()){
Area area = new Area();
area.setAid(rs.getInt(1));
area.setAname(rs.getString(2));
area.setCid(rs.getInt(3));
areas.add(area);
}
} catch (SQLException e) {
e.printStackTrace();
}
return areas;
}
}
4、创建搜索区的servlet
package cn.su.servlet;
import cn.su.dao.impl.AreaImpl;
import cn.su.domain.Area;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/findarea")
public class FindArea extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String cid = request.getParameter("cid");
AreaImpl area = new AreaImpl();
List<Area> areas = area.FindAllArea(Integer.parseInt(cid));
response.getWriter().write(JSONObject.toJSONString(areas));
this.doPost(request, response);
}
}
结果如下: