和
BaseDao
見此項目 【JSP+Servlet】實作登入和注冊
BaseServlet
文章目錄
- 1. 資料庫建表
- 2. 對應的javaBean(Bean/Book)
- 3. Dao層操作資料庫
- 4. 業務邏輯層 service
- 5. Servlet 控制器Controller,用來控制頁面轉發
-
- 顯示所有圖書
- 添加圖書
- 删除圖書
- 修改圖書
- 6. 圖書分頁
-
- 建立Bean層的分頁模型`Page`
- dao層
- Service層
- Servlet層
- 前端顯示分頁資訊
1. 資料庫建表
book表
2. 對應的javaBean(Bean/Book)
public class Book {
private int id;
private String name;
private String author;
private int price;
3. Dao層操作資料庫
BookDaoImpl
public class BookDaoImpl extends BaseDao<Book> implements BookDao {
@Override
public List<Book> getAllBook() {
String sql = "select id, name, author, price from book";
List<Book> beanList = getBeanList(sql);
return beanList;
}
// 根據id擷取一個圖書資訊
@Override
public Book getBook(Book book) {
String sql = "select id, name, author, price from book where id = ?";
Book bean = getBean(sql, book.getId());
return bean;
}
// 添加圖書
@Override
public boolean addBook(Book book) {
String sql = "insert into book (name, author, price,sales ) values(?,?,?,?,?,?)";
int update = update(sql, book.getName(), book.getAuthor(), book.getPrice());
return update>0;
}
// 删除圖書
@Override
public boolean delBook(Book book) {
String sql = "delete from book where id = ?";
int update = update(sql, book.getId());
if (update>0)
return true;
else
return false;
}
// 修改圖書
@Override
public boolean updateBook(Book book) {
String sql = "update book set name = ? ,author = ? ,price = ? , where id = ?";
int update = update(sql, book.getName(), book.getAuthor(), book.getPrice(), book.getId());
return update>0;
}
4. 業務邏輯層 service
BookServiceImpl
public class BookServiceImpl implements BookService {
private BookDao bd = new BookDaoImpl();
@Override
public boolean add(Book book) {
return bd.addBook(book);
}
@Override
public boolean delete(Book book) {
return bd.delBook(book);
}
@Override
public boolean update(Book book) {
return bd.updateBook(book);
}
@Override
public Book getOne(Book book) {
return bd.getBook(book);
}
@Override
public List<Book> getAll() {
return bd.getAllBook();
}
}
5. Servlet 控制器Controller,用來控制頁面轉發
-
顯示所有圖書
點選圖書管理-》交給servlet->查出資料-》交給頁面顯示-
界面a标簽href調用index
BookManagerServlet
index.html
-
BookManagerServlet
public void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List<Book> books = bookService.queryAllBook(); request.setAttribute("books", books); request.getRequestDispatcher("/pages/book_manager.jsp").forward(request,response); }
-
book_manager.jsp
<tr> <td>名稱</td> <td>價格</td> <td>作者</td> <td>銷量</td> <td>庫存</td> <td colspan="2">操作</td> </tr> <c:forEach items="${ requestScope.list }" var="book"> <tr> <td>${ book.name }</td> <td>${ book.price }</td> <td>${ book.author }</td> <td>${ book.sales }</td> <td>${ book.stock }</td> <td><a href="#">修改</a></td> <td><a href="#">删除</a></td> </tr> </c:forEach>
-
-
添加圖書
book_edit
<form action="/BookManagerServlet" method="post"> <input type="hidden" name="pageNo" value="${add }" />
protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 将送出的圖書資訊封裝為book對象,表單的name應該和對象的屬性一緻 Book book = WebUtils.param2bean2(request,new Book()); // 将送出的圖書儲存到資料庫 boolean b = bookService.add(book); // 儲存成功,重回清單界面 response.sendRedirect(request.getContextPath()+"/BookManagerServlet?method=list"); }
-
删除圖書
使用者點選删除-》servlet接受請求-》按照圖書id删除圖書-》傳回圖書清單界面protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 封裝要删除的Bok Book book = WebUtils.param2bean2(request,new Book()); //删除 bookService.delete(book); // 傳回 response.sendRedirect(request.getContextPath()+"/BookManagerServlet?method=list"); }
-
修改圖書
- 首先先要在servlet根據id查找圖書,将圖書資訊顯示到修改頁面
protected void getBook(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 按照id查找圖書
Book book = WebUtils.param2bean2(request,new Book());
// 擷取詳細資訊
Book one = bookService.getOne(book);
// 回到編輯界面進行顯示,放在域中
request.setAttribute("book",one);
// 轉發到界面
request.getRequestDispatcher("/pages/book_edit.jsp");
}
book_edit
修改/添加界面
<form action="/BookManagerServlet" method="post">
<input type="hidden" name="pageNo" value="${add }" />
<tr>
<td><input name="name" type="text" value="${ book.title }"/></td>
<td><input name="price" type="text" value="${ book.price }"/></td>
<td><input name="author" type="text" value="${ book.author }"/></td>
<td><input name="sales" type="text" value="${ book.sales }"/></td>
<td><input name="stock" type="text" value="${ book.stock }"/></td>
<td><input type="submit" value="送出"/></td>
</tr>
問題: 修改和增加公用一個界面book_edit.jsp, 都指向同一個方法add, 點選送出,都調用了add方法,無法動态的更換method顯示更改後的圖書資訊
book_edit
修改value為update 或者 form表單修改為 /BookManagerServlet?method=update
<form action="/BookManagerServlet" method="post">
<input type="hidden" name="pageNo" value="${update }" />
BookManagerServlet
protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 按照id查找圖書
Book book = WebUtils.param2bean2(request,new Book());
// 由于添加和修改操作,封裝出的Book id有差别,是以可以通過id直接判别
if(book.getId()==0){
//添加
bookService.add(book);
}else{
//修改
bookService.update(book);
}
response.sendRedirect(request.getContextPath()+"/BookManagerServlet?method=list");
}
6. 圖書分頁
作用:顯示部分資訊,減輕伺服器負擔
分頁原理:select * from bs_book
limit
起始的索引,要取出多少個資料
要做出分頁功能,将參數(起始的索引)設定為動态改變的即可
分頁模型Page:
select * from bs_book limit index,size;
目前是第幾頁 int pageNo;
共多少頁 int totalPage;
共多少條記錄: int totalCount;
每頁顯示多少條記錄: int pageSize; (=size)
資料庫查詢的起始索引: int index; (根據目前是第幾頁算出索引)
使用泛型的目的是因為其他子產品也可能需要分頁
-
建立Bean層的分頁模型
Page
public class Page<T> { // 總共的記錄數 查詢得到 select count(*) private int totalCount; // 每頁顯示的記錄數 可以設定 private int pageSize = 4; // 總的頁數 計算得到 公式:總記錄數 / 每頁顯示記錄數 private int totalPage; // 目前頁碼 傳進來的參數 private int pageNo; // 從哪個索引開始查 計算得到 private int index; // 是否有下一頁 判斷得到 private boolean hasNext; // 是否有下一頁 判斷得到 private boolean hasPrev; // 封裝的目前頁的資料集合 查詢出來設定進去的 select * from bs_book limit index, size; private List<T> pageData; @Override public String toString() { return "Page{" + "totalCount=" + totalCount + ", pageSize=" + pageSize + ", totalPage=" + totalPage + ", pageNo=" + pageNo + ", index=" + index + ", hasNext=" + hasNext + ", hasPrev=" + hasPrev + ", pageData=" + pageData + '}'; } public Page() { super(); } public Page(int totalCount, int pageSize, int totalPage, int pageNo, int index, boolean hasNext, boolean hasPrev, List<T> pageData) { this.totalCount = totalCount; this.pageSize = pageSize; this.totalPage = totalPage; this.pageNo = pageNo; this.index = index; this.hasNext = hasNext; this.hasPrev = hasPrev; this.pageData = pageData; } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } //計算總頁數,向上取整 public int getTotalPage() { int t =getTotalCount()/getPageSize(); if(!(getTotalCount()%getPageSize() == 0)) t = t+1; return t; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public int getPageNo() { return pageNo; } public void setPageNo(int pageNo) { this.pageNo = pageNo; } // 計算索引值 public int getIndex() { // 每頁顯示4條 // 頁碼 開始索引 結束索引 // 1 0 3 // 2 4 7 // 3 8 11 int index = (getPageNo()-1) * pageSize; return index; } //不允許修改index 删除setIndex方法 // 是否有下一頁 public boolean isHasNext() { return getPageNo()<getTotalPage(); } // 是否有上一頁 public boolean isHasPrev() { return getPageNo()>1; } public List<T> getPageData() { return pageData; } public void setPageData(List<T> pageData) { this.pageData = pageData; } }
-
dao層
List getPageList(int index, int pageSize);
(Dao層就先傳回List原始資料, 在Service層在進行封裝成Book)
擷取總記錄數的方法:int getTotalCount();
BookDaoImpl
@Override public List<Book> getPageList(int index, int size) { String sql = "select id, name, author, price from book limit ?,?"; return getBeanList(sql, index, size); } @Override public int getTotalCount() { String sql = "select count(*) from book"; Object object = getSingleValue(sql); // object->int int i = Integer.parseInt(object.toString()); return i; }
BaseDao
/** * 查詢單個值,用在BookDao擷取資料庫總記錄數 * @param sql * @param params * @return */ public Object getSingleValue(String sql, Object ...params){ Object query = null; Connection connection = JDBCUtils.getConnection(); try { // ScalarHandler封裝單個資料 query = queryRunner.query(connection, sql, new ScalarHandler(), params); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeConnection(connection); } return query; }
-
Service層
BookService
/** * 擷取分頁資料 * @param pageNo 目前是第幾頁 request傳過來的資料是String類型,在函數裡面再進行轉型 * @param pageSize * @return */ @Override public Page<Book> getPage(String pageNo, String pageSize) { // 1. 将使用者傳入的資料先封裝部分 Page<Book> page = new Page<Book>(); // 将使用者傳入的資料轉型并封裝,設定預設值 int pn = 1; int ps = page.getPageSize(); pn = Integer.parseInt(pageNo); ps = Integer.parseInt(pageSize); // 2. 因為要使用目前總記錄,是以需要查詢資料庫 //先要設定頁面大小 page.setPageSize(ps); int totalCount = bd.getTotalCount(); // 擷取總記錄數 // 再設定總記錄數(這樣才能據此算出總頁碼) page.setTotalCount(totalCount); page.setPageNo(pn); // 3. 查詢資料并封裝 List<Book> list = bd.getPageList(page.getIndex(), page.getPageSize()); page.setPageData(list); return page; }
-
Servlet層
第一次請求圖書清單應該隻顯示第一頁資料
page()
book_manager.jsp
BookManagerServlet.java<c:forEach items="${ requestScope.page.pageData }" var="book">
/** * 顯示分頁資料 * @param request * @param response * @throws ServletException * @throws IOException */ protected void page(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 使用者點選圖書管理顯示部分資料,頁碼應該是由使用者傳進來的 // 動态擷取頁碼參數 String pn = request.getParameter("pn"); Page<Book> page = bookService.getPage(pn, "4"); // 将第一頁資料放到頁面顯示 request.setAttribute("page",page); // 交給頁面 request.getRequestDispatcher("/pages/book_manager.jsp").forward(request,response); }
-
前端顯示分頁資訊
book_manager.jsp
<div id="page_nav"> <a href="manage/BookManagerServlet?method=page&pn=1">首頁</a> <c:if test="${page.hasPrev}"> <a href="manage/BookManagerServlet?method=page&pn=${page.pageNo-1}">上一頁</a> </c:if> <%--目前頁碼--%> <%--顯示所有頁碼--%> <c:forEach begin = "1" end = "${page.totalPage}" var = "pnum"> <%--判斷目前周遊的頁碼是否是目前頁碼,如果是則不加連接配接--%> <c:if test = "${pnum == page.pageNo}"> 【${page.pageNo}】 </c:if> <c:if test = "${pnum != page.pageNo}"> <a href="manage/BookManagerServlet?method=page&pn=${pnum}">${pnum}</a> </c:if> </c:forEach> <c:if test="${page.hasNext}"> <a href="manage/BookManagerServlet?method=page&pn=${page.pageNo+1}">下一頁</a> </c:if> <a href="manage/BookManagerServlet?method=page&pn=${page.totalPage}">末頁</a> 共${page.totalPage}頁,${page.totalCount}條記錄 到第<input value="${page.pageNo}" name="pn" id="pn_input"/>頁 <input type="button" value="确定" id = "gotoPage"> </div>
解決顯示頁碼過多問題
相當于顯示出目前頁碼的前兩頁和後兩頁
- 總頁碼五頁以内,全部顯示
- begin = 1 ,end = totalPage
- 總頁碼五頁以上
- 目前頁碼< 3 顯示1-5
- 目前頁碼>=3
- 目前頁碼+2>總頁碼
- 總頁碼五頁以内,全部顯示
<%--總頁碼五頁以内--%>
<c:if test ="${page.totalPage<=5}">
<!--給begin和end動态指派-->
<c:set var = "begin" value = "1" scope ="page"></c:set>
<c:set var = "end" value = "${page.totalPage}" scope ="page"></c:set>
</c:if>
<%--總頁碼五頁以外--%>
<c:if test ="${page.totalPage>5}">
<c:if test="${page.pageNo<=3}">
<c:set var = "begin" value = "1" scope ="page"></c:set>
<c:set var = "end" value = "5" scope ="page"></c:set>
</c:if>
<c:if test="${page.pageNo>3}">
<c:set var = "begin" value = "${page.pageNo-2}" scope ="page"></c:set>
<c:set var = "end" value = "${page.pageNo+2}" scope ="page"></c:set>
</c:if>
<c:if test="${page.pageNo+2 >= page.totalPage}">
<c:set var = "begin" value = "${page.totalPage-4}" scope ="page"></c:set>
<c:set var = "end" value = "${page.totalPage}" scope ="page"></c:set>
</c:if>
</c:if>
<c:forEach begin = "${begin}" end = "${end}" var = "pnum">
修改删除優化
修改删除後跳轉在目前頁面而不是首頁
利用Http協定的請求頭
修改不能這樣用:因為修改請求來源于getBook方法,而不是page方法
protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// Http協定 請求頭 Referer代表我從哪裡來
String string = request.getHeader("Referer");
// 封裝要删除的Bok
Book book = WebUtils.param2Bean(request,new Book());
//删除
bookService.delete(book);
// 傳回
response.sendRedirect(string);
}
protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String pn = request.getParameter("pn");
// 按照id查找圖書
Book book = WebUtils.param2Bean(request,new Book());
// 由于添加和修改操作,封裝出的Book id有差别,是以可以通過id直接判别
if(book.getId()==0){
//添加
bookService.add(book);
}else{
//修改
bookService.update(book);
}
//跳轉到請求頭為了修改後還停留在此界面而不是跳轉到首頁
response.sendRedirect(request.getContextPath()+"/manage/BookManagerServlet?method=page&pn="+pn);
}
<%--帶上正在修改的頁數--%>
<input type="hidden" name="pageNo" value="${param.pn}" />