Ajax实现多级联动菜单

刺骨的言语ヽ痛彻心扉 2022-06-14 11:25 223阅读 0赞

最近在重温Ajax技术，就顺手拿起了当年的《Ajax经典案例开发大全》来温习。发现里面有些错误，现在就指出来，这样可以帮助后学者少走弯路。主要的技术有：MySql、JDBC、JSON、Ajax、JSP。其实本文不算原创，主要内容还是摘自《Ajax经典案例开发大全》。

1.数据库设计

drop database if exists mydb;
    create database mydb character set gbk;
    
    --多级联动菜单
    use mydb;
    drop table if exists select_menu;
    create table select_menu(
    	id varchar(255) not null default '',
    	text varchar(255) not null,
    	pid varchar(255) not null,
    	seq int(11) not null default 0,
    	primary key (id)
    )ENGINE=InnoDB DEFAULT CHARSET=gbk;
    
    insert into select_menu values('A1','列表A选项1','INIT',1);
    insert into select_menu values('A2','列表A选项2','INIT',2);
    insert into select_menu values('B11','列表B选项11','A1',1);
    insert into select_menu values('B12','列表B选项12','A1',2);
    insert into select_menu values('B13','列表B选项13','A1',3);
    insert into select_menu values('B21','列表B选项21','A2',1);
    insert into select_menu values('B22','列表B选项22','A2',2);
    insert into select_menu values('C111','列表C选项111','B11',1);
    insert into select_menu values('C112','列表C选项112','B11',2);
    insert into select_menu values('C121','列表C选项121','B12',1);
    insert into select_menu values('C122','列表C选项122','B12',2);
    insert into select_menu values('C131','列表C选项131','B13',1);
    insert into select_menu values('C132','列表C选项132','B13',2);
    insert into select_menu values('C211','列表C选项211','B21',1);
    insert into select_menu values('C212','列表C选项212','B21',2);
    insert into select_menu values('C221','列表C选项221','B22',1);
    insert into select_menu values('C222','列表C选项222','B22',2);

2.连接数据库的工具类

package com.lanp.ajax.db;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    /**
     * 连接数据库的工具类,被定义成不可继承且是私有访问
     * @author lanp
     * @since 2012-2-29 22:27
     */
    public final class DBUtils {
    	private static String url = "jdbc:mysql://localhost:3306/mydb?characterEncoding=gbk";
    	private static String user = "root";
    	private static String psw = "root";
    	
    	private static  Connection conn;
    	
    	static {
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    			throw new RuntimeException(e);
    		}
    	}
    	
    	private DBUtils() {
    		
    	}
    	
    	/**
    	 * 获取数据库的连接
    	 * @return conn
    	 */
    	public static Connection getConnection() {
    		try {
    			conn = DriverManager.getConnection(url, user, psw);
    		} catch (SQLException e) {
    			e.printStackTrace();
    			throw new RuntimeException(e);
    		}
    		return conn;
    	}
    	
    	/**
    	 * 释放资源
    	 * @param conn
    	 * @param pstmt
    	 * @param rs
    	 */
    	public static void closeResources(Connection conn,PreparedStatement pstmt,ResultSet rs) {
    		if(null != rs) {
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    				throw new RuntimeException(e);
    			} finally {
    				if(null != pstmt) {
    					try {
    						pstmt.close();
    					} catch (SQLException e) {
    						e.printStackTrace();
    						throw new RuntimeException(e);
    					} finally {
    						if(null != conn) {
    							try {
    								conn.close();
    							} catch (SQLException e) {
    								e.printStackTrace();
    								throw new RuntimeException(e);
    							}
    						}
    					}
    				}
    			}
    		}
    	}
    }

3.select\_menu.html页面

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>多级联动菜单</title>
    
    	<script type="text/javascript">
    		var xmlHttp;					//用于保存XMLHttpRequest对象的全局变量
    		var targetSelId;				//用于保存要更新选项的列表ID	
    		var selArray = new Array();		//用于保存级联菜单ID的数组，《Ajax经典案例开发大全》中没有= new Array()代码
    		
    		//用于创建XMLHttpRequest对象
    		function createXmlHttp() {
    			if(window.XMLHttpRequest) {
    				xmlHttp = new XMLHttpRequest();
    			} else {
    				xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
    			}
    		}
    		
    		//获取列表选项的调用函数
    		function buildSelect(selectedId,targetId) {
    			if("" == selectedId) {			//selectedId为空串表示选中了默认项
    				clearSubSel(targetId);		//清楚目标列表及下级列表中的值
    				return;						//直接结束调用，不必向服务器请求信息
    			}
    			
    			targetSelId = targetId;			//将传入的目标列表ID赋值给targetSelId变量
    			createXmlHttp();				//创建XMLHttpRequest对象
    			xmlHttp.onreadystatechange = buildSelectCallBack;		//设置回调函数
    			xmlHttp.open("GET", "select_menu.jsp?selectedId="+selectedId, true);
    			xmlHttp.send(null);
    		}
    		
    		//获取列表选项的回调函数
    		function buildSelectCallBack() {
    			if(4 == xmlHttp.readyState) {
    				//将从服务器获得的文本转为对象直接量
    				var optionsInfo = eval("(" + xmlHttp.responseText + ")");
    				var targetSelNode = document.getElementById(targetSelId);
    				clearSubSel(targetSelId);
    				//遍历对象直接量中的成员
    				for(var o in optionsInfo) {
    					//在目标列表追加新的选项
    					targetSelNode.appendChild(createOption(o,optionsInfo[o]));
    				}
    			}
    		}
    		
    		//根据传入的value和text创建选项
    		function createOption(value,text) {
    			var opt = document.createElement("option");			//创建一个option节点
    			opt.setAttribute("value", value);					//设置value
    			opt.appendChild(document.createTextNode(text));		//给节点加入文本信息
    			return opt;
    		}
    		
    		//清除传入的列表节点内所有选项
    		function clearOptions(selNode) {
    			selNode.options.length = 1;
    			selNode.options[0].selected = true;
    		}
    		
    		//初始化列表数组，《Ajax经典案例开发大全》中该方法的代码是有误没有实现真正的初始化
    		function initSelArray(selA,selB,selC) {
    			selArray[0] = selA;
    			selArray[1] = selB;
    			selArray[2] = selC;
    		}
    		
    		//清除下级子列表选项
    		function clearSubSel(targetId) {
    			var len = selArray.length;
    			for(var i=0;i<len;i++) {
    				var j = 0;
    				if(selArray[i] == targetId) {
    					j = i;
    					break;
    				}
    			}
    			for(; j<len; j++) {
    				clearOptions(document.getElementById(selArray[j]));
    			}
    			//《Ajax经典案例开发大全》中该方法的代码是有误，不能实现下级列表全部清除功能，且代码冗余，如下示：
    			//var canClear = false;
    			//for(var i=0; i<selArray.length; i++) {
    			//	if(selArray[i] == targetId) {
    			//		canClear = true;
    			//	}
    			//	if(canClear) {
    			//		clearOptions(document.getElementById(selArray[i]));
    			//	}
    			//}
    		}
    	</script>
    </head>
    
    <body οnlοad="initSelArray('selA','selB','selC');buildSelect('INIT','selA')">
    	<h1>多级联动菜单</h1>
    	<table>
    		<tr>
    			<td>列表A</td>
    			<td>
    				<select name="selA" id="selA" οnchange="buildSelect(this.value,'selB')">
    					<option value="" selected>-------请选择-------</option>
    				</select>
    			</td>
    		</tr>
    		
    		<tr>
    			<td>列表B</td>
    			<td>
    				<select name="selB" id="selB" οnchange="buildSelect(this.value,'selC')">
    					<option value="" selected>-------请选择-------</option>
    				</select>
    			</td>
    		</tr>
    		
    		<tr>
    			<td>列表C</td>
    			<td>
    				<select name="selC" id="selC">
    					<option value="" selected>-------请选择-------</option>
    				</select>
    			</td>
    		</tr>
    	</table>
    </body>
    </html>

4.select\_menu.jsp后台服务

<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <%@ page import="java.sql.*,com.lanp.ajax.db.DBUtils" %>    
    <%!
    	//访问数据库取得下级选项信息
    	String getOptions(String selectedId) {
    		int counter = 0;
    		StringBuffer opts = new StringBuffer("{");
    		String sql = "select * from select_menu where pid=? order by seq asc";
    		Connection conn = null;
    		PreparedStatement pstmt = null;
    		ResultSet rs = null;
    		try {
    			conn = DBUtils.getConnection();
    			pstmt = conn.prepareStatement(sql);
    			pstmt.setString(1,selectedId);
    			rs = pstmt.executeQuery();
    			
    			while(rs.next()) {
    				//如果不是第一项，追加一个","用于分隔选项
    				if(counter > 0) {
    					opts.append(",");
    				}
    				opts.append("'");
    				opts.append(rs.getString("id"));
    				opts.append("':'");
    				opts.append(rs.getString("text"));
    				opts.append("'");
    				counter++;
    			}
    		} catch(SQLException e) {
    			System.out.println(e.toString());
    		} finally {
    			DBUtils.closeResources(conn,pstmt,rs);
    		}
    		opts.append("}");
    		System.out.println(opts.toString());
    		return opts.toString();
    	}
    %>
    
    <%
    	out.clear();
    	String selectedId = request.getParameter("selectedId");
    	String optionsInfo = getOptions(selectedId);
    	out.print(optionsInfo);
    %>

OK，TKS！