Java Web：AJAX二级下拉框联动，JSON【诗书画唱】
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `addr`
-- ----------------------------
DROP TABLE IF EXISTS `addr`;
CREATE TABLE `addr` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of addr
-- ----------------------------
中国共计34个省级行政区，包括23个省、5个自治区、4个直辖市、2个特别行政区。
23个省分别为：河北省、山西省、辽宁省、吉林省、黑龙江省、江苏省、浙江省、安徽省、
福建省、江西省、山东省、河南省、湖北省、湖南省、广东省、海南省、
四川省、贵州省、云南省、陕西省、甘肃省、青海省、台湾省。
5个自治区分别为：内蒙古自治区、广西壮族自治区、西藏自治区、宁夏回族自治区、新疆维吾尔自治区。
4个直辖市分别为：北京市、天津市、上海市、重庆市。
2个特别行政区分别为：香港特别行政区、澳门特别行政区。
INSERT INTO `addr` VALUES ('1', '0', '河北省');
INSERT INTO `addr` VALUES ('2', '0', '山西省');
INSERT INTO `addr` VALUES ('3', '0', '辽宁省');
INSERT INTO `addr` VALUES ('4', '0', '吉林省');
INSERT INTO `addr` VALUES ('5', '0', '黑龙江省');
INSERT INTO `addr` VALUES ('6', '0', '江苏省');
INSERT INTO `addr` VALUES ('7', '0', '浙江省');
INSERT INTO `addr` VALUES ('8', '0', '安徽省');
INSERT INTO `addr` VALUES ('9', '0', '福建省');
INSERT INTO `addr` VALUES ('10', '0', '江西省');
INSERT INTO `addr` VALUES ('11', '0', '山东省');
INSERT INTO `addr` VALUES ('12', '0', '河南省');
INSERT INTO `addr` VALUES ('13', '0', '湖北省');
INSERT INTO `addr` VALUES ('14', '0', '湖南省');
INSERT INTO `addr` VALUES ('15', '0', '海南省');
INSERT INTO `addr` VALUES ('16', '0', '四川省');
INSERT INTO `addr` VALUES ('17', '0', '贵州省');
INSERT INTO `addr` VALUES ('18', '0', '云南省');
INSERT INTO `addr` VALUES ('19', '0', '陕西省');
INSERT INTO `addr` VALUES ('20', '0', '甘肃省');
INSERT INTO `addr` VALUES ('21', '0', '青海省');
INSERT INTO `addr` VALUES ('22', '0', '台湾省');
INSERT INTO `addr` VALUES ('23', '0', '内蒙古自治区');
INSERT INTO `addr` VALUES ('24', '0', '广西壮族自治区');
INSERT INTO `addr` VALUES ('25', '0', '西藏自治区');
INSERT INTO `addr` VALUES ('26', '0', '宁夏回族自治区');
INSERT INTO `addr` VALUES ('27', '0', '新疆维吾尔自治区');
INSERT INTO `addr` VALUES ('28', '0', '北京市');
INSERT INTO `addr` VALUES ('29', '0', '广东省');
INSERT INTO `addr` VALUES ('30', '0', '天津市');
INSERT INTO `addr` VALUES ('31', '0', '上海市');
INSERT INTO `addr` VALUES ('32', '0', '重庆市');
INSERT INTO `addr` VALUES ('33', '0', '香港特别行政区');
INSERT INTO `addr` VALUES ('34', '0', '澳门特别行政区');
--石家庄市、唐山市、秦皇岛市、邯郸市、邢台市、保定市、张家口市、
--承德市、沧州市、廊坊市、衡水市
insert into addr(pid,name) values ( '1', '石家庄市');
insert into addr(pid,name) values ( '1', '唐山市');
insert into addr(pid,name) values ( '1', '秦皇岛市');
insert into addr(pid,name) values ( '1', '邯郸市');
insert into addr(pid,name) values ( '1', '邢台市');
insert into addr(pid,name) values ( '1', '保定市');
insert into addr(pid,name) values ( '1', '张家口市');
insert into addr(pid,name) values ( '1', '承德市');
insert into addr(pid,name) values ( '1', '沧州市');
insert into addr(pid,name) values ( '1', '廊坊市');
insert into addr(pid,name) values ( '1', '衡水市');
--11个地级市分别是太原市、大同市、阳泉市、长治市、晋城市、朔州市、
--晋中市、运城市、忻州市、临汾市、吕梁市。
insert into addr(pid,name) values ( '2', '太原市');
insert into addr(pid,name) values ( '2', '大同市');
insert into addr(pid,name) values ( '2', '阳泉市');
insert into addr(pid,name) values ( '2', '长治市');
insert into addr(pid,name) values ( '2', '晋城市');
insert into addr(pid,name) values ( '2', '朔州市');
insert into addr(pid,name) values ( '2', '晋中市');
insert into addr(pid,name) values ( '2', '运城市');
insert into addr(pid,name) values ( '2', '忻州市');
insert into addr(pid,name) values ( '2', '临汾市');
insert into addr(pid,name) values ( '2', '吕梁市');
select * from addr
drop table addr
个人理解：联动就是这里靠pid为0的省份的部分的id对应的城市的pid相同来实现的。
package com.SSHC.bean;
public class Addr {
private Integer id;
private Integer pid;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.SSHC.controller;
import java.io.IOException;
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.SSHC.bean.Addr;
import com.SSHC.dao.AddrDao;
/**
* Servlet implementation class SelServlet
*/
@WebServlet("/ss")
public class addressKuangServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public addressKuangServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request,
* HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws
ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request,
* HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
//获取数据中的数据
String strPid = request.getParameter("pid");
Integer pid = -1;
if(strPid != null && strPid.length() > 0) {
pid = Integer.parseInt(strPid);
}
AddrDao ad = new AddrDao();
List<Addr>list = ad.selectByPid(pid);
//方法一：拼接html代码（可能会有兼容性问题）
// StringBuilder html = new StringBuilder
//("<option>请选择省份</option>");
// //<option value="1">湖南</option>
// for(Addr addr : list) {
// html.append("<option value=\"");
// html.append(addr.getId());
// html.append("\">");
// html.append(addr.getName());
// html.append("</option>");
// }
// System.out.println(html);
//方法二：拼接JSON字符串{},[]
//[{"id":1,"name":"湖南"},{"id":2,"name":"湖北"},
//{"id":3,"name":"江西"}]
StringBuilder jsonStr = new StringBuilder("[");
String dot = "";
for(Addr addr : list) {
jsonStr.append(dot);
jsonStr.append("{\"id\":");
jsonStr.append(addr.getId());
jsonStr.append(",\"name\":\"");
jsonStr.append(addr.getName());
jsonStr.append("\"}");
dot = ",";
}
jsonStr.append("]");
System.out.println(jsonStr);
response.setCharacterEncoding("utf-8");
//将数据传送到回调函数中
//response.getWriter().write(html.toString());
response.getWriter().write(jsonStr.toString());
}
}
package com.SSHC.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.SSHC.bean.Addr;
import com.SSHC.util.DbUtil;
public class AddrDao {
public List<Addr>selectByPid(Integer pid){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
List<Addr>list = new ArrayList<Addr>();
String sql = "select * from addr where pid = ?";
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql);
pstm.setInt(1, pid);
rs = pstm.executeQuery();
while(rs.next()) {
Integer id = rs.getInt("id");
String name = rs.getString("name");
Addr a = new Addr();
a.setId(id);
a.setName(name);
list.add(a);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DbUtil.close(rs, pstm, conn);
}
return list;
}
}
package com.SSHC.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class DbUtil {
private static String driverName;
private static String url;
private static String user;
private static String pwd;
static {
//读取properties文件
Properties prop = new Properties();
//将db.properties文件读取到内存中去
InputStream is = DbUtil.class.getClassLoader()
.getResourceAsStream("db.properties");
//加载内容
try {
prop.load(is);
//读取内容
driverName = prop.getProperty("dn");
url = prop.getProperty("url");
user = prop.getProperty("un");
pwd = prop.getProperty("up");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取数据库连接对象的方法
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,pwd);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet rs,PreparedStatement pstm
,Connection conn){
try {
if(rs != null) {
rs.close();
}
if(pstm != null) {
pstm.close();
}
if(conn != null) {
conn.close();
}
} catch(Exception e) {
e.printStackTrace();
}
}
}
dn=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/firstjsp?useUnicode=true&characterEncoding=UTF-8
un=root
up=root
<%@ page language="java" contentType="text/html;
charset=UTF-8" pageEncoding="UTF-8"%>
<%
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 hreff="<%=basePath%>">
<title></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">
<style type="text/css">
* {
font-size: 50px;
}
</style>
<script type="text/javascript">
//页面中的所有的Html标签都被解析出来以后，就会运行下面的函数
window.onload = function(){
//获取省份下拉框
var prov = document.getElementById('province');
//获取省份下拉框中的数据（我现在执行了）
//从js代码跳转到java代码怎么实现呢？ajax
/*var xhr = createXhr();
if(xhr) {
//设置提交方式是POST提交和执行的servlet的url路径
xhr.open('POST','ss');
//设置ajax请求头
xhr.setRequestHeader("content-type",
"application/x-www-form-urlencoded");
//设置回调函数
xhr.onreadystatechange = function(){
if (xhr.readyState == 4 && xhr.status == 200){
//alert(xhr.responseText);
//有的浏览器不支持下拉框的innerHTML属性，所以会有兼容性问题。
//prov.innerHTML = xhr.responseText;
//alert(xhr.responseText);
//将json字符串转换成js数组
var arr = JSON.parse(xhr.responseText);
//对arr数组进行循环遍历
for(var i = 0;i < arr.length;i ++) {
var opt = arr[i];
//取出对象中的属性值
var id = opt.id;
var name = opt.name;
//创建一个option对象
var option = new Option(name,id);
//将option对象放到省份下拉框中
prov.add(option);
}
}
}
//提交ajax请求
xhr.send('pid=0');
}*/
ajax('ss',{pid:0},function(xhr){
//将json字符串转换成js数组
var arr = JSON.parse(xhr.responseText);
//对arr数组进行循环遍历
for(var i = 0;i < arr.length;i ++) {
var opt = arr[i];
//取出对象中的属性值
var id = opt.id;
var name = opt.name;
//创建一个option对象
var option = new Option(name,id);
//将option对象放到省份下拉框中
prov.add(option);
}
});
}
//将创建XMLHttpRequest对象的方法封装起来
function createXhr(){
var xhr = null;
if(window.XMLHttpRequest) {
//判断window对象中是否有XMLHttpRequest属性
xhr = new XMLHttpRequest();
} else {
//如果没有XMLHttpRequest属性，就使用ActiveXObject创建
xhr = new ActiveXObject("Microsoft.XMLHTTP");
}
return xhr;
}
//conf={"pid":0,pwd:1}
function ajax(url,conf,fn){
var xhr = createXhr();
if(xhr) {
xhr.open('POST',url);
xhr.setRequestHeader("content-type",
"application/x-www-form-urlencoded");
xhr.onreadystatechange = function(){
if (xhr.readyState == 4 && xhr.status == 200){
fn(xhr);
}
}
var str = '';
var dot = '';
for(var attr in conf){
str += dot;
str += attr;
str += '=';
str += conf[attr];
dot = '&';
}
xhr.send(str);
}
}
function loadCity(){
//获取选中的省份的id
var pid = document.getElementById('province').value;
/*//获取城市下拉框
var citySel = document.getElementById('city');
//清空城市下拉框中的数据
citySel.length = 0;
//alert('你选中的省份id是：' + pid);
var xhr = createXhr();
if(xhr) {//提交ajax请求
xhr.open('POST','ss');
xhr.setRequestHeader("content-type",
"application/x-www-form-urlencoded");
xhr.onreadystatechange = function(){
if (xhr.readyState == 4 && xhr.status == 200){
//arr数组中全部都是选中省份下面的城市
var arr = JSON.parse(xhr.responseText);
for(var i = 0;i < arr.length;i ++) {
var city = arr[i];
var id = city.id;
var name = city.name;
//创建下拉框中的option标签
var opt = new Option(name,id);
//将option标签放到城市下拉框中去
citySel.add(opt);
}
}
}
xhr.send('pid=' + pid);
}*/
ajax('ss',{pid:pid},function(xhr){
//arr数组中全部都是选中省份下面的城市
var arr = JSON.parse(xhr.responseText);
var citySel = document.getElementById('city');
citySel.length = 0;
for(var i = 0;i < arr.length;i ++) {
var city = arr[i];
var id = city.id;
var name = city.name;
//创建下拉框中的option标签
var opt = new Option(name,id);
//将option标签放到城市下拉框中去
citySel.add(opt);
}
});
}
</script>
</head>
<body>
<select id="province" onchange="loadCity();">
<option value='-1'>请选择省份</option>
</select>
<select id="city">
<option value='-1'>请选择城市</option>
</select>
</body>
</html>
