javaEE jdbc获取数据3（页面获取数据——存储过程）

3 jdbc获取数据3（页面获取数据——存储过程）

package aTest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import org.hibernate.HibernateException;import com.ImpStr;/** * 通用sql使用 */public class ImpSqlPage {private static String clssName = "oracle.jdbc.driver.OracleDriver";private static String url = "jdbc:oracle:thin:@*********:1521:orcl";private static String user = "*****";private static String password = "12345678";private static ArrayList<DataStr> getDataStr(ResultSet rs,int numberOfColumns) {ArrayList<DataStr> reslutList = new ArrayList<DataStr>();try {int rownum = 1;while (rs.next()) {DataStr datas = new DataStr();ArrayList strsList = new ArrayList();for (int i = 1; i <= numberOfColumns; i++) {Object colVObject = rs.getObject(i);String colValue = ImpStr.getStrFromObject(colVObject);strsList.add(colValue);}datas.setRownum(rownum);datas.setStrsList(strsList);reslutList.add(datas);rownum++;}} catch (Exception e) {e.printStackTrace();}return reslutList;}/** * inPage.getInSqlSel() != '*' * @param inPage */public static void setPage(ImpPage inPage) {Connection Conn = null;Statement Stmt = null;ResultSet rs = null;int totalCount = 0;String sql = inPage.getInSqlSel() + inPage.getInSqlFromWherel()+inPage.getInSqlOrder();try {Class.forName(clssName);Conn = DriverManager.getConnection(url, user, password);CallableStatement proc = null;proc = Conn.prepareCall("{ call PACK_TEST.TESTB5(?,?,?,?,?,?,?) }");proc.setString(3, inPage.getInSqlSel());proc.setString(4, inPage.getInSqlFromWherel());proc.setString(5, inPage.getInSqlOrder());proc.setInt(6, inPage.getInSqlPageNo());proc.setInt(7, inPage.getInSqlPageSize());proc.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);proc.execute();// 1String totalCountStr = proc.getString(1);totalCount = Integer.parseInt(totalCountStr);inPage.setTotalCount(totalCount);// 2if (totalCount > 0) {rs = (ResultSet) proc.getObject(2);ResultSetMetaData RsMeta = rs.getMetaData();int numberOfColumns = RsMeta.getColumnCount();// 字段个数ArrayList<DataStr> dataList = getDataStr(rs, numberOfColumns);inPage.setContents(dataList);}} catch (HibernateException e) {System.out.println("HibernateException====" + sql);e.printStackTrace();} catch (SQLException e) {System.out.println("SQLException====" + sql);e.printStackTrace();} catch (ClassNotFoundException e) {System.out.println("ClassNotFoundException====" + sql);e.printStackTrace();} finally{ if (Stmt != null) { try {Stmt.close();} catch (Exception e) {};} if (Conn != null) { try {Conn.close();} catch (Exception e) {};}}Integer pageSize = inPage.getInSqlPageSize();Integer pageNo = inPage.getInSqlPageNo();Integer pageCount = totalCount / pageSize;if (totalCount % pageSize > 0 && pageNo > pageCount+1) {pageNo = pageCount + 1;}}public static void main(String[] args) {ResultSet rs = null;CallableStatement proc;int totalCount = 0;ImpPage inPage = new ImpPage();String sqlOrderBy = "order by t.userid DESC";String inSqlSel = "select * ";String inSqlFromWherel = " from t_sys_user t";inPage.setInSqlSel(inSqlSel);inPage.setInSqlFromWherel(inSqlFromWherel);inPage.setInSqlOrder(sqlOrderBy);inPage.setInSqlPageNo(3);inPage.setInSqlPageSize(10);setPage(inPage);ArrayList<DataStr> reslutLis = inPage.getContents();    for (int i = 0; i < reslutLis.size(); i++){    DataStr data = reslutLis.get(i);        ArrayList strs = data.getStrsList();        System.out.println("data.getRownum()=="+data.getRownum() + "  strs[i]=="+strs.get(0).toString());    }}}

3.2 ImpPage.java

package aTest;import java.util.ArrayList;public class ImpPage {private String outRownum;private String outPageNo;private String inSqlSel;private String inSqlFromWherel;private String inSqlOrder;private int inSqlPageNo;private int inSqlPageSize;private int totalCount;private ArrayList contents;public String getOutRownum() {return outRownum;}public void setOutRownum(String outRownum) {this.outRownum = outRownum;}public String getOutPageNo() {return outPageNo;}public void setOutPageNo(String outPageNo) {this.outPageNo = outPageNo;}public String getInSqlSel() {return inSqlSel;}public void setInSqlSel(String inSqlSel) {this.inSqlSel = inSqlSel;}public String getInSqlFromWherel() {return inSqlFromWherel;}public void setInSqlFromWherel(String inSqlFromWherel) {this.inSqlFromWherel = inSqlFromWherel;}public String getInSqlOrder() {return inSqlOrder;}public void setInSqlOrder(String inSqlOrder) {this.inSqlOrder = inSqlOrder;}public int getInSqlPageNo() {return inSqlPageNo;}public void setInSqlPageNo(int inSqlPageNo) {this.inSqlPageNo = inSqlPageNo;}public int getInSqlPageSize() {return inSqlPageSize;}public void setInSqlPageSize(int inSqlPageSize) {this.inSqlPageSize = inSqlPageSize;}public int getTotalCount() {return totalCount;}public void setTotalCount(int totalCount) {this.totalCount = totalCount;}public ArrayList getContents() {return contents;}public void setContents(ArrayList contents) {this.contents = contents;}}

3.3存储过程

  --5    PROCEDURE TESTB5( O_ROWNUM OUT NUMBER, O_CUR OUT REFCURSORTYPE                    , I_SQL_SEL IN VARCHAR2, I_SQL_FROM_WHERE IN VARCHAR2, I_SQL_ORDER IN VARCHAR2                    , I_PAGE_NO IN NUMBER, I_PAGE_SIZE IN NUMBER) AS                          m_sql_count          varchar2(2000);    m_sql_context        varchar2(2000);    m_sql_order          varchar2(2000);        m_count              number;  BEGIN        --1     m_sql_count := 'select count(*) '||I_SQL_FROM_WHERE;    EXECUTE IMMEDIATE m_sql_count INTO O_ROWNUM;        --2    if I_SQL_ORDER is null or I_SQL_ORDER = '' then      m_sql_order := 'ORDER BY null';    else      m_sql_order := I_SQL_ORDER;    end if;            --3    m_sql_context := 'select * from (select * from ( '||I_SQL_SEL||' , row_number() OVER(ORDER BY null) AS row_number'||I_SQL_FROM_WHERE                  ||') p where p.row_number > ('||I_PAGE_NO||'-1)*10) q where rownum <= '||I_PAGE_SIZE;    DBMS_OUTPUT.PUT_LINE(m_sql_context);        --4 OPEN mycur(v_WHERE);    OPEN O_CUR FOR m_sql_context;        END TESTB5;