[c-sharp]
view plaincopyCREATE 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调用分页存存储过程方法如下：
[java]
view plaincopy/**
* 数据分页的方法
*
* @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;
}
