实现JavaWeb数据库动态加载全国
省市区三级联动可以按照以下步骤进行：
1. 创建
数据库表
首先需要创建
数据库表，包括省、市、区/县三个表，每个表都应该有一个唯一的ID、名称和父ID，用于建立三个表之间的关联关系。
2. 编写
JavaBean
然后需要编写对应的
JavaBean，用于存储从
数据库中查询到的省、市、区/县信息。
3. 编写DAO层
接着需要编写对应的DAO层，用于查询
数据库中的省、市、区/县信息。
4. 编写Servlet
然后需要编写一个Servlet，用于接收前端页面传来的请求，调用DAO层查询
数据库中的数据，并将结果返回给前端页面。
5. 编写前端页面
最后需要编写前端页面，用于展示省、市、区/县
三级联动的下拉框，并通过
Ajax调用Servlet获取
数据库中的数据。
具体
实现细节可以参考以下代码：
1.
数据库表结构
省表：
CREATE TABLE `province` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
市表：
CREATE TABLE `city` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`province_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_province_id` (`province_id`),
CONSTRAINT `fk_province_id` FOREIGN KEY (`province_id`) REFERENCES `province` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
区/县表：
CREATE TABLE `district` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`city_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_city_id` (`city_id`),
CONSTRAINT `fk_city_id` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.
JavaBean
省
JavaBean：
public class Province {
private int id;
private String name;
//省略getter和setter方法
}
市
JavaBean：
public class City {
private int id;
private String name;
private int provinceId;
//省略getter和setter方法
}
区/县
JavaBean：
public class District {
private int id;
private String name;
private int cityId;
//省略getter和setter方法
}
3. DAO层
省DAO：
public class ProvinceDao {
public List<Province> getAllProvinces() throws SQLException {
List<Province> provinces = new ArrayList<>();
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "SELECT * FROM province";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next()) {
Province province = new Province();
province.setId(rs.getInt("id"));
province.setName(rs.getString("name"));
provinces.add(province);
}
} finally {
DBUtil.close(conn, stmt, rs);
}
return provinces;
}
}
市DAO：
public class CityDao {
public List<City> getCitiesByProvinceId(int provinceId) throws SQLException {
List<City> cities = new ArrayList<>();
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "SELECT * FROM city WHERE province_id=?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, provinceId);
rs = stmt.executeQuery();
while (rs.next()) {
City city = new City();
city.setId(rs.getInt("id"));
city.setName(rs.getString("name"));
city.setProvinceId(provinceId);
cities.add(city);
}
} finally {
DBUtil.close(conn, stmt, rs);
}
return cities;
}
}
区/县DAO：
public class DistrictDao {
public List<District> getDistrictsByCityId(int cityId) throws SQLException {
List<District> districts = new ArrayList<>();
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "SELECT * FROM district WHERE city_id=?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, cityId);
rs = stmt.executeQuery();
while (rs.next()) {
District district = new District();
district.setId(rs.getInt("id"));
district.setName(rs.getString("name"));
district.setCityId(cityId);
districts.add(district);
}
} finally {
DBUtil.close(conn, stmt, rs);
}
return districts;
}
}
4. Servlet
省Servlet：
public class ProvinceServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
ProvinceDao provinceDao = new ProvinceDao();
List<Province> provinces = provinceDao.getAllProvinces();
String
json = new Gson().to
Json(provinces);
response.setContentType("application/
json");
response.setCharacterEncoding("UTF-8");
response.getWriter().write(
json);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
市Servlet：
public class CityServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int provinceId = Integer.parseInt(request.getParameter("provinceId"));
try {
CityDao cityDao = new CityDao();
List<City> cities = cityDao.getCitiesByProvinceId(provinceId);
String
json = new Gson().to
Json(cities);
response.setContentType("application/
json");
response.setCharacterEncoding("UTF-8");
response.getWriter().write(
json);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
区/县Servlet：
public class DistrictServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int cityId = Integer.parseInt(request.getParameter("cityId"));
try {
DistrictDao districtDao = new DistrictDao();
List<District> districts = districtDao.getDistrictsByCityId(cityId);
String
json = new Gson().to
Json(districts);
response.setContentType("application/
json");
response.setCharacterEncoding("UTF-8");
response.getWriter().write(
json);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5. 前端页面
省市区三级联动下拉框：
```
省：<select id="province">
<option value="">请选择省份</option>
</select>
市：<select id="city">
<option value="">请选择城市</option>
</select>
区/县：<select id="district">
<option value="">请选择区/县</option>
</select>
```
Ajax调用：
```
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script>
<script>
$(document).ready(function() {
$.
ajax({
type: "GET",
url: "ProvinceServlet",
success: function(data) {
var options = '';
for (var i = 0; i < data.length; i++) {
options += '<option value="' + data[i].id + '">' + data[i].name + '</option>';
}
$('#province').append(options);
},
error: function() {
alert('获取省份列表失败！');
}
});
$('#province').change(function() {
var provinceId = $(this).val();
$('#city').empty().append('<option value="">请选择城市</option>');
$('#district').empty().append('<option value="">请选择区/县</option>');
if (provinceId != '') {
$.
ajax({
type: "GET",
url: "CityServlet",
data: {
provinceId: provinceId
},
success: function(data) {
var options = '';
for (var i = 0; i < data.length; i++) {
options += '<option value="' + data[i].id + '">' + data[i].name + '</option>';
}
$('#city').append(options);
},
error: function() {
alert('获取城市列表失败！');
}
});
}
});
$('#city').change(function() {
var cityId = $(this).val();
$('#district').empty().append('<option value="">请选择区/县</option>');
if (cityId != '') {
$.
ajax({
type: "GET",
url: "DistrictServlet",
data: {
cityId: cityId
},
success: function(data) {
var options = '';
for (var i = 0; i < data.length; i++) {
options += '<option value="' + data[i].id + '">' + data[i].name + '</option>';
}
$('#district').append(options);
},
error: function() {
alert('获取区/县列表失败！');
}
});
}
});
});
</script>
```
1494
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
