基于JSP、Servlet、JQuery与非规范化MySQL表的联动动态下拉框实现

Alright, let's walk through building those dependent dropdowns using your tech stack and that denormalized table. First, since the table has duplicate values across columns (like multiple entries for "South America" and "Brazil"), we'll need to use DISTINCT in our SQL queries to avoid repeating options in the dropdowns.

1. Database Query Setup #

First, let's define the SQL queries we'll need for each dropdown level:

• Get all continents:

  SELECT DISTINCT `大洲` FROM your_table_name ORDER BY `大洲`;
  

• Get countries by continent:

  SELECT DISTINCT `国家` FROM your_table_name WHERE `大洲` = ? ORDER BY `国家`;
  

• Get states/provinces by country:

  SELECT DISTINCT `州/省` FROM your_table_name WHERE `国家` = ? ORDER BY `州/省`;
  

• Get cities by state/province:

  SELECT DISTINCT `城市` FROM your_table_name WHERE `州/省` = ? ORDER BY `城市`;
  

2. Servlet Implementation #

Create a servlet (let's call it DropdownServlet) to handle AJAX requests and return JSON data. We'll use JDBC for database interaction and Gson to convert our results to JSON (make sure to add Gson to your project dependencies).

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
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 com.google.gson.Gson;

@WebServlet("/DropdownServlet")
public class DropdownServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    // Database credentials (replace with your own)
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_db_name";
    private static final String DB_USER = "your_username";
    private static final String DB_PASS = "your_password";

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");
        
        String continent = request.getParameter("continent");
        String country = request.getParameter("country");
        String state = request.getParameter("state");
        
        List<String> options = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            
            if (continent != null) {
                // Get countries for selected continent
                String sql = "SELECT DISTINCT `国家` FROM your_table_name WHERE `大洲` = ? ORDER BY `国家`";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, continent);
                rs = stmt.executeQuery();
                while (rs.next()) {
                    options.add(rs.getString("国家"));
                }
            } else if (country != null) {
                // Get states for selected country
                String sql = "SELECT DISTINCT `州/省` FROM your_table_name WHERE `国家` = ? ORDER BY `州/省`";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, country);
                rs = stmt.executeQuery();
                while (rs.next()) {
                    options.add(rs.getString("州/省"));
                }
            } else if (state != null) {
                // Get cities for selected state
                String sql = "SELECT DISTINCT `城市` FROM your_table_name WHERE `州/省` = ? ORDER BY `城市`";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, state);
                rs = stmt.executeQuery();
                while (rs.next()) {
                    options.add(rs.getString("城市"));
                }
            } else {
                // Default: load all continents
                String sql = "SELECT DISTINCT `大洲` FROM your_table_name ORDER BY `大洲`";
                stmt = conn.prepareStatement(sql);
                rs = stmt.executeQuery();
                while (rs.next()) {
                    options.add(rs.getString("大洲"));
                }
            }
            
            // Convert list to JSON and send response
            Gson gson = new Gson();
            String json = gson.toJson(options);
            response.getWriter().write(json);
            
        } catch (Exception e) {
            e.printStackTrace();
            response.getWriter().write("[]"); // Return empty array on error
        } finally {
            // Close resources
            try { if (rs != null) rs.close(); } catch (Exception e) {}
            try { if (stmt != null) stmt.close(); } catch (Exception e) {}
            try { if (conn != null) conn.close(); } catch (Exception e) {}
        }
    }
}

3. JSP Frontend with jQuery #

Now create a JSP page that renders the dropdowns and uses jQuery to handle dynamic updates via AJAX.

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Dependent Dropdowns</title>
    <!-- Include jQuery (use a local copy or CDN) -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script>
        $(document).ready(function() {
            // Load continents on page load
            loadDropdownOptions('', '#continentDropdown');
            
            // Continent change event: load countries
            $('#continentDropdown').change(function() {
                var selectedContinent = $(this).val();
                loadDropdownOptions('continent=' + selectedContinent, '#countryDropdown');
                // Reset lower dropdowns when continent changes
                $('#stateDropdown').html('<option value="">Select State/Province</option>');
                $('#cityDropdown').html('<option value="">Select City</option>');
            });
            
            // Country change event: load states
            $('#countryDropdown').change(function() {
                var selectedCountry = $(this).val();
                loadDropdownOptions('country=' + selectedCountry, '#stateDropdown');
                // Reset city dropdown when country changes
                $('#cityDropdown').html('<option value="">Select City</option>');
            });
            
            // State change event: load cities
            $('#stateDropdown').change(function() {
                var selectedState = $(this).val();
                loadDropdownOptions('state=' + selectedState, '#cityDropdown');
            });
            
            // Helper function to load dropdown options via AJAX
            function loadDropdownOptions(params, dropdownId) {
                $.ajax({
                    url: 'DropdownServlet?' + params,
                    type: 'GET',
                    dataType: 'json',
                    success: function(options) {
                        // Clear existing options and add default
                        $(dropdownId).html('<option value="">Select...</option>');
                        // Add new options
                        $.each(options, function(index, value) {
                            $(dropdownId).append('<option value="' + value + '">' + value + '</option>');
                        });
                    },
                    error: function() {
                        $(dropdownId).html('<option value="">Error loading options</option>');
                    }
                });
            }
        });
    </script>
</head>
<body>
    <h3>Location Selection</h3>
    <div>
        <label>Continent:</label>
        <select id="continentDropdown">
            <option value="">Loading...</option>
        </select>
    </div>
    <div>
        <label>Country:</label>
        <select id="countryDropdown">
            <option value="">Select Continent First</option>
        </select>
    </div>
    <div>
        <label>State/Province:</label>
        <select id="stateDropdown">
            <option value="">Select Country First</option>
        </select>
    </div>
    <div>
        <label>City:</label>
        <select id="cityDropdown">
            <option value="">Select State/Province First</option>
        </select>
    </div>
</body>
</html>

4. Key Notes & Fixes #

• Denormalized Table Handling: The DISTINCT keyword is critical here to avoid duplicate options in dropdowns. Without it, you'd see repeated entries like "Brazil" multiple times.
• Error Handling: The servlet returns an empty JSON array on database errors, and the frontend shows an error message—you can expand this with more user-friendly alerts if needed.
• Dependency Setup: Make sure to include the MySQL JDBC driver and Gson library in your project's classpath (or add them as Maven/Gradle dependencies if using build tools).
• Encoding: We set UTF-8 in both the servlet and JSP to handle non-English characters correctly (like the Brazilian state names in your example).

内容的提问来源于stack exchange，提问作者Amey Kelekar