Java编程琐事（7）——封装JDBC分页

本文主要是介绍Java编程琐事（7）——封装JDBC分页，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、编写一个接口从ResultSet继承：

package com.solid.test;

import java.sql.ResultSet;

import java.sql.SQLException;

public interface Pageable extends ResultSet {

   

    //返回总页数

    int getPageCount();

   

    //返回当前页的记录条数

    int getPageRowsCount();

   

    //返回分页大小

    int getPageSize();

   

    //转到指定页

    void gotoPage(int page);

   

    //设置分页大小

    void setPageSize(int pageSize);

   

    //返回总记录行数

    int getRowsCount();

   

    //转到当前页的第一条记录

    void pageFirst() throws SQLException;

   

    //转到当前页的最后一条记录

    void pageLast() throws SQLException;

   

    //返回当前页号

    int getCurrentPageNo();

}

 

二、编写实现接口的方法：

public class PageableResultSet implements Pageable {

    protected ResultSet rs=null;

    //每页行数

    protected int rowsCount;

    //分页大小

    protected int pageSize;

    //当前页号

    protected int currentPageNo;

   

    protected String command = "";

   

    //构造方法

    public PageableResultSet(ResultSet rs) throws java.sql.SQLException {

       if(rs==null) throw new SQLException("given ResultSet is NULL","user");

       rs.last();

       rowsCount=rs.getRow();

       rs.beforeFirst();

       this.rs=rs;

    }

 

    //返回当前页号

    public int getCurrentPageNo() {

       return currentPageNo;

    }

 

    //返回总页数

    public int getPageCount() {

       // TODO Auto-generated method stub

       if(rowsCount == 0) return 0;

       if(pageSize == 0) return 1;

       double tmpD = (double)rowsCount/pageSize;

       int tmpI = (int)tmpD;

       if(tmpD > tmpI) tmpI++;

       return tmpI;

    }

 

    //返回当前页的记录条数

    public int getPageRowsCount() {

       if(pageSize == 0) return rowsCount;

       if(getRowsCount() == 0) return 0;

       if(currentPageNo != getPageCount()) return pageSize;

       return rowsCount-(getPageCount()-1)*pageSize;

    }

 

    //返回分页大小

    public int getPageSize() {

       return pageSize;

    }

 

    //返回每页行数

    public int getRowsCount() {

       return rowsCount;

    }

 

    //跳转到第几页

    public void gotoPage(int page) {

       if(rs == null) return;

       if(page < 1) page = 1;

       if(page > getPageCount()) page = getPageCount();

       int row = (page - 1) * pageSize + 1;

       try {

           rs.absolute(row);

           currentPageNo = page;

       } catch (SQLException e) {

           e.printStackTrace();

       }

 

    }

 

    //跳转到首页

    public void pageFirst() throws SQLException {

       int row=(currentPageNo - 1) * pageSize + 1;

       rs.absolute(row);

    }

 

    //跳转到最后一页

    public void pageLast() throws SQLException {

       int row=(currentPageNo - 1) * pageSize + getPageRowsCount();

       rs.absolute(row);

    }

 

    //设置分页大小

    public void setPageSize(int pageSize) {

       if(pageSize >= 0){

           this.pageSize = pageSize;

           currentPageNo = 1;

       }

    }

}

 

三、JSP页面中分页操作：

<table width="100%" border="0" cellpadding=0 cellspacing=0 align=left class="table_bg">

    <tr>

      <td width="100%">

        <table width="100%"  style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid; FONT-SIZE: 12px; BORDER-LEFT: 1px solid; WIDTH: 100%; BORDER-BOTTOM: 1px solid" border="1"  align=left>

          <tr bgcolor="#e6e6e6">

           <td width="50%" align="center">标题</td>

           <td width="10%" align="center">所属频道</td>

           <td width="10%" align="center">发送人</td>

           <td width="15%" align="center">发送时间</td>

           <td width="15%" align="center">送审批</td>

       </tr>

    <%

       Statement stm=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

              ResultSet.CONCUR_READ_ONLY);

       Pageable rs=null;

       String loginuserid = usersdao.getUser_id();

       String sql = "select a.paper_id,a1.name as classname,a.class_id,a.paper_name,a.public_flag,a.flow_defineid,"

           + "a.inputman," + com.approval.lib.CmUtil.getFormatDateTime("a.inputtime","yyyy-mm-dd hh24:mm:ss") + " as inputtime,b.flow_id,b.create_userid,b.create_time,b.isfinish, "

           + "c.flow_logid,c.sender,c.receiver," + com.approval.lib.CmUtil.getFormatDateTime("c.receivetime","yyyy-mm-dd hh24:mm:ss") + " as receivetime,d.user_name "

           + "from t_cms_d_paper a "

           + "join t_cms_d_channel a1 on a.class_id=a1.id "

           + "join t_cms_flow b on a.paper_id=b.paper_id "

           + "join t_cms_flowlog c on b.flow_id=c.flow_id "

           +" join t_cms_d_user d on c.sender=d.user_id "

           + "where a.del_flag=0 and b.isfinish!=1 and (a.public_flag=9 or a.public_flag=-1) and c.isfinish=0 and c.receiver='"

           + loginuserid + "' order by c.receivetime desc ";

       rs=new PageableResultSet(stm.executeQuery(sql));

       rs.setPageSize(10);

       String checkpage=request.getParameter("page");

       if(null!=checkpage && !("".equals(checkpage)))

       {

           rs.gotoPage(Integer.parseInt(checkpage));

       }

       else

       {

           rs.gotoPage(1);

       }

       for(int i=0;i<rs.getPageRowsCount();i++)

       {

           %>

           <tr>

              <td align="center"><%=rs.getString("paper_name") %></td>

               <td align="center"><%=rs.getString("classname") %></td>

              <td align="center"><%=rs.getString("user_name") %></td>

              <td align="center"><%=rs.getString("receivetime")  %></td>

              <td align="center"><span class="checkspan" onclick="opencheck(<%=rs.getString("paper_id") %>,<%=rs.getString("class_id") %>)">审批</span></td>

           </tr>

           <%

           rs.next();

       }

       rs.close();

       stm.close();

       con.close();

       String strurl=com.approval.lib.LoadProperties.getProperty("context")+"/flow/flowPaper/waitcheck.jsp?page=";

       String nextpageurl=strurl+(rs.getCurPage()+1);

       String prepageurl=strurl+(rs.getCurPage()-1);

       String lastpageurl=strurl+rs.getPageCount();

   

    %>

    <tr>

       <td colspan="4" align="center">

           <a href="<%=com.approval.lib.LoadProperties.getProperty("context") %>/flow/flowPaper/waitcheck.jsp?page=1">首页</a>

           <a href="<%=prepageurl %>">上一页</a>

           <a href="<%=nextpageurl %>">下一页</a>

           <a href="<%=lastpageurl %>">尾页</a>

       </td>

    </tr>

    </table>

    </td>

    </tr>

    </table>

这篇关于Java编程琐事（7）——封装JDBC分页的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---