注意:jsp页面需要 bootstrap与layer前端框架
1 package cn.ajax.entity; 2 3 public class Student { 4 private int stuId;//int(5) NOT NULL学生id,主键 5 private String stuName;//varchar(30) NOT NULL学生姓名 6 private String pwd;//varchar(255) NULL密码 7 8 public Student() { 9 } 10 11 public Student(int stuId, String stuName, String pwd) { 12 this.stuId = stuId; 13 this.stuName = stuName; 14 this.pwd = pwd; 15 } 16 17 @Override 18 public String toString() { 19 return "Student{" + 20 "stuId=" + stuId + 21 ", stuName='" + stuName + '\'' + 22 ", pwd='" + pwd + '\'' + 23 '}'; 24 } 25 26 public int getStuId() { 27 return stuId; 28 } 29 30 public void setStuId(int stuId) { 31 this.stuId = stuId; 32 } 33 34 public String getStuName() { 35 return stuName; 36 } 37 38 public void setStuName(String stuName) { 39 this.stuName = stuName; 40 } 41 42 public String getPwd() { 43 return pwd; 44 } 45 46 public void setPwd(String pwd) { 47 this.pwd = pwd; 48 } 49 }
1 package cn.ajax.dao; 2 3 import cn.ajax.entity.Student; 4 import cn.ajax.util.DataUtil; 5 import org.apache.commons.dbutils.QueryRunner; 6 import org.apache.commons.dbutils.handlers.BeanHandler; 7 import org.apache.commons.dbutils.handlers.BeanListHandler; 8 import org.apache.commons.dbutils.handlers.ScalarHandler; 9 10 import java.sql.SQLException; 11 import java.util.HashSet; 12 import java.util.Iterator; 13 import java.util.List; 14 import java.util.Set; 15 16 public class StudentDao { 17 private QueryRunner runner = new QueryRunner(DataUtil.dataSource); 18 19 public static void main(String[] args) { 20 List<Student> s = new StudentDao().listStudentByName("",0,10); 21 for (Student student : s) { 22 System.out.println(student); 23 } 24 } 25 26 27 /** 28 * 查询 29 * @param name 30 * @return 31 */ 32 public List<Student> listStudentByName(String name,int startIndex,int pageSize){ 33 String sql = "select * from tblstudent where stuName like concat('%',?,'%') and stuId>22 order by stuId desc limit ?,?"; 34 System.out.println("分页数据sql显示:"+sql); 35 try { 36 return runner.query(sql,new BeanListHandler<>(Student.class),name,startIndex,pageSize); 37 } catch (SQLException e) { 38 e.printStackTrace(); 39 throw new RuntimeException("listStudentByNamesql异常"); 40 } 41 } 42 43 /** 44 * 统计学生总数量 45 * @param name 46 * @return 47 */ 48 public int countStudentByName(String name){ 49 String sql = "select count(1) from tblstudent where stuName like concat('%',?,'%') and stuId > 22 order by stuId desc"; 50 try { 51 return ((Long) runner.query(sql, new ScalarHandler(),name)).intValue(); 52 } catch (SQLException e) { 53 e.printStackTrace(); 54 throw new RuntimeException("countStudentByNamesql异常"); 55 } 56 } 57 58 /** 59 * 根据id查找对象 60 * @param id 61 * @return 62 */ 63 public Student findStudentById(int id){ 64 try { 65 return runner.query("select * from tblstudent where stuId = ?",new BeanHandler<>(Student.class),id); 66 } catch (SQLException e) { 67 e.printStackTrace(); 68 throw new RuntimeException("findStudentByIdsql异常"); 69 } 70 } 71 72 /** 73 * 新增 74 * @param student 75 * @return 76 */ 77 public int doAddStudent(Student student){ 78 try { 79 return runner.update("insert into tblstudent(stuName,pwd)values(?,?)",student.getStuName(),student.getPwd()); 80 } catch (SQLException e) { 81 e.printStackTrace(); 82 throw new RuntimeException("doAddStudentsql异常"); 83 } 84 } 85 86 /** 87 * 根据id删除 88 * @param ids 89 * @return 90 */ 91 public int doRemvoeStudentById(Set<Integer> ids){ 92 if (ids!=null && ids.size()!=0){ 93 StringBuffer sql = new StringBuffer(); 94 sql.append("delete from tblstudent where stuId in("); 95 Iterator<Integer> iteid = ids.iterator(); 96 while (iteid.hasNext()){ 97 sql.append(iteid.next()).append(","); 98 } 99 sql.delete(sql.length()-1,sql.length()); 100 sql.append(")"); 101 System.out.println("sql语句"+sql); 102 try { 103 return runner.update(sql.toString()); 104 } catch (SQLException e) { 105 e.printStackTrace(); 106 throw new RuntimeException("doRemvoeStudentByIdsql异常"); 107 } 108 } 109 throw new RuntimeException("doRemvoeStudentByIdsql参数为空异常"); 110 } 111 112 /** 113 * 根据id修改全部数据 114 * @param student 115 * @return 116 */ 117 public int doUpdateStudent(Student student) { 118 try { 119 return runner.update("update tblstudent set stuName=?,pwd=? where stuId = ?",student.getStuName(),student.getPwd(), student.getStuId()); 120 } catch (SQLException e) { 121 e.printStackTrace(); 122 throw new RuntimeException("doUpdateStudentsql异常"); 123 } 124 } 125 }
1 package cn.ajax.servlet; 2 3 import cn.ajax.dao.StudentDao; 4 import cn.ajax.entity.Student; 5 import cn.ajax.util.PageUtil; 6 import com.alibaba.fastjson.JSONObject; 7 8 import javax.servlet.ServletException; 9 import javax.servlet.annotation.WebServlet; 10 import javax.servlet.http.HttpServlet; 11 import javax.servlet.http.HttpServletRequest; 12 import javax.servlet.http.HttpServletResponse; 13 import java.io.IOException; 14 import java.io.PrintWriter; 15 import java.lang.reflect.Array; 16 import java.util.Arrays; 17 import java.util.HashSet; 18 import java.util.List; 19 import java.util.Set; 20 21 @WebServlet("/student") 22 public class StudentServlet extends HttpServlet { 23 24 private StudentDao sd = new StudentDao(); 25 26 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 27 doGet(request,response); 28 } 29 30 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 31 String stu = request.getParameter("stu"); 32 if ("list".equals(stu)){ 33 stulist(request,response); 34 }else if ("change".equals(stu)){ 35 stuaddORupdate(request,response); 36 }else if ("remove".equals(stu)){ 37 sturemove(request,response); 38 }else if ("findbyidd".equals(stu)){ 39 findbyid(request,response); 40 } 41 } 42 43 /** 44 * 根据id查询对象 45 * @param request 46 * @param response 47 */ 48 private void findbyid(HttpServletRequest request, HttpServletResponse response) throws IOException { 49 int id = Integer.parseInt(request.getParameter("updatebyid")); 50 Student studentById = sd.findStudentById(id); 51 String s = JSONObject.toJSONString(studentById); 52 PrintWriter writer = response.getWriter(); 53 writer.write(s); 54 writer.close(); 55 } 56 57 /** 58 * 根据id删除对象 59 * @param request 60 * @param response 61 * @throws IOException 62 */ 63 private void sturemove(HttpServletRequest request, HttpServletResponse response) throws IOException { 64 String removeid = request.getParameter("removeid");//接收ajax传过来的数据 65 PrintWriter writer = response.getWriter();//输出对象 66 String msg = "0";//返回的参数 67 if (!(removeid==null && "".equals(removeid))){ 68 // 分割去重 69 String[] split = removeid.split(","); 70 System.out.println("拿到的字符串:"+ Arrays.toString(split)); 71 Set<Integer> set = new HashSet<>();//去重 72 for (String s : split) { 73 set.add(Integer.parseInt(s)); 74 } 75 // 调用dao层方法作删除 76 int i = sd.doRemvoeStudentById(set); 77 msg = i+""; 78 } 79 writer.write(msg);//输出数据到页面的ajax函数中 80 writer.close();//关闭流 81 } 82 83 /** 84 * 新增或者修改change 85 * @param request 86 * @param response 87 */ 88 private void stuaddORupdate(HttpServletRequest request, HttpServletResponse response) throws IOException { 89 String insertid = request.getParameter("insertid"); 90 String uname = request.getParameter("uname"); 91 String upwd = request.getParameter("upwd"); 92 PrintWriter writer = response.getWriter(); 93 Student student = new Student(); 94 String msg = "0"; 95 //开始新增 96 System.out.println(insertid+"参数id"); 97 if (insertid==null || "".equals(insertid)) { 98 student.setStuName(uname); 99 student.setPwd(upwd); 100 int i = sd.doAddStudent(student); 101 msg = i + ""; 102 }else{ 103 //开始修改 104 student.setStuId(Integer.parseInt(insertid)); 105 student.setPwd(upwd); 106 student.setStuName(uname); 107 int i = sd.doUpdateStudent(student); 108 msg = i + ""; 109 } 110 writer.write(msg); 111 writer.close(); 112 } 113 114 /** 115 * 模糊查询 116 * @param request 117 * @param response 118 */ 119 private void stulist(HttpServletRequest request, HttpServletResponse response) throws IOException { 120 PageUtil<Student> util = new PageUtil<>(); 121 String kyword = request.getParameter("keyword"); 122 String pageIndexs = request.getParameter("pageIndexs"); 123 util.setPageSize(5);//每页多少条数据 124 if (pageIndexs==null || "".equals(pageIndexs) || "undefined".equals(pageIndexs)){ 125 pageIndexs = "1"; 126 } 127 util.setStartPage(Integer.parseInt(pageIndexs));//第一页开始 128 int startIndex = (util.getStartPage()-1)*util.getPageSize();//0 5;5 5;10 5, 129 List<Student> students = sd.listStudentByName(kyword,startIndex,util.getPageSize()); 130 util.setList(students);//页面数据集合 131 util.setTotalData(sd.countStudentByName(kyword));//数据总量 132 133 //将数据转换为json格式的字符串 134 String jsonString = JSONObject.toJSONString(util); 135 System.out.println(jsonString); 136 // String jsonString = JSONObject.toJSONString(students); 137 PrintWriter writer = response.getWriter(); 138 writer.write(jsonString); 139 writer.close();//输出到页面 140 } 141 }
1 package cn.ajax.filter; 2 3 import javax.servlet.*; 4 import javax.servlet.annotation.WebFilter; 5 import java.io.IOException; 6 7 @WebFilter("/*") 8 public class StudentFilter implements Filter { 9 public void destroy() { 10 } 11 12 public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws ServletException, IOException { 13 req.setCharacterEncoding("utf-8"); 14 resp.setCharacterEncoding("utf-8"); 15 chain.doFilter(req, resp); 16 } 17 18 public void init(FilterConfig config) throws ServletException { 19 20 } 21 22 }
1 package cn.ajax.util; 2 3 import com.mchange.v2.c3p0.ComboPooledDataSource; 4 5 public class DataUtil { 6 public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql"); 7 } 8 9 10 //pageBean 11 package cn.ajax.util; 12 13 import java.util.List; 14 15 /** 16 * 分页类 17 */ 18 public class PageUtil<T> { 19 private int startPage=1;//第一页 20 private int pageSize=5;//分页每页容量 21 private List<T> list;//每页容量中的数据 22 private int totalPage;//总页数量 23 private int totalData;//数据总数量 24 25 public PageUtil() { 26 } 27 28 public int getStartPage() { 29 return startPage; 30 } 31 32 public void setStartPage(int startPage) { 33 if (startPage < 1){ 34 this.startPage = 1; 35 } 36 this.startPage = startPage; 37 } 38 39 public int getPageSize() { 40 return pageSize; 41 } 42 43 public void setPageSize(int pageSize) { 44 if (pageSize < 1){ 45 this.pageSize = 5; 46 } 47 this.pageSize = pageSize; 48 } 49 50 public List<T> getList() { 51 return list; 52 } 53 54 public void setList(List<T> list) { 55 this.list = list; 56 } 57 58 /** 59 * 共有多少页 60 * @return 61 */ 62 public int getTotalPage() { 63 int sumPage = (this.totalData-1)/this.pageSize + 1; 64 return sumPage; 65 } 66 67 public void setTotalPage(int totalPage) { 68 this.totalPage = totalPage; 69 } 70 71 public int getTotalData() { 72 return totalData; 73 } 74 75 public void setTotalData(int totalData) { 76 this.totalData = totalData; 77 } 78 }
1 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> 2 <%-- 3 Created by IntelliJ IDEA. 4 User: 冰与火之歌 5 Date: 2019/8/30 6 Time: 22:44 7 To change this template use File | Settings | File Templates. 8 --%> 9 <%@ page contentType="text/html;charset=UTF-8" language="java" %> 10 <html> 11 <head> 12 <title>列表</title> 13 <link rel="stylesheet" href="${pageContext.request.contextPath}/bootstrap/css/bootstrap.min.css" /> 14 15 <!--2.引入layerUI样式; --> 16 <link rel="stylesheet" href="${pageContext.request.contextPath}/layer/layer/theme/default/layer.css"> 17 18 <script src="${pageContext.request.contextPath}/bootstrap/js/jquery.min.js"></script> 19 <script src="${pageContext.request.contextPath}/bootstrap/js/bootstrap.min.js"></script> 20 21 <!--5.引入LayerUi的js库; --> 22 <script src="${pageContext.request.contextPath}/layer/layer/layer.js"></script> 23 </head> 24 <body> 25 26 <div class="container"> 27 <div class="panel panel-primary"> 28 <div class="panel-heading"> 29 <%--c3p0+mysql+bootstrap+javascript+jquery+ajax--%> 30 <h1>使用ajax异步加载用户数据</h1> 31 </div> 32 <div class="panel-body"> 33 <form class="form-inline"> 34 <input class="form-control" value="" id="keyword" placeholder="请输入关键字查询。。。"/> 35 <a href="javascript:fuzzyQuery()" class="btn btn-primary">根据姓名模糊查询</a> 36 <%--<a href="javascript:changeFunction()" id="add_btn" class="btn btn-success">新增用户</a>--%> 37 <a href="javascript:changeFunction()" class="btn btn-success">新增用户</a> 38 <a href="javascript:removelistFunction()" class="btn btn-danger">删除多个</a> 39 </form> 40 <table class="table table-bordered table-hover table-striped text-center"> 41 <tr> 42 <td>全选 43 <input type="checkbox" id="all_check"> 44 </td> 45 <td>用户id</td> 46 <td>用户名</td> 47 <td>密码</td> 48 <td>操作</td> 49 </tr> 50 <tbody id="tb"> 51 <%--剩下的内容由ajax异步 加载!--%> 52 </tbody> 53 </table> 54 55 <%--分页条--%> 56 <nav aria-label="Page navigation"> 57 <ul class="pagination" id="splitPage"> 58 </ul> 59 </nav> 60 </div> 61 <div class="panel-footer text-right"> 62 李某人为您服务 63 </div> 64 </div> 65 </div> 66 67 <!-- 模态框(Modal) --> 68 <div class="modal fade" id="myModal" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true"> 69 <div class="modal-dialog"> 70 <div class="modal-content"> 71 <div class="modal-header"> 72 <button type="button" class="close" data-dismiss="modal" aria-hidden="true"> 73 × 74 </button> 75 <h4 class="modal-title" id="myModalLabel"> 76 添加(修改)一个用户 77 </h4> 78 </div> 79 <div class="modal-body"> 80 <!--放正文的地方; 不要用表单提交 ,也不要配置action! --> 81 <form id="myFrm" name="myFrm" class="form-horizontal"> 82 <div class="form-group"> 83 <label for="uname" class="col-md-4 control-label" >用户名:</label> 84 <div class="col-md-6"> 85 <input type="hidden" name="cid" id="cid" value="0" /> 86 <input class="form-control" name="uname" id="uname" > 87 </div> 88 </div> 89 <div class="form-group"> 90 <label for="upwd" class="col-md-4 control-label" >密码:</label> 91 <div class="col-md-6"> 92 <input class="form-control" name="upwd" id="upwd" > 93 </div> 94 </div> 95 <div class="form-group"> 96 <label class="col-md-4 control-label"> </label> 97 <div class="col-md-8"> 98 <a href="javascript:saveFunction()" id="save" class="btn btn-success" style="font-size: 24px; text-shadow:black 5px 3px 3px"> 99 提交保存 100 </a> 101 </div> 102 </div> 103 </form> 104 105 </div> 106 </div><!-- /.modal-content --> 107 </div><!-- /.modal --> 108 </div> 109 110 </body> 111 112 <script> 113 $(function () { 114 //页面一加载,就调用方法 115 listFunction(); 116 }); 117 118 119 120 //复选框点击,则全选 121 $("#all_check").change("click",function () { 122 /*必须要用prop方法,才能实现,可能有版本的原因*/ 123 $("[name=tdbox_stuid]").prop("checked",$(this).is(":checked")) 124 }); 125 126 //删除多个,需要判断复选框,与removeFunction调用一样的后台方法 127 function removelistFunction() { 128 //清空输入框中的内容 129 $("#keyword").val(""); 130 //如果没有选中则无法删除,,,all_check 131 //遍历所有的checkbox标签,判断是否选中 132 var td_obj = $("input:checkbox[name='tdbox_stuid']:checked"); 133 //选中的长度大于0,表示有选中的,可以点击批量删除按钮 134 if (td_obj.length>0){ 135 var id_array = ''; 136 $.each(td_obj,function(){ 137 //拿到id 138 id_array = id_array+$(this).parent().next().html()+","; 139 // window.alert("你选了:"+ 140 // $('input[type=checkbox]:checked').length+"个,其中有:"+$(this).parent().next().html()); 141 }); 142 alert(id_array); 143 // //Ajax执行 144 $.post("/student?stu=remove","removeid="+id_array,function (data) { 145 if (data!="0") { 146 layer.msg("删除成功",{icon:6}); 147 }else{ 148 layer.msg("删除失败",{icon:5}); 149 } 150 },"text"); 151 listFunction();//刷新 152 } else{ 153 layer.msg("亲!至少选中一行数据哦!",{icon:5}); 154 } 155 } 156 157 //删除一个,需要带有参数id,与yuremovelistFunction调用一样的后台方法 158 function removeFunction(k_id) { 159 //清空输入框中的内容 160 $("#keyword").val(""); 161 if( confirm("确定要删除【学号"+k_id+"】的学生么?删除后数据将丢失!!!")){ 162 $.get("/student?stu=remove&removeid="+k_id,function (data) { 163 // alert(data=="1"?"删除成功":"删除失败"); 164 if (data!="0") { 165 layer.msg("删除成功",{icon:6}); 166 }else{ 167 layer.msg("删除失败",{icon:5}); 168 } 169 //刷新,将数据在查询一遍 170 listFunction(); 171 },"text"); 172 } 173 } 174 175 //新增用户,打开模态框, 同时还需要将学历、角色ajax出来(还未实现,二级联动) 176 function changeFunction() { 177 //点击新增,弹出模态框,但是在出现之前清空输入框中的数据 178 $("#cid").val(""); 179 $("#uname").val(""); 180 $("#upwd").val(""); 181 $("#myModal").modal("show"); 182 } 183 184 //根据id查询对象,用于修改操作 185 function updateFunction(k_id) { 186 //做修改之前,先根据id查询一片 187 $.get("/student?stu=findbyidd&updatebyid="+k_id,function (data) { 188 //将数据保存到收入框中 189 $("#cid").val(k_id); 190 $("#uname").val(data.stuName); 191 $("#upwd").val(data.pwd); 192 //打开模态框 193 $("#myModal").modal("show"); 194 },"json"); 195 } 196 197 //做模态框,保存新增或修改后的数据操作 198 function saveFunction() { 199 //清空输入框中的内容 200 $("#keyword").val(""); 201 var uid = $("#cid").val(); 202 var uname = $("#uname").val(); 203 var upwd = $("#upwd").val(); 204 //post方式为:url、参数、回调函数、数据类型 205 $.post("/student?stu=change","insertid="+uid+"&uname="+uname+"&upwd="+upwd,function (data) { 206 //表示新增 207 if (uid == "" || uid == null || uid == undefined){ 208 if (data=="1") { 209 // alert("新增成功") 210 layer.msg("新增成功",{icon:6}); 211 }else { 212 // alert("新增失败") 213 layer.msg("新增失败",{icon:5}); 214 } 215 listFunction(); 216 } else{ 217 //表示修改 218 if (data=="1") { 219 // alert("修改成功") 220 layer.msg("修改成功", {icon: 6}); 221 }else { 222 // alert("修改失败") 223 layer.msg("修改失败",{icon:5}); 224 } 225 listFunction(); 226 } 227 },"text"); 228 229 //完成新增后或者修改后,需在查询一遍,才会更新后的数据 230 // listFunction(); 231 //需要关闭模态框 232 $("#myModal").modal("hide"); 233 } 234 235 //模糊查询点击事件 236 function fuzzyQuery() { 237 listFunction(); 238 } 239 240 //模糊查询 241 function listFunction(index_value) { 242 //拿到关键字 243 var keyword_val = $("#keyword").val(); 244 $.get("/student?stu=list&keyword="+keyword_val+"&pageIndexs="+index_value,function (data) { 245 var tr_tag = "";//拼接 246 var data_list = data.list; 247 //遍历data数据,保存的json格式的数据,通过下标,找到对象 248 $(data_list).each(function (i,o) { 249 tr_tag += "<tr>"; 250 tr_tag += "<td><input type='checkbox' name='tdbox_stuid' /></td>"; 251 tr_tag += "<td>"+o.stuId+"</td>"; 252 tr_tag += "<td>"+o.stuName+"</td>"; 253 tr_tag += "<td>"+o.pwd+"</td>"; 254 tr_tag += "<td><a href='javascript:updateFunction("+o.stuId+")' class='btn btn-warning'>修改</a> "; 255 tr_tag += "<a href='javascript:removeFunction("+o.stuId+")' class='btn btn-danger'>删除</a></td>"; 256 tr_tag += "</tr>"; 257 }); 258 $("#tb").html(tr_tag); 259 260 /*分页条*/ 261 var nav_tag = "";//拼接分页条 262 nav_tag += "<li><a href=\"#\" aria-label=\"Previous\"><span aria-hidden=\"true\">«</span></a></li>"; 263 for(var pindex = 1; pindex <= data.totalPage ;pindex++){ 264 nav_tag += "<li><a href='javascript:getindex("+pindex+")' id='a_value' >"+pindex+"</a></li>"; 265 } 266 nav_tag += "<li><a href=\"#\" aria-label=\"Previous\"><span aria-hidden=\"true\">»</span></a></li>"; 267 $("#splitPage").html(nav_tag); 268 },"json"); 269 } 270 271 /*点击分页*/ 272 function getindex(pindex) { 273 listFunction(pindex); 274 } 275 276 /* 277 * 278 * 279 * //模糊查询 280 function listFunction() { 281 //拿到关键字 282 var keyword_val = $("#keyword").val(); 283 $.get("/student?stu=list&keyword="+keyword_val,function (data) { 284 var tr_tag = "";//拼接 285 //遍历data数据,保存的json格式的数据,通过下标,找到对象 286 $(data).each(function (i,o) { 287 tr_tag += "<tr>"; 288 tr_tag += "<td><input type='checkbox' name='tdbox_stuid' /></td>"; 289 tr_tag += "<td>"+o.stuId+"</td>"; 290 tr_tag += "<td>"+o.stuName+"</td>"; 291 tr_tag += "<td>"+o.pwd+"</td>"; 292 tr_tag += "<td><a href='javascript:updateFunction("+o.stuId+")' class='btn btn-warning'>修改</a> "; 293 tr_tag += "<a href='javascript:removeFunction("+o.stuId+")' class='btn btn-danger'>删除</a></td>"; 294 tr_tag += "</tr>"; 295 }); 296 $("#tb").html(tr_tag); 297 },"json"); 298 } 299 * 300 * 301 * */ 302 </script> 303 304 </html>