AJAX 数据的查询,删除,修改

利用AJAX进行数据的查询,删除,修改

---

一.主页面:

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>AJAX主页</title>
<style type="text/css">
*{
 padding:0;
 margin:0;
 font-size:14px;
}
.box{
	width:1000px;
	height:1500px;
	margin:0 auto;
	background:#DBC28A;
}
.temp{
	width:1000px;
	border-collapse:collapse;
}
.temp tr:hover{
	background:pink;
}
.temp td{
	border:1px solid black;
	text-align:center;
}
.temp td a{
	text-decoration: none;
	display:inline-block;
	margin-left:20px;
}
</style>
<script type="text/javascript" src="../js/jquery-2.1.1.js"></script>
<script type="text/javascript">

	function initData(){
		//AJAX请求,获取所有数据
		$.ajax({
			url:"/EmpAjax/GetAllEmpsServlet",
			dataType:"json",
			type:"get",
			success:function(rt){
				//遍历响应回来的rt数组,是一个JSON对象数组
				//创建表格
				var table=$("<table class='temp'></table>");
				var div=$(".box");
				div.text("");
				div.append(table);
				var trHead=$("<tr><td>编号</td><td>姓名</td><td>职务</td><td>操作</td></tr>");
				table.append(trHead);
				
				for(var i=0;i<rt.length;i++){
					var obj=rt[i];
					var tr=$("<tr></tr>");
					table.append(tr);
					
					var td1=$("<td></td>");
					td1.text(obj.empno);
					tr.append(td1);
					
					var td2=$("<td></td>");
					td2.text(obj.ename);
					tr.append(td2);
					
					var td3=$("<td></td>");
					td3.text(obj.job);
					tr.append(td3);
					
					var td4=$("<td></td>");
					var detail=$("<a href='EmpDetailServlet?eno="+obj.empno+"'>详情</a>");
					td4.append(detail);
					var update=$("<a href='EmpUpdate.html?eno="+obj.empno+"'>更新</a>");
					td4.append(update);
					//创建删除按钮
					var deleteEmp=$("<a href='#'>删除</a>");
					(function(index){
						var deleteObj=rt[index];
						deleteEmp.click(function(){
							//确认是否删除
							var is=confirm("确定要删除"+deleteObj.empno+"这条数据吗?");
							if(is==true){
								//AJAX请求删除
								$.ajax({
									url:"/EmpAjax/DeleteEmpServlet?eno="+deleteObj.empno,
									type:"get",
									dataType:"text",
									success:function(result){
										if(result=="true"){
											alert("删除数据成功!");
											//重新初始化数据,即清空之前的数据
											initData();
										}else{
											alert("删除数据失败!");
										}
									}
								});
							}
						});
					})(i);
					
					td4.append(deleteEmp);
					tr.append(td4);
				}
			}
		});
	}
	//页面加载完毕后自动调用
	$(function(){
		initData();
	});


</script>
</head>
<body>

<div class="box">
</div>

</body>
</html>

---

二.查询所有员工并显示

1.dao层:EmpDao.java

//AJAX版本 查询所有员工
	public List<Emp> getAllEmps(){
		List<Emp> list = new ArrayList<Emp>();
		String sql = "select * from emp";
		List<Object> para = new ArrayList<Object>();
		ResultSet rs = db.executeSelect(sql);
		try {
			while(rs.next()) {
				Emp e = new Emp();
				e.setCom(rs.getFloat("com"));
				e.setDeptno(rs.getString("deptno"));
				e.setEmpno(rs.getString("empno"));
				e.setEname(rs.getString("ename"));
				e.setJob(rs.getString("job"));
				e.setSal(rs.getFloat("sal"));
				list.add(e);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

2.service层: EmpService.java

	//AJAX 查询所有员工
	public List<Emp> getAllEmps(){
		return ed.getAllEmps();
	}

3.controller层: GetAllEmpsServlet.java

package cn.qf.emp.controller;

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

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

import cn.qf.emp.pojo.Emp;
import cn.qf.emp.service.EmpService;
import net.sf.json.JSONArray;

/**
 * Servlet implementation class GetAllEmpsServlet
 */
@WebServlet("/GetAllEmpsServlet")
public class GetAllEmpsServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetAllEmpsServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		EmpService es = new EmpService();
		List<Emp> emps = es.getAllEmps();
		//转JSON
		String jsonStr = JSONArray.fromObject(emps).toString();
		//响应到客户端的中文,在获取后可能有乱码,所以先设置字符模式
		response.setContentType("text/html;charset=utf-8");
		//再获取输出器
		PrintWriter pw = response.getWriter();
		pw.print(jsonStr);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

注意:
//转JSON,利用jar包中封装的方法实现
String jsonStr = JSONArray.fromObject(emps).toString();
//响应到客户端的中文,在获取后可能有乱码,所以先设置字符模式
response.setContentType("text/html;charset=utf-8");

---

三.根据编号删除员工

1.dao层: Empdao.java

	//AJAX根据编号删除员工
	public int deleteEmpByNo(String eno) {
		String sql="delete from emp where empno=?";
		List<Object> para = new ArrayList<Object>();
		para.add(eno);
		int result = db.executeOper(sql, para);
		return result;
	}

2.service层: EmpService.java

	//AJAX 根据编号删除员工
	public boolean deleteEmpByNo(String eno) {
		int result=ed.deleteEmpByNo(eno);
		if (result==1) {
			return true;
		}else {
			return false;
		}
	}

3.controller层: DeleteEmpServlet.java

package cn.qf.emp.controller;

import java.io.IOException;
import java.io.PrintWriter;

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

import cn.qf.emp.service.EmpService;

/**
 * Servlet implementation class DeleteEmpServlet
 */
@WebServlet("/DeleteEmpServlet")
public class DeleteEmpServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeleteEmpServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String eno =request.getParameter("eno");
		EmpService es = new EmpService();
		boolean rt = es.deleteEmpByNo(eno);
		PrintWriter pw = response.getWriter();
		pw.print(rt);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

---

四.根据员工编号修改员工信息

1.点击修改后调转到的页面(两个.jsp页面之间的跳转) EmpUpdate.html

[注意:]
var index=url.indexOf("=");//查询下标函数indexOf
var eno=url.substr(index+1);//字符串截取函数substr()

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改的首页</title>
<script type="text/javascript" src="../js/jquery-2.1.1.js"></script>
<script type="text/javascript">
	function initData(){
		//location.href   当前页面的详细网址
		var url=location.href;
		var index=url.indexOf("=");//查询下标函数indexOf
		var eno=url.substr(index+1);//字符串截取函数substr()
		//AJAX请求:查询当前用户的所有信息
		$.ajax({
			url:"/EmpAjax/GetEmpByEnoServlet?eno="+eno,
			type:"get",
			async:false,
			dataType:"json",
			success:function(data){
				//获取body里的标签,进行赋值
				$("#uname").val(data[0].ename);
				$("#uid").val(data[0].empno);
				$("#job").val(data[0].job);
				$("#sal").val(data[0].sal);
				//绑定部门
				var select=$("#dept");
				var jsobj=select.get(0);
				var deptIndex=0;
				var allOption=$(".co");
				for(var o in allOption){
					if(allOption[o].value==data[0].deptno){
						deptIndex=o;
						break;
					}
				}
				//下拉列表的选中项
				jsobj.selectedIndex=deptIndex;
			}
		});
	}
	//动态加载部门
	function initDept(){
		//AJAX请求获取部门的JSON数据
		$.ajax({
			url:"/EmpAjax/GetDeptsJsonServlet",
			type:"get",
			async:false,
			dataType:"json",
			success:function(data){
				//获取下拉列表
				var select=$("#dept");
				for(var i=0;i<data.length;i++){
					var option=$("<option class='co'></option>");
					option.val(data[i].deptno);
					option.text(data[i].dname);
					select.append(option);
				}
			}
		});
	}
	$(function(){
		initDept();
		initData();
	});
	function save(){
		//jQuery封装的方法  serialize(),只能用在form表单里
		var strData=$("#up").serialize();
		$.ajax({
			url:"/EmpAjax/SaveUpdateEmpServlet",
			type:"post",
			data:strData,
			dataType:"text",
			success:function(ret){
				if(ret=="true"){
					alert("修改成功!");
				}else{
					alert("修改失败!");
				}
			}
		});
	}
</script>
</head>
<body>

<form id="up">
<p>编号:<input type="text" id="uid" name="uid"></p>
<p>姓名:<input type="text" id="uname" name="uname"></p>
<p>职务:<input type="text" id="job" name="job"></p>
<p>工资:<input type="text" id="sal" name="sal"></p>
<p>
部门:<select id="dept" name="dept">
</select>
</p>

<p>
	<input type="button" value="保存员工信息" onclick="save()"/>
	<input type="button" value="返回" onclick="myBack()"/>
</p>
</form>

</body>
</html>

2.EmpUpdate.html 中的initData()方法,给修改页面中的form表单赋值
①dao层:Empdao.java

	//根据员工编号查询账号信息
	public Emp checkEmoIsExist(String eno) {
		Emp emp = null;
		String sql = "select * from emp where empno='"+eno+"'";
		ResultSet rs = db.executeSelect(sql);
		try {
			if (rs.next()) {
				emp = new Emp();
				emp.setEmpno(rs.getString("empno"));
				emp.setEname(rs.getString("ename"));
				emp.setJob(rs.getString("job"));
				emp.setSal(rs.getFloat("sal"));
				emp.setDeptno(rs.getString("deptno"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emp;
	}

②.service层. EmpService.java

	//AJAX  根据编号查该员工详细信息
	public Emp getEmpByEno(String eno) {
		return ed.checkEmoIsExist(eno);
	}

③.controller层. GetEmpByEnoServlet.java

package cn.qf.emp.controller;

import java.io.IOException;
import java.io.PrintWriter;

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

import cn.qf.emp.pojo.Emp;
import cn.qf.emp.service.EmpService;
import net.sf.json.JSONArray;

/**
 * Servlet implementation class GetEmpByEnoServlet
 */
@WebServlet("/GetEmpByEnoServlet")
public class GetEmpByEnoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetEmpByEnoServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取要显示的员工编号
		String eno = request.getParameter("eno");
		EmpService es = new EmpService();
		Emp emp = es.getEmpByEno(eno);
		String jsonStr = JSONArray.fromObject(emp).toString();
		response.setContentType("text/html;charset=utf-8");
		PrintWriter pw = response.getWriter();
		pw.print(jsonStr);
	}
	

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

3.EmpUpdate.html 中的initDept()方法,动态加载下拉列表中的部门
①.dao层 :DeptDao.java

	//查询全部
	public List<Dept> getAllDepts(){
		List<Dept> depts=new ArrayList<Dept>();
		String sql="select * from dept";
		ResultSet rs=db.executeSelect(sql);
		try {
			while(rs.next()){
				Dept dept=new Dept();
				dept.setDeptno(rs.getString("deptno"));
				dept.setDname(rs.getString("dname"));
				dept.setLoc(rs.getString("loc"));
				depts.add(dept);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return depts;
	}

②.service层: DeptService.java

	//查询全部
	public List<Dept> getAllDepts(){
		return dd.getAllDepts();
	}

③.controller层 :GetDeptsJsonServlet.java

package cn.qf.emp.controller;

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

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

import cn.qf.emp.pojo.Dept;
import cn.qf.emp.service.DeptService;
import net.sf.json.JSONArray;

/**
 * Servlet implementation class GetDeptsJsonServlet
 */
@WebServlet("/GetDeptsJsonServlet")
public class GetDeptsJsonServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetDeptsJsonServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		DeptService ds = new DeptService();
		List<Dept> lists = ds.getAllDepts();
		String strJson=JSONArray.fromObject(lists).toString();
		response.setContentType("text/html;charset=utf-8");
		PrintWriter pw = response.getWriter();
		pw.print(strJson);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

4.EmpUpdate.html 中的save()方法,提交修改后的信息
①.dao层: EmpDao.java

	//AJAX  根据员工编号修改员工数据
	public int updateEmp(Emp emp) {
		String sql = "update emp set ename=?,job=?,sal=?,deptno=? where empno=?";
		List<Object> para = new ArrayList<Object>();
		para.add(emp.getEname());
		para.add(emp.getJob());
		para.add(emp.getSal());
		para.add(emp.getDeptno());
		para.add(emp.getEmpno());
		int ret = db.executeOper(sql, para);
		return ret;
	}

②.service层: EmpService.java

	//AJAX  根据员工编号修改信息
	public boolean updateEmp(Emp emp) {
		int ret= ed.updateEmp(emp);
		if (ret==1) {
			return true;
		}else {
			return false;
		}
	}

③.service层: SaveUpdateEmpServlet.java

package cn.qf.emp.controller;

import java.io.IOException;
import java.io.PrintWriter;

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

import cn.qf.emp.pojo.Emp;
import cn.qf.emp.service.EmpService;

/**
 * Servlet implementation class SaveUpdateEmpServlet
 */
@WebServlet("/SaveUpdateEmpServlet")
public class SaveUpdateEmpServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SaveUpdateEmpServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取表单数据
		request.setCharacterEncoding("utf-8");
		Emp emp = new Emp();
		emp.setEmpno(request.getParameter("uid"));
		emp.setEname(request.getParameter("uname"));
		emp.setSal(Float.parseFloat(request.getParameter("sal")));
		emp.setJob(request.getParameter("job"));
		emp.setDeptno(request.getParameter("dept"));
		
		EmpService es = new EmpService();
		boolean result=es.updateEmp(emp);
		PrintWriter pw = response.getWriter();
		pw.print(result);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}