利用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("=");//查询下标函数indexOfvar 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);
}
}