Ajax实现三级联动下拉框

现在的假设为省市区三级.数据库中有相应的三个表

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的数据(全国省市区)测试通过.