JavaWeb分页技术总结 - HelloWorld开发者社区

分页技术：将所有数据分段展示给用户的技术。分页的意义：增加系统的复杂度，另外可以将大量的数据限制在某一个特定的范围。选择分页的标准：首先，判断的标准是速度,显而易见,数据库服务器,Web应用服务器和客户端之间是网络,如果网络传递的数据量越少,则客户端获得响应的速度越快。一般来说,数据库服务器和Web应用服务器的处理能力一般比客户端要强很多.从这两点来看,在客户端分页的方案是最不可取的。其次，在Web服务器端分页和在数据库端分页,如果选择在Web服务器端分页的话,大部分的被过滤掉的数据还是被传输到了Web应用服务器端,与其这样还不如直接在数据库端进行分页。因此比较好的分页做法应该是每次翻页的时候只从数据库里检索页面大小的块区的数据。虽然每次翻页都需要查询数据库，但查询出的记录数很少，网络传输数据量不大，如果使用连接池更可以略过最耗时的建立数据库连接过程。而在数据库端有各种成熟的优化技术用于提高查询速度，比在应用服务器层做缓存有效得多。 Web服务器端分页和在数据库端分页：数据库端分页：通过JDBC的方式访问数据库，根据数据库类型采取不同的SQL分页语句。对于MySql数据库：采用limit m,n语句进行分页，需要指定m和n的2个值 Limit后的两个参数中，参数m是起始下标[页码]，它从0开始；参数n是返回的记录数。 SQL语句形式：

  语句1: select * from 表名 limit m,n; --返回表中m-n行的数据
  语句2: select * from 表名 limit m offset n; --从n行开始到返回表中m行的数据
  假设 numberpage 表示每页要显示的条数,pagenumber表示页码:
  语句3：select * from 表名 limit (pagenumber-1)*numberpage,numberpage;
  语句4：select * from 表名 limit numberpage offset(pagenumber-1)*numberpage;

对于Oracle数据库：采用rownum的方式进行分页,rownum表示一条记录的行号,注意Oracle获取每一行后才赋予.因此,指定rownum区间来取得分页数据在一层查询语句中是无法做到的,要分页还要进行一次查询。 SQL形式：

  select * from (
    select page.*,rownum rn 
    from (select * from 表名) page 
    where rownum <= (?-1)*?+?)
    where rn > (?-1)*?;


  --其中page是将表中的数据查询出来取名为page

另外,不论使用哪种数据库，都的想知道数据中有几条数据，自然需要一个获取总行的SQL： SQL形式：

 select count(*) from 表名;

首先：实现分页的具体实现需要结合JDBC和Java代码实现：分页的时，返回的参数包括查询的结果集(List)，总的页数(pageCount)、当前第几页(pageNo)等等信息，所以我们封装一个查询结果Page类，当然还需要分页的一个实体类，这里我以Entity为例，具体代码：

Page.java:
//第一种实现方式：
import java.util.List;
import entity.Entity;
public class Page {
private int pageNo;//当前页数
private int hangCount;//每页显示的行数
private int sumCount;//总条数
private int sumPage;//总页数
         private List<Entity>  entitys;//需要生成setXX()和getXX()方法
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
//给当前页赋值
if (pageNo<=1) {
   this.pageNo=1;
}else if (pageNo>=this.sumPage) {
this.pageNo=this.sumPage;
}else {
this.pageNo = pageNo;

}
}
public int getHangCount() {
return hangCount;
}
public void setHangCount(int hangCount) {
this.hangCount = hangCount;
}
public int getSumCount() {
return sumCount;
}
public void setSumCount(int sumCount) {
this.sumCount = sumCount;
if(this.sumCount%this.hangCount==0){
this.sumPage = this.sumCount/this.hangCount;
}
else{
this.sumPage = this.sumCount/this.hangCount+1;
}
}
public int getSumPage() {
return sumPage;
}
public void setSumPage(int sumPage) {
this.sumPage = sumPage;
}
//List<Entity>  entitys 生成的setXX()和getXX()方法

}
//第二种实现方式：
import java.util.List;
import entity.Entity;
public class Page {
private int pageNo;//当前页数
private int hangCount;//每页显示的行数
private int sumCount;//总条数
private int sumPage;//总页数
         private List<Entity>  entitys;//需要生成setXX()和getXX()方法
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
  this.pageNo = pageNo;  
}
public int getHangCount() {
return hangCount;
}
public void setHangCount(int hangCount) {
this.hangCount = hangCount;
}
public int getSumCount() {
return sumCount;
}
public void setSumCount(int sumCount) {
this.sumCount = sumCount;
setSumPage((getSumCount()%hangCount)==0?(getSumCount()/hangCount):(getTotalNum() /hangCount+1));  
}
public int getSumPage() {
return sumPage;
}
public void setSumPage(int sumPage) {
this.sumPage = sumPage;
}
   // 获取首页  
    public int getFirstPage() {  
        return 1;  
    }  
    // 获取末页  
    public int getLastPage() {  
        return sumPage;  
    }  

    // 获取上一页  
    public int getPrePage() {  
        if (pageNo > 1)  
            return pageNo - 1;  
        return 1;  
    }  
    // 获取下一页  
    public int getBackPage() {  
        if (pageNo<sumPage)  
            return pageNo + 1;  
        return sumPage;  
    }  
    // 判断'首页'及‘上一页’是否可用  
    public String isPreable() {  
        if (pageNo == 1)  
            return "disabled";  
        return "";  
    }  
    // 判断'尾页'及‘下一页’是否可用  
    public String isBackable() {  
        if (pageNo == totalPage)  
            return "disabled";  
        return "";  
     }  
//List<Entity>  entitys 生成的setXX()和getXX()方法
}

其次：编写数据数据连接类，以BaseDao为例，具体代码：数据库驱动类名：oracle.jdbc.driver.OracleDriver 数据库连接URL：jdbc:oracle:thin:@localhost:1521:连接名数据库用户名和密码：userName，passWord

//BaseDao.java
第一种方式：
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
public class BaseDao{ 
        //使用ThreadLocal是为了保证事务的一致，使得同一个线程的所有数据库操作使用同一个Connection 
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();  
    public static Connection getConnection() {  
        Connection conn = null;  
        conn = threadLocal.get();  
        if (conn == null) {  
            try {  
                Class.forName("数据库驱动类名");  
           conn = DriverManager.getConnection(  
           "数据库连接URL", "用户名","密码");  
                threadLocal.set(conn);  
            } catch (ClassNotFoundException e) {  
                e.printStackTrace();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        return conn;  
    }  

    // 封装设置Connection自动提交  
    public static void setAutoCommit(Connection conn, Boolean flag) {  
        try {  
            conn.setAutoCommit(flag);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    // 设置事务提交  
    public static void commit(Connection conn) {  
        try {  
            conn.commit();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    // 封装设置Connection回滚  
    public static void rollBack(Connection conn) {  
        try {  
            conn.rollback();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    // 封装关闭Connection、PreparedStatement、ResultSet的函数  
    public static void closeConnection() {  
        Connection conn = threadLocal.get();  
        try {  
            if (conn != null) {  
                conn.close();  
                conn = null;  
                threadLocal.remove();  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  

    }  
      //关闭PreparedStatement
    public static void closePreparedStatement(PreparedStatement pstmt) {  
        try {  
            if (pstmt != null) {  
                pstmt.close();  
                pstmt = null;  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
      //关闭ResultSet
    public static void closeResultSet(ResultSet rs) {  
        try {  
            if (rs != null) {  
                rs.close();  
                rs = null;  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
    //公共的增删改方法
public int allUpdateAndAddAndDel(List<Object> list,String sql){
try {
conn=getConnection();
ps=conn.prepareStatement(sql);
if(list.size()>0){
for (int i = 0; i < list.size(); i++) {
ps.setObject((i+1), list.get(i));
}
}
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
return -1;
}
finally{
closeAll();
}
}
//查询公共方法
    public ResultSet select(String sql,List<Object> list) {
  try {
  conn=getConnection();
  ps=conn.prepareStatement(sql);
  if (list.size()>0) {
for (int i = 0; i < list.size(); i++) {
ps.setObject((i+1), list.get(i));
}
}
  rs=ps.executeQuery();

  return rs;  
} catch (Exception e) {
e.printStackTrace();
return null;  
}
}
}

第二种方式：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class BaseDao {
private String name="用户名";
private String pwd="密码";
private String drivername="数据库驱动名";
private String url="数据库连接URL";
public Connection conn = null;
public PreparedStatement ps = null;
public ResultSet rs = null;

/**
* 关闭所有接口对象的方法
*/
public void closeAll(){
try {
if(rs!=null)rs.close();
if(ps!=null)ps.close();
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接对象的方法
*/
public Connection getConnection(){
try {
Class.forName(drivername);
conn = DriverManager.getConnection(url, name, pwd);
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 封装设置Connection自动提交  
    public static void setAutoCommit(Connection conn, Boolean flag) {  
        try {  
            conn.setAutoCommit(flag);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    // 设置事务提交  
    public static void commit(Connection conn) {  
        try {  
            conn.commit();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    // 封装设置Connection回滚  
    public static void rollBack(Connection conn) {  
        try {  
            conn.rollback();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

    // 封装关闭Connection、PreparedStatement、ResultSet的函数  
    public static void closeConnection() {  
        Connection conn = threadLocal.get();  
        try {  
            if (conn != null) {  
                conn.close();  
                conn = null;  
                threadLocal.remove();  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  

    }  
//公共的增删改方法
public int allUpdateAndAddAndDel(List<Object> list,String sql){
try {
conn=getConnection();
ps=conn.prepareStatement(sql);
if(list.size()>0){
for (int i = 0; i < list.size(); i++) {
ps.setObject((i+1), list.get(i));
}
}
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
return -1;
}
finally{
closeAll();
}
}
//查询公共方法
    public ResultSet select(String sql,List<Object> list) {
  try {
  conn=getConnection();
  ps=conn.prepareStatement(sql);
  if (list.size()>0) {
for (int i = 0; i < list.size(); i++) {
ps.setObject((i+1), list.get(i));
}
}
  rs=ps.executeQuery();

  return rs;  
} catch (Exception e) {
e.printStackTrace();
return null;  
}
}
}

利用Jsp/Servlet实现：以实体类User为例，需要UserInfo.java:

//UserInfo.java
import java.util.Date;  
public class UserInfo {  
    private int id;  
    private String username;  
    private String password;  
    private String truename;  
    private String sex;  
    private Date birthday;  
    private String home;  
    private String colleage;  
    private String comingYear;  

    public int getId() {  
        return id;  
    }  

    public void setId(int id) {  
        this.id = id;  
    }  

    public String getUsername() {  
        return username;  
    }  

    public void setUsername(String username) {  
        this.username = username;  
    }  

    public String getPassword() {  
        return password;  
    }  

    public void setPassword(String password) {  
        this.password = password;  
    }  

    public String getTruename() {  
        return truename;  
    }  

    public void setTruename(String truename) {  
        this.truename = truename;  
    }  

    public String getSex() {  
        return sex;  
    }  

    public void setSex(String sex) {  
        this.sex = sex;  
    }  

    public Date getBirthday() {  
        return birthday;  
    }  

    public void setBirthday(Date birthday) {  
        this.birthday = birthday;  
    }  

    public String getHome() {  
        return home;  
    }  

    public void setHome(String home) {  
        this.home = home;  
    }  

    public String getColleage() {  
        return colleage;  
    }  

    public void setColleage(String colleage) {  
        this.colleage = colleage;  
    }  

    public String getCy() {  
        return comingYear;  
    }  

    public void setCy(String cy) {  
        this. comingYear= cy;  
    }  
}

在dao层实现：

import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.List;  
import entity.UserInfo;  
import entity.BaseDao;   
public class UserInfoDao {  
    public List<UserInfo> getUserList(UserInfo userInfo, int pageNo,  
            int hangCount ) throws Exception {  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;  
        List<UserInfo> userList = null;  
        try {  
            String sql = "select * from(select rownum num,u.* from(select * from user_info where sex = ? and home like '"  
                    + userInfo.getHome()  
                    + "%"  
                    + "' and colleage like '"  
                    + userInfo.getColleage()  
                    + "%"  
                    + "' and comingyear like '"  
                    + userInfo.getCy()  
                    + "%"  
                    + "' order by id) u where rownum<=?) where num>=?";  
            userList = new ArrayList<UserInfo>();  
            Connection conn = BaseDao.getConnection();  
            pstmt = conn.prepareStatement(sql);  
            pstmt.setString(1, userInfo.getSex());  
            pstmt.setInt(2, pageNo * hangCount);  
            pstmt.setInt(3, (pageNo - 1) * hangCount + 1);  
            rs = pstmt.executeQuery();  
            while (rs.next()) {  
                UserInfo user = new UserInfo();  
                user.setId(rs.getInt("id"));  
                user.setTruename(rs.getString("truename"));  
                user.setSex(rs.getString("sex"));  
                user.setHome(rs.getString("home"));  
                userList.add(user);  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
            throw new Exception(e);  
        } finally {  
            BaseDao.closeResultSet(rs);  
            BaseDao.closePreparedStatement(pstmt);  
        }  
        return userList;  
    }  

    public int getTotalNum(UserInfo userInfo) throws Exception {  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;  
        int count = 0;  
        try {  
            String sql = "select count(*) from user_info where sex=? and home like '"  
                    + userInfo.getHome()  
                    + "%"  
                    + "' and colleage like '"  
                    + userInfo.getColleage()  
                    + "%"  
                    + "' and comingyear like '"  
                    + userInfo.getCy()+ "%" + "'";  
            Connection conn = BaseDao.getConnection();  
            pstmt = conn.prepareStatement(sql);  
            pstmt.setString(1, userInfo.getSex());  
            rs = pstmt.executeQuery();  
            if (rs.next()) {  
                count = rs.getInt(1);  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
            throw new Exception(e);  
        } finally {  
            BaseDao.closeResultSet(rs);  
             BaseDao.closePreparedStatement(pstmt);  
        }  
        return count;  
    }  
}
在service层：
import java.sql.Connection;  
import util.BaseDao;  
import util.Page;  
public class UserInfoManage {  
    private UserInfoDao userInfoDao = null;  

    public UserInfoManage () {  
        userInfoDao = new UserInfoDao();  
    }  

    public Page userBasicSearch(UserInfo u, int pageNo,  
            int hangCount) throws Exception {  
        Connection connection = null;  
        Page pagination = new Page();  
        try {  
            connection = BaseDao.getConnection();  
            DBUtility.setAutoCommit(connection, false);  
            pagination.setList(userInfoDao.getUserList(u, pageNo, hangCount));  
            pagination.setPageNo(pageNo);  
            pagination.setHangCount(hangCount);  
            pagination.setSumCount(userInfoDao.getTotalNum(u));  
            BaseDao.commit(connection);  
        } catch (Exception e) {  
            DBUtility.rollBack(connection);  
            e.printStackTrace();  
            throw new Exception();  
        } finally {  
            BaseDao.closeConnection();  
        }  
        return pagination;  
    }  
}

在Servlet:

import java.io.*;  
import java.util.*;   
import javax.servlet.ServletConfig;  
import javax.servlet.ServletException;  
import javax.servlet.http.HttpServlet;  
import javax.servlet.http.HttpServletRequest;  
import javax.servlet.http.HttpServletResponse;  

import entity.UserInfo;  
import service.UserInfoManage;  
import util.Page;  

public class UserBasicSearchServlet extends HttpServlet {  
    private static final long serialVersionUID = 1L;  
    private int hangCount = 0;  

    @Override  
    public void init(ServletConfig config) throws ServletException {  
        hangCount = Integer.parseInt(config.getInitParameter("hangCount"));  
    }  
    @Override  
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)  
            throws ServletException, IOException {  
        doPost(req, resp);  
    }  

    @Override  
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)  
            throws ServletException, IOException {  
        // 1.取得页面参数并构造参数对象  
        int pageNo = Integer.parseInt(req.getParameter("pageNo"));  
        String sex = req.getParameter("gender");  
        String home = req.getParameter("newlocation");  
        String colleage = req.getParameter("colleage");  
        String comingyear = req.getParameter("ComingYear");  

        UserInfo u = new UserInfo();  
        u.setSex(sex);  
        u.setHome(home);  
        u.setColleage(colleage);  
        u.setCy(comingyear);  

        // 2.调用业务逻辑取得结果集  
        UserInfoManage userInfoManage = new UserInfoManage();  
        PageModel<UserInfo> pagination = userInfoManage.userBasicSearch(u,  
                pageNo, pageSize);  
        List<UserInfo> userList = pagination.getList();  

        // 3.封装返回结果  
        StringBuffer resultXML = new StringBuffer();  
        try {  
            resultXML.append("<?xml version='1.0' encoding='gb18030'?>/n");  
            resultXML.append("<root>/n");  
            for (Iterator<UserInfo> iterator = userList.iterator(); iterator  
                    .hasNext();) {  
                UserInfo userInfo = iterator.next();  
                resultXML.append("<data>/n");  
                resultXML.append("/t<id>" + userInfo.getId() + "</id>/n");  
                resultXML.append("/t<truename>" + userInfo.getTruename()  
                        + "</ truename >/n");  
                resultXML.append("/t<sex>" + userInfo.getSex() + "</sex>/n");  
                resultXML.append("/t<home>" + userInfo.getHome() + "</home>/n");  
                resultXML.append("</data>/n");  
            }  
            resultXML.append("<pagination>/n");  
            resultXML.append("/t<total>" + pagination.getTotalPage()  
                    + "</total>/n");  
            resultXML.append("/t<start>" + pagination.getFirstPage()  
                    + "</start>/n");  
            resultXML.append("/t<end>" + pagination.getLastPage() + "</end>/n");  
            resultXML.append("/t<pageno>" + pagination.getPageNo()  
                    + "</pageno>/n");  
            resultXML.append("</pagination>/n");  

            resultXML.append("</root>/n");  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        writeResponse(req, resp, resultXML.toString());  

    }  

    public void writeResponse(HttpServletRequest request,  
            HttpServletResponse response, String result) throws IOException {  
        response.setContentType("text/xml");  
        response.setHeader("Cache-Control", "no-cache");  
        response.setHeader("Content-Type", "text/xml; charset=gb18030");  
        PrintWriter pw = response.getWriter();  
        pw.write(result);  
        pw.close();  
    }  
}

利用SSH实现：在dao层编写getSumCount()和List getStuByPage(Integer pageNo, Integer pageCount)2个方法：有2种方法：第一种：使用普通的分页方法

//实现获取总行数的方法
 import org.hibernate.HibernateException;
 import org.hibernate.Session;
 import org.hibernate.Query;
 private BaseUtil baseUtil;//需要生成setXX()和getXX()方法
 public Integer getSumCount() {
     //获取session
Session session = baseUtil.getHibernateTemplate().getSessionFactory().openSession();
//执行查询，调用查询结果唯一的uniqueResult()方法;
Object object =session.createQuery("select count(别名.id) from 类名 别名 ").uniqueResult();
//根据数据类型确定返回值，类型不对时需要强制转换
return Integer.valueOf(object.toString());
}
//实现分页的方法
public List<Entity> getEntityByPage(Integer pageNo, Integer pageCount) {
Session session = baseUtil.getHibernateTemplate().getSessionFactory().openSession();
Query query = session.createQuery("from 类名 别名 ");
query.setMaxResults(pageCount);
return query.setFirstResult((pageNo-1)*pageCount).list();
}

第二种：使用Spring框架提供的方法

import org.hibernate.HibernateException;
  import org.springframework.orm.hibernate3.HibernateCallback;
  import org.hibernate.Query;
  //实现获取总行数的方法
 public Integer getSumCount() {
Object obj = baseUtil.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session){
Query query = session.createQuery("select count(别名.id) from 类名 别名 ");
Object object = query.uniqueResult();
return object;
}
});
return Integer.valueOf(obj.toString());
}
  //实现分页的方法
  getEntityByPage(final Integer pageNo, final Integer pageCount)：方法内的变量必须是final
public List<Entity> getEntityByPage(final Integer pageNo, final Integer pageCount) {
List<Entity> entitys = baseUtil.getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session){
Query query = session.createQuery("from 类名 别名");
query.setMaxResults(pageCount);
query.setFirstResult((pageNo-1)*pageCount);
return query.list();
}
});
return entitys ;
}

在service层中：

在action层中：

//EntityAction.java
import service.EntityService;
import util.Page;
import com.opensymphony.xwork2.ActionSupport;
public class EntityAction extends ActionSupport {
private EntityService EntityService;//需要生成setXX()方法
private Page page;//当前页，需要生成setXX()和getXX()方法
private Integer pageNo=1;//当前页数，需要生成setXX()和getXX()方法
private Integer pageCount=2;//每页显示行数，需要生成setXX()和getXX()方法
@SuppressWarnings("unchecked")
@Override
public String execute() throws Exception {
page=entityService.getEntityByPage(pageNo, pageCount);
return SUCCESS;
}
 //EntityService.java
private Page page;//需要生成setXX()和getXX()方法
  private Integer pageNo=1；
  private Integer pageCount=1；
  public Page getEntityByPage(Integer pageNo, Integer pageCount) {
if(page==null){
page=new Page();
}
page.setHangCount(pageCount);
page.setSumCount(stuDao.getSumCount());
page.setPageNo(pageNo);
page.setEntitys(stuDao.getEntityByPage(page.getPageNo(), page.getHangCount()));
return page;
}

最后在jsp页面：

<div class=pager>
*   [首页](form表单action名?pageNo=1)
*   [上一页](form表单action名?pageNo=${page.pageNo-1})
*   [下一页](form表单action名?pageNo=${page.pageNo+1})
*   [末页](form表单action名?pageNo=${page.sumPage})
</div>

综上所述，分页功能可以基本实现。

android内涵段子项目-(架构第一期)

Android进阶之音视频技术

热门文章