实现效果图
1.优化分页
使用jstl,所以需要在web–>web-inf下导包,再导入引用
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%--页码--%>
<c:forEach var="num" begin="${pageBean.startPage}" end="${pageBean.endPage}">
<c:if test="${pageBean.pageNum==num}">
<a class="class1" href="${pageContext.request.contextPath}/booklist?pageNum=${num}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">${num}</a>
</c:if>
<c:if test="${pageBean.pageNum!=num}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${num}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">${num} </a>
</c:if>
</c:forEach>
2.搜索跳转
<style type="text/css">
a{
text-decoration: none;
color: cornflowerblue;
}
a:hover{
text-decoration: underline;
}
.class1 {
background-color: brown;
color: white;
}
</style>
在每个页面跳转的a标签中拼接上自己的搜索条件:
&search=${search}&author=${author}
<%–添加搜索框,点击搜索跳转至booklist处理数据–%>
<form action="${pageContext.request.contextPath}/booklist" method="post">
<input type="text" name="search" value="${search}">
<input type="text" name="author" value="${author}">
<input type="submit" value="搜索">
</form>
<div>
<a href="${pageContext.request.contextPath}/booklist?pageNum=1&pageSize=${pageBean.pageSize}">首页</a>
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageNum-1}&pageSize=${pageBean.pageSize}">上一页</a>
<%--页码--%>
<c:forEach var="num" begin="${pageBean.startPage}" end="${pageBean.endPage}">
<c:if test="${pageBean.pageNum==num}">
<a class="class1" href="${pageContext.request.contextPath}/booklist?pageNum=${num}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">${num}</a>
</c:if>
<c:if test="${pageBean.pageNum!=num}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${num}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">${num} </a>
</c:if>
</c:forEach>
<c:if test="${pageBean.pageNum>=pageBean.pageCount}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageCount}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">下一页</a>
</c:if>
<c:if test="${pageBean.pageNum<pageBean.pageCount}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageNum+1}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">下一页</a>
</c:if>
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageCount}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">尾页</a>
共【${pageBean.pageCount}/${pageBean.pageNum}】页 <input id="page" type="number" name="page" min="1" max="${pageBean.pageCount}"><input type="button" value="跳转" onclick="judgePage()">
</div>
<script type="text/javascript">
function judgePage() {
var n=document.getElementById("page").value;
window.location="${pageContext.request.contextPath}/booklist?pageNum="+n+"&pageSize=${pageBean.pageSize}&search=${search}&author=${author}";
}
</script>
在servlet中获取jsp中输入的条件(书名称,作者),并且将它们放入request域中(servlet跳转至booklist时,在booklist中获取request域中的数据来拼接到a标签后面进行查询满足条件的书籍数据)
//获取页码和页大小
//获取页码和页大小
String pageNum = request.getParameter("pageNum");
String pageSize = request.getParameter("pageSize");
String search = request.getParameter("search");
String author = request.getParameter("author");
String condition="";
if(!StringUtils.isEmpty(search)){
condition+=" where title like '%"+search+"%'";
}
if(!StringUtils.isEmpty(author)){
if(condition.equals("")){
condition += " where author like '%"+author+"%'";
}else{
condition += " and author like '%"+author+"%'";
}
}
PageBean<Book> pageBean=bookService.queryByPage(page_num, page_size,condition);
request.setAttribute("pageBean", pageBean);
request.setAttribute("search", search);
request.setAttribute("author", author);
request.getRequestDispatcher("/booklist.jsp").forward(request, response);
修改dao层与service层中的查询方法以及它们的参数,将condition添加进去,如:
private QueryRunner qr=new QueryRunner();
@Override
public List<Book> queryByPage(int pageNum, int pageSize, String condition) {
Connection conn= DruidUtils.getConnection();
String sql=null;
if(condition.equals("")) {
sql = "select id,title,author,publicDate,publisher,isbn,price,picture,cid from book order by id limit ?,?";
}else{
sql= "select id,title,author,publicDate,publisher,isbn,price,picture,cid from book "+condition+" order by id limit ?,?";
}
try {
Object[] params={(pageNum-1)*pageSize,pageSize};
return qr.query(conn, sql, new BeanListHandler<Book>(Book.class),params);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("分页查询失败",e);
}finally {
DruidUtils.closeAll(null,null,conn);
}
}
3完整代码
booklist.jsp页面
<%--
Created by IntelliJ IDEA.
User: wgy
Date: 2020/9/2
Time: 11:10
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>书籍列表</title>
</head>
<style type="text/css">
a{
text-decoration: none;
color: cornflowerblue;
}
a:hover{
text-decoration: underline;
}
.class1 {
background-color: brown;
color: white;
}
</style>
<body>
<h2>书籍列表</h2>
<%--添加搜索框跳转至booklist处理数据--%>
<form action="${pageContext.request.contextPath}/booklist" method="post">
<input type="text" name="search" value="${search}">
<input type="text" name="author" value="${author}">
<input type="submit" value="搜索">
</form>
<table border="1" width="100%">
<tr>
<th>编号</th>
<th>书名</th>
<th>作者</th>
<th>出版日期</th>
<th>出版社</th>
<th>isbn</th>
<th>价格</th>
<th>图片</th>
<th>类别</th>
<th>操作</th>
</tr>
<c:forEach var="book" items="${pageBean.data}">
<tr>
<td>${book.id}</td>
<td>${book.title}</td>
<td>${book.author}</td>
<td><fmt:formatDate value="${book.publicDate}" pattern="yyyy-MM-dd"></fmt:formatDate></td>
<td>${book.publisher}</td>
<td>${book.isbn}</td>
<td>${book.price}</td>
<td><img src="${pageContext.request.contextPath}/picture?pic=${book.picture}" width="50" height="50"></td>
<td>${book.cid}</td>
<td><a href="${pageContext.request.contextPath}/editbook?bid=${book.id}">修改</a><a>删除</a></td>
</tr>
</c:forEach>
</table>
<div>
<a href="${pageContext.request.contextPath}/booklist?pageNum=1&pageSize=${pageBean.pageSize}">首页</a>
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageNum-1}&pageSize=${pageBean.pageSize}">上一页</a>
<%--页码--%>
<c:forEach var="num" begin="${pageBean.startPage}" end="${pageBean.endPage}">
<c:if test="${pageBean.pageNum==num}">
<a class="class1" href="${pageContext.request.contextPath}/booklist?pageNum=${num}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">${num}</a>
</c:if>
<c:if test="${pageBean.pageNum!=num}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${num}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">${num} </a>
</c:if>
</c:forEach>
<c:if test="${pageBean.pageNum>=pageBean.pageCount}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageCount}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">下一页</a>
</c:if>
<c:if test="${pageBean.pageNum<pageBean.pageCount}">
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageNum+1}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">下一页</a>
</c:if>
<a href="${pageContext.request.contextPath}/booklist?pageNum=${pageBean.pageCount}&pageSize=${pageBean.pageSize}&search=${search}&author=${author}">尾页</a>
共【${pageBean.pageCount}/${pageBean.pageNum}】页 <input id="page" type="number" name="page" min="1" max="${pageBean.pageCount}"><input type="button" value="跳转" onclick="judgePage()">
</div>
<script type="text/javascript">
function judgePage() {
var n=document.getElementById("page").value;
window.location="${pageContext.request.contextPath}/booklist?pageNum="+n+"&pageSize=${pageBean.pageSize}&search=${search}&author=${author}";
}
</script>
</body>
</html>
servlet层:BookListServlet
package com.qf.web.servlet;
import com.qf.domain.Book;
import com.qf.service.BookService;
import com.qf.service.impl.BookServiceImpl;
import com.qf.utils.PageBean;
import com.qf.utils.StringUtils;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/*
* wgy
* 2020/9/2
* 17:43
*/
@WebServlet(name = "BookListServlet",value = "/booklist")
public class BookListServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
//获取页码和页大小
String pageNum = request.getParameter("pageNum");
String pageSize = request.getParameter("pageSize");
String search = request.getParameter("search");
String author = request.getParameter("author");
String condition="";
if(!StringUtils.isEmpty(search)){
condition+=" where title like '%"+search+"%'";
}
if(!StringUtils.isEmpty(author)){
if(condition.equals("")){
condition += " where author like '%"+author+"%'";
}else{
condition += " and author like '%"+author+"%'";
}
}
int page_num=1;
int page_size=5;
if(!StringUtils.isEmpty(pageNum)){
page_num=Integer.parseInt(pageNum);
if(page_num<=0){
page_num=1;
}
}
if(!StringUtils.isEmpty(pageSize)){
page_size=Integer.parseInt(pageSize);
}
//查询
BookService bookService=new BookServiceImpl();
try {
PageBean<Book> pageBean=bookService.queryByPage(page_num, page_size,condition);
request.setAttribute("pageBean", pageBean);
request.setAttribute("search", search);
request.setAttribute("author", author);
request.getRequestDispatcher("/booklist.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
request.setAttribute("msg", "查询失败:"+e.getMessage());
request.getRequestDispatcher("/message.jsp").forward(request, response);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
utils层DruidUtils
package com.qf.utils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
* wgy
* 2020/8/31
* 9:35
*/
public class DruidUtils {
private static DruidDataSource dataSource=null;
private static ThreadLocal<Connection> threadLocal=new ThreadLocal<>();
static {
//初始化dataSource
try {
Properties properties=new Properties();
InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
is.close();
dataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
System.out.println("初始化连接池shibai");
}
}
public static DataSource getDateSource(){
return dataSource;
}
public static Connection getConnection(){
try {
Connection conn = threadLocal.get();
if(conn==null){
conn=dataSource.getConnection();
threadLocal.set(conn);
}
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void closeAll(ResultSet rs, Statement stat,Connection conn){
try {
if(rs!=null){
rs.close();
}
if(stat!=null){
stat.close();
}
if(conn!=null){
if(conn.getAutoCommit()) {
threadLocal.remove();
conn.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//事务有关的方法
public static void beginTransaction() throws SQLException{
Connection conn=getConnection();
if(conn!=null){
conn.setAutoCommit(false);
}
}
public static void commit() throws SQLException{
Connection conn=getConnection();
if(conn!=null){
conn.commit();
}
}
public static void rollback() throws SQLException{
Connection conn=getConnection();
if(conn!=null){
conn.rollback();
}
}
public static void close() throws SQLException{
Connection conn=getConnection();
if(conn!=null){
threadLocal.remove();
conn.close();
}
}
}
utils层PageBean(自己创建的,用来存储分页数据的实体类)
package com.qf.utils;
import java.util.List;
/*
* wgy
* 2020/9/2
* 17:39
*/
public class PageBean<T> {
//当前页
private int pageNum;
//总页数
private int pageCount;
//页大小
private int pageSize;
//总数据个数
private long totalSize;
//分页数据
private List<T> data;
private int startPage;
private int endPage;
public int getStartPage() {
return startPage;
}
public void setStartPage(int startPage) {
this.startPage = startPage;
}
public int getEndPage() {
return endPage;
}
public void setEndPage(int endPage) {
this.endPage = endPage;
}
public PageBean(int pageNum, int pageSize, long totalSize, List<T> data) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.totalSize = totalSize;
this.data = data;
//计算pageCount
pageCount= (int) (totalSize%pageSize==0?totalSize/pageSize:totalSize/pageSize+1);//1
//页码,第一种情况
startPage=pageNum-4;
endPage=pageNum+5;
//第二种情况
if(pageNum<5){
startPage=1;
endPage=10;
}
//第三种情况,页码小于5页
if(pageNum>pageCount-5){
startPage=pageCount-9;
endPage=pageCount;
}
//第四种情况,总页数小于10
if(pageCount<10){
startPage=1;
endPage=pageCount;
}
}
public PageBean() {
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public long getTotalSize() {
return totalSize;
}
public void setTotalSize(long totalSize) {
this.totalSize = totalSize;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
@Override
public String toString() {
return "PageBean{" +
"pageNum=" + pageNum +
", pageCount=" + pageCount +
", pageSize=" + pageSize +
", totalSize=" + totalSize +
", data=" + data +
'}';
}
}
utils层StringUtils
public class StringUtils {
public static boolean isEmpty(String str){
return str==null||str.trim().equals("");
}
}
dao层BookDaoImpl
public class BookDaoImpl implements BookDao {
private QueryRunner qr=new QueryRunner();
@Override
public List<Book> queryByPage(int pageNum, int pageSize, String condition) {
Connection conn= DruidUtils.getConnection();
String sql=null;
if(condition.equals("")) {
sql = "select id,title,author,publicDate,publisher,isbn,price,picture,cid from book order by id limit ?,?";
}else{
sql= "select id,title,author,publicDate,publisher,isbn,price,picture,cid from book "+condition+" order by id limit ?,?";
}
try {
Object[] params={(pageNum-1)*pageSize,pageSize};
return qr.query(conn, sql, new BeanListHandler<Book>(Book.class),params);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("分页查询失败",e);
}finally {
DruidUtils.closeAll(null,null,conn);
}
}
@Override
public long getCount(String condition) {
Connection conn= DruidUtils.getConnection();
String sql="select count(*) from book;";
try {
return qr.query(conn, sql, new ScalarHandler<>());
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("获取数据个数",e);
}finally {
DruidUtils.closeAll(null,null,conn);
}
}
@Override
public void add(Book book) {
Connection conn= DruidUtils.getConnection();
String sql="insert into book(title,author,publicDate,publisher,isbn,price,picture,cid) values(?,?,?,?,?,?,?,?)";
Object[] params={book.getTitle(),book.getAuthor(),book.getPublicDate(),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid()};
try {
qr.update(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("添加书籍失败",e);
}finally {
DruidUtils.closeAll(null,null,conn);
}
}
}
dao层BookDao
public interface BookDao {
//分页查询方法
List<Book> queryByPage(int pageNum, int pageSize, String condition);
//获取数据个数
long getCount(String condition);
void add(Book book);
}
service层BookService
public interface BookService {
void save(Book book);
PageBean<Book> queryByPage(int page_num, int page_size, String condition);
}
service层BookServiceImpl
public class BookServiceImpl implements BookService {
private BookDao bookDao=new BookDaoImpl();
@Override
public PageBean<Book> queryByPage(int pageNum, int pageSize,String condition) {
//查询总的数据个数
long count = bookDao.getCount(condition);
List<Book> books = bookDao.queryByPage(pageNum, pageSize,condition);
PageBean<Book> pageBean=new PageBean<>(pageNum,pageSize,count,books);
return pageBean;
}
@Override
public void save(Book book) {
bookDao.add(book);
}
}