省市县三级联动(同一页面内)——【jQuery-Ajax技术与数据库查询】

使用jQuery-Ajax动态响应查询数据库

准备工作——创建web工程并生成三级联动表

Ajax只能接收打印流
此处提供了.sql文件的下载，在mysql中的数据库里导入此文件即可

此项目所需的各种依赖包及sql文件

新建web project项目，记得勾选生成WebRoot文件

在项目web Root文件夹下新建js文件夹用于存放需要使用的jQuery.jar文件

jquery.js取压缩包内dist文件夹下的jquery.js或jquery.min.js或一起复制到WebRoot下的js文件夹下即可

mysql驱动以及fastjson.jar依赖文件包需要放入WebRoot下的WEB-INF下的lib文件夹内

编写 util层——数据库连接工具类

DBIno.properties配置文件，放入src下或新建source folder类型文件夹下

drive=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java0610?useSSL=false&serverTimezone=UTC
user=root
password=tiger

注意上方url中数据库名需要配置成自己的数据库名，此处是楼主自己的java0610，楼主的三张表也都在java0610内

数据库连接获取与资源释放

package com.db.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnGet {
	private static String drive;
	private static String url;
	private static String user;
	private static String pwd;
	
	//获取类的Class对象只获取一次，所以使用静态代码块
	static{
		Properties prop=new Properties();
		//获取配置文件的内容
		InputStream in=Thread.currentThread().getContextClassLoader().getResourceAsStream("DBInfo.properties");
		try {
			prop.load(in);
			drive=prop.getProperty("drive");
			url=prop.getProperty("url");
			user=prop.getProperty("user");
			pwd=prop.getProperty("password");
			Class.forName(drive);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//获取数据库连接
	public static Connection getConnection(){
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(url, user, pwd);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	//关闭资源连接
	public static void close(Connection connection,Statement statement, ResultSet resultSet){
		try {
			if(resultSet!=null){
				resultSet.close();
			}
			if(statement!=null){
				statement.close();
			}
			if(connection!=null){
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			connection=null;
			statement=null;
			resultSet=null;
		}
	}
}

万能查询方法类

package com.db.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.info.bean.T_class;

public class Quary {

	// 构建访问器方法
	public static String createGet(String columName) {
		String geter;
		geter = "set" + columName.substring(0, 1).toUpperCase() + columName.substring(1).toLowerCase();
		return geter;
	}

	// 查询类
	public static List<?> infoQuary(Connection conn, String whereSql, Object[] params, Class<?> clszz) {

		List<Object> list = new ArrayList<Object>();

		String sql = "select * from "+ clszz.getSimpleName()+ whereSql ;


		PreparedStatement ps = null;

		ResultSet rs = null;

		ResultSetMetaData rsmd = null;
		try {
			ps = conn.prepareStatement(sql);
			// 绑定参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			rsmd = ps.getMetaData();
			while (rs.next()) {
				Object instance = clszz.newInstance();


				for (int k = 0; k < rsmd.getColumnCount(); k++) {

					String columName = rsmd.getColumnName(k+1).toLowerCase();


					Field field = clszz.getDeclaredField(columName.toLowerCase());

					Method getMethod = clszz.getDeclaredMethod(createGet(columName), field.getType());
					Object coluVal = rs.getObject(k + 1);

					if (coluVal instanceof Number) {
						if (field.getType().getName().equals("int") || field.getType().equals("java.lang.Integer")) {
							getMethod.invoke(instance, ((Number) coluVal).intValue());
						} else if (field.getType().getName().equals("float") || field.getType().getName().equals("java.lang.Float")) {
							getMethod.invoke(instance, ((Number) coluVal).doubleValue());
						} else if (field.getType().getName().equals("double") || field.getType().getName().equals("java.lang.Double")) {
							getMethod.invoke(instance, ((Number) coluVal).doubleValue());
						}
					} else if (coluVal == null) {
						getMethod.invoke(instance, (Object)null);
					} else if (coluVal instanceof Date) {
						// 获取时间戳
						long dateStamp = ((Date) coluVal).getTime();
						getMethod.invoke(instance, new Date(dateStamp));
					} else {
						getMethod.invoke(instance, coluVal);
					}
				}
				list.add(instance);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	//测试方法——已注释掉
	/*public static void main(String[] args) {
		String whereSql="";
		Object[] params={};
		Connection conn=ConnGet.getConnection();
		List<?> list=Quary.infoQuary(conn,whereSql, params, T_class.class);
		System.out.println(list);
	}*/
}

创建与三张表对应的JavaBean类

1. T_address_province表

package com.info.bean;

public class T_address_province {
	private int id;
	private String code;
	private String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public T_address_province(int id, String code, String name) {
		super();
		this.id = id;
		this.code = code;
		this.name = name;
	}
	public T_address_province() {
		super();
	}
	
}

2. T_address_city表

package com.info.bean;

public class T_address_city {
	private int id;
	private String code;
	private String name;
	private String provincecode;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getProvincecode() {
		return provincecode;
	}
	public void setProvincecode(String provincecode) {
		this.provincecode = provincecode;
	}
	public T_address_city(int id, String code, String name, String provincecode) {
		super();
		this.id = id;
		this.code = code;
		this.name = name;
		this.provincecode = provincecode;
	}
	public T_address_city() {
		super();
	}
	
}

3. T_address_town表

package com.info.bean;

public class T_address_town {
	private int id;
	private String code;
	private String name;
	private String citycode;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getCitycode() {
		return citycode;
	}
	public void setCitycode(String citycode) {
		this.citycode = citycode;
	}
	public T_address_town(int id, String code, String name, String citycode) {
		super();
		this.id = id;
		this.code = code;
		this.name = name;
		this.citycode = citycode;
	}
	public T_address_town() {
		super();
	}
	
}

创建与三级表对应的三级servlet

可以自行优化为一个servlet，在Ajax中多传入一不同的变量用于表示哪个Ajax发出的请求，在servlet中对这个变量进行判断，进而查询对应的表

省

package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.db.utils.ConnGet;
import com.db.utils.Quary;
import com.info.bean.T_address_province;

public class ProvinceQuary extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	this.doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//设置服务器返回数据字符集
		response.setContentType("text/html;charset=utf-8");
		Connection conn=ConnGet.getConnection();
		String whereSql="";
		Object[] params={};
		List<?> pList=Quary.infoQuary(conn, whereSql, params, T_address_province.class);
		PrintWriter out=response.getWriter();
		//使用json把集合打包成字符串形式，并使用打印流输出
		out.write(JSON.toJSONString(pList));
	}

}

市

package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.db.utils.ConnGet;
import com.db.utils.Quary;
import com.info.bean.T_address_city;

public class CityQuary extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doPost(request, response);
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=utf-8");
		String pcode=request.getParameter("pcode");
		Connection conn=ConnGet.getConnection();
		String whereSql=" where provinceCode = ? ";
		Object[] params={pcode};
		List<?> cList=Quary.infoQuary(conn, whereSql, params, T_address_city.class);
		PrintWriter out=response.getWriter();
		out.write(JSON.toJSONString(cList));
	}

}

县

package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.db.utils.ConnGet;
import com.db.utils.Quary;
import com.info.bean.T_address_town;

public class Town extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=utf-8");
		Connection conn=ConnGet.getConnection();
		String ccode=request.getParameter("ccode");
		String whereSql=" where cityCode = ? ";
		Object[] params={ccode};
		List<?> tList=Quary.infoQuary(conn, whereSql, params, T_address_town.class);
		PrintWriter out=response.getWriter();
		out.write(JSON.toJSONString(tList));
	}

}

WEB-INF下的web.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>userRegister_08</display-name>
  <servlet>
    <servlet-name>InfoQuary</servlet-name>
    <servlet-class>com.servlet.ClassInfoQuary</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>ProvinceQuary</servlet-name>
    <servlet-class>com.servlet.ProvinceQuary</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>CityQuary</servlet-name>
    <servlet-class>com.servlet.CityQuary</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>Town</servlet-name>
    <servlet-class>com.servlet.Town</servlet-class>
  </servlet>




  <servlet-mapping>
    <servlet-name>InfoQuary</servlet-name>
    <url-pattern>/classQuary</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>ProvinceQuary</servlet-name>
    <url-pattern>/province</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>CityQuary</servlet-name>
    <url-pattern>/city</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>Town</servlet-name>
    <url-pattern>/town</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
</web-app>

集成了Ajax的用于动态显示的jsp页面

<!-- 设置页面编码并导入依赖包 -->
<%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'ChinaCity.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
	<style type="text/css">
	body{
	background-color: pink;
	}
#dv1 {
	height: 200px;
	border: 1px solid red;
	vertial-align: middle;
	text-align: center;
	line-height: 200px;
	background-color: skyblue;
}
	</style>
	<!-- 导入jQuery -->
	<script src="js/jquery.js"></script>
	<script type="text/javascript">
	/*
	$(document).ready()页面加载完成事件
	*/
	$(document).ready(function(){
	/*  调用jQuery中封装的Ajax方法  $.ajax({...}) */
		$.ajax({
			type:"get",
			url:"province",
			async:true,
			/*设置接收的数据格式为json，其内部封装的方法会自动把字符串数据解析为json数据使用下标就可调用 */
			dataType:"json",
			/* 定义页面成功请求时的success方法，使用形参data为接收的后台打印流数据，这里后台发的json字符串已经被解析为集合，可直接使用下标调用 */
			success:function(data){
				$("#sel2").html("<option>请选择</option>");
				$("#sel3").html("<option>请选择</option>");
				for(var i=0;i<data.length;i++){
					var pname="<option value="+data[i].code+">"+data[i].name+"</option>";
					$("#sel1").append(pname);
				}
			}
		})
	/*
	绑定<option>选项change事件
	$(选择器).on("事件",function(){})
	*/
		$("#sel1").on("change",function(){
			var pcode=$("#sel1").val();
			$.ajax({
				url:"city",
				type:"get",
				async:true,
				dataType:"json",
				data:{"pcode":pcode},
				success:function(data){
					$("#sel2").html("<option>请选择</option>");
					$("#sel3").html("<option>请选择</option>");
					for(var i=0;i<data.length;i++){
						var cname="<option value="+data[i].code+">"+data[i].name+"</option>";
						$("#sel2").append(cname);
					}
				}
			})
		/*
		绑定<option>选项change事件
		$(选择器).on("事件",function(){})
		*/
			$("#sel2").on("change",function(){
				var ccode=$("#sel2").val();
				$.ajax({
					url:"town",
					type:"get",
					async:true,
					data:{"ccode":ccode},
					dataType:"json",
					success:function(data){
				    	$("#sel3").html("<option>请选择</option>");
						for(var i=0;i<data.length;i++){
							var tname="<option value="+data[i].code+">"+data[i].name+"</option>";
							$("#sel3").append(tname);
						}
						
					}
				})
			})
		})
	})
	</script>
  </head>
  
  <body>
  <div id="dv1">
    <h1>中国县市区</h1>
    <select id="sel1">
    <option>请选择</option>
    </select>
    <select id="sel2">
    <option>请选择</option>
    </select>
    <select id="sel3">
    <option>请选择</option>
    </select>
    </div>
  </body>
</html>

项目工程截图

项目大纲

项目启动过程

1. 添加项目到tomcat
   
   
2. 运行项目
   
3. 查看工程上下文路径
   
4. 打开浏览器并数据http://ip:端口号/上下文路径/目标jsp
   

运行效果

演示一

演示二

演示三

演示四

演示五