使用JDBC实现分页查询

最新推荐文章于 2022-01-19 19:39:42 发布

无忧无虑的小胖子

最新推荐文章于 2022-01-19 19:39:42 发布

阅读量2.8k

点赞数 4

CC 4.0 BY-SA版权

分类专栏： javaEE底层文章标签： jdbc sql 分页

本文链接：https://blog.youkuaiyun.com/qq_35079585/article/details/53321538

javaEE底层专栏收录该内容

2 篇文章

订阅专栏

前言

对于分页查询首先要知道在数据库中，首先需要了解分页查询的SQL语句，然后根据SQL语句的分析，在页面实现分页查询！
实现分页查询，
1、首先需要查询总记录数，并且通过它来计算总页数
–SELECT count(*) FROM 表名；
页数=总记录数/每页显示的数据数量+1
2、分页查询的SQL(其中 0 为数据的下标，下标由0开始，2为每页显示的数量)
– SELECT * FROM 表名 LIMIT 0，2;
3、而在现实情况中，页面获取的肯定是第几页，第几页，而不是数据的下标，所以需要一个换算关系！举例：我们已两条数据一页为例

//  数据信息 8条数据
1   2    3    4    5    6     7     8
//数据下标
0   1    2    3    4    5     6     7
//页码
  1        2          3          4 
//可以发规律： 每页第一个下标 = （页面-1）*每页显示的数据数量

了解这些就可以开始编码工作了

编写Dao层实现由数据库中获取所需信息

//获取总记录数
public int getAllCount();
//分页显示：pageNo 页码 pageSize 每页显示数量
public List<Book> findByPage(int pageNo,int pageSize);

编写Dao实现类，得到所需信息

//获取总条数
    @Override
    public int getAllCount() {
        String url = "jdbc:mysql://localhost:3306/bookmanager?useUnicode=true&characterEncoding=UTF-8";
        String user = "root";
        String password = "";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
                    try {
                        Class.forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection(url, user, password);
                        String sql = "SELECT COUNT(*) as count FROM bm_book";
                        ps = conn.prepareStatement(sql);
                        rs = ps.executeQuery();
                        while(rs.next()){
                            count = rs.getInt("count");
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        try {
                            if (ps != null) {
                                ps.close();
                            }
                            if (conn != null) {
                                conn.close();
                            }
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }

        return count;
    }
    //获取所需信息
        @Override
    public List<Book> findByPage(int pageNo,int pageSize) {
        String url = "jdbc:mysql://localhost:3306/bookmanager?useUnicode=true&characterEncoding=UTF-8";
        String user = "root";
        String password = "";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> books = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
            String sql = "SELECT id,name,author,publish,publishdate,"
                    + "page,price,content FROM bm_book LIMIT ?,?";  
            ps = conn.prepareStatement(sql);
            pageNo=(pageNo-1)*pageSize;
            ps.setInt(1,pageNo);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            if (rs != null) {
                books = new ArrayList<Book>();
                while (rs.next()) {
                    Book b = new Book();
                    b = new Book();
                    b.setId(rs.getInt("id"));
                    b.setName(rs.getString("name"));
                    b.setAuthor(rs.getString("author"));
                    b.setPublish(rs.getString("publish"));
                    b.setPublishDate(rs.getDate("publishdate"));
                    b.setPage(rs.getInt("page"));
                    b.setPrice(rs.getFloat("price"));
                    b.setContent(rs.getString("content"));
                    books.add(b);
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return books;
    }

编写Service层引入Dao 进行业务处理

//获取图书的总条数
public int getAllCount();
//分页信息
public  List<Book> findByPage(int pageNo,int pageSize); 
//实现类
//首先引入Dao层，并调用其方法
BookDao bookDao =new BookDaoImpl();
@Override
public int getAllCount() {
    return bookDao.getAllCount();
    }
    @Override
public List<Book> findByPage(int pageNo,int pageSize) {
    return bookDao.findByPage(pageNo, pageSize);
    }

编写页面参数工具类（为实现分页跳转并进行简单的逻辑处理）

public class PageSupport {
//总页数
    private int totalPageCount=1;
//总记录数  
    private int totalCount=0;
//当前页码
    private int currentPageNo=1;
//页码容量
    private int pageSize=0;
    public int getTotalPageCount() {
        return totalPageCount;
    }
    public void setTotalPageCount(int totalPageCount) {
        this.totalPageCount = totalPageCount;
    }
    public int getTotalCount() {
        return totalCount;
    }
    public void setTotalCount(int totalCount) {
        if(totalCount>0){
            this.totalCount = totalCount;
            //设置总页数
            this.setTotalPageCountByAs();
        }
    }
    public int getCurrentPageNo() {
        return currentPageNo;
    }
    public void setCurrentPageNo(int currentPageNo) {
        if(currentPageNo>0){
            this.currentPageNo = currentPageNo;
        }   
    }
    public int getPageSize() {
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        if(pageSize>0){
            this.pageSize = pageSize;   
        }   
    }
    /**
     * 设置总页数
     */
public void setTotalPageCountByAs(){
    if(this.totalCount % this.pageSize==0){
    this.totalPageCount=this.totalCount /this.pageSize;
    }else if(this.totalCount % this.pageSize>0) {
        this.totalPageCount=this.totalCount /this.pageSize+1;
    }else {
        this.totalCount=0;
        }   
    }

实现页面显示-使用JSP

<%
BookService bookService = new BookServiceImpl();
//页码容量
int pageSize=4;
//当前页码=如果页面的页面参数未赋值 ！默认显示第一页
String pageIndex=request.getParameter("pageIndex");
int currentPageNo;
if(pageIndex==null){
    currentPageNo=1;
}else{
    currentPageNo =Integer.parseInt(pageIndex);
    }
//总记录数
int totalCount =bookService.getAllCount();
// 通过页面工具类将所得参数赋给工具类中的属性
PageSupport pageSupport =new PageSupport();             pageSupport.setPageSize(pageSize);          pageSupport.setCurrentPageNo(currentPageNo);        pageSupport.setTotalCount(totalCount);
int pageTotalPageCount=pageSupport.getTotalPageCount();
//调用分页显示方法，保存到List集合中，遍历此集合
List<Book> bookList =bookService.findByPage(currentPageNo, pageSize);
    for (Book book : bookList) {
    %>
    <tbody>
    <tr>
        <td><%=book.getName()%></td>
        <td><%=book.getAuthor()%></td>
        <td><%=book.getPublish()%></td>
        <td><%=book.getPublishDate()%></td>
        <td><%=book.getPage()%></td>
        <td><%=book.getPrice()%></td>
        <td><%=book.getContent()%></td>
        <td>
        <a href="update.jsp?id=<%=book.getId()%>">修改</a>
        <a href="index.jsp?id=<%=book.getId()%>">删除</a>
        </td>
    </tr>
        </tbody>
            <%
        }   
        %>
<!-- 页码的跳转 -->
<div class="page-bar">
<a href="bookAddPage.jsp"><input type="button" value="增加"></input></a>
    <ul>
    <li>一共<%=totalCount%>条记录&nbsp;&nbsp; <%=currentPageNo %>/<%=pageTotalPageCount %>页</li>
    <%if(currentPageNo>1){ %>
    <a href="index.jsp?pageIndex=1">首页&nbsp;</a>
    <a href="index.jsp?pageIndex=<%=currentPageNo-1 %>">上一页&nbsp;</a>
    <% }if(currentPageNo<pageTotalPageCount){%>
    <a href="index.jsp?pageIndex=<%=currentPageNo+1 %>">下一页&nbsp;</a>
    <a href="index.jsp?pageIndex=<%=pageTotalPageCount %>">最后一页</a>
    <%} %>
    </ul>
    <span class="page-go-form1">
    <form id="1" name="name" action="index.jsp" method="post">
    <span class="page-go-form"><label>跳转至</label> </span>
    <input type="text" name="pageIndex" id="inputPage" class="page-key" />页
    <input type="submit" value="GO"/>
              </form>
        </span> 
        </div>