大家好,我是雄雄,今天分享的技术很简单,即ajax结合jdbc动态实现及时刷新表单数据。
前言:相信大家在网上冲浪的时候,肯定会发现这样的场景,在实现某个查询功能时,下方表格中会显示需要展示的结果,当查询条件换掉之后,数据表格中显示的信息也会及时更新,今天,我们就来实现一下这样的功能。
效果图如下所示:
数据库:mysql
开发编辑器:myeclipse
浏览器:chrome
采用java三层架构分层开发,首先我们先来看看数据库的表结构:
Emp表:
Dept表:
接下来就是按照表结构写实体类,代码如下:
Emp实体类:
package org.entity; public class Emp { private int eid; private String ename; private String epass; private int edid; private Dept dept; public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } public int getEid() { return eid; } public void setEid(int eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getEpass() { return epass; } public void setEpass(String epass) { this.epass = epass; } public int getEdid() { return edid; } public void setEdid(int edid) { this.edid = edid; } public Emp( String ename, String epass, int edid) { super(); this.ename = ename; this.epass = epass; this.edid = edid; } public Emp(){ } }
Dept实体类:
package org.entity; public class Emp { private int eid; private String ename; private String epass; private int edid; private Dept dept; public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } public int getEid() { return eid; } public void setEid(int eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getEpass() { return epass; } public void setEpass(String epass) { this.epass = epass; } public int getEdid() { return edid; } public void setEdid(int edid) { this.edid = edid; } public Emp( String ename, String epass, int edid) { super(); this.ename = ename; this.epass = epass; this.edid = edid; } public Emp(){ } }
连接数据库所需的BaseDao:
package org.entity; public class Emp { private int eid; private String ename; private String epass; private int edid; private Dept dept; public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } public int getEid() { return eid; } public void setEid(int eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getEpass() { return epass; } public void setEpass(String epass) { this.epass = epass; } public int getEdid() { return edid; } public void setEdid(int edid) { this.edid = edid; } public Emp( String ename, String epass, int edid) { super(); this.ename = ename; this.epass = epass; this.edid = edid; } public Emp(){ } }
接着,就是Dao层接口,IEmpDao:
package org.entity; public class Emp { private int eid; private String ename; private String epass; private int edid; private Dept dept; public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } public int getEid() { return eid; } public void setEid(int eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getEpass() { return epass; } public void setEpass(String epass) { this.epass = epass; } public int getEdid() { return edid; } public void setEdid(int edid) { this.edid = edid; } public Emp( String ename, String epass, int edid) { super(); this.ename = ename; this.epass = epass; this.edid = edid; } public Emp(){ } }
接口实现类EmpDaoImpl:
package org.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.dao.BaseDao; import org.dao.IEmpDao; import org.entity.Dept; import org.entity.Emp; import com.sun.corba.se.spi.orbutil.fsm.Guard.Result; public class EmpDaoImpl implements IEmpDao { private Connection conn; private PreparedStatement p; private ResultSet rs; BaseDao base = new BaseDao(); @Override public int addEmp(Emp emp) { String sql = "insert into Emp(ename,epass,edid) values(?,?,?);"; List<Object> prama = new ArrayList<Object>(); prama.add(emp.getEname()); prama.add(emp.getEpass()); prama.add(emp.getEdid()); int rel = 0; try { rel = base.ExecuteUpdate(sql, prama); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, p, rs); } return rel; } //查询全部 @Override public List<Emp> findEmpAll() { String sql = "SELECT * FROM emp ,dept WHERE emp.edid = dept.did"; List<Emp> eList = new ArrayList<Emp>(); try { rs = base.ExecuteQuery(sql, null); while(rs.next()){ Emp emp = new Emp(); emp.setEid(rs.getInt("eid")); emp.setEname(rs.getString("ename")); emp.setEpass(rs.getString("epass")); emp.setEdid(rs.getInt("edid")); Dept dept = new Dept(); dept.setDid(rs.getInt("did")); dept.setDname(rs.getString("dname")); emp.setDept(dept); eList.add(emp); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ base.closeConn(conn, p, rs); } return eList; } @Override public int delEmp(int eid) { String sql = "delete from emp where eid = ?;"; List<Object> prama = new ArrayList<Object>(); prama.add(eid); int rel = 0; try { rel = base.ExecuteUpdate(sql, prama); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, p, rs); } return rel; } @Override public Emp findEmpByName(String name) { String sql = "select * from Emp where ename =?"; List<Object> pa= new ArrayList<Object>(); pa.add(name); Emp emp = new Emp(); try { rs = base.ExecuteQuery(sql, pa); while(rs.next()){ emp.setEid(rs.getInt("eid")); emp.setEname(rs.getString(2)); emp.setEpass(rs.getString(3)); emp.setEdid(rs.getInt("edid")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ base.closeConn(conn, p, rs); } return emp; } //根据部门编号查询 @Override public List<Emp> findEmpByDid(int edid) { List<Emp> empList = new ArrayList<Emp>(); List<Object> param = new ArrayList<Object>(); String sql = null; if(edid!=0){ sql = "SELECT * FROM emp ,dept WHERE emp.edid = dept.did and edid = ?"; param.add(edid); }else{ sql = "SELECT * FROM emp ,dept WHERE emp.edid = dept.did"; } try { rs = base.ExecuteQuery(sql, param); while(rs.next()){ Emp emp = new Emp(); emp.setEid(rs.getInt("eid")); emp.setEname(rs.getString("ename")); emp.setEpass(rs.getString("epass")); emp.setEdid(rs.getInt("edid")); Dept dept = new Dept(); dept.setDid(rs.getInt("did")); dept.setDname(rs.getString("dname")); emp.setDept(dept); empList.add(emp); } } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, p, rs); } return empList; } }
Service层接口IEmpService:
package org.service; import org.dao.IEmpDao; public interface IEmpService extends IEmpDao { }
Service层实现类:EmpServiceImpl:
package org.service.impl; import java.util.List; import org.dao.IEmpDao; import org.dao.impl.EmpDaoImpl; import org.entity.Emp; import org.service.IEmpService; public class EmpServiceImpl implements IEmpService { IEmpDao empDao = new EmpDaoImpl(); @Override public int addEmp(Emp emp) { return empDao.addEmp(emp); } @Override public List<Emp> findEmpAll() { // TODO Auto-generated method stub return empDao.findEmpAll(); } @Override public int delEmp(int eid) { // TODO Auto-generated method stub return empDao.delEmp(eid); } @Override public Emp findEmpByName(String name) { // TODO Auto-generated method stub return empDao.findEmpByName(name); } @Override public List<Emp> findEmpByDid(int edid) { return empDao.findEmpByDid(edid); } }
主要内容在前台jsp页面,我们先来写一个下拉列表,用来存放Dept表中的所有部门名称,当加载该jsp页面时,先从数据库中查询所有部门名称,然后通过jstl遍历至下拉列表中。代码如下:
<% IEmpService empService = new EmpServiceImpl(); List<Emp> empList = empService.findEmpAll(); request.setAttribute("empList", empList); IDeptService deptService = new DeptServiceImpl(); List<Dept> deptList = deptService.findAllDept(); request.setAttribute("deptList", deptList); %>
部门编号: <!-- <input type="text" name="edid"/> --> <select id="deptid"> <option value="0">全部</option> <c:forEach items="${deptList }" var="dept"> <option value="${dept.did }">${dept.dname }</option> </c:forEach> </select> <input type="button" id="serch" value="查询"/>
当点击查询按钮时,通过ajax去Servlet中,根据部门编号查询员工信息,在回调函数(success)中处理返回的json数据,遍历动态添加至表格中。
“查询”按钮 的点击事件:
//点击查询查询值 $("#serch").click(function(){ //获取部门编号 //var edid = $("input[name='edid']").val(); //获取下拉列表中的值 var edid = $("#deptid").val(); var data = {"edid":edid,"tag":"getEmpByEdid"}; $.getJSON("EmpServlet",data,function(data){ $("#dataTable").html("<tr><td>编号</td><td>姓名</td><td>密码</td><td>部门编号</td><td>操作</td></tr>"); for(var i in data){ //给表格中添加数据 $("#dataTable").append("<tr><td>" +data[i].eid+"</td><td>" +data[i].ename+"</td><td>" +data[i].epass+"</td><td>" +data[i].dept.dname+"</td><td><a href='EmpServlet?tag=del&eid="+data[i].eid+"'>删除</a></td></tr>"); } }); });
数据表格的代码:
<table border="1" id="dataTable"> <tr> <td>编号</td> <td>姓名</td> <td>密码</td> <td>部门名称</td> <td>操作</td> </tr> <c:forEach items="${empList }" var="emp"> <tr> <td>${emp.eid }</td> <td>${emp.ename }</td> <td>${emp.epass}</td> <td>${emp.dept.dname}</td> <td> <a href="EmpServlet?tag=del&eid=${emp.eid }">删除</a> </td> </tr> </c:forEach> </table>
Servlet类中关键代码:
//根据部门编号查询信息 public void getEmpByEdid(HttpServletRequest request, HttpServletResponse response) throws IOException{ Integer edid = Integer.parseInt(request.getParameter("edid")); List<Emp> emplist = empService.findEmpByDid(edid); String jsonresult = JSON.toJSONString(emplist); System.out.println(jsonresult); PrintWriter out = response.getWriter(); out.print(jsonresult); }
基本思路就是这样的,怎么样,是不是很简单?
需要辣椒酱的可以在小商店中直接下单哦~