java指定查找职工信息_Ajax案例：三级联动查询员工的信息(三张表进行内连接)...

需求分析：

通过数据库连接池，可以查询到数据库中员工的各种信息，通过点击下拉框的方式实现三级联动，即：没有点击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；

packagecom.lanqiao.javaweb.jdbc;public classEmployee {privateInteger employeeId;privateString lastName;privateString email;private doublesalary;publicEmployee() {super();//TODO Auto-generated constructor stub

}public Employee(Integer employeeId, String lastName, String email, doublesalary) {super();this.employeeId =employeeId;this.lastName =lastName;this.email =email;this.salary =salary;

}publicInteger getEmployeeId() {returnemployeeId;

}public voidsetEmployeeId(Integer employeeId) {this.employeeId =employeeId;

}publicString getLastName() {returnlastName;

}public voidsetLastName(String lastName) {this.lastName =lastName;

}publicString getEmail() {returnemail;

}public voidsetEmail(String email) {this.email =email;

}public doublegetSalary() {returnsalary;

}public void setSalary(doublesalary) {this.salary =salary;

}

@OverridepublicString toString() {return "Employee [employeeId=" + employeeId + ", lastName=" + lastName + ", email=" + email + ", salary="

+ salary + "]";

}

}

packagecom.lanqiao.javaweb.jdbc;public classDepartment {privateInteger departmentId;privateString departmentName;publicDepartment() {super();//TODO Auto-generated constructor stub

}publicDepartment(Integer departmentId, String departmentName) {super();this.departmentId =departmentId;this.departmentName =departmentName;

}publicInteger getDepartmentId() {returndepartmentId;

}public voidsetDepartmentId(Integer departmentId) {this.departmentId =departmentId;

}publicString getDepartmentName() {returndepartmentName;

}public voidsetDepartmentName(String departmentName) {this.departmentName =departmentName;

}

@OverridepublicString toString() {return "Department [departmentId=" + departmentId + ", departmentName=" + departmentName + "]";

}

}

packagecom.lanqiao.javaweb.jdbc;public classLocation {privateInteger locationId;privateString city;publicLocation() {super();//TODO Auto-generated constructor stub

}publicLocation(Integer locationId, String city) {super();this.locationId =locationId;this.city =city;

}publicInteger getLocationId() {returnlocationId;

}public voidsetLocationId(Integer locationId) {this.locationId =locationId;

}publicString getCity() {returncity;

}public voidsetCity(String city) {this.city =city;

}

@OverridepublicString toString() {return "Location [locationId=" + locationId + ", city=" + city + "]";

}

}

在src目录下建立c3p0-config.xml文件，配置和映射连接数据库的属性；

oraclejava

lxn123

oracle.jdbc.driver.OracleDriver

jdbc:oracle:thin:@localhost:1521:orcl1

5

10

10

50

20

5

建立JdbcUtils类：利用JdbcUtils包中的方法，简便的实现数据库连接池的连接

packagecom.lanqiao.javaweb.jdbc;importjava.sql.Connection;importjava.sql.SQLException;importorg.junit.Test;importcom.mchange.v2.c3p0.ComboPooledDataSource;//c3p0数据库连接池的方法类

public classJdbcUtils {//释放数据库连接的方法

public static voidreleaseConnection(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() throwsException{returndataSource.getConnection();

}

@Test//测试数据库连接池是否连接成功

public void testTT() throwsException{

System.out.println(getConnection());

}//类的私有构造方法

privateJdbcUtils() {}//此方法，是本类的调用方法，可以调用本类的各个方法

private static JdbcUtils instance=newJdbcUtils();public staticJdbcUtils getInstanece(){returninstance;

}

}

建立BaseDAO类，其中有查询数据库中数据的方法；

packagecom.lanqiao.javaweb.jdbc;importjava.sql.Connection;importjava.util.List;importorg.apache.commons.dbutils.DbUtils;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;public classBaseDAO {//此类是 对数据库的增伤改查的一些方法，调用的是QueryRunner类中的一些方法，实现对数据库中数据的增伤改查

private static final QueryRunner runner=newQueryRunner();//返回所对应的list集合,获得的是数据库中所有数据的集合

public List getForList(String sql,Class clazz,Object...args) throwsException{//Object...args,为可变参数//Class clazz,反射参数

List list=null;

Connection conn=null;try{

conn=JdbcUtils.getConnection();

list=runner.query(conn, sql, new BeanListHandler(clazz),args);

}catch(Exception e) {

e.printStackTrace();

}finally{//DbUtils是，架包commons-dbutils-1.3.jar里面的类，//方法closeQuietly为该类的方法，实现了关闭数据库连接池

DbUtils.closeQuietly(conn);

}returnlist;

}//此方法是传入一个sql语句进入，返回某一个对象

public T get(String sql,Classclazz,Object...args){

T result=null;

Connection conn=null;try{

conn=JdbcUtils.getConnection();

result=runner.query(conn, sql, new BeanHandler(clazz),args);

}catch(Exception e) {

}finally{

DbUtils.closeQuietly(conn);

}returnresult;

}

}

------------------------------------------------------------------------------------------

数据库连接池，连接的方法，查询方法等前期准备工作完成了；然后是servlet类的建立和jsp页面的处理工作；

建立一个点击触发的jsp页面：employee.jsp(比较简单)；

web.xml文件中的配置和映射；是servlet类：EmployeeServlet与两个jsp页面实现连接；

Ajax-1

EmployeeServlet

EmployeeServlet

com.lanqiao.javaweb.jdbc.EmployeeServlet

EmployeeServlet

/employeeServlet

建立servlet类：EmployeeServlet，及employees.jsp页面，实现三级联动的功能；

packagecom.lanqiao.javaweb.jdbc;importjava.io.IOException;importjava.lang.reflect.Method;importjava.util.List;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importorg.codehaus.jackson.map.ObjectMapper;public class EmployeeServlet extendsHttpServlet {private static final long serialVersionUID = 1L;protected voiddoGet(HttpServletRequest request, HttpServletResponse response)throwsServletException, 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=newBaseDAO();//通过locations查询员工id,所在城市

protected voidlistLocation

(HttpServletRequest request, HttpServletResponse response)throwsException{

String sql="select location_id locationId,city from locations";//调用BaseDAO的getForList方法，查询所有的值

List locations=baseDao.getForList(sql, Location.class);

request.setAttribute("locations", locations);

request.getRequestDispatcher("/employees.jsp").forward(request, response);

}protected voidlistDepartments(HttpServletRequest request, HttpServletResponse response)throwsException{

String locationId=request.getParameter("locationId");

String sql="select department_id departmentId,department_name departmentName "

+ "from departments d where d.location_id=?";

List departments=baseDao.getForList(sql, Department.class, Integer.parseInt(locationId));//Jackson架包，简化JSON代码，使用其类，方法；ObjectMapper mapper=new ObjectMapper();//String result=mapper.writeValueAsString(employee);其可以获取类中get方法名，并且获取其值；

ObjectMapper mapper=newObjectMapper();

String result=mapper.writeValueAsString(departments);//System.out.println(result);//text/javascript重定向到jsp页面的类型

response.setContentType("text/javascript");

response.getWriter().print(result);

}// protected voidlistEmployees(HttpServletRequest request, HttpServletResponse response)throwsException{

String departmentId=request.getParameter("departmentId");

String sql="select employee_id employeeId,last_name lastName "

+ "from employees e where e.department_id=?";

List employees=baseDao.getForList(sql, Employee.class, Integer.parseInt(departmentId));

ObjectMapper mapper=newObjectMapper();

String result=mapper.writeValueAsString(employees);

response.setContentType("text/javascript");

response.getWriter().print(result);

}//通过employeeId 查看某个员工的信息

protected voidlistMessage(HttpServletRequest request, HttpServletResponse response)throwsException{

String employeeId=request.getParameter("employeeId");

String sql="select employee_id employeeId,last_name lastName,email,salary "

+ "from employees where employee_id=?";

List employee=baseDao.getForList(sql, Employee.class, Integer.parseInt(employeeId));

ObjectMapper mapper=newObjectMapper();

String result=mapper.writeValueAsString(employee);//System.out.println(result);

response.setContentType("text/javascript");

response.getWriter().print(result);

}

}

employees.jsp页面

Insert title here

$("#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":newDate()};

$.getJSON(url,args,function(data){if(data.length==0){

alert("当前城市没有部门！！！");

}else{for(var i=0;i

var deptId=data[i].departmentId;

var deptName=data[i].departmentName;

$("#department").append(""+deptName+"");

}

}

});

}

});//第二个下拉框

$("#department").change(function(){

$("#employee option:not(:first)").remove();

var departmentName=$(this).val();if(departmentName !=""){

var url="employeeServlet?method=listEmployees";

var args={"departmentId":departmentName,"time":newDate()};

$.getJSON(url,args,function(data){if(data.length==0){

alert("当前部门没有员工！！！");

}else{for(var i=0;i

var employeeId=data[i].employeeId;

var employeeName=data[i].lastName;//alert("dd");

$("#employee").append(""+employeeName+"");

}

}

});

}

});//第三个下拉框

$("#employee").change(function(){

$("#emp").show();

var employeeName=$(this).val();if(employeeName!=""){

var url="employeeServlet?method=listMessage";

var args={"employeeId":employeeName,"time":newDate()};

$.getJSON(url,args,function(data){if(data.length==0){

alert("此员工无信息无信息！！！");

}else{for(var i=0;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("

"+employeeId+"");

$("#name").append("

"+lastName+"");

$("#email").append("

"+email+"");

$("#salary").append("

"+salary+"");

}

}

});

}

});

});

City:

请选择...

${location.city }

Department:

请选择...

Employee:

请选择...

Id	Name	Email	Salary