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();
        }
    }

}