oracle 10gR2
Spring JDBCTemplate
JDK 1.6.0_10
1. 定义返回结果集的游标
CREATE OR REPLACE PACKAGE PKG_QUERY_PAGE AS
TYPE CUR_QUERY_PAGE IS REF CURSOR;
END PKG_QUERY_PAGE;
/
2. 存储过程
CREATE OR REPLACE PROCEDURE PRC_QUERY_PAGE(P_TABLENAME IN VARCHAR2, --表名
P_STRWHERE IN VARCHAR2, --查询条件
P_ORDERCOLUMN IN VARCHAR2, --排序的列
P_ORDERSTYLE IN VARCHAR2, -- 排序类型,ASC或DSC
P_CURPAGE IN OUT NUMBER, -- 当前第几页
P_PAGESIZE IN OUT NUMBER, -- 页面大小
P_TOTALRECORDS OUT NUMBER, -- 总记录数
P_TOTALPAGES OUT NUMBER, -- 总页数
V_CUR OUT PKG_QUERY_PAGE.CUR_QUERY_PAGE
/* 返回查询到的数据信息*/) IS
V_SQL VARCHAR2(1000) := ''; -- sql语句
V_STARTRECORD NUMBER(4); -- 起始记录数
V_ENDRECORD NUMBER(4); -- 结束记录数
BEGIN
-- 查询记录数
V_SQL := 'SELECT TO_Number(count(1)) from ' || P_TABLENAME ||
' where 1=1 ';
IF P_STRWHERE IS NOT NULL THEN
V_SQL := V_SQL || P_STRWHERE;
END IF;
EXECUTE IMMEDIATE V_SQL
INTO P_TOTALRECORDS;
--验证页面记录大小
IF P_PAGESIZE < 0 THEN
P_PAGESIZE := 0;
END IF;
--根据页大小计算总页数
IF MOD(P_TOTALRECORDS, P_PAGESIZE) = 0 THEN
P_TOTALPAGES := TRUNC(P_TOTALRECORDS / P_PAGESIZE, 0);
ELSE
P_TOTALPAGES := TRUNC(P_TOTALRECORDS / P_PAGESIZE, 0) + 1;
END IF;
--验证页号
IF P_CURPAGE < 1 THEN
P_CURPAGE := 1;
END IF;
IF P_CURPAGE > P_TOTALPAGES THEN
P_CURPAGE := P_TOTALPAGES;
END IF;
--实现分页查询
V_STARTRECORD := (P_CURPAGE - 1) * P_PAGESIZE + 1;
V_ENDRECORD := P_CURPAGE * P_PAGESIZE;
V_SQL := 'SELECT * FROM (SELECT A.*, rownum r FROM ' || '(SELECT * FROM ' ||
P_TABLENAME;
IF P_STRWHERE IS NOT NULL OR P_STRWHERE <> '' THEN
V_SQL := V_SQL || ' WHERE 1=1 ' || P_STRWHERE;
END IF;
IF P_ORDERCOLUMN IS NOT NULL OR P_ORDERCOLUMN <> '' THEN
V_SQL := V_SQL || ' ORDER BY ' || P_ORDERCOLUMN || ' ' || P_ORDERSTYLE;
END IF;
V_SQL := V_SQL || ') A WHERE rownum <= ' || V_ENDRECORD ||
') B WHERE r >= ' || V_STARTRECORD;
DBMS_OUTPUT.PUT_LINE(V_SQL);
OPEN V_CUR FOR V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('query page fail:' || SQLERRM);
END PRC_QUERY_PAGE;
/
3. dao层代码
/**
* @Title: findUserByPage_proc
* @deprecated: 调用存储过程进行分页查询
* @param tableName
* 表名称
* @param where
* 查询条件(如: and ename = 'scott')
* @param orderCol
* 排序的列
* @param orderStyle
* 排序类型,ASC或DSC
* @param curPage
* 当前第几页
* @param pageSize
* 每页记录数
* @return PageResult
* @throws Exception
* @author
* @date 2013年10月18日
*/
public PageResult findByPage_proc(final String tableName,
final String where, final String orderCol, final String orderStyle,
final int curPage, final int pageSize) throws Exception {
final PageResult page = new PageResult();
getJdbcTemplate().execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(
final Connection con) throws SQLException {
String proc = "{CALL PRC_QUERY_PAGE(?,?,?,?,?,?,?,?,?)}";
CallableStatement cs = con.prepareCall(proc);
// 设置入参
cs.setString(1, tableName);
cs.setString(2, where);
cs.setString(3, orderCol);
cs.setString(4, orderStyle);
cs.setInt(5, curPage);
cs.setInt(6, pageSize);
// 设置出参
cs.registerOutParameter(5, OracleTypes.INTEGER);
cs.registerOutParameter(6, OracleTypes.INTEGER);
cs.registerOutParameter(7, OracleTypes.INTEGER);
cs.registerOutParameter(8, OracleTypes.INTEGER);
cs.registerOutParameter(9, OracleTypes.CURSOR);
return cs;
}
}, new CallableStatementCallback() {
@Override
public Object doInCallableStatement(final CallableStatement cs)
throws SQLException, DataAccessException {
// 执行存储过程
cs.execute();
/* 获取结果 */
// 当前第几页
int curPage = (Integer) cs.getObject(5);
// 每页记录数
int pageSize = (Integer) cs.getObject(6);
// 总记录数
int totalRecords = (Integer) cs.getObject(7);
// 总页数
int totalPages = (Integer) cs.getObject(8);
// 结果集
ResultSet datas = (ResultSet) cs.getObject(9);
page.setCurPage(curPage);
page.setPageSize(pageSize);
page.setTotalRecords(totalRecords);
page.setTotalPages(totalPages);
List<Map<String, Object>> resultsMap = null;
try {
resultsMap = getResultSet(datas);
} catch (Exception e) {
throw new SQLException(e);
}
page.setDatas(resultsMap);
datas.close();
return resultsMap;
}
});
return page;
}
/**
* @Title: getResultSet
* @deprecated: <p>
* 将分页取出的结果集ResultSet对象组装成
* List<--Map<--(columnName:columnValue)
* </p>
* <p>
* 每一个map对应一条记录,map长度 == column数量
* </p>
* @param rs
* @return List
* @throws Exception
* @author
* @date 2013年10月18日
*/
private List<Map<String, Object>> getResultSet(ResultSet rs)
throws Exception {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(10);
try {
ResultSetMetaData rsmd = rs.getMetaData();
Map<String, Object> map = null;
int colCount = 0;
String colName = null;
// 每循环一次遍历出来1条记录,记录对应的所有列值存放在map中(columnName:columnValue)
while (rs.next()) {
map = new HashMap<String, Object>(16);
colCount = rsmd.getColumnCount();
for (int i = 0; i < colCount; i++) {
colName = rsmd.getColumnName(i + 1);
map.put(colName, rs.getObject(i + 1));
}
list.add(map);
}
} catch (Exception e) {
log.error("Get ResultSet fail:".concat(e.getMessage()));
throw new Exception(e);
}
return list;
}
4. 分页信息
import java.util.List;
import java.util.Map;
public class PageResult {
// 当前第几页
private int curPage;
// 每页记录数
private int pageSize;
// 总记录数
private int totalRecords;
// 总页数
private int totalPages;
// 结果集
private List<Map<String, Object>> datas;
// 省略 getter/setter
}