ajax（三）之省市二级菜单联动（从sql获取数据），无刷新翻页

本文链接：https://blog.csdn.net/xiaozhegaa/article/details/64190601

分析：二级菜单栏，主要是数据库的设计，设计好就非常简单。第2级菜单栏中带有1级菜单栏的索引，只要有一级菜单栏的索引，就可以找到城市。上代码
这里写图片描述
思路：获取一级菜单省份de值，在Servlet中收集到该数值，然后调用Dao中根据省份查询城市的函数，找到所有返回的城市，最后打包成json格式数据返回去

use zz2017
city.sql
create table if not exists city(
    id int primary key auto_increment,
    city_name varchar(10) not null,
    province_name varchar(10) not null
) default charset=utf8;
insert into city(city_name,province_name) values('长春','吉林省');
insert into city(city_name,province_name) values('吉林市','吉林省');
insert into city(city_name,province_name) values('松原','吉林省');
insert into city(city_name,province_name) values('通化','吉林省');
insert into city(city_name,province_name) values('沈阳','辽宁省');
insert into city(city_name,province_name) values('大连','辽宁省');
insert into city(city_name,province_name) values('鞍山','辽宁省');
insert into city(city_name,province_name) values('抚顺','辽宁省');
insert into city(city_name,province_name) values('铁岭','辽宁省');
insert into city(city_name,province_name) values('济南','山东省');
insert into city(city_name,province_name) values('青岛','山东省');
insert into city(city_name,province_name) values('威海','山东省');
insert into city(city_name,province_name) values('长沙','湖南省');
insert into city(city_name,province_name) values('株洲','湖南省');
select * from city;

City

public class City {
    private int id;
    private String city_name;
    private String province_name;
    public String getProvince_name() {
        return province_name;
    }
    public void setProvince_name(String province_name) {
        this.province_name = province_name;
    }
    public City(int id,String city_name){
        this.id = id;
        this.city_name = city_name;
    }
    public City(){}
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCity_name() {
        return city_name;
    }
    public void setCity_name(String city_name) {
        this.city_name = city_name;
    }
}


CityDao
//根据省份去查询该省份下所有的city，
    public List<City> findCityByProvince(String province) throws SQLException{
        //这里采用c3p0连接池。你用jdbc也可以。反正能实现根据省份去查询该省份下所有的city，就可以
        QueryRunner runner  = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "select * from city where province_name = ? ";
        List<City> cityList = new ArrayList<City>();
        cityList = (List<City>) runner.query(sql, province,new BeanListHandler(City.class));
        return cityList;
    }


CityServlet1

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String province = request.getParameter("province");
        byte[] buf = province.getBytes("ISO8859-1");
        province = new String(buf,"UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter pw = response.getWriter();
        String jsonString = null;

        try {
            CityDao cityDao = new CityDao();
            List<City> cityList = new ArrayList<City>();
            cityList = cityDao.findCityByProvince(province);

            JsonConfig jsonConfig = new JsonConfig();
            jsonConfig.setExcludes(new String[]{"id"});

            JSONArray jsonArray = JSONArray.fromObject(cityList, jsonConfig);
            System.out.println(jsonArray);
            jsonString = jsonArray.toString();
            pw.write(jsonString);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

ajax.jsp
<body>
    <select id="provinceID">
        <option>选择省份</option>   
        <option>吉林省</option>    
        <option>辽宁省</option>    
        <option>山东省</option>    
        <option>湖南省</option>    
    </select>
    <select id="cityID">
        <option>选择城市</option>   
    </select>
    <hr/>
    <div>
        <!-- 显示结果 -->
    </div>
    <script type="text/javascript">
        document.getElementById("provinceID").onchange=function(){
            //清空原来的city列表
            var citySelectElement = document.getElementById("cityID");
            var cityElementArray = citySelectElement.options;
            var size = cityElementArray.length;
            if(size > 1){
                for(var i = size-1 ; i>0 ;i--){
                    citySelectElement.removeChild(cityElementArray[i])
                }
            }
            var province = this[this.selectedIndex].innerHTML;
            province = encodeURI(province);
            //创建AJAX引警对象
            var xhr = createXHR();
            //设置函数监听
            xhr.onreadystatechange = function(){
                if(xhr.readyState==4){
                    if(xhr.status==200){
                        //以字符串形式接收
                        var jsonString = xhr.responseText;

                        //将json字符串转化成json格式
                        var jsonObject = eval("("+jsonString+")");

                        var size = jsonObject.length;

                        for(var i = 0 ; i < size ; i++){

                            var city = jsonObject[i].city_name;

                            var optionElement = document.createElement("option");

                            optionElement.innerHTML = city;

                            var divElemenet = document.getElementById("cityID");

                            divElemenet.appendChild(optionElement);
                        }
                    }
                }
            }
            //准备以POST方式发送请求
            xhr.open("get","/ajax/CityServlet1?time="+new Date().getTime()+"&province="+province);

            //真正发送请求
            xhr.send(null);

        }

        function createXHR(){
            var xhr = null;
            try{
                xhr = new ActiveXObject("microsoft.xmlhttp");
            }catch(e){
                try{
                    xhr = new XMLHttpRequest();
                }catch(e){
                    window.alert("你的浏览器太差");
                }
            }
            return xhr;
        }
    </script>
  </body>