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 + "]";    }        }

<?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>

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;    }}

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;    }    }

<%response.sendRedirect("employeeServlet?method=listLocation");%>

<?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>

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);                            }}

<%@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="http://www.mamicode.com/${pageContext.request.contextPath }/scripts/jquery-1.7.2.js"></script><script type="text/javascript" src="http://www.mamicode.com/${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=http://www.mamicode.com/‘"+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=http://www.mamicode.com/‘"+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  id="loading" src="http://www.mamicode.com/images/1.jpg" style="display:none">        <center>        <br><br>        City:        <select id="city">            <option value="">请选择...</option>            <c:forEach items="${locations }" var="location">                <option value="http://www.mamicode.com/${location.locationId }">${location.city }</option>            </c:forEach>        </select>                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;                Department:        <select id="department">            <option value="">请选择...</option>                    </select>                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;                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>