Day15
一、数据库分页
上一节课中虽然我们写出来了查看数据，但是查看到的数据都在一页显示，如果这样显示数据，当数据量很大了，比如几千万条数据，取到list之后迭代出
的customer对象就会有几千万个，加入每个1k就会占据很大内存，如果占用的内存超过了虚拟机的内存则会出现内存溢出，就会导致数据丢失等情况
所以获取查看数据必须要分页显示
•Select * from table limit M,N
•M：记录开始索引位置
•N：取多少条记录。
select id,name from customer limit 0,10;
通过上面这个命令说明mysql提供了支持分页的技术，当我要显示第二页就从第10条数据显示
select id,name from customer limit 10,20;
然后我们从上面这条还可以看出如果不满10则取完剩余的数据
知道这些之后我们就可以开始动手做分页了，不过还是很麻烦的
老张要求分页要统一，以下是张老大的要求
下面是分页的设计图
解说：当在显示页面点击某页的时候，会将请求交给一个servlet，这个servlet收到请求后会将请求参数数据封装到pageInfo中，这个pageInfo中有几个属性
currentPage记录用户想看的第页面，pageSize记录每页记录多少条，starIndex记录该页在数据库的起始位置。servlet将请求参数封装之后交给一个service
这个service（BusinessService），然后这个service通过调用dao将数据取出来封装到一个叫做QueryResult，这个QueryResult里面有个list记录取到的数据
还有一个totalRecord记录总记录数，这个totalRecord总记录数是为了计算出在页面中显示共多少页和当前第多少页。然后service（BusinessService）将
QueryResult对象中封装的查询结果，生产显示页面数据的pageBean对象，这个pageBean对象中记录了totalrecord总共多少记录，每页多少条数据pagesize
总共页数totalpage，当前第几页currentpage，上一页previousPage，下一页nextPage，还有页码条pageBar，页码条是用整型数组记录的，最后jsp页面拿到
pageBean中的数据显示就可以了
完成WEB页面的分页显示
•先获得需分页显示的记录总数，然后在web页面中显示页码。
•根据页码，从数据库中查询相应的记录显示在web页面中。
•以上两项操作通常使用Page对象进行封装。
开始写代码
由于几个bean对象在分页主键中起着传递作用，所以先做bean对象，然后在做dao，在做service，在做jsp
/day14_customer/src/cn/itcast/domain/PageInfo.java
这个bean类中有三个属性，currentPage记录当前页，pageSize记录每页多少条数据，startIndex记录从数据库的那条开始取
由于用户第一次访问时没有代入当前页和每页多少条数据，所以currentPage和pageSize有默认初始值分别是1和5
/day14_customer/src/cn/itcast/domain/PageInfo.java
package cn.itcast.domain; public class PageInfo { private int currentpage = 1; private int pagesize = 5; private int startindex; public int getCurrentpage() { return currentpage; } public void setCurrentpage(int currentpage) { this.currentpage = currentpage; } public int getPagesize() { return pagesize; } public void setPagesize(int pagesize) { this.pagesize = pagesize; } public int getStartindex() { this.startindex = (this.currentpage-1)*this.pagesize; //startindex开始页是根据当前页和每页条数算出来的 return startindex; } }
/day14_customer/src/cn/itcast/domain/QueryResult.java
这个bean类用来封装查询结果以及记录总记录数
package cn.itcast.domain; import java.util.List; public class QueryResult { private List list; //记往查询的页面数据 private int totalrecord; //记住总记录数 public List getList() { return list; } public void setList(List list) { this.list = list; } public int getTotalrecord() { return totalrecord; } public void setTotalrecord(int totalrecord) { this.totalrecord = totalrecord; } }
/day14_customer/src/cn/itcast/domain/PageBean.java
这个pageBean对象有些复杂，属性比较多，有封装获取到的数据的list，记录总共记录totalRecord，每页多少条pageSize，共多少页totalPage，当前第几页currentPage
上一页previousPage，下一页nextPage，页码条pageBar[]
package cn.itcast.domain; import java.util.List; public class PageBean { //QueryResult private List list; //可以从QureryResult中获取 private int totalrecord; //也可以从QureryResult中获取 private int pagesize; //pageInfor中有直接带过来就可以 private int totalpage; //总页数要根据总记录数算出来，可以根据总记录数和每页记录数据来计算 private int currentpage;//pageInfor中有，可以直接带过来 private int previouspage; //根据当前页算出来的，但是有限制，必须判断当前页是否小于1 private int nextpage; //也是根据当前页算出来的，但是有限制，必须判断当前页是否大于总页数 private int pagebar[];//根据总页数算出来的 public List getList() { return list; } public void setList(List list) { this.list = list; } public int getTotalrecord() { return totalrecord; } public void setTotalrecord(int totalrecord) { this.totalrecord = totalrecord; } public int getPagesize() { return pagesize; } public void setPagesize(int pagesize) { this.pagesize = pagesize; } public int getTotalpage() { //总页数是根据总记录数和每页多少条数据计算出来的，所以没有必要有setTotalpage if(this.totalrecord%this.pagesize==0){ this.totalpage = this.totalrecord/this.pagesize; }else{ this.totalpage = this.totalrecord/this.pagesize+1; } return totalpage; } public int getCurrentpage() { return currentpage; } public void setCurrentpage(int currentpage) { this.currentpage = currentpage; } public int getPreviouspage() { //根据当前页算出来的，但是有限制，必须判断当前页是否小于1 if(this.currentpage-1<1){ this.previouspage = 1; }else{ this.previouspage = this.currentpage-1; } return previouspage; } public int getNextpage() {//也是根据当前页算出来的，但是有限制，必须判断当前页是否大于总页数 if(this.currentpage+1>this.totalpage){ this.nextpage = this.totalpage; }else{ this.nextpage = this.currentpage+1; } return nextpage; } public int[] getPagebar() {//页码条可以根据总页数算出来的，所以也没有必要要setPagebar()这个方法 //页码条的总个数是10个 if(this.totalpage<=10){ this.pagebar = new int[this.totalpage]; //根据总页数来new一个int数组，这个int数组就代表页码条 for(int i=1;i<=this.totalpage;i++){ this.pagebar[i-1] = i; } return pagebar; } //[-1.....,23]; 20 18 int startpage = this.currentpage - 4; int endpage = this.currentpage + 5; if(startpage<1){ startpage = 1; endpage = 10; } if(endpage>this.totalpage){ endpage = this.totalpage; startpage = this.totalpage - 9; } this.pagebar = new int[10]; int index = 0; for(int i=startpage;i<=endpage;i++){ this.pagebar[index++] = i; } return pagebar; } }
接下来做Dao
/day14_customer/src/cn/itcast/dao/CustomerDao.java
在这个dao层中从数据库中获取到需要的数据然后封装到QueryResult中，不过在这里用了两次sql语句查询
package cn.itcast.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.QueryResult; import cn.itcast.exception.DaoException; import cn.itcast.utils.JdbcUtils; public class CustomerDaoImpl implements CustomerDao { /* * id varchar(40) primary key, name varchar(20) not null, gender varchar(4) not null, birthday date, cellphone varchar(40) not null, email varchar(100), preference varchar(100), type varchar(40), description varchar(255) */ public void add(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into customer(id,name,gender,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setString(1, customer.getId()); st.setString(2, customer.getName()); st.setString(3, customer.getGender()); st.setDate(4, new java.sql.Date(customer.getBirthday().getTime())); //将java.util.Date转换成java.sql.Date st.setString(5, customer.getCellphone()); st.setString(6, customer.getEmail()); st.setString(7, customer.getPreference()); st.setString(8, customer.getType()); st.setString(9, customer.getDescription()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void update(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void delete(String id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public Customer find(String id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } return null; } // public List<Customer> getAll(){ //getAll分页中不用getAll方法 // Connection conn = null; // PreparedStatement st = null; // ResultSet rs = null; // try{ // conn = JdbcUtils.getConnection(); // String sql = "select * from customer"; // st = conn.prepareStatement(sql); // rs = st.executeQuery(); // List list = new ArrayList(); // while(rs.next()){ // Customer c = new Customer(); // c.setBirthday(rs.getDate("birthday")); // c.setCellphone(rs.getString("cellphone")); // c.setDescription(rs.getString("description")); // c.setEmail(rs.getString("email")); // c.setGender(rs.getString("gender")); // c.setId(rs.getString("id")); // c.setName(rs.getString("name")); // c.setPreference(rs.getString("preference")); // c.setType(rs.getString("type")); // list.add(c); // } // return list; // }catch (Exception e) { // throw new DaoException(e); // }finally{ // JdbcUtils.release(conn, st, rs); // } // } public QueryResult pageQuery(int startindex,int pagesize){ //注意这个方法返回的不是List而是QueryResult Connection conn = null; PreparedStatement st = null; ResultSet rs = null; QueryResult qr = new QueryResult(); //准备好一个QueryResult try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer limit ?,?"; //第一次SQL查询获取到所有的数据 st = conn.prepareStatement(sql); st.setInt(1, startindex); st.setInt(2, pagesize); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } qr.setList(list); sql = "select count(*) from customer"; //通过第二次查询获取总记录数 st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ //这里数据库表中只有一行一列，所以就取第一列的总数就可以取出总记录数了，没有必要取出 qr.setTotalrecord(rs.getInt(1));//结果集中获取总记录数，获取后然后又将总记录数存入到总记录属性中 } return qr; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } }
接下来是写service
/day14_customer/src/cn/itcast/service/BusinessService.java
这个类通过接收PageInfo中的数据返回pageBean
package cn.itcast.service; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.PageBean; import cn.itcast.domain.PageInfo; import cn.itcast.domain.QueryResult; import cn.itcast.utils.DaoFactory; public class BusinessService { //此处引用接口调用，而且这里要用工厂来创建dao层，便于将来解耦 private CustomerDao customerDao = DaoFactory.getInstance().createDao(); public void addCustomer(Customer customer){ customerDao.add(customer); } public PageBean pageQuery(PageInfo pageInfo){ //这里设计方法的时候一定要用面向对象的思想去考虑，接收参数PageInfo，返回PageBean QueryResult qr = customerDao.pageQuery(pageInfo.getStartindex(), pageInfo.getPagesize()); PageBean pageBean = new PageBean();//pageBean虽然属性有很多，但是有很多属性都在自己类中算出来的，所以这里才设置这么少的 pageBean.setCurrentpage(pageInfo.getCurrentpage()); pageBean.setList(qr.getList()); pageBean.setPagesize(pageInfo.getPagesize()); pageBean.setTotalrecord(qr.getTotalrecord()); return pageBean; //到这里分页算写完了，接下来就是写页面显示的内容了 } }
接下来就是改造servlet，servlet的作用是连接jsp和service层的，所以它就用两个作用，将JSP中的请求数据传给service，还有就是将service层中的数据存到域中带给JSP
/day14_customer/src/cn/itcast/web/controller/ListCustomerServlet.java
这个类第一是将页面请求数据封装pageInfo中去，第二是将通过service层获取到pageBean对象
package cn.itcast.web.controller; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import cn.itcast.domain.PageBean; import cn.itcast.domain.PageInfo; import cn.itcast.service.BusinessService; import cn.itcast.utils.WebUtils; public class ListCustomerServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PageInfo pageInfo = WebUtils.request2Bean(request, PageInfo.class); //将用户的请求参数封装到PageInfo中去 BusinessService service = new BusinessService(); PageBean pageBean = service.pageQuery(pageInfo); //servlet还要将service层中返回的pageBean通过存入到request域中带给JSP显示 request.setAttribute("pageBean", pageBean); request.getRequestDispatcher("/WEB-INF/jsp/listcustomer.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
然后我们来改造listcustomer.jsp
这里获取数据时充pageBean中的list中获取数据，另外还要加上分页
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>显示所有用户</title> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}"> <tr> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${c.description }</td> <td> <a href="#">修改客户</a> <a href="#">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ var pagesize = document.getElementsByName("pagesize")[0].value; var pagenum = input.value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页
<!--这里无论点击上一页还是页码还是下一页的请求还是发给ListCustomerServlet处理，为了避免重复这里干脆写javascript --> <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum"> <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> </body> </html>
————————————————————————————————————————————————————————————————————————————————
上一节课虽然讲分页做出来了，但是还是有问题，当分页有很多时，分页条会将数据显示在同一个页面导致页面全是分页条数据，所以这个时候就必须限定每个分页条最多能显示多少
页，根据百度和谷歌的分页，都是以当前页为参照，假如规定每页最多能显示10条，当前页是第10页，则分页条显示的数据是6到15，也就是当前页向后减去4，向前加上5，也就是起始页为6，结束页为15
但是这里还要分几种情况：
第一种总页数小于10页的就直接返回页码条数组
第二种总页数大于10页的时候(比如20页)，则需要知道起始页和结束也，也就是起始页=当前页-3，结束页=当前页+5，但是这样则又会有两种情况出现
（1）当起始页小于1（比如，当前页是第3页，则起始页=3-4=-1，结束页=3+5=8，如果是这样就不对了），这时起始页就必须等于1，结束页必须等于10
（2）当结束也大于总页数（比如，总共页10页，当前页是18页，则起始页=18-4=14，结束页=18+5=23，明显结束也超出了总共页数），这个时候需要结束页
等于总共页，起始页等于总共页-9
求完了起始页和结束页之后我们就用起始页到结束页来填充大小为10的int数组，这个时候我们有两种为数组下标复制的方法，第一种定义一个int变量，让它自增
第二种就是i-startPage
/day14_customer/src/cn/itcast/domain/PageBean.java
package cn.itcast.domain; import java.util.List; public class PageBean { //QueryResult private List list; private int totalrecord; private int pagesize; private int totalpage; //总页数 private int currentpage; private int previouspage; private int nextpage; private int pagebar[]; public List getList() { return list; } public void setList(List list) { this.list = list; } public int getTotalrecord() { return totalrecord; } public void setTotalrecord(int totalrecord) { this.totalrecord = totalrecord; } public int getPagesize() { return pagesize; } public void setPagesize(int pagesize) { this.pagesize = pagesize; } public int getTotalpage() { if(this.totalrecord%this.pagesize==0){ this.totalpage = this.totalrecord/this.pagesize; }else{ this.totalpage = this.totalrecord/this.pagesize+1; } return totalpage; } public int getCurrentpage() { return currentpage; } public void setCurrentpage(int currentpage) { this.currentpage = currentpage; } public int getPreviouspage() { if(this.currentpage-1<1){ this.previouspage = 1; }else{ this.previouspage = this.currentpage-1; } return previouspage; } public int getNextpage() { if(this.currentpage+1>this.totalpage){ this.nextpage = this.totalpage; }else{ this.nextpage = this.currentpage+1; } return nextpage; } public int[] getPagebar() { //此处是整个项目中最难的算法了，要分成很多种条件 //页码条的总个数是10个 if(this.totalpage<=10){ //一上来就需要检测页码条是否超过10个，如果没有超过10个则就直接返回页码条 this.pagebar = new int[this.totalpage]; for(int i=1;i<=this.totalpage;i++){ this.pagebar[i-1] = i; } return pagebar; } //[-1.....,23]; 20 18 int startpage = this.currentpage - 4; //开始页 int endpage = this.currentpage + 5; //结束也 if(startpage<1){ //当开始页小于1的时候，比如当前页是第3页，规定每页10页，则起始页就会等于-1，小于1了 startpage = 1; endpage = 10; } if(endpage>this.totalpage){ //假如结束页大于总共页，比如总共页有20页，当前页为18页 endpage = this.totalpage; //这个时候结束页面就是总共页 startpage = this.totalpage - 9; //开始页就是总共页减去9，如果当前页是18页，总共页是20页，那么开始也就是20-9=11页，结束也就是总共页 } this.pagebar = new int[10]; int index = 0; for(int i=startpage;i<=endpage;i++){ this.pagebar[index++] = i; //此处为数组定义下角标页有两种方法，第一种向这样定义一个int变量index，让它自增，第二种其实就是i-startPage } return pagebar; } }
上面做好了每页显示固定条数的页码条之后，还有位当前页做一个选中后的效果，以便和其它未选中页面区别开来，这个就可以在JSP页面中去做了
在listCustomer.jsp中可以判断如果当前页等于pageNumber这显示字体为红色，如果当前页不等于pageNumber则还是超链接
/day14_customer/WebRoot/WEB-INF/jsp/listcustomer.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %> <%@taglib uri="/WEB-INF/my.tld" prefix="itcast" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color: #FFFF66} .odd{background-color: #FF99FF} tr:hover{background-color: #0066FF} </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}" varStatus="status"> <tr class="${status.count%2==0?'even':'odd' }"> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${fn:escapeXml(c.description)}</td> <td> <a href="${pageContext.request.contextPath }/servlet/EditCustomerUIServlet?id=${c.id }">修改客户</a> <a href="javascript:void(0)" onclick="del('${c.id }')">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function del(id){ var b = window.confirm("删除吗？"); if(b){ window.location.href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=" + id; } } function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ var pagesize = document.getElementsByName("pagesize")[0].value; var pagenum = input.value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <!-- 如果 当前页不等于pagenum则显示超链接--> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <!-- 这里做一个if，如果检测到当前页等于某个pagenum则显示红色 --> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum"> <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> </body> </html>
然后我们还要做每页可以自己设置1显示多少条，这里还是在JSP页面中做，我们将每页xx条这一项用input文本框阔气来，然后再javascript中向
servlet传递值时传给seerlet就可以了
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %> <%@taglib uri="/WEB-INF/my.tld" prefix="itcast" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color: #FFFF66} .odd{background-color: #FF99FF} tr:hover{background-color: #0066FF} </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}" varStatus="status"> <tr class="${status.count%2==0?'even':'odd' }"> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${fn:escapeXml(c.description)}</td> <td> <a href="${pageContext.request.contextPath }/servlet/EditCustomerUIServlet?id=${c.id }">修改客户</a> <a href="javascript:void(0)" onclick="del('${c.id }')">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function del(id){ var b = window.confirm("删除吗？"); if(b){ window.location.href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=" + id; } } function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; //在向servlet发请求之前会将当前页currentPage和每页多少条pagesize传递过去 window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ var pagesize = document.getElementsByName("pagesize")[0].value; var pagenum = input.value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, <!-- 此处可以输入每页多少条，默认每页的条数是pageSize --> 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum"> <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> </body> </html>
然后分页还有一个跳转到某个页面的功能，跳转功能就在go按钮上做一个javascript方法goWhich，将input传递给这个方法
之后通过获取input输入框中的value值，然后将这个值传递给servlet，但同时也要将pagesize传递给servlet
/day14_customer/WebRoot/WEB-INF/jsp/listcustomer.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %> <%@taglib uri="/WEB-INF/my.tld" prefix="itcast" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color: #FFFF66} .odd{background-color: #FF99FF} tr:hover{background-color: #0066FF} </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}" varStatus="status"> <tr class="${status.count%2==0?'even':'odd' }"> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${fn:escapeXml(c.description)}</td> <td> <a href="${pageContext.request.contextPath }/servlet/EditCustomerUIServlet?id=${c.id }">修改客户</a> <a href="javascript:void(0)" onclick="del('${c.id }')">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function del(id){ var b = window.confirm("删除吗？"); if(b){ window.location.href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=" + id; } } function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; //在向servlet发请求之前会将当前页currentPage和每页多少条pagesize传递过去 window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ //这个goWhich方法接收一个input输入项 var pagesize = document.getElementsByName("pagesize")[0].value; //注意这里还要获取pagesize，并将一起传递给servlet，不然当你改变pagesize后在输入去某页则在这页中显示条数是默认的条数 var pagenum = input.value; //得到你传入的pagenum window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; //把这个pageNum发给servlet } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum">页 <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> <!-- 当按了go之后就会去执行goWhich方法，这个方法拿到了跳转页的pagenum --> </body> </html>
到这儿分页算完成了，但是在实际开发中还要对分页中写入的数据进行检查，比如输入每页多少条时有的人可能不输入数字，或者到某夜页去也不输入数字，这时就需要对这些数据进行检测
综上要做一个分页其实还是很简单的，复杂主要在jsp页面中，另外一点就是页码条pagebar的几种情况
——————————————————————————————————————————————————————————————————————————————————
下面还是来巩固一下，准备在做一个分页：
第一步：准备好数据库
create datebase test character set utf8 collate utf8_general_ci;
use test
create table student
(
id int primary key auto_increment,
name varchar(40)
);
insert into student(name) values("a");
insert into student(name) values("b");
insert into student(name) values("c");
insert into student(name) values("d");
insert into student(name) values("e");
insert into student(name) values("f");
insert into student(name) values("g");
insert into student(name) values("h");
insert into student(name) values("i");
insert into student(name) values("j");
insert into student(name) values("k");
insert into student(name) values("l");
insert into student(name) values("m");
insert into student(name) values("n");
insert into student(name) values("o");
insert into student(name) values("p");
insert into student(name) values("q");
insert into student(name) values("r");
insert into student(name) values("s");
insert into student(name) values("t");
第二步：搭建工程环境
导入jar包
将day14_customer中以上的奶瓶复制到WEB-INF中的lib目录下
第三步:复制day14_customer中的工具包，并删除掉不用的工具包，DaoFactory.utils,Globals,java,MyEl.java，然后还要复制配置文件
删除之后
复制配置文件
以上为准备工作
第四步：开始创建
这步又有分几步
(1)首先创建出Studen的实体
package cn.itcast.domain; public class Student { private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
(2)导入分页需要的三个实体PageInfo，QueryResult，PageBean
PageInfo.java
package cn.itcast.domain; public class PageInfo { private int currentpage = 1; private int pagesize = 5; private int startindex; public int getCurrentpage() { return currentpage; } public void setCurrentpage(int currentpage) { this.currentpage = currentpage; } public int getPagesize() { return pagesize; } public void setPagesize(int pagesize) { this.pagesize = pagesize; } public int getStartindex() { this.startindex = (this.currentpage-1)*this.pagesize; return startindex; } }
QueryResult.java
package cn.itcast.domain; import java.util.List; public class QueryResult { private List list; //记往查询的页面数据 private int totalrecord; //记住总记录数 public List getList() { return list; } public void setList(List list) { this.list = list; } public int getTotalrecord() { return totalrecord; } public void setTotalrecord(int totalrecord) { this.totalrecord = totalrecord; } }
PageBean.java
package cn.itcast.domain; import java.util.List; public class PageBean { //QueryResult private List list; private int totalrecord; private int pagesize; private int totalpage; //总页数 private int currentpage; private int previouspage; private int nextpage; private int pagebar[]; public List getList() { return list; } public void setList(List list) { this.list = list; } public int getTotalrecord() { return totalrecord; } public void setTotalrecord(int totalrecord) { this.totalrecord = totalrecord; } public int getPagesize() { return pagesize; } public void setPagesize(int pagesize) { this.pagesize = pagesize; } public int getTotalpage() { if(this.totalrecord%this.pagesize==0){ this.totalpage = this.totalrecord/this.pagesize; }else{ this.totalpage = this.totalrecord/this.pagesize+1; } return totalpage; } public int getCurrentpage() { return currentpage; } public void setCurrentpage(int currentpage) { this.currentpage = currentpage; } public int getPreviouspage() { if(this.currentpage-1<1){ this.previouspage = 1; }else{ this.previouspage = this.currentpage-1; } return previouspage; } public int getNextpage() { if(this.currentpage+1>this.totalpage){ this.nextpage = this.totalpage; }else{ this.nextpage = this.currentpage+1; } return nextpage; } public int[] getPagebar() { //页码条的总个数是10个 if(this.totalpage<=10){ this.pagebar = new int[this.totalpage]; for(int i=1;i<=this.totalpage;i++){ this.pagebar[i-1] = i; } return pagebar; } //[-1.....,23]; 20 18 int startpage = this.currentpage - 4; int endpage = this.currentpage + 5; if(startpage<1){ startpage = 1; endpage = 10; } if(endpage>this.totalpage){ endpage = this.totalpage; startpage = this.totalpage - 9; } this.pagebar = new int[10]; int index = 0; for(int i=startpage;i<=endpage;i++){ this.pagebar[index++] = i; } return pagebar; } }
(3)接下来写dao
package cn.itcast.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.domain.QueryResult; import cn.itcast.domain.Student; import cn.itcast.utils.JdbcUtils; public class StudengDao { public QueryResult pageQuery(int startindex,int pagesize){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; QueryResult qr = new QueryResult(); try{ conn = JdbcUtils.getConnection(); String sql = "select * from studengt limit ?,?"; st = conn.prepareStatement(sql); st.setInt(1, startindex); st.setInt(2, pagesize); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Student s = new Student(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); list.add(s); } qr.setList(list); sql = "select count(*) from student"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ qr.setTotalrecord(rs.getInt(1)); } return qr; }catch (Exception e) { throw new RuntimeException(e); }finally{ JdbcUtils.release(conn, st, rs); } } }
（4）创建service
service层接收PageInfo，然后将PageInfo中的StartIndex和PageSize传递给dao层，返回PageBean
package cn.itcast.service; import cn.itcast.dao.StudentDao; import cn.itcast.domain.PageBean; import cn.itcast.domain.PageInfo; import cn.itcast.domain.QueryResult; public class BusinessService { public PageBean pageQuery(PageInfo info){ StudentDao dao = new StudentDao(); QueryResult qr = dao.pageQuery(info.getStartindex(),info.getPagesize()); PageBean bean = new PageBean(); bean.setCurrentpage(info.getCurrentpage()); bean.setList(qr.getList()); bean.setPagesize(info.getPagesize()); bean.setTotalrecord(qr.getTotalrecord()); return bean; } }
（5）接下来servlet
这个servlet将请求数据工具类WebUtils存入到PageInfo中，还要将service层中返回的PageBean通过存入到request域中然后带给JSP页面，然后跳转到JSP页面去
package cn.itcast.servlet; import java.io.IOException; import java.io.PrintWriter; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import cn.itcast.domain.PageBean; import cn.itcast.domain.PageInfo; import cn.itcast.service.BusinessService; import cn.itcast.utils.WebUtils; public class ListStudentServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PageInfo info = WebUtils.request2Bean(request, PageInfo.class); BusinessService service = new BusinessService(); PageBean bean = service.pageQuery(info); request.setAttribute("bean",bean); request.getRequestDispatcher("/liststudent.jsp").forward(request,response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request,response); } }
（6）接下来就是JSP
注意在写JSP时，只是将day14_customer中的Listcustomer.jsp复制过来然后将显示列改成对应的，还要将发给servlet的地址改成相应的地址，最重要的是要将servlet发过的pageBean改为对应的名字
liststudent.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>显示所有用户</title>
</head>
<body style="text-align: center;">
<br/><br/>
<table width="90%" border="1">
<tr>
<td>id</td>
<td>姓名</td>
</tr>
<c:forEach var="s" items="${bean.list}">
<tr>
<td>${s.id }</td>
<td>${s.name }</td>
</tr>
</c:forEach>
</table>
<script type="text/javascript">
function gotopage(pagenum){
var pagesize = document.getElementsByName("pagesize")[0].value;
window.location.href="${pageContext.request.contextPath}/servlet/ListStudentServlet?currentpage=" + pagenum + "&pagesize=" + pagesize;
}
function goWhich(input){
var pagesize = document.getElementsByName("pagesize")[0].value;
var pagenum = input.value;
window.location.href="${pageContext.request.contextPath}/servlet/ListStudentServlet?currentpage=" + pagenum + "&pagesize=" + pagesize;
}
</script>
共[${bean.totalrecord }]记录，
每页<input type="text" name="pagesize" value="${bean.pagesize }" style="width: 20px">条,
共[${bean.totalpage }]页,
当前[${bean.currentpage }]页
<a href="javascript:void(0)" onclick="gotopage(${bean.previouspage })">上一页</a>
<c:forEach var="pagenum" items="${bean.pagebar}">
<c:if test="${bean.currentpage!=pagenum}">
<a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a>
</c:if>
<c:if test="${bean.currentpage==pagenum}">
<font color="red">${pagenum }</font>
</c:if>
</c:forEach>
<a href="javascript:void(0)" onclick="gotopage(${bean.nextpage })">下一页</a>
跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum">页
<input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))">
</body>
</html>
（7）最后一个关于分页的小细节，就是在选中一行数据时就会变颜色
这个就是在迭代出的每个tr上加色，我们为这个tr加上一个class，当迭代出的计数是奇数时为一个颜色，当迭代出的计数是偶数时为一个颜色。
还有一点当鼠标移动到一个tr上时则会变颜色，这个用hover，但是注意要用hover翻转颜色，必须要声明这个页面为XHTML,这个声明可以在Tomcat的首页源码中找到
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color:darkgoldenrod} .odd{background-color:darkred} tr:hover{background-color:chocolate} <!--要支持这个就必须说这个页面是一个XHTML，而不是HTML，所以头上的声明要改，这声明头可以在TomCat开始页源码中有--> </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>id</td> <td>姓名</td> </tr> <c:forEach var="s" items="${bean.list}" varStatus="status"> <!-- 这个地方用varStatus记录下每次迭代 --> <tr class="${status.count%2==0?'even':'odd'}"> <!-- 给tr一个class，当计数为偶数class=even，当计数为奇数class=odd ，然后我们通过css样式控制样式--> <td>${s.id }</td> <td>${s.name }</td> </tr> </c:forEach> </table> <script type="text/javascript"> function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; window.location.href="${pageContext.request.contextPath}/servlet/ListStudentServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ var pagesize = document.getElementsByName("pagesize")[0].value; var pagenum = input.value; window.location.href="${pageContext.request.contextPath}/servlet/ListStudentServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } </script> 共[${bean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${bean.pagesize }" style="width: 20px">条, 共[${bean.totalpage }]页, 当前[${bean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${bean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${bean.pagebar}"> <c:if test="${bean.currentpage!=pagenum}"> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${bean.currentpage==pagenum}"> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${bean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum">页 <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> </body> </html>
以上练习要求在20分钟内完成，这个有肯能成为面试的题目
这时一种分页，在实际开发中还有一种带条件的分类，以后会讲
—————————————————————————————————————————————————————————————————————————————————
在day14_customer中我们还有删除客户和修改客户没有完成， 这节课我们来做删除客户
我们要完成这个功能首先要底层dao支持删除功能，然后在做service，在做servelet，在做jsp
首先我们做dao层中的删除方法
package cn.itcast.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.QueryResult; import cn.itcast.exception.DaoException; import cn.itcast.utils.JdbcUtils; public class CustomerDaoImpl implements CustomerDao { /* * id varchar(40) primary key, name varchar(20) not null, gender varchar(4) not null, birthday date, cellphone varchar(40) not null, email varchar(100), preference varchar(100), type varchar(40), description varchar(255) */ public void add(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into customer(id,name,gender,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setString(1, customer.getId()); st.setString(2, customer.getName()); st.setString(3, customer.getGender()); st.setDate(4, new java.sql.Date(customer.getBirthday().getTime())); st.setString(5, customer.getCellphone()); st.setString(6, customer.getEmail()); st.setString(7, customer.getPreference()); st.setString(8, customer.getType()); st.setString(9, customer.getDescription()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void update(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ //name,gender,birthday,cellphone,email,preference,type,description) conn = JdbcUtils.getConnection(); String sql = "update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?"; st = conn.prepareStatement(sql); st.setString(1, customer.getName()); st.setString(2, customer.getGender()); st.setDate(3, new java.sql.Date(customer.getBirthday().getTime())); st.setString(4, customer.getCellphone()); st.setString(5, customer.getEmail()); st.setString(6, customer.getPreference()); st.setString(7, customer.getType()); st.setString(8, customer.getDescription()); st.setString(9, customer.getId()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void delete(String id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "delete from customer where id=?"; st = conn.prepareStatement(sql); st.setString(1, id); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public Customer find(String id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer where id=?"; st = conn.prepareStatement(sql); st.setString(1, id); rs = st.executeQuery(); if(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); return c; } return null; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public List<Customer> getAll(){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } return list; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public QueryResult pageQuery(int startindex,int pagesize){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; QueryResult qr = new QueryResult(); try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer limit ?,?"; st = conn.prepareStatement(sql); st.setInt(1, startindex); st.setInt(2, pagesize); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } qr.setList(list); sql = "select count(*) from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ qr.setTotalrecord(rs.getInt(1)); } return qr; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } }
然后我们在到service层将删除方法做出来，这里的删除方法主要是调用CustomerDaoImpl中的delete方法
package cn.itcast.service; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.PageBean; import cn.itcast.domain.PageInfo; import cn.itcast.domain.QueryResult; import cn.itcast.utils.DaoFactory; public class BusinessService { private CustomerDao customerDao = DaoFactory.getInstance().createDao(); public void addCustomer(Customer customer){ customerDao.add(customer); } public PageBean pageQuery(PageInfo pageInfo){ QueryResult qr = customerDao.pageQuery(pageInfo.getStartindex(), pageInfo.getPagesize()); PageBean pageBean = new PageBean(); pageBean.setCurrentpage(pageInfo.getCurrentpage()); pageBean.setList(qr.getList()); pageBean.setPagesize(pageInfo.getPagesize()); pageBean.setTotalrecord(qr.getTotalrecord()); return pageBean; } public void deleteCustomer(String id){ //通过接口调用CustomerDaodImpl中的删除方法 customerDao.delete(id); }
public Customer findCustomer(String id){ return customerDao.find(id); } public void updateCustomer(Customer c) { customerDao.update(c); } }
接着我们开始做jsp中的删除请求，请求发给DeleteCustomerServlet
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %> <%@taglib uri="/WEB-INF/my.tld" prefix="itcast" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color: #FFFF66} .odd{background-color: #FF99FF} tr:hover{background-color: #0066FF} </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}" varStatus="status"> <tr class="${status.count%2==0?'even':'odd' }"> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${fn:escapeXml(c.description)}</td> <td> <a href="${pageContext.request.contextPath }/servlet/EditCustomerUIServlet?id=${c.id }">修改客户</a>
<!--<a href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=${c.id }" >删除客户</a>--> <a href="javascript:void(0)" onclick="del('${c.id }')">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function del(id){ var b = window.confirm("删除吗？"); if(b){ window.location.href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=" + id; } } function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; //在向servlet发请求之前会将当前页currentPage和每页多少条pagesize传递过去 window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ //这个goWhich方法接收一个input输入项 var pagesize = document.getElementsByName("pagesize")[0].value; //注意这里还要获取pagesize，并将一起传递给servlet，不然当你改变pagesize后在输入去某页则在这页中显示条数是默认的条数 var pagenum = input.value; //得到你传入的pagenum window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; //把这个pageNum发给servlet } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, <!-- 此处可以输入每页多少条，默认每页的条数是pageSize --> 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <!-- 如果 当前页不等于pagenum则显示超链接--> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <!-- 这里做一个if，如果检测到当前页等于某个pagenum则显示红色 --> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum">页 <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> <!-- 当按了go之后就会去执行goWhich方法，这个方法拿到了跳转页的pagenum --> </body> </html>
接下来我们来做DeleteCustomerServlt
package cn.itcast.web.controller; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import cn.itcast.service.BusinessService; public class DeleteCustomerServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try{ String id = request.getParameter("id"); //从request域中取出要删除数据的id BusinessService service = new BusinessService(); service.deleteCustomer(id); request.setAttribute("message", "删除成功！！"); //删除成功则将成功字样存入到request域中 }catch (Exception e) { e.printStackTrace(); request.setAttribute("message", "删除失败！！"); //如果删除失败则页将删除失败字样存入到request域中 } request.getRequestDispatcher("/message.jsp").forward(request, response); //最后不论删除成功还是失败都会跳转到message页面 } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
对于删除还要做做限制，要确定是否删除，如果不小心删除了就完蛋了
这里要做限制还是在JSP页面中修改，当你点击删除这个删除超链接时，用javascript去掉默认的行为，然后我们在给它添加一个onclick方法，然后在javascript中写这个方法
这里还要特别特别注意呀
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %> <%@taglib uri="/WEB-INF/my.tld" prefix="itcast" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color: #FFFF66} .odd{background-color: #FF99FF} tr:hover{background-color: #0066FF} </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}" varStatus="status"> <tr class="${status.count%2==0?'even':'odd' }"> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${fn:escapeXml(c.description)}</td> <td> <a href="${pageContext.request.contextPath }/servlet/EditCustomerUIServlet?id=${c.id }">修改客户</a> <!-- 注意此处del方法中传的参数要加单引号，注意这里，容易出错，而且很不容易找出出来，一定要注意，这个地方别人花了三天三夜才查找出来 --> <a href="javascript:void(0)" onclick="del('${c.id }')">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function del(id){ var b = window.confirm("删除吗？"); //弹出一个对话框问你确认删除吗， //这里还要注意一点在if括号中不能直接放window.confirm("删除吗？"),必须在外面定义一个变量，在括号中引用这个变量，否则要出错，非常不好调试，这里课程期间调了很久 if(b){ //如果返回true则就将要删除数据的ID传递给DeleteCustomerServlet。 window.location.href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=" + id; } } function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; //在向servlet发请求之前会将当前页currentPage和每页多少条pagesize传递过去 window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ //这个goWhich方法接收一个input输入项 var pagesize = document.getElementsByName("pagesize")[0].value; //注意这里还要获取pagesize，并将一起传递给servlet，不然当你改变pagesize后在输入去某页则在这页中显示条数是默认的条数 var pagenum = input.value; //得到你传入的pagenum window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; //把这个pageNum发给servlet } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, <!-- 此处可以输入每页多少条，默认每页的条数是pageSize --> 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <!-- 如果 当前页不等于pagenum则显示超链接--> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <!-- 这里做一个if，如果检测到当前页等于某个pagenum则显示红色 --> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum">页 <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> <!-- 当按了go之后就会去执行goWhich方法，这个方法拿到了跳转页的pagenum --> </body> </html>
删除数据到此为止，接下来解决一个问题，关于添加用户时出现的添加用户的表单中爱好和类型每次点击添加都会在静态List中添加一份，解决方法就是将添加的数据放到静态代码块中
package cn.itcast.utils; import java.util.ArrayList; import java.util.List; import cn.itcast.domain.Preference; import cn.itcast.domain.Type; //全局 public class Globals { private static List plist = new ArrayList(); //在开发时，静态的东西千万要高度重视，特别是静态的集合，不然开发一个项目运行之后没多久就会内存溢出 private static List tlist = new ArrayList(); static{ plist.add(new Preference("1","唱歌")); plist.add(new Preference("2","爱春晚")); plist.add(new Preference("3","看林志玲")); plist.add(new Preference("4","看赵本山")); plist.add(new Preference("5","洗桑拿")); plist.add(new Preference("6","此处省略23个字")); tlist.add(new Type("1","vip客户")); tlist.add(new Type("2","重要客户")); tlist.add(new Type("3","普通客户")); tlist.add(new Type("4","意向客户")); } public static List getAllPreference(){ //这些添加不能放在方法中，如果你放在方法中每次点击添加客户都会在静态List中添加一份，这些添加应该放在静态代码块中 // plist.add(new Preference("1","唱歌")); // plist.add(new Preference("2","爱春晚")); // plist.add(new Preference("3","看林志玲")); // plist.add(new Preference("4","看赵本山")); // plist.add(new Preference("5","洗桑拿")); // plist.add(new Preference("6","此处省略23个字")); return plist; } public static Preference findPreference(String id){ return null; } public static List getAllType(){ //同样这些添加不能放在方法中，如果你放在方法中每次点击添加客户都会在静态List中添加一份，这些添加应该放在静态代码块中 // tlist.add(new Type("1","vip客户")); // tlist.add(new Type("2","重要客户")); // tlist.add(new Type("3","普通客户")); // tlist.add(new Type("4","意向客户")); return tlist; } public static Type findType(String id){ return null; } }
______________________________________________________________________________________________________________
接下来开始写修改客户的，我们先在jsp页面中为修改客户加上超链接，当点击修改客户就将修改的id发给EditCustomerUIServlet，这个servlet是用来回显
要修改id客户的信息界面，也就是为修改用户提供UI界面的servlet
/day14_customer/WebRoot/WEB-INF/jsp/listcustomer.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %> <%@taglib uri="/WEB-INF/my.tld" prefix="itcast" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>显示所有用户</title> <style type="text/css"> .even{background-color: #FFFF66} .odd{background-color: #FF99FF} tr:hover{background-color: #0066FF} </style> </head> <body style="text-align: center;"> <br/><br/> <table width="90%" border="1"> <tr> <td>客户姓名</td> <td>性别</td> <td>生日</td> <td>手机</td> <td>邮箱</td> <td>爱好</td> <td>类型</td> <td>备注</td> <td>操作</td> </tr> <c:forEach var="c" items="${pageBean.list}" varStatus="status"> <tr class="${status.count%2==0?'even':'odd' }"> <td>${c.name }</td> <td>${c.gender }</td> <td>${c.birthday }</td> <td>${c.cellphone }</td> <td>${c.email }</td> <td>${c.preference }</td> <td>${c.type }</td> <td>${fn:escapeXml(c.description)}</td> <td> <a href="${pageContext.request.contextPath }/servlet/EditCustomerUIServlet?id=${c.id }">修改客户</a> <a href="javascript:void(0)" onclick="del('${c.id }')">删除客户</a> </td> </tr> </c:forEach> </table> <script type="text/javascript"> function del(id){ var b = window.confirm("删除吗？"); if(b){ window.location.href="${pageContext.request.contextPath}/servlet/DeleteCustomerServlet?id=" + id; } } function gotopage(pagenum){ var pagesize = document.getElementsByName("pagesize")[0].value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } function goWhich(input){ var pagesize = document.getElementsByName("pagesize")[0].value; var pagenum = input.value; window.location.href="${pageContext.request.contextPath}/servlet/ListCustomerServlet?currentpage=" + pagenum + "&pagesize=" + pagesize; } </script> 共[${pageBean.totalrecord }]记录， 每页<input type="text" name="pagesize" value="${pageBean.pagesize }" style="width: 20px">条, 共[${pageBean.totalpage }]页, 当前[${pageBean.currentpage }]页 <a href="javascript:void(0)" onclick="gotopage(${pageBean.previouspage })">上一页</a> <c:forEach var="pagenum" items="${pageBean.pagebar}"> <c:if test="${pageBean.currentpage!=pagenum}"> <a href="javascript:void(0)" onclick="gotopage(${pagenum })">${pagenum }</a> </c:if> <c:if test="${pageBean.currentpage==pagenum}"> <font color="red">${pagenum }</font> </c:if> </c:forEach> <a href="javascript:void(0)" onclick="gotopage(${pageBean.nextpage })">下一页</a> 跳转到<input type="text" name="pagenum" style="width: 30px" id="pagenum"> <input type="button" value=" GO " onclick="goWhich(document.getElementById('pagenum'))"> </body> </html>
当点击了修改客户按钮后，用户的id就会发给EditCustomerUIServlet.java，在这个类中将需要修改用户的ID获取到然后通过service层将用户数据找出来，
/day14_customer/src/cn/itcast/web/UI/EditCustomerUIServlet.java
package cn.itcast.web.UI; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import cn.itcast.domain.Customer; import cn.itcast.service.BusinessService; import cn.itcast.utils.Globals; //为修改客户信息提供用户界面 public class EditCustomerUIServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //获取到要修改客户的id，然后通过service层找到这个客户的数据存入到域中，然后跳转到修改页面 String id = request.getParameter("id"); BusinessService service = new BusinessService(); Customer c = service.findCustomer(id); request.setAttribute("c", c); request.setAttribute("preferences", Globals.getAllPreference()); request.setAttribute("types", Globals.getAllType()); //跳转到修改客户页面editcustomer.jsp request.getRequestDispatcher("/WEB-INF/jsp/editcustomer.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
servlet在调用service层，service层调用dao层，dao查找数据，所以我们还要晚上dao层的find方法，dao层的find方法通过id到数据库中去查找用户，如果
查找到了就返回Customer，如果没有查找到就返回null
/day14_customer/src/cn/itcast/dao/impl/CustomerDaoImpl.java
package cn.itcast.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.QueryResult; import cn.itcast.exception.DaoException; import cn.itcast.utils.JdbcUtils; public class CustomerDaoImpl implements CustomerDao { /* * id varchar(40) primary key, name varchar(20) not null, gender varchar(4) not null, birthday date, cellphone varchar(40) not null, email varchar(100), preference varchar(100), type varchar(40), description varchar(255) */ public void add(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into customer(id,name,gender,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setString(1, customer.getId()); st.setString(2, customer.getName()); st.setString(3, customer.getGender()); st.setDate(4, new java.sql.Date(customer.getBirthday().getTime())); st.setString(5, customer.getCellphone()); st.setString(6, customer.getEmail()); st.setString(7, customer.getPreference()); st.setString(8, customer.getType()); st.setString(9, customer.getDescription()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void update(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ //name,gender,birthday,cellphone,email,preference,type,description) conn = JdbcUtils.getConnection(); String sql = "update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?"; st = conn.prepareStatement(sql); st.setString(1, customer.getName()); st.setString(2, customer.getGender()); st.setDate(3, new java.sql.Date(customer.getBirthday().getTime())); st.setString(4, customer.getCellphone()); st.setString(5, customer.getEmail()); st.setString(6, customer.getPreference()); st.setString(7, customer.getType()); st.setString(8, customer.getDescription()); st.setString(9, customer.getId()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void delete(String id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "delete from customer where id=?"; st = conn.prepareStatement(sql); st.setString(1, id); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public Customer find(String id){ //根据ID查找客户 Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer where id=?"; st = conn.prepareStatement(sql); st.setString(1, id); rs = st.executeQuery(); if(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); return c; //如果查找到了就返回Customer } return null; //如果没有查询到数据就返回null }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public List<Customer> getAll(){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } return list; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public QueryResult pageQuery(int startindex,int pagesize){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; QueryResult qr = new QueryResult(); try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer limit ?,?"; st = conn.prepareStatement(sql); st.setInt(1, startindex); st.setInt(2, pagesize); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } qr.setList(list); sql = "select count(*) from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ qr.setTotalrecord(rs.getInt(1)); } return qr; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } }
dao做完了接着就做service
/day14_customer/src/cn/itcast/service/BusinessService.java
package cn.itcast.service; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.PageBean; import cn.itcast.domain.PageInfo; import cn.itcast.domain.QueryResult; import cn.itcast.utils.DaoFactory; public class BusinessService { private CustomerDao customerDao = DaoFactory.getInstance().createDao(); public void addCustomer(Customer customer){ customerDao.add(customer); } public PageBean pageQuery(PageInfo pageInfo){ QueryResult qr = customerDao.pageQuery(pageInfo.getStartindex(), pageInfo.getPagesize()); PageBean pageBean = new PageBean(); pageBean.setCurrentpage(pageInfo.getCurrentpage()); pageBean.setList(qr.getList()); pageBean.setPagesize(pageInfo.getPagesize()); pageBean.setTotalrecord(qr.getTotalrecord()); return pageBean; } public void deleteCustomer(String id){ customerDao.delete(id); } public Customer findCustomer(String id){ //service层将id传递给dao层，然后返回Customer //这里只是调用dao层的方法，很简单 return customerDao.find(id); } public void updateCustomer(Customer c) { customerDao.update(c); } }
通过service层查到客户数据之后接着我们将数据回显到一个jsp页面，这里要通过EditCustomerUIServlet.jsp传递给editcustomer.jsp页面
/day14_customer/src/cn/itcast/web/UI/EditCustomerUIServlet.java
package cn.itcast.web.UI; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import cn.itcast.domain.Customer; import cn.itcast.service.BusinessService; import cn.itcast.utils.Globals; //为修改客户信息提供用户界面 public class EditCustomerUIServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //获取到要修改客户的id，然后通过service层找到这个客户的数据存入到域中，然后跳转到修改页面 String id = request.getParameter("id"); BusinessService service = new BusinessService(); Customer c = service.findCustomer(id); request.setAttribute("c", c);
//在做回显的时候也要讲爱好和类型传递给editcustomer.jsp，不然在editcustomer页面没有爱好和类型数据显示 request.setAttribute("preferences", Globals.getAllPreference()); request.setAttribute("types", Globals.getAllType()); //跳转到修改客户页面editcustomer.jsp request.getRequestDispatcher("/WEB-INF/jsp/editcustomer.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
然后我们将editcustomer.jsp这个页面做出来，这个修改客户的页面和添加客户的页面差不多，所以我们将添加客户的界面复制过来修改，
首先我们将javascript代码抽取出来存放到js文件中，在修改和添加页面直接引入js文件即可
对于回显数据，这里要特别注意
性别回显用二元表达式
时间要用EL的分割函数
手机号码和邮箱直接获取
最麻烦的是爱好的回显
然后回显类型，回显类型时要注意传递的是类型的id
然后是回显备注
然后将添加改为修改
最后将数据传递给EditCustomerServlet，在这里要注意，在将数据传递给EditCustomerServlet时，还用通过隐藏域将客户的id传递过去
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %><!-- 导入EL函数库 --> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>修改客户</title> <script type="text/javascript" src="${pageContext.request.contextPath }/js/sys.js"></script> </head> <body onload="init()" style="text-align: center;"> <br/><br/> <!-- 最后将修改后的数据传递给EditCustomerServlet --> <form id="form" action="${pageContext.request.contextPath }/servlet/EditCustomerServlet" method="post" onsubmit="return doSubmit()"> <table border="1" width="60%"> <input type="hidden" name="id" value="${c.id }"> <tr> <td>客户姓名</td> <td> <input type="text" name="name" value="${c.name }"> <!-- 同过回显将姓名显示出来 --> </td> </tr> <tr> <td>性别</td> <td> <!-- 这里要通过二元表达式来做回显--> <!-- 如果返回的值是男则被checked，如果不是则什么都没有 --> <input type="radio" name="gender" value="男" ${c.gender=='男'?'checked':'' }>男 <!-- 如果返回的值是女则被checked，如果噢不是则什么都没有 --> <input type="radio" name="gender" value="女" ${c.gender=='女'?'checked':'' }>女 </td> </tr> <tr> <!-- 这个生日回显还有些麻烦 ，由于返回的是一个时间字符串（如：1980-09-09），所以这里需要用-这个符号好分割这个时间字符串，将分割的年月日分别付以下的中--> <td>生日</td> <td> <select id="year"> <option value="${fn:split(c.birthday,'-')[0]}">${fn:split(c.birthday,"-")[0]}</option> <!-- 这里要分割就用到了EL函数split，在用EL函数时需要导入El函数库 --> </select>年 <select id="month"> <option value="${fn:split(c.birthday,'-')[1]}">${fn:split(c.birthday,"-")[1]}</option> <!-- 分割之后去第1个位置上的数 --> </select>月 <select id="day"> <option value="${fn:split(c.birthday,'-')[2]}">${fn:split(c.birthday,"-")[2]}</option> <!-- 分割之后去第2个位置上的数 --> </select>日 </td> </tr> <tr> <td>手机号码</td> <td> <input type="text" name="cellphone" value="${c.cellphone }"> <!-- 手机可以直接回显 --> </td> </tr> <tr> <td>邮箱地址</td> <td> <input type="text" name="email" value="${c.email }"> <!-- 邮箱也可以直接回显 --> </td> </tr> <tr> <!-- 关于爱好是整个回显页面最难做的了，我日真的有些难，需要多看多练习下 --> <td>爱好</td> <td> <c:forEach var="pre" items="${preferences}"> <c:remove var="b"/> <!-- 在每次迭代前都域中的b移出掉 --> <c:forEach var="c_pre" items="${fn:split(c.preference,',')}"> <!-- 迭代获取到的客户的爱好的id，将迭代到的值付给c_pre --> <c:if test="${c_pre==pre.id}"> <!-- 当客户的爱好id和系统的爱好id相等时，就在域中定义一个变量b，值为true --> <c:set var="b" value="true"></c:set> </c:if> </c:forEach> <input type="checkbox" name="pre" value="${pre.id }" ${b=='true'?'checked':'' }>${pre.name }<!-- 当b为true就选中这个爱好 --> <!-- 以下这种写法是 不行的，原因是当爱好增加到10个以上时就会出错，比如返回来的爱好id是（10）， 当验证到第1个爱好id时就出错了，因为10里面包含1，就会把第1个爱好选中，而实际上是爱好10，所以出错了--> <%--<input type="checkbox" name="pre" value="${c.preference,pre.id }" ${fn:contain(pre.id)?'checked':'' }>${pre.name }--%> </c:forEach> </td> </tr> <tr> <td>客户类型</td> <td> <c:forEach var="type" items="${types}"> <input type="radio" name="type" value="${type.id }" ${c.type==type.id?'checked':'' }>${type.name }<!-- 注意这里查询的客户类型返回的是字符串的id --> ${type.id }/${c.type} </c:forEach> </td> </tr> <tr> <td>备注</td> <td> <textarea rows="5" cols="50" name="description">${c.description }</textarea> </td> </tr> <tr> <td> <input type="reset" value="清空"> </td> <td> <input type="submit" value="修改"> <!-- 这里将添加改为修改 --> </td> </tr> </table> </form> </body> </html>
引入JS文件，这个js文件既可以被addcustomer引用，也可以被listcustomer引用
function generateYear(){ var year = document.getElementById("year"); for(var i=1901;i<=new Date().getYear();i++){ var option = document.createElement("option"); option.value = i; option.innerText = i; year.appendChild(option); } } function generateMonth(){ var month = document.getElementById("month"); for(var i=2;i<=12;i++){ var option = document.createElement("option"); if(i<10){ option.value = '0' + i; option.innerText = '0' + i; }else{ option.value = i; option.innerText = i; } month.appendChild(option); } } function generateDay(){ var day = document.getElementById("day"); for(var i=2;i<=31;i++){ var option = document.createElement("option"); if(i<10){ option.value = '0' + i; option.innerText = '0' + i; }else{ option.value = i; option.innerText = i; } day.appendChild(option); } } function init(){ generateYear(); generateMonth(); generateDay(); } function makePreferences(){ var pres = document.getElementsByName("pre"); var preference = ""; for(var i=0;i<pres.length;i++){ var pre = pres[i]; if(pre.checked==true){ var id = pre.value; //2 preference = preference + id + ","; } } preference = preference.substr(0,preference.length-1); //2,3,4 var input = document.createElement("input"); input.type="hidden"; input.name="preference"; input.value=preference; document.getElementById("form").appendChild(input); } function makeBirthday(){ var year = document.getElementById("year").value; var month = document.getElementById("month").value; var day = document.getElementById("day").value; //1980-09-09 var birthday = year + "-" + month + "-" + day; var form = document.getElementById("form"); var input = document.createElement("input"); input.type="hidden"; input.name="birthday"; input.value=birthday; form.appendChild(input); } function doSubmit(){ makeBirthday(); makePreferences(); return true; }
接着就做EditCustomerSerclet.java
其实EditCustomerServlet内部是调用dao层去修改用户，最后无论是否修改成功都要跳转到提示页面提示，还有要注意在获取数据是一定要设置字符集，不然
更新到数据库中的中文是乱码
/day14_customer/src/cn/itcast/web/controller/EditCustomerServlet.java
package cn.itcast.web.controller; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import cn.itcast.domain.Customer; import cn.itcast.service.BusinessService; import cn.itcast.utils.WebUtils; public class EditCustomerServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try{ request.setCharacterEncoding("UTF-8"); //注意一定要设置字符集，不然存储到数据库中的是乱码 //将请求数据通过工具方法封装到Customer中去 Customer c = WebUtils.request2Bean(request, Customer.class); BusinessService service = new BusinessService(); service.updateCustomer(c); request.setAttribute("message", "修改成功！！"); }catch (Exception e) { e.printStackTrace(); request.setAttribute("message", "修改失败！!"); } request.getRequestDispatcher("/message.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
然后我们去将dao层的update方法完善
/day14_customer/src/cn/itcast/dao/impl/CustomerDaoImpl.java
package cn.itcast.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.QueryResult; import cn.itcast.exception.DaoException; import cn.itcast.utils.JdbcUtils; public class CustomerDaoImpl implements CustomerDao { /* * id varchar(40) primary key, name varchar(20) not null, gender varchar(4) not null, birthday date, cellphone varchar(40) not null, email varchar(100), preference varchar(100), type varchar(40), description varchar(255) */ public void add(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into customer(id,name,gender,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setString(1, customer.getId()); st.setString(2, customer.getName()); st.setString(3, customer.getGender()); st.setDate(4, new java.sql.Date(customer.getBirthday().getTime())); st.setString(5, customer.getCellphone()); st.setString(6, customer.getEmail()); st.setString(7, customer.getPreference()); st.setString(8, customer.getType()); st.setString(9, customer.getDescription()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void update(Customer customer){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ //name,gender,birthday,cellphone,email,preference,type,description) conn = JdbcUtils.getConnection(); String sql = "update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?"; st = conn.prepareStatement(sql); st.setString(1, customer.getName()); st.setString(2, customer.getGender()); st.setDate(3, new java.sql.Date(customer.getBirthday().getTime())); st.setString(4, customer.getCellphone()); st.setString(5, customer.getEmail()); st.setString(6, customer.getPreference()); st.setString(7, customer.getType()); st.setString(8, customer.getDescription()); st.setString(9, customer.getId()); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public void delete(String id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "delete from customer where id=?"; st = conn.prepareStatement(sql); st.setString(1, id); st.executeUpdate(); }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public Customer find(String id){ //根据ID查找客户 Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer where id=?"; st = conn.prepareStatement(sql); st.setString(1, id); rs = st.executeQuery(); if(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); return c; } return null; //如果没有查询到数据就返回null }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public List<Customer> getAll(){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } return list; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } public QueryResult pageQuery(int startindex,int pagesize){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; QueryResult qr = new QueryResult(); try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer limit ?,?"; st = conn.prepareStatement(sql); st.setInt(1, startindex); st.setInt(2, pagesize); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } qr.setList(list); sql = "select count(*) from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ qr.setTotalrecord(rs.getInt(1)); } return qr; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } } }
以上我们算是完成了一个客户数据管理，但是还有一些问题
比如：在备注中数据数据过长就会导致表格变形，这个时候就需要限制一下备注中存放的数据大小，这里就采用截取前20个数据，剩下的就用...代替
但是要用...就需要自己写EL函数，因为SUN公司的EL库中部支持+运算符的，所以我们必须自己写了
如何自定义EL函数呢？
第一步写java函数
/day14_customer/src/cn/itcast/utils/MyEl.java
package cn.itcast.utils; public class MyEl { public static String substring(String source){ if(source.length()>20){ //如果字符串长度大于20则截取0到20的长度并加上....，并放回 return source.substring(0, 20) + "...."; }else{ //如果字符串长度小于20则返回字符串 return source; } } }
第二步鞋tld文件，
/day14_customer/WebRoot/WEB-INF/my.tld
<?xml version="1.0" encoding="UTF-8" ?> <taglib xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-jsptaglibrary_2_0.xsd" version="2.0"> <description>JSTL 1.1 functions library</description> <display-name>JSTL functions</display-name> <tlib-version>1.1</tlib-version> <short-name>itcast</short-name> <uri>/itcast</uri> <function> <name>substring</name> <function-class>cn.itcast.utils.MyEl</function-class> <function-signature>java.lang.String substring(java.lang.String)</function-signature> </function> </taglib>
然后在listcustomer中导入自定义的EL函数，
接着就可以用自定的方法了
这里除了要限制数据的长度之外还要限制在备注中输入javascript等恶意代码
比如数据javascript循环代码
<script>
for(;;){
alert("heihei");
}
</script>
所以还要用到EL库中的escapeXML方法
综上可以将备注中的数据限定在20长度，剩下的用...代替，然后也限定了恶意代码的写入
———————————————————————————————————————————————————————————————————————
Tip：使用JDBC处理大数据
在实际开发中，程序需要把大文本或二进制数据保存到数据库，JDBC也可以处理大数据
l基本概念：大数据也称之为LOB(Large Objects)，LOB又分为：
•clob和blob
•clob用于存储大文本。Text（在，Mysql中是不存在clob的，只存在Text）
•blob用于存储二进制数据，例如图像、声音、二进制文等。
l对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是Text，Text和blob分别又分为：
•TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT（这个最大存储4G）
•TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
接下来我们来学习JDBC如何处理大的文本数据和大的二进制数据
Tip：使用JDBC处理大文本
l对于MySQL中的Text类型，可调用如下方法设置：
这个PreparedStatement有一个setCharacterStream(index,reader,length)的方法，这个方法是如何存数据，其中的参数index表示你想插入大文本到数据库中的那一列，reader表示大文本读取流，length表示这个文本的大小。这个方法会自动从reader这个流中读取数据填充到index列位置。
l对MySQL中的Text类型，可调用如下方法获取：
结果集resultSet拥有三个方法获取数据，其中最常用的方法getCharacterStream(i)可以获取拥有文本数据相关的流，通过这个流就可以从数据库中读取数据，
注意我们在开发中不要用getString(i)这个方法，虽然这个方法可以直接从数据库中读取到字符串，但是这个方法是将数据直接保存到内存中的，假如现在我们
获取的文本数据大于java虚拟机内存大小则会导致内存溢出而导致数据丢失
所以这里就有一个原则性问题：在开发时，只要涉及到数据量大的传输一定要用流
我们现在来做练习，由于这个练习涉及到操作Mysql数据库，所以在做练习之前我们要将JDBC驱动导入到项目中，然后还要将工具类复制一份，工具包中涉及到释放资源以及读取数据库，由于工具类是通过配置文件读取的，所以还要将配置文件复制一份
导入JDBC驱动包
复制工具类JdbcUtils.java以及配置文件db.properties
/day15/src/cn/itcast/utils/JdbcUtils.java
package cn.itcast.utils; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcUtils { private static String url; private static String username; private static String password; private static String driver; static{ try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties prop = new Properties(); prop.load(in); url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); driver = prop.getProperty("driver"); Class.forName(driver); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } //加载驱动，获取链接 public static Connection getConnection() throws SQLException{ Connection conn = DriverManager.getConnection(url,username,password); return conn; } //释放资源 public static void release(Connection conn,Statement st,ResultSet rs){ try{ if(rs!=null) rs.close(); }catch (Exception e) { e.printStackTrace(); rs=null; } try{ if(st!=null) st.close(); }catch (Exception e) { e.printStackTrace(); st=null; } try{ if(conn!=null) conn.close(); }catch (Exception e) { e.printStackTrace(); conn=null; } } }
/day15/src/db.properties
url=jdbc:mysql://localhost:3306/day15 username=root password=root driver=com.mysql.jdbc.Driver #url=jdbc:oracle:thin:@localhost:1521:ORCL #username=system #password=itcast #driver=oracle.jdbc.driver.OracleDriver
由于要操作Mysql数据库，所以我们必须先建立好一张数据表
create database day15;
use day15;
create table testclob
(
id int primary key auto_increment,
resume text
);
/day15/src/cn/itcast/demo/Demo1.java
package cn.itcast.demo; import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.FileWriter; import java.io.Reader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import cn.itcast.utils.JdbcUtils; //大文本的存储 /* create database day15; use day15; create table testclob ( id int primary key auto_increment, resume text ); */ public class Demo1 { //将大文本存入数据库 @Test public void save() throws SQLException, FileNotFoundException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into testclob(resume) values(?)"; st = conn.prepareStatement(sql); //通过类加载获取到要读取的资源路径,此处将配置文件作为了对象传入了数据库 String path = Demo1.class.getClassLoader().getResource("db.properties").getPath(); //然后通过路径建立文件对象 File file = new File(path); //PreparedStatement的存储大文本的方法setCharcaterStream(index,reader,length) //这里要注意file.length()返回的是long类型，由于我们创建的项目是基于JDK1.5的，所以需要强转成int类型,其实在JDK1.6之后可以不用强转 //setCharacterStream(int parameterIndex, Reader reader, int length) 这个是JDK1.5之前的 //setCharacterStream(int parameterIndex, Reader reader, long length) 这个是JDK1.6之后的 st.setCharacterStream(1, new FileReader(file), (int) file.length()); int num = st.executeUpdate(); if(num>0){ System.out.println("插入成功！！"); } }finally{ JdbcUtils.release(conn, st, rs); } } //从数据库中取出大文本 @Test public void find() throws Exception{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from testclob where id=1"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ //通过读取流来获取大文本，当然你也可以用getString()方法，但是这样做不好，容易引起内存溢出，对于大文本一点要用流来获取 //String resume = rs.getString("resume"); Reader reader = rs.getCharacterStream("resume"); char buffer[] = new char[1024]; //要用字符数组，不是字节数组 int len = 0; FileWriter fw = new FileWriter("c:\\1.txt"); while((len = reader.read(buffer))>0){ fw.write(buffer, 0, len); //将数据写入到指定位置 } reader.close(); fw.close(); } }finally{ JdbcUtils.release(conn, st, rs); } } }
setCharacterStream这个方法在jdk1.5和jdk1，6之后不同
Tip：使用JDBC处理二进制数据
l对于MySQL中的BLOB类型，可调用如下方法存储：
要将大的二进制数据（比如电影，音乐，图片）存入到数据库，就可以用PreparedStatement的setBinamary(i,inputStream,length)方法，i表示要存入到哪一列
inputStream是指定的一个字节输入流，可以通过这个流将二进制文件输入到数据库，length表示二进制文件的长度
l对MySQL中的BLOB类型，可调用如下方法获取获取：
我们通过用结果集resultSet的getBinaryStream(i)获取数据库中的大二的进制数据
现在我们来做测试
首先我们建立一张测试大的二进制数据的数据库表testblob
create table testblob
(
id int primary key auto_increment,
image blob
);
/day15/src/cn/itcast/demo/Demo2.java
package cn.itcast.demo; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FileReader; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import cn.itcast.utils.JdbcUtils; public class Demo2 { /* create table testblob ( id int primary key auto_increment, image blob //这个blob存储大小为64k， ); */ @Test public void save() throws SQLException, FileNotFoundException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into testblob(image) values(?)"; st = conn.prepareStatement(sql); //通过类加载获取1.jpg图片的路径 String path = Demo2.class.getClassLoader().getResource("1.jpg").getPath(); //将图片封装成一个文件对象 File file = new File(path); //PreparedStatement存储大的二进制数据的方法，注意这里还是需要强转，将lang强转为int st.setBinaryStream(1, new FileInputStream(file), (int) file.length()); int num = st.executeUpdate(); if(num>0){ System.out.println("插入成功！！"); } }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void find() throws SQLException, IOException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select image from testblob where id=1"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ InputStream in = rs.getBinaryStream("image"); //通过结果集去获取image列的数据，得到的是一个InputStream流 byte buffer[] = new byte[1024]; //此处用字节数组 int len = 0; FileOutputStream out = new FileOutputStream("c:\\1.jpg"); //准备一个输出流 while((len=in.read(buffer))>0){ out.write(buffer, 0, len); //将字节数组中的数据写入到输入流中 } in.close(); out.close(); } }finally{ JdbcUtils.release(conn, st, rs); } } }
由于blob存储大小为64k，不能容下我们给的图片1.jgp，所以就抛异常了，这里我们要将blob改为mediumblob类型就可以存下我们给的数据了MEDIUMBLOB
存储大小为16M
alter table testblob modify image MEDIUMBLOB;
此时之前要存入的图片就可以顺利的存入了，但是注意在dos命令行中千万不要去查看存入的大的二进制数据，否则就会是乱码。
另注意一点：在实际开发当中，不要把大的二进制数据存入到数据库中，因为数据库的链接资源是有限的，如果你将大的二进制数据存入到数据库中，在取大的二进制数据时就会占用链接资源很久，这样就会使网站性能严重降低。
Tip：Oracle中大数据处理
由于还没有学到事物，所以这个将在JDBC最后讲，此处暂且放下...
Tip：使用JDBC进行批处理
l业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。
l实现批处理有两种方式，第一种方式用Statement的方法：
•Statement.addBatch(sql) list //Statement有一个addBatch(sql)方法，这个方法可以将传入的sql语句存入到一个list集合中
l执行批处理SQL语句
•executeBatch()方法：执行批处理命令 //当将多条sql语句传入到list中后可以用这个方法一次性执行，注意这个返回的是一个int数组，元素是表示sql语句影响多少行
•clearBatch()方法：清除批处理命令 //最后执行完了sql语句之后，存在list中的sql语句就没有了，这个时候我们就必须将这些sql语句清理掉
我们开始做练习
首先我们建立一张表testbatch来测试
create table testbatch
(
id int primary key auto_increment,
name varchar(40)
);
@Test public void testBatch1() throws Exception{ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql1 = "insert into testbatch(name) values('aa')"; String sql2 = "update testbatch set name='bb' where id=1"; st.addBatch(sql1); st.addBatch(sql2); //[3,7] st.executeBatch(); //返回的是一个int数组，表示每句sql语句影响多少行 st.clearBatch(); //别忘记清除list中的sql语句 }finally{ JdbcUtils.release(conn, st, rs); } }
实现批处理的第二种方式采用PreparedStatement类的addBatch()方法
•PreparedStatement.addBatch()中并没有传入sql语句作物参数，这是因为PrepareStatement中已经保存了一条sql语句，当调用
addBatch()这个方法时就会将sql语句保存到一个list集合中
•采用PreparedStatement.addBatch()实现批处理优点：发送的是预编译后的SQL语句，执行效率高。
•采用PreparedStatement.addBatch()实现批处理缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。，如果要插入多条不同的sql语句则可以用Stetement.addBatch(sql)这个方法来执行，但是在开发中PreparedStatement最常用
注意在模拟执行千万条sql语句时，mysql要分成每1000条执行一次的，然后清空集合的方式，不然就会出现内存溢出
@Test public void testBatch2ForMysql() throws Exception{ long starttime = System.currentTimeMillis(); //开始执行sql语句之前的时间 Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into testbatch(name) values(?)"; st = conn.prepareStatement(sql); for(int i=0;i<10000005;i++){ //模拟千万条sql命令数据 st.setString(1, "a" + i); //将"a"+i插入到第一列上 st.addBatch(); //将sql语句加到集合list中 //这里必须这样做，要分成每1000条就去执行以下，然后再清空集合中的sql语句，不然的话就会导致内存溢出 //理由是假如有一千万条sql，每句sql语句存入到集合中占用几十字节，一千万条就会占用十几兆到几百兆不等 //由于每个集合都是有大小的，这个时候集合就不能同时容下一千万条数据，就会导致内存溢出，数据丢失 if(i%1000==0){ //每一千条sql语句就去执行一下 st.executeBatch(); //执行集合中的sql语句 st.clearBatch(); //清理掉集合中的sql语句 } } st.executeBatch(); //这里会执行少于1000条的数据，比如10000005条，它只执行最后5条， }finally{ JdbcUtils.release(conn, st, rs); } long endtime = System.currentTimeMillis(); //执行完sql语句的结束时间 System.out.println("花费了：" + (endtime-starttime)/1000 + "秒！！"); //花费多少时间 }
虽然通过这种方式Mysql可以处理千万条sql数据，但是花费的时间大概一千万条数据花费3个小时，但是已经很不错了，毕竟Mysql是免费的
如果采用的是Oracle要执行千万条sql语句只会要几分钟就搞定，这就是免费和收费的区别
我们要将采用Oracle只需要倒入Oracle驱动，然后修改db.properities
/day15/src/db.properties
url=jdbc:mysql://localhost:3306/day15 username=root password=root driver=com.mysql.jdbc.Driver #url=jdbc:oracle:thin:@localhost:1521:ORCL #username=system #password=itcast #driver=oracle.jdbc.driver.OracleDriver
下面是Oracle插入千万条数据
@Test public void testBatch2ForOracle() throws Exception{ long starttime = System.currentTimeMillis(); Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into testbatch(id,name) values(?,?)"; st = conn.prepareStatement(sql); for(int i=0;i<10000005;i++){ st.setInt(1, i); st.setString(2, "a" + i); st.addBatch(); //i=1000 2000 if(i%1000==0){ st.executeBatch(); st.clearBatch(); } } st.executeBatch(); }finally{ JdbcUtils.release(conn, st, rs); } long endtime = System.currentTimeMillis(); System.out.println("花费了：" + (endtime-starttime)/1000 + "秒！！"); }
测试用Oracle插入千万条数据只需要几分钟，很NB，但是要给钱
Tip：获得数据库自动生成的主键
在实际开发中，我们有时候需要获取到自动增长的主键
但是注意：此参数仅对insert操作有效。
下面开始做练习
先建立一张表来测试test1
create table test1
(
id int primary key auto_increment,
name varchar(100)
);
/day15/src/cn/itcast/demo/Demo4.java
package cn.itcast.demo; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; import cn.itcast.utils.JdbcUtils; //得到数据库自动增长的主键 public class Demo4 { /* create table test1 ( id int primary key auto_increment, name varchar(100) ); */ @Test public void test() throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "insert into test1(name) values('aa')"; //这里这个参数中其实可以不加Statement.RETURN_GENERATED_KEYS，这个参数表示指示是否应该返回自动生成的键的标志 //在Mysql中这个参数是缺省值，但是在Oracle中却不是缺省值，为了兼容这里建立加上 st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); st.executeUpdate(); rs = st.getGeneratedKeys(); //得到产生的主键，存入到结果集中 if(rs.next()){ System.out.println(rs.getInt(1)); //由于得到的结果集只有一行一列数据，所以获取getInt(1) } }finally{ JdbcUtils.release(conn, st, rs); } } }
Tip：JDBC调用存储过程
这个知识点最后讲，暂且放一下
Tip：事务
事物是JDBC中最重要的部分，这个也会在后面专题讲解
浙公网安备 33010602011771号