需求分析：
通过数据库连接池，可以查询到数据库中员工的各种信息，通过点击下拉框的方式实现三级联动，即：没有点击city下拉框，后面两个下拉框中没有值，这样，点击city下拉框，department下拉框中才有值，点击department下拉框后employee下拉框中才有值，才可以进行选择，不可以跨级点击；点击最后一个下拉框可以将员工的id，last_name，email，salary，显示在下面的表格中；
实现上述功能的方法：
1.c3p0数据库连接池，实现数据库的链接；JdbcUtils类，连接数据库，代码更加简洁；
2.Jackson架包，简化JSON代码，使用其类，方法；ObjectMapper mapper=new ObjectMapper();String result=mapper.writeValueAsString(employee);其可以获取类中get方法名，并且获取其值；
3.使用了jstl标准标签库，jQuery，不用在jsp中插入代码
4.使用到了Ajax：不需要刷新页面，就可实现局部页面更新的技术；其方法：$.getJSON(url,args,function(data){}向指定的jsp页面中插入标签；
5.使用blockUI，使用时要插入jQuery插件jquery.blockUI.js，每次点击下拉框都出现正在刷新的假象，其实就是一张静态的图片
----------------------------------------------------------------------------------------------
先将一些架包导进去：
连接c3p0数据连接池的架包：c3p0-0.9.1.2.jar；
实现oracle数据库和Eclipse软件跨平台连接的包：ojdbc6.jar
实现数据库连接池方法的包：commons-dbutils-1.3.jar
Jackson架包，简化JSON代码：jackson-core-asl-1.9.11.jar，jackson-mapper-asl-1.9.11.jar
jstl标准标签库相关的包：jstl.jar，standard.jar
jQuery架包，放在WebContent/scripts：jquery-1.7.2.js，jQuery插件：jquery.blockUI.js
----------------------------------------------------------------------------------------------
连接c3p0数据库连接池，成功后，获取oracle数据中的三张表：分别是：employees，departments，locations；两两之间有相同的列，可以实现内连接；
三个封装表中部分信息的类：employee，department，location；
package com.lanqiao.javaweb.jdbc;
public class Employee {
private Integer employeeId;
private String lastName;
private String email;
private double salary;
public Employee() {
super();
// TODO Auto-generated constructor stub
}
public Employee(Integer employeeId, String lastName, String email, double salary) {
super();
this.employeeId = employeeId;
this.lastName = lastName;
this.email = email;
this.salary = salary;
}
public Integer getEmployeeId() {
return employeeId;
}
public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
@Override
public String toString() {
return "Employee [employeeId=" + employeeId + ", lastName=" + lastName + ", email=" + email + ", salary="
+ salary + "]";
}
}
package com.lanqiao.javaweb.jdbc;
public class Department {
private Integer departmentId;
private String departmentName;
public Department() {
super();
// TODO Auto-generated constructor stub
}
public Department(Integer departmentId, String departmentName) {
super();
this.departmentId = departmentId;
this.departmentName = departmentName;
}
public Integer getDepartmentId() {
return departmentId;
}
public void setDepartmentId(Integer departmentId) {
this.departmentId = departmentId;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department [departmentId=" + departmentId + ", departmentName=" + departmentName + "]";
}
}
package com.lanqiao.javaweb.jdbc;
public class Location {
private Integer locationId;
private String city;
public Location() {
super();
// TODO Auto-generated constructor stub
}
public Location(Integer locationId, String city) {
super();
this.locationId = locationId;
this.city = city;
}
public Integer getLocationId() {
return locationId;
}
public void setLocationId(Integer locationId) {
this.locationId = locationId;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
@Override
public String toString() {
return "Location [locationId=" + locationId + ", city=" + city + "]";
}
}
在src目录下建立c3p0-config.xml文件，配置和映射连接数据库的属性；
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config>
<named-config name="mvcapp"> <!-- 指定连接数据源的基本属性 -->
<property name="user">oraclejava</property>
<property name="password">lxn123</property>
<property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
<property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl1</property> <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">10</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">10</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">50</property> <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
<property name="maxStatements">20</property>
<!-- 每个连接同时可以使用的 Statement 对象的个数 -->
<property name="maxStatementsPerConnection">5</property> </named-config> </c3p0-config>
建立JdbcUtils类：利用JdbcUtils包中的方法，简便的实现数据库连接池的连接
package com.lanqiao.javaweb.jdbc; import java.sql.Connection;
import java.sql.SQLException; import org.junit.Test; import com.mchange.v2.c3p0.ComboPooledDataSource; //c3p0数据库连接池的方法类
public class JdbcUtils {
//释放数据库连接的方法
public static void releaseConnection(Connection connection){
try {
if(connection!=null){
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
}
} //连接数据库的方法
public static ComboPooledDataSource dataSource=null;
static{
dataSource=new ComboPooledDataSource("mvcapp");
} //获取数据库的链接，返回connection对象
public static Connection getConnection() throws Exception{
return dataSource.getConnection();
}
@Test
//测试数据库连接池是否连接成功
public void testTT() throws Exception{
System.out.println(getConnection());
} //类的私有构造方法
private JdbcUtils() {} //此方法，是本类的调用方法，可以调用本类的各个方法
private static JdbcUtils instance=new JdbcUtils();
public static JdbcUtils getInstanece(){
return instance;
}
}
建立BaseDAO类，其中有查询数据库中数据的方法；
package com.lanqiao.javaweb.jdbc; import java.sql.Connection;
import java.util.List; import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler; public class BaseDAO {
//此类是 对数据库的增伤改查的一些方法，调用的是QueryRunner类中的一些方法，实现对数据库中数据的增伤改查 private static final QueryRunner runner=new QueryRunner(); //返回所对应的list集合,获得的是数据库中所有数据的集合
public <T> List<T> getForList(String sql,Class<T> clazz,Object...args) throws Exception{
//Object...args,为可变参数
//Class<T> clazz,反射参数 List<T> list=null;
Connection conn=null;
try {
conn=JdbcUtils.getConnection();
list=runner.query(conn, sql, new BeanListHandler<T>(clazz),args);
} catch (Exception e) {
e.printStackTrace(); }finally {
//DbUtils是，架包commons-dbutils-1.3.jar里面的类，
//方法closeQuietly为该类的方法，实现了关闭数据库连接池
DbUtils.closeQuietly(conn);
} return list;
} //此方法是传入一个sql语句进入，返回某一个对象
public <T> T get(String sql,Class<T> clazz,Object...args){
T result=null;
Connection conn=null;
try {
conn=JdbcUtils.getConnection();
result=runner.query(conn, sql, new BeanHandler<T>(clazz),args);
} catch (Exception e) { }finally {
DbUtils.closeQuietly(conn);
}
return result;
} }
------------------------------------------------------------------------------------------
数据库连接池，连接的方法，查询方法等前期准备工作完成了；然后是servlet类的建立和jsp页面的处理工作；
建立一个点击触发的jsp页面：employee.jsp(比较简单)；
<%response.sendRedirect("employeeServlet?method=listLocation");%>
web.xml文件中的配置和映射；是servlet类：EmployeeServlet与两个jsp页面实现连接；
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>Ajax-1</display-name>
<servlet>
<description></description>
<display-name>EmployeeServlet</display-name>
<servlet-name>EmployeeServlet</servlet-name>
<servlet-class>com.lanqiao.javaweb.jdbc.EmployeeServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>EmployeeServlet</servlet-name>
<url-pattern>/employeeServlet</url-pattern>
</servlet-mapping>
</web-app>
建立servlet类：EmployeeServlet，及employees.jsp页面，实现三级联动的功能；
package com.lanqiao.javaweb.jdbc; import java.io.IOException;
import java.lang.reflect.Method;
import java.util.List; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.codehaus.jackson.map.ObjectMapper; public class EmployeeServlet extends HttpServlet {
private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { //获取method的值
String methodName=request.getParameter("method"); try {
//利用反射获取本类中method=?，的方法名，
Method method=
getClass().getDeclaredMethod(methodName, HttpServletRequest.class,HttpServletResponse.class);
method.invoke(this, request, response);
} catch (Exception e) { e.printStackTrace();
}
} //调用 BaseDAO类，其中有查询员工信息的方法
private static BaseDAO baseDao=new BaseDAO(); //通过locations查询员工id,所在城市
protected void listLocation
(HttpServletRequest request, HttpServletResponse response) throws Exception{ String sql="select location_id locationId,city from locations"; //调用BaseDAO的getForList方法，查询所有的值
List<Location> locations=baseDao.getForList(sql, Location.class); request.setAttribute("locations", locations);
request.getRequestDispatcher("/employees.jsp").forward(request, response);
} protected void listDepartments(HttpServletRequest request, HttpServletResponse response)
throws Exception{
String locationId=request.getParameter("locationId"); String sql="select department_id departmentId,department_name departmentName "
+ "from departments d where d.location_id=?"; List<Department> departments=
baseDao.getForList(sql, Department.class, Integer.parseInt(locationId)); //Jackson架包，简化JSON代码，使用其类，方法；ObjectMapper mapper=new ObjectMapper();
//String result=mapper.writeValueAsString(employee);其可以获取类中get方法名，并且获取其值；
ObjectMapper mapper=new ObjectMapper();
String result=mapper.writeValueAsString(departments); //System.out.println(result);
//text/javascript重定向到jsp页面的类型
response.setContentType("text/javascript");
response.getWriter().print(result); } //
protected void listEmployees(HttpServletRequest request, HttpServletResponse response)
throws Exception{
String departmentId=request.getParameter("departmentId"); String sql="select employee_id employeeId,last_name lastName "
+ "from employees e where e.department_id=?"; List<Employee> employees=
baseDao.getForList(sql, Employee.class, Integer.parseInt(departmentId)); ObjectMapper mapper=new ObjectMapper();
String result=mapper.writeValueAsString(employees); response.setContentType("text/javascript");
response.getWriter().print(result); }
//通过employeeId 查看某个员工的信息
protected void listMessage(HttpServletRequest request, HttpServletResponse response)
throws Exception{
String employeeId=request.getParameter("employeeId"); String sql="select employee_id employeeId,last_name lastName,email,salary "
+ "from employees where employee_id=?"; List<Employee> employee=
baseDao.getForList(sql, Employee.class, Integer.parseInt(employeeId)); ObjectMapper mapper=new ObjectMapper();
String result=mapper.writeValueAsString(employee); //System.out.println(result);
response.setContentType("text/javascript");
response.getWriter().print(result); }
}
employees.jsp页面
<%@page import="com.lanqiao.javaweb.jdbc.Location"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!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>Insert title here</title>
<script type="text/javascript" src="${pageContext.request.contextPath }/scripts/jquery-1.7.2.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath }/scripts/jquery.blockUI.js"></script>
<script type="text/javascript">
$(function(){
//刚开始隐藏查询的table
$("#emp").hide(); //使用blockUI，使用时要插入jQuery插件jquery.blockUI.js
//每次点击下拉框都出现正在刷新的假象，其实就是一张静态的图片
$(document).ajaxStart(function(){
$.blockUI({
message: $('#loading'),
css:{
top:($(window).height()-400)/2+'px',
lift:($(window).width()-400)/2+'px',
width:'400px',
border:'none'
},
overlayCSS:{backgroundColor:'#fff'}
})
}).ajaxStop($.unblockUI);
//第一个下拉框，
$("#city").change(function(){
$("#department option:not(:first)").remove();
var city=$(this).val(); if(city != ""){
var url="employeeServlet?method=listDepartments";
var args={"locationId":city,"time":new Date()}; $.getJSON(url,args,function(data){
if(data.length==0){
alert("当前城市没有部门！！！");
}
else{
for(var i=0;i<data.length;i++){
var deptId=data[i].departmentId;
var deptName=data[i].departmentName; $("#department").append("<option value='"+deptId+"'>"+deptName+"</option>");
}
}
});
}
});
//第二个下拉框
$("#department").change(function(){
$("#employee option:not(:first)").remove();
var departmentName=$(this).val(); if(departmentName !=""){ var url="employeeServlet?method=listEmployees";
var args={"departmentId":departmentName,"time":new Date()}; $.getJSON(url,args,function(data){
if(data.length==0){
alert("当前部门没有员工！！！");
}
else{ for(var i=0;i<data.length;i++){
var employeeId=data[i].employeeId;
var employeeName=data[i].lastName;
//alert("dd");
$("#employee").append("<option value='"+employeeId+"'>"+employeeName+"</option>");
}
}
});
}
});
//第三个下拉框
$("#employee").change(function(){
$("#emp").show(); var employeeName=$(this).val();
if(employeeName!=""){
var url="employeeServlet?method=listMessage";
var args={"employeeId":employeeName,"time":new Date()}; $.getJSON(url,args,function(data){
if(data.length==0){
alert("此员工无信息无信息！！！");
}
else{
for(var i=0;i<data.length;i++){
var employeeId=data[i].employeeId;
var lastName=data[i].lastName;
var email=data[i].email;
var salary=data[i].salary;
//alert(email);
$("#id").append("<td>"+employeeId+"</td>");
$("#name").append("<td>"+lastName+"</td>");
$("#email").append("<td>"+email+"</td>");
$("#salary").append("<td>"+salary+"</td>");
}
}
});
} });
}); </script>
</head>
<body> <img alt="" id="loading" src="data:images/1.jpg" style="display:none"> <center>
<br><br>
City:
<select id="city">
<option value="">请选择...</option>
<c:forEach items="${locations }" var="location">
<option value="${location.locationId }">${location.city }</option>
</c:forEach>
</select> Department:
<select id="department">
<option value="">请选择...</option> </select> Employee:
<select id="employee">
<option value="">请选择...</option> </select>
<br><br>
<br><br> <table id="emp" width="350" border="1" cellpadding="5" cellspacing="0" >
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Salary</th>
</tr>
<td id="id"></td>
<td id="name"></td>
<td id="email"></td>
<td id="salary"></td>
</table>
</center> </body>
</html>