java操作oracle分页过程

java操作oracle分页过程

因为在做一个项目中存在大量的分页操作，每一个表都要写一段分页的SQL，所以萌生出自己写一个通用的过程的方法，因为本人刚入门，所以有漏洞的话请帮忙指出啦！

oracle分页SQL

SELECT * FROM 
	(SELECT childTable.*,ROWNUM childRowNum FROM(SELECT * FROM demo ORDER BY id) 
	childTable WHERE ROWNUM<=(pageIndex*pageSize)) 
WHERE childRowNum>(pageIndex - 1)*pageSize;

oracle通用分页SQL

使用时传入 表名、每页显示的数据、显示的页码即可获得分页后的集合

CREATE OR REPLACE PROCEDURE xcsp_PAGING( 
    tableName IN VARCHAR2,
    pageSize  IN INT,
    pageIndex IN INT,
    out_list  OUT SYS_REFCURSOR)  
IS
   pagingSql VARCHAR2(2000);
BEGIN
    pagingSql := 'SELECT * FROM 
    (SELECT childTable.*,ROWNUM childRowNum FROM(SELECT * FROM '||tableName||' ORDER BY id) childTable WHERE ROWNUM<=('||pageindex||' * '||pageSize||')) 
     WHERE childRowNum>('||pageindex||' - 1)*'||pageSize||'';
    OPEN  out_list FOR pagingSql;
END;

java操作oracle过程代码

@Test
public void test1() { 
    Connection connection = DBUtils.getConnection();
    CallableStatement callableStatement = null;
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;
        try {
        callableStatement = connection.prepareCall("{call xcsp_PAGING(?,?,?,?)}");
        //这里的demo指的是数据库中的表名
        callableStatement.setString(1, "demo");
        callableStatement.setInt(2, 10);
        callableStatement.setInt(3, 4);
        callableStatement.registerOutParameter(4, OracleTypes.CURSOR);
        callableStatement.execute();
        List<Map> list = new ArrayList<Map>();
        rs = (ResultSet) callableStatement.getObject(4);//拿取存储过程返回的集合
        while (rs.next()) {
            Map map = new HashMap();
            map.put("id", rs.getInt("id"));
            map.put("name", rs.getString("name"));
            map.put("sex", rs.getString("sex"));
            list.add(map);
            }
            System.out.println(list.size());
        } catch (SQLException e) {
        e.printStackTrace();
    } 
}