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();
			// 1
			String totalCountStr = proc.getString(1);
			totalCount = Integer.parseInt(totalCountStr);
			inPage.setTotalCount(totalCount);
			// 2
			if (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;