现在的假设为省市区三级.数据库中有相应的三个表
CREATE TABLE [dbo].[area] (
[id] [int] NOT NULL ,
[areaID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[area] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[father] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[city] (
[id] [int] NOT NULL ,
[cityID] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[father] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[province] (
[id] [int] NOT NULL ,
[provinceID] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[province] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
显示页面showinof.jsp
<%@ page pageEncoding="GBK"%>
<jsp:directive.page import="java.sql.*" />
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAd>
<TITLE>Ajax实现三级联动下拉框</TITLE>
<script type="text/javascript">
if (window.ActiveXObject && !window.XMLHttpRequest) ...{
window.XMLHttpRequest=function() ...{
return new ActiveXObject((navigator.userAgent.toLowerCase().indexOf('msie 6') != -1) ? 'Microsoft.XMLHTTP' : 'Msxml2.XMLHTTP');
};
}//取得XMLHttpRequest对象
var req;
var flagSelect;
function testName(flag,value)//使用Ajax访问服务器
...{
flagSelect = flag; //标记一下当前是选择省,还是选择市
req=new XMLHttpRequest();
if (req)
...{
req.onreadystatechange=setValue;
}
req.open('POST',"getinfo.jsp?flag="+flag+"&value="+value);//把参数带到服务器中
req.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
req.send(null);
}
function setValue()
...{
if (req.readyState==4 )//访问到服务器
...{
if(req.status==200)//正确返回
...{
if(flagSelect=="1")//如果选择某个省要更新市和区
...{
var v=req.responseText.split("||");//req.responseText是服务器返回来的字符串
paint(document.all("shi"),v[0]);//更新市下拉框
paint(document.all("qu"),v[1]);//更新区下拉框
}
if(flagSelect=="2")//如果选择某市,只需改变区
...{
var v=req.responseText;
paint(document.all("qu"),v);//更新区下拉框
}
}
}
}
function paint(obj,value)//根据一对数据去更新一个下拉框
...{
var ops = obj.options;
var v=value.split(";");//得到一些数据,(修改过了..)
while(ops.length>0)//先清空原来的数据
...{
ops.remove(0);
}
for(var i=0;i<v.length-1;i++)//把新得到的数据显示上去
...{
var o = document.createElement("OPTION");//创建一个option把它加到下拉框中
o.value=v[i].split(",")[0];
o.text=v[i].split(",")[1];
ops.add(o);
}
}
</script>
<body>
<%
try ...{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=hibernate",
"sa", "");
ResultSet rs = conn.createStatement().executeQuery("select * from province order by provinceID");
%>
<table>
<tr>
<td>省<select name="pro" onchange="testName(1,this.value);">
<%
while (rs.next()) ...{
out.println("<option value='" + rs.getString("provinceID")
+ "'>" + rs.getString("province") + "</option>");
}
%>
</select>
</td>
<%
rs = conn.createStatement().executeQuery(
"select * from city t2 where t2.father=(select min(t1.provinceID) from province t1) order by cityID");
%>
<td>市<select name="shi" onchange="testName(2,this.value);">
<%
while (rs.next()) ...{
out.println("<option value='" + rs.getString("cityID")
+ "'>" + rs.getString("city") + "</option>");
}
%>
</select>
</td>
<%
rs = conn.createStatement().executeQuery(
"select * from area t3 where t3.father=(select min(t2.cityid) from city t2 where t2.father=(select min(t1.provinceID) from province t1) ) order by areaid");
%>
<td>区<select name="qu">
<%
while (rs.next()) ...{
out.println("<option value='" + rs.getString("areaid")
+ "'>" + rs.getString("area") + "</option>");
}
rs.close();
conn.close();
} catch (Exception e) ...{
e.printStackTrace();
}
%>
</select>
</td>
</tr>
</table>
</body>
</HTML>
取得数据库页面getinfo.jsp
<%@ page pageEncoding="GBK"%>
<jsp:directive.page import="java.sql.*" />
<jsp:directive.page import="java.io.IOException" />
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<body>
<%
response.setContentType("text/xml; charset=GBK");
out.clear();
try ...{
String flag = request.getParameter("flag");
String value = request.getParameter("value");
if (flag == null) ...{
return;
}
StringBuffer values = new StringBuffer("");
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
.newInstance();
Connection conn = DriverManager
.getConnection(
"jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=hibernate",
"sa", "");
Statement stmt = conn.createStatement();
ResultSet rs = null;
//getconnection;
//query
//rs
if (flag.equals("1")) ...{
rs = stmt
.executeQuery("select cityid,city from city where father='"
+ value + "' order by cityid");
while (rs.next()) ...{
values.append(rs.getString(1) + "," + rs.getString(2)
+ ";");
}
String ff = values.substring(0, values.indexOf(","));//最得敢小的市编号
System.out.println(ff);
rs = stmt
.executeQuery("select areaid,area from area where father='"
+ ff + "' order by areaid");
values.append("||");//区分市和区信息.
while (rs.next()) ...{
values.append(rs.getString(1) + "," + rs.getString(2)
+ ";");
}
System.out.println(values.toString());
} else if (flag.equals("2")) ...{
rs = stmt
.executeQuery("select areaid,area from area where father='"
+ value + "' order by areaid");
while (rs.next()) ...{
values.append(rs.getString(1) + "," + rs.getString(2)
+ ";");
}
} else ...{
}
out.println(values.toString());
out.flush();
out.close();
} catch (IOException e) ...{
e.printStackTrace();
}
%>
</body>
</HTML>
以上代码利用SQL 2000和利用
http://www.cnblogs.com/Files/singlepine/area1.rar 中的三个表area,city,province的数据(全国省市区)测试通过.
发表于 @ 2007年10月23日 11:15:00|评论(loading...)|编辑