DB2分页存储过程

CREATE PROCEDURE INTIER_COMMONALITY_PAGE_SELECT_LIST_03 ( IN ISIZESQL VARCHAR(2000), -- 查询总数量的方法 IN IWITH VARCHAR(3000), -- 递归语句 IN ISQL VARCHAR(3000), -- 查询的SQL语句 IN IPAGESIZE INTEGER, -- 每页要显示的大小,如果为0,默认为20 INOUT IOCURRENTPAGEIX INTEGER, -- 输入和输出：当前页 OUT OPAGESTARTROW INTEGER, -- 输出：当前开始行 OUT OPAGEENDROW INTEGER, -- 输出：当前结束行 OUT OTOTALROWS INTEGER, -- 输出：当前总记录数 OUT OHASPREVIOUSPAGE INTEGER, -- 输出：是否有上一页 OUT OHASNEXTPAGE INTEGER, -- 输出：是否有下一页 OUT OTOTALPAGES INTEGER, -- 输出：总页数 OUT OERROR VARCHAR(9000) -- 输出：错误信息 ) RESULT SETS 1 MODIFIES SQL DATA NOT DETERMINISTIC LANGUAGE SQL P1: BEGIN ------------------------------------------------------------------------ -- SQL 存储过程 ------------------------------------------------------------------------ DECLARE STRSQL VARCHAR(6000); -- 声明游标 DECLARE RESULT CURSOR WITH RETURN TO CALLER FOR S2; DECLARE EXIT HANDLER FOR SQLEXCEPTION-- 异常捕获 BEGIN SET OERROR = 'ERROR:' || STRSQL; END; SET STRSQL = ISIZESQL; PREPARE S2 FROM STRSQL; -- 游标对调用者保持打开 OPEN RESULT; -- 当前总记录数 FETCH RESULT INTO OTOTALROWS; -- 关闭结果集 CLOSE RESULT; -- 如果每页显示大小参数为NULL时,把每页大小设置为0 SET IPAGESIZE = COALESCE(IPAGESIZE, 0); -- 如果每页显示大小为0时，设置每页显示大小为20 IF(IPAGESIZE = 0) THEN SET IPAGESIZE = 20; END IF; -- 设置总页数 SET OTOTALPAGES = (OTOTALROWS - 1) / IPAGESIZE + 1; -- 如果当前页参数为NULL时,把当前页设置为0 SET IOCURRENTPAGEIX = COALESCE(IOCURRENTPAGEIX, 0); -- 设置当前页 IF(IOCURRENTPAGEIX < 1) THEN SET IOCURRENTPAGEIX = 1; ELSE IF(IOCURRENTPAGEIX > OTOTALPAGES) THEN SET IOCURRENTPAGEIX = OTOTALPAGES; END IF; END IF; -- 设置当前开始数 SET OPAGESTARTROW = IPAGESIZE * (IOCURRENTPAGEIX -1) + 1; -- 每页结束数 IF(IOCURRENTPAGEIX = OTOTALPAGES) THEN SET OPAGEENDROW = OTOTALROWS; ELSE SET OPAGEENDROW = IPAGESIZE * IOCURRENTPAGEIX; END IF; -- 是否有上一页 IF(IOCURRENTPAGEIX > 1) THEN SET OHASPREVIOUSPAGE = 1; ELSE SET OHASPREVIOUSPAGE = 0; END IF; -- 是否有下一页 IF(IOCURRENTPAGEIX < OTOTALPAGES) THEN SET OHASNEXTPAGE = 1; ELSE SET OHASNEXTPAGE = 0; END IF; SET STRSQL = IWITH || ' SELECT * FROM (' || ISQL || ') AS TEMP WHERE ROWNUM BETWEEN ' || RTRIM(CHAR(OPAGESTARTROW)) || ' AND ' || RTRIM(CHAR(OPAGEENDROW)); set OERROR = STRSQL; PREPARE S2 FROM STRSQL; -- 游标对调用者保持打开 OPEN RESULT; END P1

 

JAVA调用分页存存储过程方法如下：

/** * 数据分页的方法 * * @param fieldNames 要查询的字段名 * @param sizeSql 查询的数据的总条数的语句 * @param withSql with格式查询的语句 * @param sql 在调用分页存储过程之前，SQL语句必需按以下格式定义:<br/> *     <b><font color='red'>SELECT * ROWNUMBER() OVER(ORDER BY 要排序的字段和排序规则) AS ROWNUM,</font></b><br/> * 后面加要传入的SQL参数 * @param pageSize 每页大小 * @param currentPageIx 当前页 * @return JSONObject对象 * @throws GenericDataSourceException 抛出GenericDataSource异常 */ public JSONObject getJSONObject(String[] fieldNames, String sizeSql, String withSql, String sql, int pageSize, int currentPageIx) throws GenericDataSourceException { JSONObject json = new JSONObject(); SQLProcessor sqlP = new SQLProcessor(PageHelper.HELPER_NAME); List<Map<String, Object>> result = FastList.newInstance(); try { // 获取连接对象 Connection conn = sqlP.getConnection(); // 调用存储过程 CallableStatement cs = conn.prepareCall("{ call INTIER_COMMONALITY_PAGE_SELECT_LIST_03(?,?,?,?,?,?,?,?,?,?,?,?) }"); cs.setString(1, sizeSql); cs.setString(2, withSql); cs.setString(3, sql); cs.setInt(4, pageSize); cs.setInt(5, currentPageIx); cs.registerOutParameter(5, Types.INTEGER); cs.registerOutParameter(6, Types.INTEGER); cs.registerOutParameter(7, Types.INTEGER); cs.registerOutParameter(8, Types.INTEGER); cs.registerOutParameter(9, Types.INTEGER); cs.registerOutParameter(10, Types.INTEGER); cs.registerOutParameter(11, Types.INTEGER); cs.registerOutParameter(12, Types.VARCHAR); cs.execute(); String return_message = cs.getString(12); System.err.println(return_message); int totalCount = cs.getInt(8); if (return_message != null && !return_message.startsWith("ERROR:")) { Debug.log("(method:getJSONObject) -> call procedure success. message for:" + return_message, module); ResultSet rs = cs.getResultSet(); while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (String name : fieldNames) { map.put(name, rs.getString(convent(name))); } result.add(map); } if (rs != null) { rs.close(); } if (UtilValidate.isNotEmpty(result)) { json = JSONHelper.getInstance().getJSONObject(fieldNames, result, "CommonEntityUiLabels", totalCount); } else { json.put("data", new JSONArray()); } } else { json.put("data", new JSONArray()); Debug.logError("(method:getJSONObject) -> call procedure error.message for:" + return_message, module); } } catch (SQLException e) { e.printStackTrace(); } catch (GenericEntityException e) { e.printStackTrace(); } finally { if (sqlP != null) { sqlP.close(); } } return json; }