Javaweb中结合mysql数据库实现分页功能

Javaweb分页技术实现

分页技术就是通过SQL语句(如下)来获取数据，具体实现看下面代码

//分页查询语句select * from 表名 where limit page , count;和//获取表中的总数据，确定页数select count(*) from 表名;
1
2
3
4
5

1.配置数据源

在项目的WebContent/META-INF目录下创建一个context.xml文件。如图:

在context.xml文件中配置：

<Context>          <Resource name="jdbc/bookstore" auth="Container" type="javax.sql.DataSource"    maxActive="100" maxIdle="30" maxWait="10000"    username="root" password="root" driverClassName="com.mysql.jdbc.Driver"    url="jdbc:mysql://localhost:3306/db_bookstore"/></Context>
1
2
3
4
5
6

这里采用的是Javaweb自带的DBCP配置，详细参考Javaweb配置常用的数据源配置

2.Java代码

• DBUtil

public class DBUtils {    public static Connection getConn(){        try {            InitialContext context = new InitialContext();            DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/bookstore");            return dataSource.getConnection();        } catch (SQLException e) {            e.printStackTrace();        } catch (NamingException e) {            e.printStackTrace();        }        return null;    }    public static void close(Connection conn){        try {            if(conn!=null){                conn.close();            }        } catch (SQLException e) {            e.printStackTrace();        }    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

• BookDao

public interface BookDao {    public List<Book> findBooks(int page , int count) throws SQLException;    public int count() throws SQLException;}
1
2
3
4
5
6

• BookDaoImpl

public class BookDaoImpl implements BookDao{    private Connection conn = DBUtils.getConn();    @Override    public List<Book> findBooks(int page, int count) throws SQLException {        if(conn==null){            throw new NullPointerException("conn is null");        }        PreparedStatement ps = conn.prepareStatement("SELECT id,name,price,category,author,descs FROM tb_bookstore LIMIT ?,?");        if(ps==null){            throw new NullPointerException("ps is null");        }        ps.setInt(1, (page-1)*count);        ps.setInt(2, count);        ResultSet rs = ps.executeQuery();        if(rs==null){            throw new NullPointerException("rs is null");        }           List<Book> books = new ArrayList<>();        while (rs.next()) {            Book book = new Book();            book.setId(rs.getInt(1));            book.setName(rs.getString(2));            book.setPrice(rs.getDouble(3));            book.setCategory(rs.getString(4));            book.setAuthor(rs.getString(5));            book.setDescs(rs.getString(6));            books.add(book);        }           return books;    }    @Override    public int count() throws SQLException {        if(conn==null){            throw new NullPointerException("conn is null");        }        PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM tb_bookstore");        if(ps==null){            throw new NullPointerException("ps is null");        }        ResultSet rs = ps.executeQuery();        if(rs==null){            throw new NullPointerException("rs is null");        }           int count = 0;        if (rs.next()) {            count = rs.getInt(1);        }        return count;    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

• BookService

public interface BookService {    public Page findPage(int page,int count);}
1
2
3
4

• BookServiceImpl

package com.yundoku.service.impl;import java.sql.SQLException;import java.util.List;import com.yundoku.dao.BookDao;import com.yundoku.dao.impl.BookDaoImpl;import com.yundoku.domain.Book;import com.yundoku.domain.Page;import com.yundoku.service.BookService;public class BookServiceImpl implements BookService{    private BookDao bookDao = new BookDaoImpl();    @Override    public Page findPage(int page, int count) {        if(bookDao==null){            bookDao = new BookDaoImpl();        }        try {            List<Book> books = bookDao.findBooks(page, count);            System.out.println(books);            int totle = bookDao.count();            System.out.println(totle);            Page p = new Page();            p.setBooks(books);            p.setCurrentPage(page);            p.setCount(count);            p.setTotalCount(totle);            int totlePage = totle%count==0?totle/count:(totle/count)+1;            p.setTotalPage(totlePage);            return p;        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

• Book

public class Book {    private int id;    private String name;    private double price;    private String category;    private String author;    private String descs;    ...    //省略get set方法}
1
2
3
4
5
6
7
8
9
10
11
12

• Page

public class Page {    private int currentPage;//当前页    private int totalPage;//总页数    private int count;//一页多少条数据    private List<Book> books;//当前页的图书数据    private int totalCount;//数据总条数    ...    //省略get set方法}
1
2
3
4
5
6
7
8
9
10

• HomeServlet

@WebServlet({ "/HomeServlet", "/home" })public class HomeServlet extends HttpServlet {    private static final long serialVersionUID = 1L;    public HomeServlet() {        super();    }    protected void doGet(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {        request.setCharacterEncoding("utf-8");        response.setContentType("text/html;charset=utf-8");           BookService service = new BookServiceImpl();        int currentPage=1;        int count=10;        String value = request.getParameter("page");        if(value!=null&&!"".equals(value)){                     currentPage = Integer.parseInt(value);        }        Page page  = service.findPage(currentPage, count);        request.setAttribute("page", page);        request.getRequestDispatcher("/jsp/home.jsp?page="+currentPage).forward(request, response);     }    protected void doPost(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {        doGet(request, response);    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

3.jsp代码

<%@ page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8" isELIgnored="false"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><html><head><title>Insert title here</title></head><body>    <div align="center" >    <font size="5">        那些年一起追过的小说    </font>    </div>    <br/>    <table border="1" width="100%">            <tr>                <td>单号</td>                <td>名称</td>                <td>价格</td>                <td>类别</td>                <td>作者</td>                <td>描述</td>            </tr>        <c:if test="${empty page.books }">            <tr>                <td colspan="8" align="center">没有商品</td>            </tr>        </c:if>         <c:forEach items="${page.books }" var="book">            <tr>                <td>${book.id}</td>                <td>${book.name}</td>                <td>${book.price}</td>                <td>${book.category}</td>                <td>${book.author}</td>                <td>${book.descs}</td>            </tr>        </c:forEach>    </table>    <br/>    <div align="center">    <c:if test="${page.currentPage>1 }">        <a href="${pageContext.request.contextPath }/home?page=${page.currentPage-1}">上一页</a>    </c:if>        <a href="${pageContext.request.contextPath }/home?page=${1}">首页</a>            <c:forEach begin="1" end="${page.totalPage }" step="1" var="i">                <c:if test="${page.currentPage==i }">                    <a href="${pageContext.request.contextPath }/home?page=${i}"><font color="#ff0000">${i}</font></a>                </c:if>                <c:if test="${page.currentPage!=i }">                    <a href="${pageContext.request.contextPath }/home?page=${i}">${i}</a>                </c:if>            </c:forEach>        <a href="${pageContext.request.contextPath }/home?page=${page.totalPage}">末页</a>    <c:if test="${page.currentPage< page.totalPage }">        <a href="${pageContext.request.contextPath }/home?page=${page.currentPage+1}">下一页</a>    </c:if>    </div></body></html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63