jQuery：级联查询-省份城市信息

喵俺第一专栏

于 2022-07-25 22:50:55 发布

阅读量622

点赞数 1

分类专栏： # jQuery总结文章标签： javascript 前端 servlet

本文链接：https://blog.csdn.net/dengfengling999/article/details/125984909

版权

jQuery总结专栏收录该内容

6 篇文章 0 订阅

订阅专栏

实体类：

Province：

package com.bjpowernode.entity;

public class Province {

    private Integer id;
    private String name;
    private String jiancheng;
    private String shenghui;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getJiancheng() {
        return jiancheng;
    }

    public void setJiancheng(String jiancheng) {
        this.jiancheng = jiancheng;
    }

    public String getShenghui() {
        return shenghui;
    }

    public void setShenghui(String shenghui) {
        this.shenghui = shenghui;
    }

    @Override
    public String toString() {
        return "Province{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", jiancheng='" + jiancheng + '\'' +
                ", shenghui='" + shenghui + '\'' +
                '}';
    }
}

City：

package com.bjpowernode.entity;

public class City {
    private Integer id;
    private String name;
    private Integer provinceId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getProvinceId() {
        return provinceId;
    }

    public void setProvinceId(Integer provinceId) {
        this.provinceId = provinceId;
    }

    @Override
    public String toString() {
        return "City{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", provinceId=" + provinceId +
                '}';
    }
}

dao层：QueryDao：

package com.bjpowernode.dao;

import com.bjpowernode.entity.City;
import com.bjpowernode.entity.Province;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class QueryDao {

    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;
    private String sql;
    private String url="jdbc:mysql://localhost:3306/springdb";
    private String username="root";
    private String password="123456";

    //查询所有的省份信息
    public List<Province> queryProvinceList(){
        List<Province> provinces  = new ArrayList<>();
        try{
            Province p = null;
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);
            sql="select id,name,jiancheng,shenghui from province order by id";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                p  = new Province();
                p.setId( rs.getInt("id"));
                p.setName( rs.getString("name"));
                p.setJiancheng( rs.getString("jiancheng"));
                p.setShenghui( rs.getString("shenghui"));
                provinces.add(p);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if( rs != null){
                    rs.close();
                }
                if( pst != null){
                    pst.close();
                }
                if( conn != null){
                    conn.close();
                }
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
        return provinces;
    }

    //查询一个省份下面的所有城市
    public List<City> queryCityList(Integer provinceId){
        List<City> cities  = new ArrayList<>();
        try{
            City city = null;
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);
            sql="select  id, name from city where provinceid = ? ";
            pst = conn.prepareStatement(sql);
            //设置省份的参数值
            pst.setInt(1, provinceId);
            rs = pst.executeQuery();
            while(rs.next()){
               city = new City();
               city.setId( rs.getInt("id"));
               city.setName( rs.getString("name"));
               cities.add(city);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if( rs != null){
                    rs.close();
                }
                if( pst != null){
                    pst.close();
                }
                if( conn != null){
                    conn.close();
                }
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
        return cities;
    }
}

controller层：

QueryProviceServlet：

package com.bjpowernode.controller;

import com.bjpowernode.dao.QueryDao;
import com.bjpowernode.entity.Province;
import com.fasterxml.jackson.databind.ObjectMapper;

import javax.management.Query;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

public class QueryProviceServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String json = "{}";
        //调用dao，获取所有的省份信息， 是一个List集合
        QueryDao dao  = new QueryDao();
        List<Province> provinces = dao.queryProvinceList();
        //把list转为json格式的数据，输出给ajax请求
        if( provinces != null ){
            //调用jackson工具库，实现List--json
            ObjectMapper om  = new ObjectMapper();
            json = om.writeValueAsString(provinces);
        }

        //输出json数据，响应ajax请求的，返回数据
        response.setContentType("application/json;charset=utf-8");
        PrintWriter pw  = response.getWriter();
        pw.println(json);
        pw.flush();
        pw.close();
    }
}

QueryCityServlet：

package com.bjpowernode.controller;

import com.bjpowernode.dao.QueryDao;
import com.bjpowernode.entity.City;
import com.fasterxml.jackson.databind.ObjectMapper;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

public class QueryCityServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String json = "{}";
        // 获取请求传过来的， 省份id
        String strProviceId =  request.getParameter("proid");
        if( strProviceId != null  && !"".equals(strProviceId.trim())){
            QueryDao dao  =new QueryDao();
            List<City> cityList = dao.queryCityList( Integer.valueOf( strProviceId));
            //把list转为json
            ObjectMapper om = new ObjectMapper();
            json  = om.writeValueAsString(cityList);
        }

        //输出数据
        response.setContentType("application/json;charset=utf-8");
        PrintWriter pw = response.getWriter();
        pw.println(json);
        pw.flush();
        pw.close();

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
    <!--查询所有的省份servlet声明开始-->
    <servlet>
        <servlet-name>QueryProviceServlet</servlet-name>
        <servlet-class>com.bjpowernode.controller.QueryProviceServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>QueryProviceServlet</servlet-name>
        <url-pattern>/queryProvince</url-pattern>
    </servlet-mapping>
    <!--查询所有的省份servlet声明完成-->


    <!--注册查询city的servlet开始-->
    <servlet>
        <servlet-name>QueryCityServlet</servlet-name>
        <servlet-class>com.bjpowernode.controller.QueryCityServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>QueryCityServlet</servlet-name>
        <url-pattern>/queryCity</url-pattern>
    </servlet-mapping>
    <!--注册查询city的servlet完成-->
</web-app>

index.jsp:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>省市级联查询</title>
      <script type="text/javascript" src="js/jquery-3.4.1.js"></script>
      <script type="text/javascript">
          function loadDataAjax() {
              //做ajax请求，使用jquery的$.ajax()
              $.ajax({
                  url:"queryProvince",
                  dataType:"json",
                  success:function( resp ){
                      //删除旧的数据，把已经存在的数据清空 empty清空子对象
                      $("#province").empty();
                      //[{"id":1,"name":"河北","jiancheng":"冀","shenghui":"石家庄"},{}]
                      $.each( resp, function (i,n) {
                          //获取select这个dom对象
                          $("#province").append("<option value='"+n.id+ "'>" +  n.name + "</option>");
                      })
                  }
              })
          }


         $(function(){
             //  $(function()）在页面的dom的对象加载成功后执行的函数， 在此发起ajax。
             loadDataAjax();

             //绑定事件
             $("#btnLoad").click(function(){
                 loadDataAjax();
             })

             //给省份的select绑定一个change事件，当select内容发生变化时，触发事件
             $("#province").change(function () {
                 //获取选中的列表框的值
                 var obj = $("#province>option:selected");
                 // alert(" select 的change 事件" + obj.val() + "===="+obj.text())
                 var provinceId = obj.val(); // 1 ,2, 3

                 //做一个ajax请求，获取省份的所有城市信息
                 $.post("queryCity",{proid:provinceId,name:"Lisi",age:20},callback,"json");

             })

         })


          //定义一个处理返回数据的函数
          function callback(resp){
              //清空select列表
              $("#city").empty();
              $.each( resp, function(i,n){
                  $("#city").append("<option value='"+n.id+"'>"+n.name+"</option>")
              })
          }

      </script>
  </head>
  <body>
    <p>省市级联查询,使用ajax</p>
    <div>
       <table border="1" cellpadding="0" cellspacing="0">
         <tr>
             <td>
               省份：
             </td>
             <td>
                 <select id="province">
                     <option value="0">请选择.....</option>
                 </select>
                 <!--<input type="button" value="load数据" id="btnLoad" />-->
             </td>

         </tr>
           <tr>
               <td>城市：</td>
               <td>
               <select id="city">
                   <option value="0">请选择.....</option>
               </select>
               </td>
           </tr>
       </table>
    </div>
  </body>
</html>

数据库表：