ajax+json+mysql实现的省市联动——案例

亦凉 2021-06-22 15:36 443阅读 0赞

# 省市联动 Demo #

## 大体思路 ##

1.  **dao：提供两个方法**  
    一个是查询所有省  
    通过省名称查询指定的市
2.  **servlet：两个方法**  
    一个把所有省转换成json，发送给客户端  
    通过获取省名称这个参数，然后查询该省下的所有市，转换成json，发送给客户端
3.  前端页面：`ajax1.jsp`  
    页面加载完成后：访问servlet，得到所有省，然后显示在`<select id="province">`  
    页面加载完成后：给`<select id="province">`添加onchange事件监听，获取选择的省名称，访问servlet，得到所有市，显示在`<select id="city">`中

## 目录结构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]

## 数据库 ##

### 1. t\_city ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 1]

### 2. t\_province ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 2]

## 代码 ##

### 1. Dao ###

package com.veeja.dao;
    
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    
    import cn.itcast.jdbc.TxQueryRunner;
    
    import com.veeja.domain.City;
    import com.veeja.domain.Province;
    
    public class Dao { 
    	private QueryRunner qr = new TxQueryRunner();
    
    	public List<Province> findAllPronvince() { 
    		try { 
    			String sql = "select * from t_province";
    			return qr.query(sql, new BeanListHandler<Province>(Province.class));
    		} catch (SQLException e) { 
    			throw new RuntimeException(e);
    		}
    	}
    
    	public List<City> findByProvince(int pid) { 
    		try { 
    			String sql = "select * from t_city where pid = ?";
    			return qr.query(sql, new BeanListHandler<City>(City.class), pid);
    		} catch (SQLException e) { 
    			throw new RuntimeException(e);
    		}
    	}
    }

### 2.City.class ###

package com.veeja.domain;
    
    public class City { 
    	private int cid;
    	private String name;// 市名
    	private Province province;// 多方关联一方
    
    	public int getCid() { 
    		return cid;
    	}
    
    	public void setCid(int cid) { 
    		this.cid = cid;
    	}
    
    	public String getName() { 
    		return name;
    	}
    
    	public void setName(String name) { 
    		this.name = name;
    	}
    
    	public Province getProvince() { 
    		return province;
    	}
    
    	public void setProvince(Province province) { 
    		this.province = province;
    	}
    
    	@Override
    	public String toString() { 
    		return "City [cid=" + cid + ", name=" + name + ", province=" + province
    				+ "]";
    	}
    	
    	
    }

### 3.Province.class ###

package com.veeja.domain;
    
    import java.util.List;
    
    public class Province { 
    	private int pid;
    	private String name;
    	private List<City> cityList;// 一方关联多方
    
    	public int getPid() { 
    		return pid;
    	}
    
    	public void setPid(int pid) { 
    		this.pid = pid;
    	}
    
    	public String getName() { 
    		return name;
    	}
    
    	public void setName(String name) { 
    		this.name = name;
    	}
    
    	public List<City> getCityList() { 
    		return cityList;
    	}
    
    	public void setCityList(List<City> cityList) { 
    		this.cityList = cityList;
    	}
    
    	@Override
    	public String toString() { 
    		return "Province [pid=" + pid + ", name=" + name + ", cityList="
    				+ cityList + "]";
    	}
    }

### 4. CityServlet ###

package com.veeja.web.servlet;
    
    import java.io.IOException;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import net.sf.json.JSONArray;
    
    import com.veeja.dao.Dao;
    import com.veeja.domain.City;
    
    public class CityServlet extends HttpServlet { 
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException { 
    		request.setCharacterEncoding("utf-8");
    		response.setContentType("text/html;charset=utf-8");
    
    		// 获取名为pid的参数
    		// 使用这个省的id查询数据，得到List<City>
    		// 转换成json，转发给客户端
    		int pid = Integer.parseInt(request.getParameter("pid"));
    		Dao dao = new Dao();
    		List<City> cityList = dao.findByProvince(pid);
    
    		String json = JSONArray.fromObject(cityList).toString();
    		response.getWriter().print(json);
    	}
    
    }

### 5. ProvinceServlet ###

package com.veeja.web.servlet;
    
    import java.io.IOException;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import net.sf.json.JSONArray;
    
    import com.veeja.dao.Dao;
    import com.veeja.domain.Province;
    
    public class ProvinceServlet extends HttpServlet { 
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException { 
    		response.setContentType("text/html;charset=utf-8");
    		// 通过dao得到所有的省
    		Dao dao = new Dao();
    		List<Province> provinceList = dao.findAllPronvince();
    		// 通过List<Province>转换成json
    		String json = JSONArray.fromObject(provinceList).toString();
    		// 发送给客户端
    		response.getWriter().print(json);
    	}
    
    }

### 6. index.jsp ###

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <%
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://"
    			+ request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    	<head>
    		<base href="<%=basePath%>">
    
    		<title>index.jsp</title>
    		<meta http-equiv="pragma" content="no-cache">
    		<meta http-equiv="cache-control" content="no-cache">
    		<meta http-equiv="expires" content="0">
    		<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    		<meta http-equiv="description" content="This is my page">
    		
    
    		<script type="text/javascript" src="<c:url value='/ajax-lib/ajaxutils.js'/>"></script>
    		<script type="text/javascript"> // 在文档加载完成后 // 获取所有省,添加到<select id="province">中 // 给<select id="province">这个元素添加onchange事件中 // 事件： // 获取当前选择的省的ID // 使用省id访问servlet，得到该省下所有的市 // 把每个市都添加到<select id="city"> 中 window.onload = function() { //发送异步请求，得到所有的省，然后使用每个省生成一个<option>元素添加到<select id="province">中 //得到核心对象 var xmlHttp = createXMLHttpRequest(); // 打开连接 xmlHttp.open("GET", "<c:url value='/ProvinceServlet'/>", true); // 发送 xmlHttp.send(null); // 添加监听 xmlHttp.onreadystatechange = function() { if (xmlHttp.readyState == 4 && xmlHttp.status == 200) { // 执行服务器发送过来的字符串，得到js的对象 var proArray = eval("(" + xmlHttp.responseText + ")"); for (var i = 0; i < proArray.length; i++) { var pro = proArray[i]; //得到每个pro对象 var optionEle = document.createElement("option"); //创建<option>对象 optionEle.value = pro.pid; var textNode = document.createTextNode(pro.name); optionEle.appendChild(textNode); document.getElementById("p").appendChild(optionEle); } } }; // 给<select id="province">添加onchange监听 document.getElementById("p").onchange = function() { var xmlHttp = createXMLHttpRequest(); xmlHttp.open("POST", "<c:url value='CityServlet'/>", true); xmlHttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded"); // 发送 xmlHttp.send("pid=" + this.value); xmlHttp.onreadystatechange = function() { if (xmlHttp.readyState == 4 && xmlHttp.status == 200) { //清空原来的option var citySelect = document.getElementById("c"); // 获取select中所有的子元素 var cityOptionArray = citySelect .getElementsByTagName("option"); while (cityOptionArray.length > 1) { citySelect.removeChild(cityOptionArray[1]); } //得到服务器发送过来的所有的市 var cityArray = eval("(" + xmlHttp.responseText + ")"); for (var i = 0; i < cityArray.length; i++) { var city = cityArray[i]; var optionEle = document.createElement("option"); optionEle.value = city.pid; var textNode = document.createTextNode(city.name); optionEle.appendChild(textNode); citySelect.appendChild(optionEle); } } }; }; }; </script>
    	</head>
    
    	<body>
    		<select name="province" id="p">
    			<option>===请选择省份===</option>
    		</select>
    		<select name="city" id="c">
    			<option>===请选择城市===</option>
    		</select>
    
    	</body>
    </html>

--------------------

END.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]: /images/20210615/b14e7da8b5c04f10b6fdceddfabbd7eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 1]: /images/20210615/d374b50aa5304137801afd689293146a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70 2]: /images/20210615/248c0e3f0e4d445bb42e19f4c66dbb8a.png