插入数据库语句
create table COUNTRY
(
? NAME VARCHAR2(100) not null,
? TYPE NUMBER not null
)
;
alter table COUNTRY
? add constraint PRIMARY_KEY primary key (TYPE);
insert into COUNTRY (NAME, TYPE)
values ('中国', 1);
insert into COUNTRY (NAME, TYPE)
values ('美国', 2);
insert into COUNTRY (NAME, TYPE)
values ('日本', 3);
?
?
?
create table CITY
(
? NAME??????? VARCHAR2(100) not null,
? TYPE??????? NUMBER not null,
? COUNTRYTYPE NUMBER not null
)
;
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('沈阳', 1, 1);
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('大连', 2, 1);
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('american_city1', 3, 2);
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('american_city2', 4, 2);
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('american_city3', 5, 2);
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('japan_city1', 6, 3);
insert into CITY (NAME, TYPE, COUNTRYTYPE)
values ('japan_city2', 7, 3);
?
?
create table STREET
(
? NAME???? VARCHAR2(100) not null,
? TYPE???? NUMBER not null,
? CITYTYPE NUMBER not null
)
;
insert into STREET (NAME, TYPE, CITYTYPE)
values ('中街', 1, 1);
insert into STREET (NAME, TYPE, CITYTYPE)
values ('太原街', 2, 1);
insert into STREET (NAME, TYPE, CITYTYPE)
values ('大连street1', 3, 2);
insert into STREET (NAME, TYPE, CITYTYPE)
values ('大连street2', 4, 2);
insert into STREET (NAME, TYPE, CITYTYPE)
values ('大连street3', 5, 2);
?
新建servlet 类两个
?
package test.servlet;
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class SelectServlet extends HttpServlet {
?public void doGet(HttpServletRequest request, HttpServletResponse response)
???throws ServletException, IOException {
??response.setContentType("text/xml;charset=gb2312");
??response.setHeader("Cache-Control", "no-cache");
??String targetId = request.getParameter("id").toString();
??String xml_start = "<selects>";
??String xml_end = "</selects>";
??String xml = "";
??List list1 = new ArrayList();
??List list2 = new ArrayList();
???String sql = "select * from city where countrytype='" + targetId + "'";
???ConnDB53 db=new ConnDB53();
???ResultSet rs=db.executeQuery(sql);
???try{
????while (rs.next()) {
?????list1.add(rs.getString("name"));
?????list2.add(rs.getString("type"));
????}
???}catch(Exception e){
????e.printStackTrace();
???}
???
??
??if (targetId.equalsIgnoreCase("0")) {
???xml = "<select><value>0</value><text>--请选择--</text></select>";
??} else {
???xml = "<select><value>0</value><text>--请选择--</text></select>";
???for (int i = 0; i < list1.size(); i++) {
????xml += "<select><value>" + list2.get(i) + "</value><text>"
??????+ list1.get(i) + "</text></select>";
???}
??}
??String last_xml = "<?xml version='1.0' encoding='GB2312'?>" + xml_start
????+ xml + xml_end;
??response.getWriter().write(last_xml);
?}
?public void doPost(HttpServletRequest request, HttpServletResponse response)
???throws ServletException, IOException {
??doGet(request, response);
?}
}
?
package test.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class SelectServlet2 extends HttpServlet {
?public void doGet(HttpServletRequest request, HttpServletResponse response)
???throws ServletException, IOException {
??response.setContentType("text/xml;charset=gb2312");
??response.setHeader("Cache-Control", "no-cache");
??String targetId = request.getParameter("id").toString();
??String xml_start = "<selects>";
??String xml_end = "</selects>";
??String xml = "";
??List list1 = new ArrayList();
??List list2 = new ArrayList();
??String sql = "select * from street where citytype='"+ targetId + "'";
??ConnDB53 db=new ConnDB53();
??ResultSet rs=db.executeQuery(sql);
??try {
???
???while (rs.next()) {
????list1.add(rs.getString("name"));
????list2.add(rs.getString("type"));
???}
??} catch (Exception e) {
???e.printStackTrace();
??}
??if (targetId.equalsIgnoreCase("0")) {
???xml = "<select><value>0</value><text>--请选择--</text></select>";
??} else {
???xml = "<select><value>0</value><text>--请选择--</text></select>";
???for (int i = 0; i < list1.size(); i++) {
????xml += "<select><value>" + list2.get(i) + "</value><text>"
??????+ list1.get(i) + "</text></select>";
???}
??}
??String last_xml = "<?xml version='1.0' encoding='GB2312'?>" + xml_start
????+ xml + xml_end;
??response.getWriter().write(last_xml);
?}
?public void doPost(HttpServletRequest request, HttpServletResponse response)
???throws ServletException, IOException {
??doGet(request, response);
?}
}
?
?
package test.servlet;
import java.sql.*;
public class ConnDB53 {
?Connection conn = null;
?Statement stmt = null;
?Statement stmt2 = null;
?ResultSet rs = null;
?String user = "maximo";
?String pass = "maximo53";
?String url = "jdbc:oracle:thin:@10.64.27.53:1521:PROD";
?
?public ConnDB53() {
??try {
???Class.forName("oracle.jdbc.driver.OracleDriver");
???conn = DriverManager.getConnection(url, user, pass);
??} catch (java.lang.ClassNotFoundException e) {
???System.err.println(e.getMessage());
??}catch (SQLException e) {
???System.err.println(e.getMessage());
??}
?}
?public ResultSet executeQuery(String sql) {
??try {
??? //conn=DriverManager.getConnection(url,user,pass);
???stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
?????ResultSet.CONCUR_READ_ONLY);
???rs = stmt.executeQuery(sql);
??} catch (SQLException ex) {
???ex.printStackTrace();
???System.err.println(ex.getMessage());
??} finally {
??}
??return rs;
?}
?public int executeUpdate(String sql) {
??int result = 0;
??try {
??? //conn=DriverManager.getConnection(url,user,pass);
???stmt2 = conn.createStatement();
???result = stmt2.executeUpdate(sql);
??} catch (SQLException ex) {
???ex.printStackTrace();
???result = 0;
??} finally {
??}
??return result;
?}
?public void freeConn() {
??try {
???if (conn != null) {
????conn.close();
???}
??} catch (Exception e) {
???e.printStackTrace(System.err);
??} finally {
??}
?}
?public void close() {
??
??try {
???if (rs != null)
????rs.close();
???if (stmt != null)
????stmt.close();
???if (stmt2 != null)
????stmt2.close();
??} catch (Exception e) {
???e.printStackTrace(System.err);
??}
?}
}
?
?
web文件加入
?
?
?
??? <servlet>
??????? <description>
??????????? This is the description of my J2EE component
??????? </description>
??????? <display-name>
??????????? This is the display name of my J2EE component
??????? </display-name>
??????? <servlet-name>
??????????? CookieServlet
??????? </servlet-name>
??????? <servlet-class>
??????????? test.servlet.SelectServlet
??????? </servlet-class>
??? </servlet>
?
?
??? <servlet>
??????? <description>
??????????? This is the description of my J2EE component
??????? </description>
??????? <display-name>
??????????? This is the display name of my J2EE component
??????? </display-name>
??????? <servlet-name>
??????????? CookieServlet2
??????? </servlet-name>
??????? <servlet-class>
??????????? test.servlet.SelectServlet2
??????? </servlet-class>
??? </servlet>
???
??? <servlet-mapping>
??????? <servlet-name>
??????????? CookieServlet
??????? </servlet-name>
??????? <url-pattern>
??????????? /servlet/select
??????? </url-pattern>
??? </servlet-mapping>
?
?
?
??? <servlet-mapping>
??????? <servlet-name>
??????????? CookieServlet2
??????? </servlet-name>
??????? <url-pattern>
??????????? /servlet/select2
??????? </url-pattern>
??? </servlet-mapping>
?
?
新建JSP文件
?
<%@ page language="java" contentType="text/html; charset=gb2312"
?pageEncoding="gb2312"%>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<jsp:useBean id ="db"? scope="page" class="test.servlet.ConnDB53" ></jsp:useBean>
<html>
<head>
<title>3级菜单联动演示</title>
<script type="text/javascript">
??? var req;
??? function Change_Select(){
??? //当第一个下拉框的选项发生改变时调用该函数
????? var country = document.getElementByIdx('one').value;
????? var url = "servlet/select?id="+ escape(country);
????? if(window.XMLHttpRequest){
??????? req = new XMLHttpRequest();
????? }else if(window.ActiveXObject){
??????? req = new ActiveXObject("Microsoft.XMLHTTP");
????? }
????? if(req){
??????? req.open("GET",url,true);
??????? req.onreadystatechange = callback; //指定回调函数为callback
??????? req.send(null);
????? }
??? }
??? function callback(){
????? if(req.readyState ==4){
??????? if(req.status ==200){
????????? parseMessage();//解析XML文档
??????? }else{
????????? alert("Not able to retrieve description" + req.statusText);
??????? }
????? }
??? }
??? function parseMessage(){
????? var xmlDoc = req.responseXML.documentElement;
????? //获得返回的XML文档
????? var xSel = xmlDoc.getElementsByTagName_r('select');
????? //获得XML文档中的所有<select>标记
????? var city = document.getElementByIdx('two');
????? //获得网页中的第二个下拉框
????? city.options.length=0;
????? //每次获得新的数据的时候先把每二个下拉框架的长度清0
?????
????? for(var i=0;i<xSel.length;i++){
??????? var xValue = xSel[i].childNodes[0].firstChild.nodeValue;
??????? //获得每个<select>标记中的第一个标记的值,也就是<value>标记的值
??????? var xText = xSel[i].childNodes[1].firstChild.nodeValue;
??????? //获得每个<select>标记中的第二个标记的值,也就是<text>标记的值
???????
??????? var option = new Option(xText, xValue);
??????? //根据每组value和text标记的值创建一个option对象
??????? try{
????????? city.add(option);//将option对象添加到第二个下拉框中
??????? }catch(e){
??????? }
????? }
??? }
??? //*************************************************************************
????? function Change_Select2(){
????? var city = document.getElementByIdx('two').value;
????? var url = "servlet/select2?id="+ escape(city);
????? if(window.XMLHttpRequest){
??????? req = new XMLHttpRequest();
????? }else if(window.ActiveXObject){
??????? req = new ActiveXObject("Microsoft.XMLHTTP");
????? }
????? if(req){
??????? req.open("GET",url,true);
??????? req.onreadystatechange = callback2;
??????? req.send(null);
????? }
??? }
??? function callback2(){
????? if(req.readyState ==4){
??????? if(req.status ==200){
????????? parseMessage2();
??????? }else{
????????? alert("Not able to retrieve description" + req.statusText);
??????? }
????? }
??? }
??? function parseMessage2(){
????? var xmlDoc = req.responseXML.documentElement;
????? var xSel = xmlDoc.getElementsByTagName_r('select');
????? var street = document.getElementByIdx('three');
????? street.options.length=0;
????? for(var i=0;i<xSel.length;i++){
??????? var xValue = xSel[i].childNodes[0].firstChild.nodeValue;
??????? var xText = xSel[i].childNodes[1].firstChild.nodeValue;
??????? var option = new Option(xText, xValue);
??????? try{
????????? street.add(option);
??????? }catch(e){
??????? }
????? }
??? }???????
? </script>
</head>
<body>
<%
?String sql=" select * from? country ";
?ResultSet rs =db.executeQuery(sql);
?
?List dlist = new ArrayList();
?List vlist = new ArrayList();
?try {
??while (rs.next()) {
???dlist.add((String) rs.getString("name"));
???vlist.add(rs.getString("type"));
??}
?} catch (Exception e) {
??e.printStackTrace();
?}
%>
<div align="center">
<form name="form1" method="post" action="">
<table width="70%" border="0" cellspacing="0" cellpadding="0">
?<tr>
??<td align="center">国家城市选择</td>
?</tr>
?<tr>
??<td><select name="one" id="one" onChange="Change_Select()">
???<!--第一个下拉菜单-->
???<option value="0">--请选择--</option>
???<%
????for (int i = 0; i < dlist.size(); i++) {
???%>
???<option value="<%=vlist.get(i) %>"><%=dlist.get(i)%></option>
???<%
????}
???%>
??</select> <select name="two" id="two" onChange="Change_Select2()">
???<!--第二个下拉菜单-->
???<option value="0">--请选择--</option>
??</select> <select name="three" id="three">
???<!--第三个下拉菜单-->
???<option value="0">--请选择--</option>
??</select></td>
?</tr>
</table>
</form>
</div>
</body>
</html>