一.添加 1.跳转添加页面 2.获取主表数据并追加到下拉框中 3.完善表单数据成功添加并跳转到列表页面 二.删除 1.通过id实现单个删除和批量删除 2.利用delete from staff where yid in ( );语句实现单个删除与批量删除共用删除方法,减少代码冗余 三.改 1.跳转修改页面 2.获取主表数据并追加到下拉框中 3.通过id获取要修改的数据并回显的表单中 4.通过id修改数据 四.查 1.查询所有信息 案例 主表 从表 列表jsp
<%@ 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="<%=request.getContextPath() %>/js/jquery-1.8.3.js"></script> <script type="text/javascript"> $(function() { //全选 $("#qx").click(function() { $(":checkbox:not(:first)").attr("checked", true); }) //全不选 $("#qbx").click(function() { $(":checkbox:not(:first)").attr("checked", false); }) //反选 $("#fx").click(function() { $(":checkbox:not(:first)").each(function() { $(this).attr("checked",!$(this).attr("checked")) }) }) //全选或全不选 $("#chs").click(function() { if($(this).attr("checked")){ $(":checkbox").attr("checked", true); }else{ $(":checkbox").attr("checked", false); } }) //添加数据 $("#add").click(function() { location="add.jsp"; }) //批量删除 $("#dels").click(function() { var a=""; $(":checkbox:not(:first):checked").each(function() { a+=","+$(this).val(); }) var yid=a.substring(1); $.post("staff?action=del", {yid:yid}, function(i) { if(i>0){ alert("删除成功"); location="getall.jsp"; } }, "text") }) }) //修改 function xg(yid) { location="show.jsp?yid="+yid; } //单个删除 function del(yid) { $.post("staff?action=del", {yid:yid}, function(i) { if(i>0){ alert("删除成功"); location="getall.jsp"; } }, "text") } </script> </head> <body> <table border="1" cellspacing="0"> <tr> <td><input type="checkbox" id="chs"></td> <th>编号</th> <th>姓名</th> <th>性别</th> <th>生日</th> <th>爱好</th> <th>部门</th> <th>操作1</th> <th>操作2</th> </tr> <c:if test="${list==null }"> <c:redirect url="staff?action=getall"></c:redirect> </c:if> <c:forEach items="${list }" var="lis" varStatus="a"> <tr> <td><input type="checkbox" value="${lis.yid}"></td> <td>${a.count }</td> <td>${lis.yname }</td> <td>${lis.sex }</td> <td>${lis.birth }</td> <td>${lis.hobby }</td> <td>${lis.bname }</td> <td><input type="button" value="修改" onclick="xg(${lis.yid})"> </td> <td><input type="button" value="删除" onclick="del(${lis.yid})"></td> </tr> </c:forEach> <tr> <td align="center" colspan="10"> <input type="button" value="全选" id="qx"> <input type="button" value="全不选" id="qbx"> <input type="button" value="反选" id="fx"> <input type="button" value="添加" id="add"> <input type="button" value="批量删除" id="dels"> </td> </tr> </table> </body> </html>添加jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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="<%=request.getContextPath() %>/js/jquery-1.8.3.js"></script> <script type="text/javascript"> $(function() { //获取下拉框的值并追加到下拉框 $.post("staff?action=option", function(obj) { for ( var i in obj) { $("[name='bname']").append("<option value='"+obj[i].bmid+"'>"+obj[i].bname+"</option>") } }, "json") //从form表单获取数据并添加 $("#add").click(function() { $.post("staff?action=add", $("form").serialize(), function(i) { if(i>0){ alert("添加成功"); location="getall.jsp"; } }, "text") }) }) </script> </head> <body> <form> <input type="hidden" name="yid"> 姓名: <input type="text" name="yname"><br> 性别: <input type="radio" value="男" name="sex">男 <input type="radio" value="女" name="sex">女<br> 生日: <input type="text" name="birth"><br> 爱好: <input type="checkbox" value="旅游" name="hobby" >旅游 <input type="checkbox" value="读书" name="hobby" >读书 <input type="checkbox" value="音乐" name="hobby" >音乐 <input type="checkbox" value="运动" name="hobby" >运动 <input type="checkbox" value="美食" name="hobby" >美食<br> 部门: <select name="bname"> </select><br> <input type="button" value="添加" id="add"> </form> </body> </html>修改jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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="<%=request.getContextPath() %>/js/jquery-1.8.3.js"></script> <script type="text/javascript"> $(function() { var yid=${param.yid} alert(yid); $.post("staff?action=show", {yid:yid}, function(obj) { var staff=obj.staff; var lis=obj.list; //获取下拉框 for ( var i in lis) { $("[name='bname']").append("<option value='"+lis[i].bmid+"'>"+lis[i].bname+"</option>") } ///回显 $("[name='yid']").val(staff.yid); $("[name='yname']").val(staff.yname); $("[name='sex'][value='"+staff.sex+"']").attr("checked", true); $("[name='birth']").val(staff.birth); //爱好 var hobbys=staff.hobby.split(","); for ( var i in hobbys) { $("[name='hobby'][value='"+hobbys[i]+"']").attr("checked", true); } //部门 $("[name='bname']").val(staff.bmid); }, "json") /提交修改内容 $("#change").click(function() { $.post("staff?action=change", $("form").serialize(), function(i) { if(i>0){ alert("修改成功"); location="getall.jsp" } }, "text") }) }) </script> </head> <body> <form> <input type="hidden" name="yid"> 姓名: <input type="text" name="yname"><br> 性别: <input type="radio" value="男" name="sex">男 <input type="radio" value="女" name="sex">女<br> 生日: <input type="text" name="birth"><br> 爱好: <input type="checkbox" value="旅游" name="hobby" >旅游 <input type="checkbox" value="读书" name="hobby" >读书 <input type="checkbox" value="音乐" name="hobby" >音乐 <input type="checkbox" value="运动" name="hobby" >运动 <input type="checkbox" value="美食" name="hobby" >美食<br> 部门: <select name="bname"> </select><br> <input type="button" value="修改" id="change"> </form> </body> </html>表示层servlet
package control; import java.io.IOException; import java.util.HashMap; import java.util.List; import java.util.Map; 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 bean.Bumen; import bean.Staff; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import servicedao.ServiceDao; import serviceimpl.ServiceImpl; @WebServlet("/staff") public class StaffServlet extends HttpServlet{ ServiceDao sdao=new ServiceImpl(); @Override protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); String action=request.getParameter("action"); System.out.println(action); if(action.equals("getall")) { getall(request,response); } if(action.equals("show")) { show(request,response); } if(action.equals("change")) { change(request,response); } if(action.equals("del")) { del(request,response); } if(action.equals("add")) { add(request,response); } if(action.equals("option")) { option(request,response); } } private void add(HttpServletRequest request, HttpServletResponse response) { String yname=request.getParameter("yname"); String sex=request.getParameter("sex"); String birth=request.getParameter("birth"); String bmid=request.getParameter("bname"); String hobbys[]=request.getParameterValues("hobby"); String hobby=""; for (String string : hobbys) { hobby+=","+string; } Staff staff=new Staff(); staff.setYname(yname); staff.setSex(sex); staff.setBirth(birth); staff.setBmid(Integer.parseInt(bmid)); staff.setHobby(hobby); int i=sdao.add(staff); try { response.getWriter().print(i); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //获取下拉框 private void option(HttpServletRequest request, HttpServletResponse response) { // TODO Auto-generated method stub List<Bumen> list=sdao.option(); JSONArray json=JSONArray.fromObject(list); try { response.getWriter().print(json); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //删除 private void del(HttpServletRequest request, HttpServletResponse response) { String yid=request.getParameter("yid"); int i=sdao.del(yid); try { response.getWriter().print(i); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //修改 private void change(HttpServletRequest request, HttpServletResponse response) { String yname=request.getParameter("yname"); String yid=request.getParameter("yid"); String sex=request.getParameter("sex"); String birth=request.getParameter("birth"); String bmid=request.getParameter("bname"); String hobbys[]=request.getParameterValues("hobby"); String hobby=""; for (String string : hobbys) { hobby+=","+string; } Staff staff=new Staff(Integer.parseInt(yid), yname, sex, birth, hobby.substring(1),Integer.parseInt(bmid), null); int i=sdao.change(staff); try { response.getWriter().print(i); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //回显 private void show(HttpServletRequest request, HttpServletResponse response) { String yid=request.getParameter("yid"); Staff staff=sdao.show(yid); List<Bumen> list=sdao.option(); System.out.println(list); //将获取到的下拉框list集合,和需要回显的数据对象放入到map集合中 Map<String, Object> map=new HashMap<String, Object>(); map.put("staff",staff); map.put("list", list); //将map集合通过json技术转换成json对象 JSONObject json=JSONObject.fromObject(map); try { //将json对象传到jsp页面中的回调函数中 response.getWriter().print(json); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //获取列表信息 private void getall(HttpServletRequest request, HttpServletResponse response) { List<Staff> list=sdao.getall(); if(!list.isEmpty()) { request.setAttribute("list", list); try { request.getRequestDispatcher("getall.jsp").forward(request, response); } catch (ServletException | IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }数据访问层
package 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 bean.Bumen; import bean.Staff; import dao.StaffDao; import util.Getconn; public class StaffImpl implements StaffDao{ //获取列表所有信息 @Override public List<Staff> getall() { List<Staff> list =new ArrayList<Staff>(); Connection conn=Getconn.getconn(); String sql="select a.*,b.bname from staff a , bumen b where a.bmid=b.bmid"; try { PreparedStatement pre=conn.prepareStatement(sql); ResultSet re=pre.executeQuery(); while(re.next()) { Staff staff=new Staff(re.getInt(1),re.getString(2),re.getString(3),re.getString(4),re.getString(5),re.getInt(6),re.getString(7)); list.add(staff); } Getconn.closes(conn, pre, re); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } //回显要修改数据的值 @Override public Staff show(String yid) { Staff staff=null; Connection conn=Getconn.getconn(); String sql="select * from staff where yid=?"; try { PreparedStatement pre=conn.prepareStatement(sql); pre.setString(1, yid); ResultSet re=pre.executeQuery(); if(re.next()) { staff=new Staff(re.getInt(1),re.getString(2),re.getString(3),re.getString(4),re.getString(5),re.getInt(6),null); } Getconn.closes(conn, pre, re); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return staff; } //查询部门表,获取下拉框值 @Override public List<Bumen> option() { List<Bumen> list =new ArrayList<Bumen>(); Connection conn=Getconn.getconn(); String sql="select * from bumen"; try { PreparedStatement pre=conn.prepareStatement(sql); ResultSet re=pre.executeQuery(); while(re.next()) { Bumen bumen=new Bumen(re.getInt(1),re.getString(2)); list.add(bumen); } Getconn.closes(conn, pre, re); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } //修改 @Override public int change(Staff staff) { int i=0; Connection conn=Getconn.getconn(); String sql="update staff set yname=?,sex=?,birth=?,hobby=?,bmid=? where yid=?"; try { PreparedStatement pre=conn.prepareStatement(sql); pre.setString(1, staff.getYname()); pre.setString(2, staff.getSex()); pre.setString(3, staff.getBirth()); pre.setString(4, staff.getHobby()); pre.setInt(5, staff.getBmid()); pre.setInt(6, staff.getYid()); i=pre.executeUpdate(); Getconn.closes(conn, pre); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } //删除 @Override public int del(String yid) { int i=0; Connection conn=Getconn.getconn(); String sql="delete from staff where yid in ("+yid+")"; try { PreparedStatement pre=conn.prepareStatement(sql); i=pre.executeUpdate(); Getconn.closes(conn, pre); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } @Override public int add(Staff staff) { int i=0; Connection conn=Getconn.getconn(); String sql="insert into staff value(null,?,?,?,?,?)"; try { PreparedStatement pre=conn.prepareStatement(sql); pre.setString(1, staff.getYname()); pre.setString(2, staff.getSex()); pre.setString(3, staff.getBirth()); pre.setString(4, staff.getHobby()); pre.setInt(5, staff.getBmid()); i=pre.executeUpdate(); Getconn.closes(conn, pre); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } }以上是两表连查的核心代码部分 小白技术,大佬勿喷
