现在的假设为省市区三级.数据库中有相应的三个表
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
[ 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 >
< 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 >
< 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的数据(全国省市区)测试通过.