maven中操作数据库实现省市联动最后拓展到redis（1）

本文链接：https://blog.youkuaiyun.com/qq_47518713/article/details/125016649

本文介绍了一种基于Java Web技术实现省市联动下拉框的方法，包括Maven依赖配置、数据库连接、DAO层、Service层及Action层的设计，并通过jQuery实现了前端页面与后台交互。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一：准备工作

数据库，在城市表中省份id作为外键

pom. xml文件

 <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.0.1</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.2</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.0.5</version>
    </dependency>
    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <!-- JSONObject对象依赖的jar包 -->
    <dependency>
      <groupId>commons-beanutils</groupId>
      <artifactId>commons-beanutils</artifactId>
      <version>1.9.3</version>
    </dependency>
    <dependency>
      <groupId>commons-collections</groupId>
      <artifactId>commons-collections</artifactId>
      <version>3.2.1</version>
    </dependency>
    <dependency>
      <groupId>commons-lang</groupId>
      <artifactId>commons-lang</artifactId>
      <version>2.6</version>
    </dependency>
    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.1.1</version>
    </dependency>
    <dependency>
      <groupId>net.sf.ezmorph</groupId>
      <artifactId>ezmorph</artifactId>
      <version>1.0.6</version>
    </dependency>
    <dependency>
      <groupId>net.sf.json-lib</groupId>
      <artifactId>json-lib</artifactId>
      <version>2.4</version>
      <classifier>jdk13</classifier><!-- 指定jdk版本（一定要有） -->
    </dependency>

maven工程结构

index.jsp文件

<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<html>
<head>
    <script src="js/jquery-3.3.1.js"></script>
    <script>

    </script>
</head>
<body>
    <select id="province" onchange="getCity(this.value)">
        <option>--请选择省份--</option>
    </select>
    <select id="city">
        <option>--请选择市--</option>
    </select>
</body>
</html>

接下来我们完成dao层，service层，action,util,jsp

ProvinceCityDao

public interface ProvinceCityDao {
    /**
     * 获取省数据
     * @return
     */
    public List<Province> getProvince();
    /**
     * 获取省数据
     * @return
     */
    public List<City>  getCity(String codePid);//省份的id
}

ProvinceCityService

public interface ProvinceCityService {
    /**
     * 获取省数据
     * @return
     */
    public List<Province> getProvince();
    /**
     * 获取省数据
     * @return
     */
    public List<City>  getCity(String codePid);//省份的id
}

ProvinceCityServiceImpl

public class ProvinceCityServiceImpl implements ProvinceCityService{
    ProvinceCityDao provinceCityDao = new ProvinceDaoImpl();
    //查询省
    public List<Province> getProvince() {
        return provinceCityDao.getProvince();
    }

    //查询市
    public List<City> getCity(String codePid) {
        return provinceCityDao.getCity(codePid);
    }
}

ProvinceAction

@WebServlet("/getProvince")
public class ProvinceAction extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //处理请求编码格式 Request
        req.setCharacterEncoding("UTF-8");
        //处理  Response 响映json编码格式
        resp.setContentType("application/json;charset=UTF-8");

        ProvinceCityService provinceCityService = new ProvinceCityServiceImpl();
        List<Province> list = provinceCityService.getProvince();

        //citys转换成jsonArray格式
        JSONArray jsonArray = JSONArray.fromObject(list);
        resp.getWriter().println(jsonArray);
    }
}

CityAction

@WebServlet("/getCity")
public class CityAction extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //处理请求编码格式 Request
        req.setCharacterEncoding("UTF-8");
        //处理  Response 响映json编码格式'
        resp.setContentType("application/json;charset=UTF-8");
        //获取参数
        String codePid = req.getParameter("id");
        //创建控制层
        ProvinceCityService provinceCityService = new ProvinceCityServiceImpl();
        List<City> list = provinceCityService.getCity(codePid);

        //citys转换成jsonArray格式
        JSONArray jsonArray = JSONArray.fromObject(list);
        resp.getWriter().println(jsonArray);
    }
}

entity层

public class Province {
    private  String id;
    private  String codePid;
    private  String name;

public class City {
    private  String id;
    private  String codeCid;
    private  String name;
    private  String codePid;

util

public class BaseDao {
    private static String driveName;
    private static String url;
    private static String username;
    private static String password;
    static {
        init();
    }

    public static void init() {
        //获取配置文件
        InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
            driveName = properties.getProperty("db.driverName");
            url = properties.getProperty("db.url");
            username = properties.getProperty("db.userName");
            password = properties.getProperty("db.pwd");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(driveName);
            conn = DriverManager.getConnection(url,username,password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 关闭流
     * @param rs
     * @param sta
     * @param conn
     */
    public static void closeAll(ResultSet rs, Statement sta, Connection conn){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (sta != null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

配置文件

db.driverName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://127.0.0.1:3306/mysql02?useUnicode=true&characterEncoding=utf-8
db.userName=root
db.pwd=123456

最关键的index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<html>
<head>
    <script src="js/jquery-3.3.1.js"></script>
    <script>
        $(function () {
            $.get("${pageContext.request.contextPath}/getProvince", function(data){
                var province = $("#province")
                document.getElementById('province').options.length = 0
                province.append('<option value="-1">--请选择省份--</option>')
                $(data).each(function () {
                    var option = "<option value='"+this.codePid+"'>"+this.name+"</option>"
                    province.append(option)
                })
            });
        })

        function getCity(codePid) {
            $.ajax({
                type: "POST",
                url: "${pageContext.request.contextPath}/getCity",
                data: "id="+codePid,
                data_type: "json",
                success: function(data){
                    var city = $("#city")
                    document.getElementById('city').options.length = 0
                    city.append("<option>--请选择市--</option>")
                    $(data).each(function () {
                        var option = "<option value='"+this.codePid+"'>"+this.name+"</option>"
                        city.append(option)
                    })
                }
            });
        }
    </script>
</head>
<body>
    <select id="province" onchange="getCity(this.value)">
        <option>--请选择省份--</option>
    </select>
    <select id="city">
        <option>--请选择市--</option>
    </select>
</body>
</html>

最后效果