数据库的分页方式

http://blog.youkuaiyun.com/yudaoai/article/details/4278030

一.逻辑分页
 
1.逻辑分页的第一种方式,利用ResultSet的滚动分页。步骤如下：
 a.根据条件sql查询数据库。
 b.得到ResultSet的结果集,由于ResultSet带有游标，因此可以使用其next()方法来指向下一条记录。
 c.利用next()方法，得到分页所需的结果集。

 这种分页方式依靠的是对结果集的算法来分页，因此通常被称为“逻辑分页”。

 代码如下：

/**
 * TestPageResultSetDAO.java
 *
 * Copyright 2008. All Rights Reserved.
 */
package  com.cosmow.pageresultset.dao;

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

import  com.cosmow.pageresultset.entity.Bars;

/**
 * TODO dao class TestPageResultSetDAO
 * 
 * Revision History
 * 
 * 2008-7-7,Cosmo,created it
 */
public   class  TestPageResultSetDAO  {

    private final String FIND_BARS_PAGE = "SELECT * FROM YYBARS ORDER BY id";

    /**
     * 提供JDBC连接方法，返回一个Connection的实例
     * 
     * @return
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        try {
            final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
            final String user = "store";
            final String password = "store_password";
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(url, user, password);
            return con;
        } catch (ClassNotFoundException e) {
            throw new SQLException(e.getMessage());
        }
    }

    /**
     * 逻辑分页方法一，该方法使用移位(rs.next)来进行
     * 
     * @param currentPage
     *            当前页
     * @param showRows
     *            一页显示的数据量
     */
    public List<Bars> pageListOne(int currentPage, int showRows) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Bars> resultList = new ArrayList<Bars>();
        try {
            con = getConnection();
            ps = con.prepareStatement(FIND_BARS_PAGE);
            rs = ps.executeQuery();
            // 过滤结果集的变量
            int skipBegin = (currentPage - 1) * showRows;
            int skipEnd = currentPage * showRows;
            // 翻页计数器
            int currentNum = 0;
            // 当返回结果集中有记录时
            while (rs.next()) {
                // 以下情况将保证在结果集中有记录时的应用
                if (currentNum >= skipBegin && currentNum < skipEnd) {
                    Bars bar = new Bars();
                    bar.setId(rs.getLong("id"));
                    bar.setName(rs.getString("name"));
                    bar.setType(rs.getInt("type"));
                    bar.setCreatorId(rs.getLong("creator_id"));
                    resultList.add(bar);
                    if (currentNum == skipEnd - 1)
                        break;
                }
                currentNum++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }
}

 

2.逻辑分页的第二种方式
利用Scrollable ResultSets(可滚动结果集合)来快速定位到某个游标所指定的记录行,所使用的是ResultSet的absolute()方法。
改进代码如下：

/**
 * TestPageResultSetDAO.java
 *
 * Copyright 2008. All Rights Reserved.
 */
package  com.cosmow.pageresultset.dao;

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

import  com.cosmow.pageresultset.entity.Bars;

/**
 * TODO dao class TestPageResultSetDAO
 * 
 * Revision History
 * 
 * 2008-7-7,Cosmo,created it
 */
public   class  TestPageResultSetDAO  {

    private final String FIND_BARS_PAGE = "SELECT * FROM YYBARS ORDER BY id";
    
    /**
     * 提供JDBC连接方法，返回一个Connection的实例
     * 
     * @return
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        try {
            final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
            final String user = "store";
            final String password = "store_password";
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(url, user, password);
            return con;
        } catch (ClassNotFoundException e) {
            throw new SQLException(e.getMessage());
        }
    }

    /**
     * 逻辑分页方法二,使用absolute()方法分页
     * 
     * @param currentPage
     *            当前页
     * @param showRows
     *            一页显示的数据量
     */
    public List<Bars> pageListTwo(int currentPage, int showRows) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Bars> resultList = new ArrayList<Bars>();
        try {
            con = getConnection();
            ps = con.prepareStatement(FIND_BARS_PAGE);
            rs = ps.executeQuery();

            // 过滤结果集的变量
            int skipBegin = (currentPage - 1) * showRows;
            int skipEnd = currentPage * showRows;
            // 利用rs.absolute进行定位
            if (!rs.absolute(skipBegin))
                return resultList;
            // 当返回结果集中有记录时
            while (rs.next()) {
                // 以下情况将保证在结果集中有记录时的应用
                if (skipBegin < skipEnd) {
                    Bars bar = new Bars();
                    bar.setId(rs.getLong("id"));
                    bar.setName(rs.getString("name"));
                    bar.setType(rs.getInt("type"));
                    bar.setCreatorId(rs.getLong("creator_id"));
                    resultList.add(bar);
                    if (skipBegin == skipEnd - 1)
                        break;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }
}

虽然和第一种方式区别不大，单效率比ResultSet滚动要好，但是absolute()方法并不是所有jdbc驱动都支持。
可用如下代码测试当前jdbc驱动是否支持可滚动结果集：

int  type  =  rs.getType();
if  (type  ==  ResultSet.TYPE_SCROLL_INSENSITIVE  ||  type  ==  ResultSet.TYPE_SCROLL_SENSITIVE)
    System.out.println( " Result set is scrollable " );
else
    System.out.println( " Result set is not scrollable " );

 

二.物理分页
利用数据库本身的一些特性来分页。即：利用了数据库对sql语法的优化，提高分页性能。
1.针对Oracle数据库
步骤如下：
a.根据所使用的数据库特性来组织sql进行分页。
b.每次跳转页面的sql查询都不相同。
 通用的sql分页方式,“限制行数结果集的倒序”分页，步骤如下：
 (1).取得符合条件的所有结果集中可以唯一标识的Key值(通常是主键),并正向排序。
 (2).利用数据库提供的特殊方法进行“最大结果集”的限制（在Oracle中使用rownum, sql server中使用top, mysql中使用limit...),
 该“最大结果集”指包含当前所处页的所有记录数，“最大结果集”应该只包含惟一的Key值。
 (3).对步骤(2)中的“最大结果集”进行逆序，并取得“显示当前页显示数量的结果集”，该结果集中只包含惟一的Key值。
 (4).通过步骤(3)中所取得的Key值取得显示数据，该显示数据就是当前页应该显示的数据。

代码如下：

/**
 * TestPageResultSetDAO.java
 *
 * Copyright 2008. All Rights Reserved.
 */
package  com.cosmow.pageresultset.dao;

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

import  com.cosmow.pageresultset.entity.Bars;

/**
 * TODO dao class TestPageResultSetDAO
 * 
 * Revision History
 * 
 * 2008-7-7,Cosmo,created it
 */
public   class  TestPageResultSetDAO  {

    //针对Oracle
    private final String FIND_BARS_ORACLE = "select b3.* from "
            + "(select b1.id from "
            + "(select b.id from yybars b where rownum <= ? order by b.id desc) b1 "
            + "where rownum <= ? order by b1.id desc) b2, "
            + "yybars b3 where b2.id = b3.id order by b2.id";
    
    /**
     * 提供JDBC连接方法，返回一个Connection的实例
     * 
     * @return
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        try {
            final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
            final String user = "store";
            final String password = "store_password";
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(url, user, password);
            return con;
        } catch (ClassNotFoundException e) {
            throw new SQLException(e.getMessage());
        }
    }

    /**
     * 物理分页方法一针对Oracle,使用sql语句的id查询来进行
     * 
     * @param currentPage
     *            当前页
     * @param showRows
     *            一页显示的数据量
     */
    public List<Bars> pageListThree(int currentPage, int showRows) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Bars> resultList = new ArrayList<Bars>();
        try {
            con = getConnection();
            ps = con.prepareStatement(FIND_BARS_ORACLE);
            //传入参数，第一个参数标示包含该页总共有几条数据
            ps.setInt(1, showRows * currentPage);
            //第二个参数标示将取得在第一个参数所指定的数据中应该显示的数据
            ps.setInt(2, showRows);
            rs = ps.executeQuery();
            // 当返回结果集中有记录时
            while (rs.next()) {
                Bars bar = new Bars();
                bar.setId(rs.getLong("id"));
                bar.setName(rs.getString("name"));
                bar.setType(rs.getInt("type"));
                bar.setCreatorId(rs.getLong("creator_id"));
                resultList.add(bar);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }
}

 

2.针对MySQL数据库
在MySQL数据库中offset关键字的意思是"越过"，而limit关键字的意思是“限制”，利用这两者结合可轻松分页。
(1)取得符合条件的结果集，包含全字段。
(2)利用offset关键字越过一段结果集(被越过的结果集就是"(当前页 - 1) * 一页显示数")。
(3)利用limit关键字限制取得一段结果集(被限制取得的结果集就是一页显示数)

代码如下：

/**
 * TestPageResultSetDAO.java
 *
 * Copyright 2008. All Rights Reserved.
 */
package  com.cosmow.pageresultset.dao;

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

import  com.cosmow.pageresultset.entity.Bars;

/**
 * TODO dao class TestPageResultSetDAO
 * 
 * Revision History
 * 
 * 2008-7-7,Cosmo,created it
 */
public   class  TestPageResultSetDAO  {
    
    private final String FIND_BARS_MYSQL = "select * from yybars order by id limit ? offset ?";
    

    /**
     * 提供JDBC连接方法，返回一个Connection的实例
     * 
     * @return
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        try {
            final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
            final String user = "store";
            final String password = "store_password";
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(url, user, password);
            return con;
        } catch (ClassNotFoundException e) {
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 物理分页方法二针对mysql,使用sql语句的limit和offset来进行分页
     * 
     * @param currentPage
     *            当前页
     * @param showRows
     *            一页显示的数据量
     */
    public List<Bars> pageListFour(int currentPage, int showRows) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Bars> resultList = new ArrayList<Bars>();
        try {
            con = getConnection();
            ps = con.prepareStatement(FIND_BARS_MYSQL);
            //传入参数，第一个参数表示显示几条记录(limit关键字的含义)
            ps.setInt(1, showRows);
            //第二个参数表示丢弃几条记录(offset关键字的含义)
            ps.setInt(2, showRows * (currentPage - 1));
            rs = ps.executeQuery();
            // 当返回结果集中有记录时
            while (rs.next()) {
                Bars bar = new Bars();
                bar.setId(rs.getLong("id"));
                bar.setName(rs.getString("name"));
                bar.setType(rs.getInt("type"));
                bar.setCreatorId(rs.getLong("creator_id"));
                resultList.add(bar);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }
}

 

分页结论：
1.物理分页速度上并不一定快于逻辑分页，逻辑分页速度上也并不一定快于物理分页。
2.物理分页总是优于逻辑分页：没有必要将属于数据库端的压力加诸到应用端来，就算速度上存在优势,
然而其它性能上的优点足以弥补这个缺点。
3.在分页工作前，有必要了解使用数据库本身的一些sql语句特点更好的分页。