java调用oracle分页存储过程示例

1.分页类

package org.zh.basic;

/**
 * 页面类
 *
 * @author keven
 *
 */
public class pageinfo {

    // 定义
    private string p_tablename; // -表名
    private string p_strwhere; // --查询条件
    private string p_ordercolumn; // --排序的列
    private string p_orderstyle; // --排序方式
    private int p_curpage; // --当前页
    private int p_pagesize; // --每页显示记录条数
    private int p_totalrecords; // --总记录数
    private int p_totalpages; // --总页数

    // / <summary>
    // / 定义函数
    // / </summary>
    public pageinfo() {
    }

    public pageinfo(string p_tablename, string p_strwhere,
            string p_ordercolumn, string p_orderstyle, int p_curpage,
            int p_pagesize, int p_totalrecords, int p_totalpages) {
        this.p_tablename = p_tablename;
        this.p_strwhere = p_strwhere;
        this.p_ordercolumn = p_ordercolumn;
        this.p_orderstyle = p_orderstyle;
        this.p_curpage = p_curpage;
        this.p_pagesize = p_pagesize;
        this.p_totalrecords = p_totalrecords;
        this.p_totalpages = p_totalpages;
    }

    public string getp_tablename() {
        return p_tablename;
    }

    public void setp_tablename(string ptablename) {
        p_tablename = ptablename;
    }

    public string getp_strwhere() {
        return p_strwhere;
    }

    public void setp_strwhere(string pstrwhere) {
        p_strwhere = pstrwhere;
    }

    public string getp_ordercolumn() {
        return p_ordercolumn;
    }

    public void setp_ordercolumn(string pordercolumn) {
        p_ordercolumn = pordercolumn;
    }

    public string getp_orderstyle() {
        return p_orderstyle;
    }

    public void setp_orderstyle(string porderstyle) {
        p_orderstyle = porderstyle;
    }

    public int getp_curpage() {
        return p_curpage;
    }

    public void setp_curpage(int pcurpage) {
        p_curpage = pcurpage;
    }

    public int getp_pagesize() {
        return p_pagesize;
    }

    public void setp_pagesize(int ppagesize) {
        p_pagesize = ppagesize;
    }

    public int getp_totalrecords() {
        return p_totalrecords;
    }

    public void setp_totalrecords(int ptotalrecords) {
        p_totalrecords = ptotalrecords;
    }

    public int getp_totalpages() {
        return p_totalpages;
    }

    public void setp_totalpages(int ptotalpages) {
        p_totalpages = ptotalpages;
    }

}

2 调用

package org.zh.sys.server;

import java.sql.callablestatement;
import java.sql.connection;
import java.sql.resultset;
import java.sql.resultsetmetadata;
import java.sql.sqlexception;
import java.util.arraylist;
import java.util.map;
import java.util.hashmap;
import org.hibernate.session;
import org.zh.basic.pageinfo;
import org.zh.dao.hibernatesessionfactory;
import oracle.jdbc.driver.oracletypes;
import oracle.jdbc.oraclecallablestatement;

public class generatepage {

    public generatepage() {

    }

    public static arraylist prc_page(pageinfo page) {
        arraylist list = new arraylist();
        map mp;
        session s = null;
        connection conn = null;
        resultset rs = null;
        callablestatement proc = null;
        try {
            s = hibernatesessionfactory.getsession();
            conn = s.connection();
            proc = conn.preparecall("{call pages.prc_page(?,?,?,?,?,?,?,?,?)}");
            proc.setstring(1, page.getp_tablename());
            proc.setstring(2, page.getp_strwhere());
            proc.setstring(3, page.getp_ordercolumn());
            proc.setstring(4, page.getp_orderstyle());
            proc.setint(5, page.getp_curpage());
            proc.setint(6, page.getp_pagesize());
            proc.registeroutparameter(7, oracletypes.number);
            proc.registeroutparameter(8, oracletypes.number);
            proc.registeroutparameter(9, oracletypes.cursor);
            proc.execute();
            // page.setp_totalrecords(proc.getint("p_totalrecords"));
            // page.setp_totalpages(proc.getint("p_totalpages"));
            // list = (arraylist) proc.getobject("v_cur");
            page.setp_totalrecords(proc.getint(7));
            page.setp_totalpages(proc.getint(8));
            rs = ((oraclecallablestatement) proc).getcursor(9); // 得到输出结果集参数
            resultsetmetadata rsmd = rs.getmetadata();
            int numberofcolumns = rsmd.getcolumncount();
            while (rs.next()) {
                mp = new hashmap(numberofcolumns);
                for (int r = 1; r < numberofcolumns; r++) {
                    mp.put(rsmd.getcolumnname(r), rs.getobject(r));
                }
                list.add(mp);
            }
            return list;
        } catch (sqlexception ex) {
            ex.printstacktrace();
            return list;
        } catch (exception ex2) {
            ex2.printstacktrace();
            return list;
        } finally {
            try {
                if (proc != null) {
                    proc.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (sqlexception ex1) {
                ex1.printstacktrace();
            }
        }
    }
}