Apache Torque实现分页查询

Apache Torque实现分页查询

Torque的初始化参见Apache Torque连接多个数据库及其使用

1.分页查询工具类

import java.util.List;import org.apache.torque.TorqueException;import org.apache.torque.util.LargeSelectExtend;import com.workingdogs.village.Record;public class TorqueQueryByPageUtils {    private static LargeSelectExtend largeSelect;    /**     * 查询分页结果     * @param isFirstQuery 是否是第一次查询     * @param pageConstant     * @param countSql     * @param querySql     * @param dbName     * @return     */    @SuppressWarnings("unchecked")    public static List<Record> getResultsWithSql(boolean isFirstQuery, PageConstant pageConstant,             String countSql, String querySql, String dbName) {        List<Record> results = null;        try {            int pageSize = pageConstant.getPageSize() <= 0 ? 10 : pageConstant.getPageSize();            /**默认的读入内存的页数，数据读入内存，可以提高前台响应用户请求数据的速度，            但如果读入内存的数据太多，会占用服务器过多内存*/            int memoryPageLimit = pageConstant.getMemoryPageLimit() <= 0 ? 10 : pageConstant.getMemoryPageLimit();            if (isFirstQuery) {                /**使用标准的大查询器查询，标准的大查询器返回的List里都是具体的数据对象的集合*/                largeSelect = new LargeSelectExtend(countSql, querySql, pageSize,                        memoryPageLimit, dbName);                results = largeSelect.getPage(pageConstant.getPageIndex());                largeSelectExtendProperties();                /**数据库里的总页数*/                int realTotalPages = largeSelect.getRealTotalPages();                System.out.println("realTotalPages:"+realTotalPages);            } else {                /**数据库里的总页数*/                int realTotalPages = largeSelect.getRealTotalPages();                System.out.println("realTotalPages:"+realTotalPages);                if (!(pageConstant.getPageIndex() > realTotalPages)) {                    /**获取某页数的结果*/                    results = largeSelect.getPage(pageConstant.getPageIndex());                    largeSelectExtendProperties();                }            }        } catch (TorqueException e) {            e.printStackTrace();        }        return results;    }    @SuppressWarnings("unchecked")    private static void largeSelectExtendProperties() {        try {            /**当前页码数*/            int currentPageNumber = largeSelect.getCurrentPageNumber();            /**读入内存的页数*/            int memoryTotalPages = largeSelect.getMemoryTotalPages();            /**读入内存的总记录数*/            int memoryTotalRecords = largeSelect.getMemoryTotalRecords();            /**数据库实际总记录数*/            int realTotalCount = largeSelect.getRealTotalCount();            /**是否有上一页结果*/            boolean previousResultsAvailable = largeSelect.getPreviousResultsAvailable();            if (previousResultsAvailable) {                /**上一页数据结果*/                List<Record> previousResults = largeSelect.getPreviousResults();                System.out.println("previousResults:"+previousResults);            }            /**是否有下一页结果*/            boolean nextResultsAvailable = largeSelect.getNextResultsAvailable();            if (nextResultsAvailable) {                /**下一页数据结果*/                List<Record> nextResults = largeSelect.getNextResults();                System.out.println("nextResults:"+nextResults);            }            System.out.println("currentPageNumber:"+currentPageNumber);            System.out.println("memoryTotalPages:"+memoryTotalPages);            System.out.println("memoryTotalRecords:"+memoryTotalRecords);            System.out.println("realTotalCount:"+realTotalCount);            System.out.println("previousResultsAvailable:"+previousResultsAvailable);            System.out.println("nextResultsAvailable:"+nextResultsAvailable);        } catch (Exception e) {            e.printStackTrace();        }    }}

注：上述若能执行getPreviousResults()，说明已经翻页到上一页了，那么执行到getNextResultsAvailable()和getNextResults()就是按照上一页的结果进行的，反之亦然，此处只是显示largeSelectExtend的一些属性数据。

思考：上述getResultsWithSql()方法中间部分代码块改写为如下代码，打印出的realTotalPages一直是-1；将休眠的代码注释打开，realTotalPages能正常获取到数值。所以先进行一次页数的查询，就可以正常获取到realTotalPages的数值。

if (isFirstQuery) {    /**使用标准的大查询器查询，标准的大查询器返回的List里都是具体的数据对象的集合*/    largeSelect = new LargeSelectExtend(countSql, querySql, pageSize,            memoryPageLimit, dbName);}//try {//    Thread.sleep(1000);//} catch (InterruptedException e) {//    e.printStackTrace();//}/**数据库里的总页数*/int realTotalPages = largeSelect.getRealTotalPages();System.out.println("realTotalPages:"+realTotalPages);if (!(pageConstant.getPageIndex() > realTotalPages)) {    /**获取某页数的结果*/    results = largeSelect.getPage(pageConstant.getPageIndex());    largeSelectExtendProperties();}

public class PageConstant {    /**     * 页码     */    private int pageIndex;    /**     * 每页包含记录条数     */    private int pageSize;    /**     * 每次读入内存的页数     */    private int memoryPageLimit;    /**     * 已经读入到内存的页数     */    private int memoryTotalPages;    /**     * 下一页     */    private int nextPage;    /**     * 上一页     */    private int previousPage;    /**     * 数据库总记录数     */    private int totalCount;    /**     * 数据库总页数     */    private int totalPages;    /**     * 当前页     */    private int currentPage;    public int getPageIndex() {        return pageIndex;    }    public void setPageIndex(int pageIndex) {        this.pageIndex = pageIndex;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }    public int getMemoryPageLimit() {        return memoryPageLimit;    }    public void setMemoryPageLimit(int memoryPageLimit) {        this.memoryPageLimit = memoryPageLimit;    }    public int getMemoryTotalPages() {        return memoryTotalPages;    }    public void setMemoryTotalPages(int memoryTotalPages) {        this.memoryTotalPages = memoryTotalPages;    }    public int getNextPage() {        return nextPage;    }    public void setNextPage(int nextPage) {        this.nextPage = nextPage;    }    public int getPreviousPage() {        return previousPage;    }    public void setPreviousPage(int previousPage) {        this.previousPage = previousPage;    }    public int getTotalCount() {        return totalCount;    }    public void setTotalCount(int totalCount) {        this.totalCount = totalCount;    }    public int getTotalPages() {        return totalPages;    }    public void setTotalPages(int totalPages) {        this.totalPages = totalPages;    }    public int getCurrentPage() {        return currentPage;    }    public void setCurrentPage(int currentPage) {        this.currentPage = currentPage;    }    @Override    public String toString() {        return "PageConstant [pageIndex=" + pageIndex + ", pageSize=" + pageSize + ", memoryPageLimit=" + memoryPageLimit                + ", memoryTotalPages=" + memoryTotalPages + ", nextPage=" + nextPage + ", previousPage=" + previousPage                + ", totalCount=" + totalCount + ", totalPages=" + totalPages + ", currentPage=" + currentPage + "]";    }}

2.测试类

import java.util.Iterator;import java.util.List;import org.apache.torque.Torque;import com.ideal.server.InitServer;import com.workingdogs.village.Record;public class Test {    public static void main(String[] args) {        InitServer.init();        try {            PageConstant pageConstant = new PageConstant();            pageConstant.setPageSize(10);            pageConstant.setMemoryPageLimit(1);            boolean isWhile = true;            List<Record> results = null;            int index = 1;            String countSql = "select count(1) from user where status = 'A'";            String querySql = "select * from user where status = 'A'";            String dbName = Torque.getDefaultDB();            while (isWhile) {                pageConstant.setPageIndex(index);                if (1 == index) {                    results = TorqueQueryByPageUtils.getResultsWithSql(true, pageConstant, countSql, querySql, dbName);                } else {                    results = TorqueQueryByPageUtils.getResultsWithSql(false, pageConstant, countSql, querySql, dbName);                }                if (null == results || results.size() <= 0) {                    isWhile = false;                    break;                } else {                    Iterator<Record> it = results.iterator();                    while (it.hasNext()) {                        Record rc = (Record) it.next();                        //columnName                        String name= rc.getValue("name").toString();                        String password= rc.getValue("password").toString();                        System.out.println(name+":"+password);                    }                }                index = index + 1;            }         } catch (Exception e) {            e.printStackTrace();        }    }}