基于Servlet、JSP、MySQL实现省市区联动下拉框遇阻求助

首先，我先分析你当前代码存在的几个核心问题，然后给出两种实现方案：一种是修复现有同步提交的问题，另一种是更优的AJAX无刷新联动方案。

当前代码的核心问题 #

• JSP中直接嵌入JDBC代码，耦合度极高，难以维护，且未关闭数据库连接，会导致连接池耗尽
• 选择国家时提交整个表单，用户体验差，且未实现城市的联动逻辑
• 当request.getParameter("country")为空时，Integer.parseInt会抛出NumberFormatException
• 异常处理简单粗暴，直接打印堆栈信息到页面，对用户不友好

方案一：修复现有同步提交逻辑（快速临时解决） #

如果你暂时不想重构代码，可以先做以下修改来解决错误并实现完整联动：

修改index.jsp中的国家、州、城市下拉框部分 #

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> 
<%@ page import="java.sql.*" %> 
<!DOCTYPE html> 
<html> 
<head> 
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> 
    <title>Registration</title> 
    <style> th { text-align: left } </style> 
</head> 
<body> 
<fieldset > 
<legend>Registration Form</legend> 
<div class="ex"> 
<form action="RegistrationController" method="post">
<table border="1" align="center" width="50%" cellpadding="5" cellspacing="6">
<!-- 其他表单字段保持不变 -->

<% 
// 处理选中的国家ID，避免空指针
String selectedCountryStr = request.getParameter("country");
int selectedCountryId = 0;
if(selectedCountryStr != null && !selectedCountryStr.isEmpty()){
    try{
        selectedCountryId = Integer.parseInt(selectedCountryStr);
    }catch(NumberFormatException e){
        selectedCountryId = 0;
    }
}

// 处理选中的州ID
String selectedStateStr = request.getParameter("state");
int selectedStateId = 0;
if(selectedStateStr != null && !selectedStateStr.isEmpty()){
    try{
        selectedStateId = Integer.parseInt(selectedStateStr);
    }catch(NumberFormatException e){
        selectedStateId = 0;
    }
}
%>

<tr> 
<th>Country</th> 
<td> 
<select name="country" id="country" onchange="this.form.submit();"> 
<option value="0">Select Country</option> 
<% try{ 
    String Query="select * from country"; 
    Class.forName("com.mysql.cj.jdbc.Driver"); // 注意最新驱动类名
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/chiru","root","admin123"); 
    Statement st = con.createStatement(); 
    ResultSet rs=st.executeQuery(Query); 
    while(rs.next()){ 
%> 
<option value="<%=rs.getInt("country_id")%>" <% if(rs.getInt("country_id") == selectedCountryId) { %>selected<% } %> ><%=rs.getString("country_name")%></option> 
<% 
    } 
    // 关闭资源，避免泄漏
    rs.close();
    st.close();
    con.close();
}catch (Exception ex){ 
    ex.printStackTrace(); 
} %> 
</select> 
</td> 
</tr> 

<tr> 
<th >State</th> 
<td> 
<select name="state" id="state" onchange="this.form.submit();"> 
<option value="0">Select State</option> 
<% try{ 
    if(selectedCountryId != 0){ // 只有选中国家才查询州
        String Query = "select * from state where country_id=?"; 
        Class.forName("com.mysql.cj.jdbc.Driver"); 
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/chiru","root","admin123"); 
        PreparedStatement pst = con.prepareStatement(Query); 
        pst.setInt(1, selectedCountryId); // 使用int类型参数，避免类型转换问题
        ResultSet rs = pst.executeQuery(); 
        while(rs.next()){ 
%> 
<option value="<%=rs.getInt("state_id")%>" <% if(rs.getInt("state_id") == selectedStateId) { %>selected<% } %> ><%=rs.getString("state_name")%></option> 
<% 
        } 
        rs.close();
        pst.close();
        con.close();
    }
}catch (Exception ex){ 
    ex.printStackTrace(); 
} %> 
</select> 
</td> 
</tr> 

<tr> 
<th>City</th> 
<td> 
<select name="city" id="city"> 
<option value="0">Select City</option> 
<% try{ 
    if(selectedStateId != 0){ // 只有选中州才查询城市
        String Query = "select * from city where state_id=?"; 
        Class.forName("com.mysql.cj.jdbc.Driver"); 
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/chiru","root","admin123"); 
        PreparedStatement pst = con.prepareStatement(Query); 
        pst.setInt(1, selectedStateId); 
        ResultSet rs = pst.executeQuery(); 
        while(rs.next()){ 
%> 
<option value="<%=rs.getInt("city_id")%>"><%=rs.getString("city_name")%></option> 
<% 
        } 
        rs.close();
        pst.close();
        con.close();
    }
}catch (Exception ex){ 
    ex.printStackTrace(); 
} %> 
</select> 
</td> 
</tr> 

<!-- 其他表单字段保持不变 -->
</table><br> 
<input type="submit" value="Register" style=" margin-left: 45%; padding: 10px; width: auto; background-color:Crimson; color:Cornsilk" /> 
</form> 
</div> 
</fieldset> 
</body> 
</html>

方案二：AJAX无刷新联动（推荐，用户体验更好） #

这种方案将数据库操作封装到后端，通过异步请求实现无刷新联动，代码结构更清晰。

1. 封装数据库工具类（DBUtil.java） #

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/chiru?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "admin123";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("JDBC驱动加载失败", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void closeResources(AutoCloseable... resources) {
        for (AutoCloseable res : resources) {
            if (res != null) {
                try {
                    res.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2. 封装位置服务类（LocationService.java） #

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 LocationService {
    public List<Country> getAllCountries() {
        List<Country> countries = new ArrayList<>();
        String query = "SELECT country_id, country_name FROM country";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(query);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Country country = new Country();
                country.setId(rs.getInt("country_id"));
                country.setName(rs.getString("country_name"));
                countries.add(country);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResources(rs, pstmt, conn);
        }
        return countries;
    }

    public List<State> getStatesByCountryId(int countryId) {
        List<State> states = new ArrayList<>();
        String query = "SELECT state_id, state_name FROM state WHERE country_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, countryId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                State state = new State();
                state.setId(rs.getInt("state_id"));
                state.setName(rs.getString("state_name"));
                states.add(state);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResources(rs, pstmt, conn);
        }
        return states;
    }

    public List<City> getCitiesByStateId(int stateId) {
        List<City> cities = new ArrayList<>();
        String query = "SELECT city_id, city_name FROM city WHERE state_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, stateId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                City city = new City();
                city.setId(rs.getInt("city_id"));
                city.setName(rs.getString("city_name"));
                cities.add(city);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResources(rs, pstmt, conn);
        }
        return cities;
    }

    // 实体类
    public static class Country {
        private int id;
        private String name;
        // getter和setter
        public int getId() { return id; }
        public void setId(int id) { this.id = id; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
    }

    public static class State {
        private int id;
        private String name;
        // getter和setter
        public int getId() { return id; }
        public void setId(int id) { this.id = id; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
    }

    public static class City {
        private int id;
        private String name;
        // getter和setter
        public int getId() { return id; }
        public void setId(int id) { this.id = id; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
    }
}

3. 修改RegistrationController.java，添加AJAX接口 #

import com.google.gson.Gson;
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("/RegistrationController")
public class RegistrationController extends HttpServlet {
    private LocationService locationService = new LocationService();
    private Gson gson = new Gson();

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");

        if ("getStates".equals(action)) {
            String countryIdStr = request.getParameter("countryId");
            if (countryIdStr != null && !countryIdStr.isEmpty()) {
                try {
                    int countryId = Integer.parseInt(countryIdStr);
                    List<LocationService.State> states = locationService.getStatesByCountryId(countryId);
                    response.getWriter().write(gson.toJson(states));
                } catch (NumberFormatException e) {
                    response.getWriter().write(gson.toJson(List.of()));
                }
            } else {
                response.getWriter().write(gson.toJson(List.of()));
            }
        } else if ("getCities".equals(action)) {
            String stateIdStr = request.getParameter("stateId");
            if (stateIdStr != null && !stateIdStr.isEmpty()) {
                try {
                    int stateId = Integer.parseInt(stateIdStr);
                    List<LocationService.City> cities = locationService.getCitiesByStateId(stateId);
                    response.getWriter().write(gson.toJson(cities));
                } catch (NumberFormatException e) {
                    response.getWriter().write(gson.toJson(List.of()));
                }
            } else {
                response.getWriter().write(gson.toJson(List.of()));
            }
        } else {
            // 加载初始页面，传递国家列表
            List<LocationService.Country> countries = locationService.getAllCountries();
            request.setAttribute("countries", countries);
            request.getRequestDispatcher("/index.jsp").forward(request, response);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 处理注册表单提交逻辑
        // 获取表单参数并保存到数据库
        request.getRequestDispatcher("/home.jsp").forward(request, response);
    }
}

4. 修改index.jsp，实现AJAX联动 #

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.List" %>
<%@ page import="com.yourpackage.LocationService" %>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Registration</title>
    <style> th { text-align: left } </style>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <script>
        $(document).ready(function() {
            // 国家选择变化时加载州
            $("#country").change(function() {
                var countryId = $(this).val();
                if (countryId === "0") {
                    $("#state").html('<option value="0">Select State</option>');
                    $("#city").html('<option value="0">Select City</option>');
                    return;
                }
                $.ajax({
                    url: "RegistrationController?action=getStates",
                    type: "GET",
                    data: { countryId: countryId },
                    dataType: "json",
                    success: function(states) {
                        var options = '<option value="0">Select State</option>';
                        $.each(states, function(index, state) {
                            options += `<option value="${state.id}">${state.name}</option>`;
                        });
                        $("#state").html(options);
                        $("#city").html('<option value="0">Select City</option>');
                    },
                    error: function() {
                        $("#state").html('<option value="0">加载州失败</option>');
                    }
                });
            });

            // 州选择变化时加载城市
            $("#state").change(function() {
                var stateId = $(this).val();
                if (stateId === "0") {
                    $("#city").html('<option value="0">Select City</option>');
                    return;
                }
                $.ajax({
                    url: "RegistrationController?action=getCities",
                    type: "GET",
                    data: { stateId: stateId },
                    dataType: "json",
                    success: function(cities) {
                        var options = '<option value="0">Select City</option>';
                        $.each(cities, function(index, city) {
                            options += `<option value="${city.id}">${city.name}</option>`;
                        });
                        $("#city").html(options);
                    },
                    error: function() {
                        $("#city").html('<option value="0">加载城市失败</option>');
                    }
                });
            });
        });
    </script>
</head>
<body>
<fieldset>
<legend>Registration Form</legend>
<div class="ex">
<form action="RegistrationController" method="post">
<table border="1" align="center" width="50%" cellpadding="5" cellspacing="6">
<!-- 其他表单字段保持不变 -->

<tr>
<th>Country</th>
<td>
<select name="country" id="country">
<option value="0">Select Country</option>
<% 
List<LocationService.Country> countries = (List<LocationService.Country>) request.getAttribute("countries");
for (LocationService.Country country : countries) {
%>
<option value="<%= country.getId() %>"><%= country.getName() %></option>
<% } %>
</select>
</td>
</tr>

<tr>
<th>State</th>
<td>
<select name="state" id="state">
<option value="0">Select State</option>
</select>
</td>
</tr>

<tr>
<th>City</th>
<td>
<