Dbutils + C3P0实现oracle分页

很少写文章开始之前，先大致说下文章的内容。。

本文采用了Dbutils 和C3P0 连接池实现数据库表的简单分页。

Dbutils 是Apache 对JDBC的封装，支持对数据库表的CRUD操作，C3P0 是一种通用的连接池，管理数据库连接资源。

具体的请谷歌或百度。。

OK，进入正题。。

1、开发依赖的jar包：c3p0-0.9.1.2、commons-dbutils-1.6.jar、ojdbc6.jar

下载jar包，点此出！

2、实现数据库分页，还要准备一张数据库表，这里找个数据量只有7条数据的小表

3、打开eclipse创建个java工程，如下。。导入c3p0-0.9.1.2、commons-dbutils-1.6.jar、ojdbc6.jar

4、代码：

OfferType.java 实体bean 定义 和数据库表字段保持一致，不区分大小写。

package com.test;

/**
 * <Description> <br>
 * 
 * @author <br>
 * @version 1.0<br>
 * @taskId <br>
 * @CreateDate 2016-10-12 <br>
 * @since<br>
 * @see com.test <br>
 */
public class OfferType {
    private int offer_Type;

    private String offer_Type_Name;

    private String comments;

    public String getComments() {
        return comments;
    }

    public void setComments(String comments) {
        this.comments = comments;
    }

    public int getOffer_Type() {
        return offer_Type;
    }

    public void setOffer_Type(int offer_Type) {
        this.offer_Type = offer_Type;
    }

    public String getOffer_Type_Name() {
        return offer_Type_Name;
    }

    public void setOffer_Type_Name(String offer_Type_Name) {
        this.offer_Type_Name = offer_Type_Name;
    }

    @Override
    public String toString() {
        return "OfferType [offer_Type=" + offer_Type + ", offer_Type_Name=" + offer_Type_Name + ", comments="
            + comments + "]";
    }
}

Page.java

package com.test;

public class Page {

    // 总条数
    private int totalSize;

    // 每页大小
    private int pageSize;

    // 总页数
    private int totalPage;

    // 第几页
    private int pageNum = 1;

    // 每页开始条数
    private int pageBegin;

    // 每页结束条数
    private int pageEnd;

    public Page(int pageSize) {
        this.pageSize = pageSize;
    }

    public Page() {
    }

    public void init() {
        // pageSize 默认为5
        if (pageSize <= 0) {
            pageSize = 5;
        }

        totalPage = totalSize / pageSize;

        if (0 != totalSize % pageSize) {
            totalPage += 1;
        }

        if (pageNum > totalPage) {
            pageNum = totalPage;
        }
        if (pageNum < 1) {
            pageNum = 1;
        }
        pageBegin = (pageNum - 1) * pageSize + 1;
        pageEnd = (pageNum) * pageSize;
    }

    public int getTotalSize() {
        return totalSize;
    }

    public void setTotalSize(int totalSize) {
        this.totalSize = totalSize;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageBegin() {
        return pageBegin;
    }

    public void setPageBegin(int pageBegin) {
        this.pageBegin = pageBegin;
    }

    public int getPageEnd() {
        return pageEnd;
    }

    public void setPageEnd(int pageEnd) {
        this.pageEnd = pageEnd;
    }

    @Override
    public String toString() {
        return "Page [totalSize=" + totalSize + ", pageSize=" + pageSize + ", totalPage=" + totalPage + ", pageNum="
            + pageNum + ", pageBegin=" + pageBegin + ", pageEnd=" + pageEnd + "]";
    }
}

QueryRemote.java  定义分页查询方法

package com.test;

import java.beans.PropertyVetoException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class QueryRemote {

    // 获得c3p0连接池对象
    private static ComboPooledDataSource ds;

    @SuppressWarnings({
        "unchecked", "rawtypes"
    })
    public static List<Object> query(String sql, Page page, Object[] params, Class<?> clss) {
        ds = getResource();
        QueryRunner runner = new QueryRunner(ds);
     
        List<Object> list = null;
        try {
            sql = sql.toUpperCase();
            if (null == page) {
                list = runner.query(sql, new BeanListHandler(clss.newInstance().getClass()), params);
            }
            else {
                if (null == params) {
                    params = new Object[]{};
                }
                String pageSql = "SELECT * FROM (SELECT A.*,ROWNUM RN " + "FROM (" + sql + ") A WHERE ROWNUM <=?"
                    + ") " + "WHERE RN >=?";

                // 查询总数
                String totalSql = "SELECT COUNT(*) FROM (" + sql + ")";
                BigDecimal count = runner.query(totalSql,new ScalarHandler<BigDecimal>(),params);
                page.setTotalSize(count.intValue());
                page.init();
                
                Object[] pageParams = new Object[params.length + 2];
                System.arraycopy(params, 0, pageParams, 0, params.length);
                pageParams[params.length] = page.getPageEnd();
                pageParams[params.length + 1] = page.getPageBegin();
                list = runner.query(pageSql, new BeanListHandler(clss.newInstance().getClass()), pageParams);
            }
        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    private static ComboPooledDataSource getResource() {
        if (null == ds) {
            try {
                ds = new ComboPooledDataSource();
                ds.setUser("xxxx");
                ds.setPassword("xxxx");
                ds.setJdbcUrl("jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:xx");
                ds.setDriverClass("oracle.jdbc.driver.OracleDriver");
            }
            catch (PropertyVetoException e) {
                // 获取数据库连接是失败
                e.printStackTrace();
            }
        }
        return ds;
    }
}

Test.java 测试类

</pre><pre name="code" class="java">package com.test;

import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        Page page = new Page();
        page.setPageNum(1);
        page.setPageSize(4);
        Object[] params = new Object[]{1} ;
        
        List<OfferType> offerTypes = new ArrayList<OfferType>();
        String sql = "select * from offer_type where offer_type > ?";
        List<Object> list = QueryRemote.query(sql, page, params, OfferType.class);
        
        OfferType offerType = null;
        for (int i = 0; i < list.size(); i++) {
            offerType = new OfferType();
            offerType = (OfferType) list.get(i);
            System.out.println(offerType.toString());
            offerTypes.add(offerType);
        }
        
        System.out.println();
        System.out.println("共"+page.getTotalSize()+"条记录，每页大小"+page.getPageSize()+"，当前第"+page.getPageNum()+"页，查询到 "+list.size()+" 条记录。。。");
    }
}

运行结果！！！  这里只有6条记录 是因为 sql 有查询条件offer_type > ?，Object[] params = new Object[]{1} ;传了参1，offer_type > 1的只有六条。。

结束。。。。